inserting data into SQL Server from vb.net

Question

I am facing problem trying to insert data into a SQL Server database.

This is the function

 Public Sub Processsales()
 Dim cust_guid As Guid = Session("guid")
 Dim Iden As Guid = System.Guid.NewGuid
 Dim ssql As String
 ssql = "Insert into WebSite.wTranH ([WebTranHGUID],[TranType],[LOCTN]) values ([Iden],[2],[5])"
 Using connection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("SqlConnectionString"))
 Dim command As New SqlCommand(ssql, connection)
 connection.Open()
 command.ExecuteNonQuery()
 End Using
 End Sub

but its giving these errors

Invalid column name 'Iden'.

Invalid column name '2'.

Invalid column name '5'.

Any solutions?

Thanks

• 4b0: remove [] from values. ssql = "Insert into WebSite.wTranH ([WebTranHGUID],[TranType],[LOCTN]) values (Iden,2,5)"

Answer 1

The best approach would be to use a parametrized query to avoid SQL injection attacks:

Public Sub Processsales()
 Dim cust_guid As Guid = Session("guid")
 Dim Iden As Guid = System.Guid.NewGuid()
 ' define your SQL query and use parameters for the values to be inserted 
 Dim sqlQuery As String = "INSERT INTO WebSite.wTranH([WebTranHGUID], [TranType], [LOCTN]) VALUES (@HGuid, @TranType, @LocTn)"
 Dim connString As String = ConfigurationSettings.AppSettings("SqlConnectionString")
 Using connection As New SqlConnection(connString)
 Using command As New SqlCommand(sqlQuery, connection)
 connection.Open()
 ' add paramters and their values to the SqlCommand instance
 command.Parameters.AddWithValue("@HGuid", Iden)
 command.Parameters.AddWithValue("@TranType", 2)
 command.Parameters.AddWithValue("@LocTn", 5)
 command.ExecuteNonQuery()
 connection.Close()
 End Using
 End Using
End Sub

• marc_s: @user2008654: WHERE (on what line) do you get this error? You do have a variable Iden declared - right??

Answer 2

You should use:

values ('Iden',2 ,5 ) 

instead.

• TomTom: -1. See, 'Iden' is obviously a variable (declared 2 lines before the string) and you try to insert the name there.

Answer 3

You have two errors in your sql string.
You pass fixed values for TranType and LOCTN columns, but the WebTranHGUID column should get the value of the structure Iden not its name. Of course the values should be passed without brackets to not confuse with column names.
You should change your code to concatenate the value of Iden to the sql command in this way:

Public Sub Processsales()
 Dim cust_guid As Guid = Session("guid")
 Dim Iden As Guid = System.Guid.NewGuid
 Dim ssql As String
 ssql = "Insert into WebSite.wTranH ([WebTranHGUID],[TranType],[LOCTN]) " + 
 "values (" + Iden.ToString + ",2,5)"
 Using connection As New SqlConnection(....))
 Dim command As New SqlCommand(ssql, connection)
 connection.Open()
 command.ExecuteNonQuery()
 End Using
End Sub