am trying to add and update the database the add part of my code is working fine but the update gives me an error saying invalid column name

Question

Private Sub Btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnadd.Click
    Dim cmd As New SqlCommand
    If Not cnn.State = ConnectionState.Open Then
        'open connection if it is not yet open
        cnn.Open()
    End If

    cmd.Connection = cnn
    'check whether add new or update
    If Me.Txtproductname.Tag & "" = "" Then
        'add new 
        'add data to table
        cmd.CommandText = "INSERT INTO stock(product_name, product_id, unit,price, item_type, date_in) " & _
                        " VALUES('" & Me.Txtproductname.Text & "','" & Me.Txtproductid.Text & "','" & _
                        Me.txtunit.Text & "','" & Me.txtprice.Text & "','" & _
                        Me.Txtitem_type.Text & "','" & Me.txtdate_in.Text & "')"
        cmd.ExecuteNonQuery()
    Else
        'update data in table
        cmd.CommandText = "UPDATE stock " & _
                    " SET product_name=" & Me.Txtproductname.Text & _
                    ", product_id='" & Me.Txtproductid.Text & "'" & _
                    ", unit='" & Me.txtunit.Text & "'" & _
                    ", price='" & Me.txtprice.Text & "'" & _
                    ", item_type='" & Me.Txtitem_type.Text & "'" & _
                    ", date_in='" & Me.txtdate_in.Text & "'" & _
                    " WHERE product_name=" & Me.Txtproductname.Tag
        cmd.ExecuteNonQuery()
    End If
    'refresh data in list
    RefreshData()
    'clear form
    Me.Btnclear.PerformClick()

    'close connection
    cnn.Close()
End Sub

score 2 · Answer 1 · edited May 23 '17 at 12:04

Probably the error is in this part of your query

 SET product_name=" & Me.Txtproductname.Text &

it is not enclosed in single quote.

But let me say that this way to insert or update is really wrong.

First, if, any of your text fields contains a single quote you get a syntax error
Second, you expose your code to any malicious text your user write See Sql Injection

The correct way to handle an update or an insert is through a parametrized query.
This, for example, could be used for the update

cmd.CommandText = "UPDATE stock " & _
                  " SET product_name=@prodName " & _
                  ", product_id=@prodID" & _
                  ", unit=@unit" & _
                  ", price=@price" & _
                  ", item_type=@itemType" & _
                  ", date_in=@date" & _
                  " WHERE product_name=@prodTag"
cmd.Parameters.AddWithValue("@prodName", Me.Txtproductname.Text)
cmd.Parameters.AddWithValue("@prodID", Me.Txtproductid.Text)
cmd.Parameters.AddWithValue("@unit", Me.txtunit.Text)
cmd.Parameters.AddWithValue("@price", Me.txtprice.Text)
cmd.Parameters.AddWithValue("@itemType", Me.Txtitem_type.Text)
cmd.Parameters.AddWithValue("@date", Me.txtdate_in.Text)
cmd.Parameters.AddWithValue("@prodTag", Me.Txtproductname.Tag)
cmd.ExecuteNonQuery()

In this way you let the framework code handle the correct parsing of your textboxes and prevent any possibility to pass unexpected commands to your database.

Apart from this I wish to inquiry if your database fields are really all of string type.
Some fields seems to be of different kind like date_in and price.
In case these fields are not of text type then you should add a conversion

cmd.Parameters.AddWithValue("@date", Convert.ToDateTime(Me.txtdate_in.Text))

I'm not OP but i'm just curious. How could the Insert/Update be changed to make it Injection proof ? — phadaphunk, Apr 15 '13 at 20:25
@PhaDaPhunk: Simple, use [`sql-parameters`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.100).aspx). — Tim Schmelter, Apr 15 '13 at 20:28
these is the full code on my form its working. i just want you to have a look at it and help me get ride of any SQL Injection attack as this is a new concept to me : — macphill, Apr 15 '13 at 21:49
I can't see your 'full code', but in the code of your question the same problem of the update query is present also in the insert statement. Just check every text that represent an sql command (SELECT, INSERT, UPDATE, DELETE are the most common). If you build this text concatenating values that your user types in you are in trouble. — Steve, Apr 15 '13 at 22:00

score 0 · Answer 2 · answered Apr 15 '13 at 20:24

0

You forgot the single quote ' after the product_name in your update.

answered Apr 15 '13 at 20:24

phadaphunk

12,785
15
73
107

score 0 · Answer 3 · answered Apr 15 '13 at 20:27

0

Might sound stupid, but did it not specify the column name that was invalid?

Do you need single tics around the value of the product_name, in both the SET and Where clauses?

answered Apr 15 '13 at 20:27

local idiot

97
1
10

am trying to add and update the database the add part of my code is working fine but the update gives me an error saying invalid column name

3 Answers3