美国留学

首先在SQL SERVER数据库新建一个存储过程，名称为：sup_入库提交，代码如下：

CREATE PROCEDURE usp_入库操作

@部品番号 NVARCHAR(30),

@入库数量 decimal(18,0),

@库位号 NVARCHAR(20)

AS

BEGIN

SET NOCOUNT ON;

IF @入库数量<=0

BEGIN

RAISERROR('入库数量必须大于0',16,1);

RETURN;

END

BEGIN TRY

BEGIN TRANSACTION;

IF EXISTS(SELECT 1 FROM 结存 WHERE 部品番号=@部品番号 AND 库位号=@库位号)

BEGIN

UPDATE 结存 SET 结存数量=结存数量+@入库数量 WHERE 部品番号=@部品番号 AND 库位号=@库位号;

END

ELSE

BEGIN

INSERT INTO 结存(部品番号,结存数量,库位号,预用数量) VALUES(@部品番号,@入库数量,@库位号,0);

END

INSERT INTO 历史入库(部品番号,入库数量,库位号,入库日期) VALUES(@部品番号,@入库数量,@库位号,GETDATE());

COMMIT TRANSACTION

SELECT 0 AS 结果代码,'成功' AS 结果消息

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

SELECT -1 AS 结果代码,ERROR_MESSAGE() AS 结果消息

END CATCH

END

然后在窗体界面的提交按钮写如下代码：

Imports System.Data.SqlClient

Public Class Form1

Private connString As String = "Server=CW.shscth.com.cn;Database=SCTH;Integrated Security=True;"

Private da As SqlDataAdapter

Private dt As DataTable

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

InitializeDataGrisView()

End Sub

Private Sub InitializeDataGrisView()

DataGridView1.Columns.Clear()

DataGridView1.Columns.Add("部品番号", "部品番号")

DataGridView1.Columns.Add("数量", "数量")

DataGridView1.Columns.Add("库位号", "库位号")

DataGridView1.Columns("部品番号").Width = 150

DataGridView1.Columns("数量").Width = 100

DataGridView1.Columns("库位号").Width = 120

DataGridView1.Columns("数量").ValueType = GetType(Decimal)

End Sub

Private Sub Button提交入库_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button提交入库.Click

If Not ValidateData() Then

Return

End If

BulkUpdateDatabase()

End Sub

Private Function BulkUpdateDatabase() As Boolean

Dim conn As SqlConnection = Nothing

Dim transaction As SqlTransaction = Nothing

Try

conn = New SqlConnection(connString)

conn.Open()

transaction = conn.BeginTransaction()

'执行批量更新

Using cmd As New SqlCommand("usp_入库操作", conn, transaction)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@部品番号", SqlDbType.NVarChar, 30)

cmd.Parameters.Add("@入库数量", SqlDbType.Decimal)

cmd.Parameters.Add("@库位号", SqlDbType.NVarChar, 20)

For Each row As DataGridViewRow In DataGridView1.Rows

If row.IsNewRow Then Continue For

'设置参数值

cmd.Parameters("@部品番号").Value = row.Cells("部品番号").Value.ToString().Trim()

cmd.Parameters("@入库数量").Value = CDec(row.Cells("数量").Value)

cmd.Parameters("@库位号").Value = row.Cells("库位号").Value.ToString().Trim()

'执行存储过程

cmd.ExecuteNonQuery()

Next

End Using

'提交事务

transaction.Commit()

MessageBox.Show("提交成功！")

DataGridView1.Rows.Clear()

Return True

Catch ex As Exception

If transaction IsNot Nothing Then

Try

transaction.Rollback()

Catch rollbackEx As Exception

'记录回滚错误

End Try

End If

MessageBox.Show("提交失败:" & ex.Message)

Return False

Finally

If conn IsNot Nothing AndAlso conn.State = ConnectionState.Open Then

conn.Close()

End If

End Try

End Function

End Class

版权声明：本文转载于今日头条，版权归作者所有，如果侵权，请联系本站编辑删除