How to use Function VB.NET Insert To Database?

Question

i using function with vb.net. i'm query sql to datagridview and insert data from datagridview to Databse By function.

But Error in function: The name 'EXHBK13004' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

i want using function with insert to database.

Table Clother

Name     Type
No (PK)  int
Code     nvarchar(12)
RClother int
CIDetail int
PO       nvarchar(50)

Code (Button Save)

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

Code Function

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

I try Debug this result

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)"

Only field "PO" don't insert to database.

Thanks you for your time. :))

Steve · Accepted Answer · 2013-07-19 11:01:30Z

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

jfrankcarr · Accepted Answer · 2013-07-19 10:55:14Z

1

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).

answered Jul 19, 2013 at 10:55

jfrankcarr

4813 silver badges6 bronze badges

Collectives™ on Stack Overflow

How to use Function VB.NET Insert To Database?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related