CODEWITHSUNDEEP
.netsqlsql-servervb.net
user2008654
user2008654

Reputation: 151

inserting data into SQL Server from vb.net

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

Upvotes: 0

Views: 1430

Answers (3)

Steve
Steve

Reputation: 216243

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

Upvotes: 0

marc_s
marc_s

Reputation: 754200

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

Upvotes: 2

Shai Aharoni
Shai Aharoni

Reputation: 1957

You should use: 

values ('Iden',2 ,5 )

instead.

Upvotes: 0

Related Questions