如何使用函數 VB.NET 插入數據庫？ (How to use Function VB.NET Insert To Database?)

Private Sub btSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btSave.Click

    For i As Integer = 0 To DgvItem.Rows.Count ‑ 1
        sendPDTStatus = FInsertClother(CStr(DgvItem.Rows(i).Cells(0).Value), CInt(DgvItem.Rows(i).Cells(1).Value), CInt(DgvItem.Rows(i).Cells(2).Value), _
        DgvItem.Rows(i).Cells(3).Value)
    Next

End Sub

代碼功能

Public Function FInsertClother(ByVal Code As String, ByVal RClother As Integer, ByVal CIDetail As Integer, ByVal PO As String)    
            Dim Tr As SqlTransaction
            Dim sqlCom As New SqlCommand

            Dim sqlInsert As String
            Dim ReturnValue As Integer

            Tr = Conn.BeginTransaction
            sqlCom.Connection = Conn

            sqlInsert = "INSERT INTO Clother "
            sqlInsert &= "(Code,RClother,CIDetail,PO) "
            sqlInsert &= "VALUES(" & Code & "," & RClother & "," & CIDetail & "," & PO & ")"

            sqlCom.Transaction = Tr
            sqlCom.CommandText = sqlInsert
            sqlCom.CommandType = CommandType.Text

            ReturnValue = sqlCom.ExecuteScalar << Line Error
            If ReturnValue = 0 Then
                Tr.Commit()
            Else
                Tr.Rollback()
            End If
     Return ReturnValue    
End Function

我嘗試調試這個結果

Name                 Value
sqlCom.CommandText   "INSERT INTO Clother (Code,RClother,CIDetail,PO) VALUES(050030543003,5022,30543,EXHBK13004/3)"

sqlInsert            "INSERT INTO Clother (Code,RClother,CIDetail,PO) VALUES(050030543003,5022,30543,EXHBK13004/3)"

只有字段“ PO”不要插入數據庫。

謝謝你的時間。:))

參考解法

方法 1:

First of all I would remove the string concatenation and use a parameterized query to avoid parsing problems and Sql Injections (In your code you have passed two strings without using quotes and this will surely fail the insert because string fields require a quote delimiter)

Then I remove also the Transaction because, as it stands now the loop executes and confirms a single command for each row.

Also you seems to have a global connection object and this is a bad practice, you should open the connection and close it as soon as possible without keeping it open for the lifetime of your application.

Public Function FInsertClother(ByVal Code As String, ByVal RClother As Integer, ByVal CIDetail As Integer, ByVal PO As String)    

    Dim sqlInsert As String
    Dim ReturnValue As Integer

    sqlInsert = "INSERT INTO Clother " & _
                "(Code,RClother,CIDetail,PO) " & _
                "VALUES(@code, @clot, @id, @po)"

    Using sqlCom = new SqlCommand(sqlInsert, conn)
        sqlCom.Connection = Conn
        sqlCom.Parameters.AddWithValue("@code",Code)
        sqlCom.Parameters.AddWithValue("@clot", RClother)
        sqlCom.Parameters.AddWithValue("@id",CIDetail)
        sqlCom.Parameters.AddWithValue("@po",PO)
        ReturnValue = sqlCom.ExecuteNonQuery
        Return ReturnValue    
    End Using
End Function

A very useful enhancements would be to open the connection on the button click and pass it to this function. So when you have finished to loop over the rows you could close the connection via a Using Statement

方法 2:

You need to put the string values in quotes.

sqlInsert &= "VALUES('" & Code & "'," & RClother & "," & CIDetail & ",'" & PO & "')"

That said, you should not build a query string using concatenation. This makes your query subject to a SQL Injection attack. Instead, you should use a parametrized query. (as Steve shows in his answer).

(by nettoon493、Steve、jfrankcarr)

參考文件

1. How to use Function VB.NET Insert To Database? (CC BY‑SA 2.5/3.0/4.0)