UPDATE STATEMENT ERROR.vb.net

Question

timothy0726 0 Newbie Poster

12 Years Ago

myqry = "UPDATE tblReceive SET "
myqry = myqry + " ProductName = '" & txtProd.Text & "',"
myqry = myqry + "' Quantity= " & txtQuan.Text & "',"
myqry = myqry + " Quantity Unit= '" & ComboBox3.SelectedItem & "',"
myqry = myqry + " Category = '" & ComboBox2.SelectedItem & "',"
myqry = myqry + " Supplier = '" & ComboBox1.SelectedItem & "',"
myqry = myqry + " ReceivedDate = '" & txtDate.Text & "',"
myqry = myqry + " ReceivedBy = '" & ComboBox4.SelectedItem & "'"
myqry = myqry + " WHERE "
myqry = myqry + "' ProductID = " & txtID.Text

mycmd = New OleDbCommand(myqry, con) <<<< error here.. syntax error.Update statement error
mycmd.ExecuteNonQuery()

pls help..
quantity unit, Category,Supplier and Received by.. are all Combo boxes. by the way im using listview control where im putting those items...pls help.. im realy newbie here.. the first time i tried this code.,its ok because im using all textboxes but when im using combo boxes..its not ok..plss help....pls

vb.net

3 Contributors
11 Replies
296 Views
23 Hours Discussion Span
Latest Post 12 Years Ago Latest Post by kingsonprisonic

Mitja Bonca 557 Nearly a Posting Maven

12 Years Ago

You had some error, this should work now:

Dim myqry As String = "UPDATE tblReceive SET "
myqry += "ProductName = '" + txtProd.Text & "', "
myqry += "Quantity= " + txtQuan.Text & "', "
myqry += "Quantity Unit= '" + ComboBox3.SelectedItem & "', "
myqry += "Category = '" + ComboBox2.SelectedItem & "', "
myqry += "Supplier = '" + ComboBox1.SelectedItem & "', "
myqry += "ReceivedDate = '" + txtDate.Text & "', "
myqry += "ReceivedBy = '" + ComboBox4.SelectedItem & "' "
myqry += "WHERE "
myqry += "ProductID = '" + txtID.Text & "'"

kingsonprisonic commented: error Solution : myqry += "Quantity= " + txtQuan.Text & "', " -1

ja928 commented: Good effort to help improve someone else's vague post +1

kingsonprisonic 42 Posting Whiz in Training

12 Years Ago

Please provide the table structure.. Like this....

Field Name   |  Data Type
___________________________
Field1       | Varchar(50)

Mitja Bonca 557 Nearly a Posting Maven

12 Years Ago

And you had t vote -1? Thx, I really appreciate it. I missed one sinlge quotation mark.
It should be:

Dim myqry As String = "UPDATE tblReceive SET "
myqry += "ProductName = '" + txtProd.Text & "', "
myqry += "Quantity= '" + txtQuan.Text & "', "
myqry += "Quantity Unit= '" + ComboBox3.SelectedItem & "', "
myqry += "Category = '" + ComboBox2.SelectedItem & "', "
myqry += "Supplier = '" + ComboBox1.SelectedItem & "', "
myqry += "ReceivedDate = '" + txtDate.Text & "', "
myqry += "ReceivedBy = '" + ComboBox4.SelectedItem & "' "
myqry += "WHERE "
myqry += "ProductID = '" + txtID.Text & "'"

Happy now?

ANd thx ones again for voting -1.

BTW: what is your point in showing this table? The table structure has nothing to do with this code. I could vote -1 for you too, but Iam not such an ass. Iam trying to help here for a difference from some of you here.

Edited 12 Years Ago by Mitja Bonca because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

error Solution : myqry += "Quantity= " + txtQuan.Text & "', "

kingsonprisonic 42 Posting Whiz in Training · Answer 1 · 2012-02-26T05:04:34+00:00

The table structure has nothing to do with this code.

Ok then tell me what is the data type for Quantity field?????

timothy0726 0 Newbie Poster · Answer 2 · 2012-02-26T17:08:52+00:00

the data type for quantity is number, and the rest is text in my database..
sir. this don't have an error at all..
No errors at all because im using textboxes. But when i used comboboxes it show me that error.. quantity unit, Category,Supplier and Received by are all text boxes once but i change them to combo boxes.. and now.this is the error..
Using textboxes to put data in listview no errors, using combo boxes to put data in listview, has errors. pls help..i really dont know the answer for this.. i tried changeing ComboBox1.SelectedItem to ComboBox1.SelectedValue. I also tried
ComboBox1.SelectedItem.ToString(). but still error.. pls help

timothy0726 0 Newbie Poster · Answer 3 · 2012-02-26T17:29:51+00:00

here is the whole code for saving items. adding items using combo boxes is ok.. editing is ok but saving it after editing..it has an error..

Private Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
        open_connection()
        If str = "add" Then
            ''''''ADD NEW RECORD'''''''
            If txtProd.Text = "" Or txtQuan.Text = "" Or txtDate.Text = "" Then
                MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                myqry = "INSERT INTO tblReceive(ProductName,Quantity,QuantityUnit,Category,Supplier,ReceivedDate,ReceivedBy)"
                myqry = myqry + "VALUES('" & txtProd.Text & "','" & txtQuan.Text & "','" & ComboBox3.SelectedItem & "','" & ComboBox2.SelectedItem & "','" & ComboBox1.SelectedItem & "','" & txtDate.Text & "','" & ComboBox4.SelectedItem & "')"
                mycmd = New OleDbCommand
                With mycmd
                    .CommandText = myqry
                    .Connection = con
                    .ExecuteNonQuery()
                End With
                Call Set1()
            End If


        Else
            ''''''''''UPDATE RECORD'''''''
            If txtProd.Text = "" Or txtQuan.Text = "" Or txtDate.Text = "" Then
                MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                myqry = "UPDATE tblReceive SET "
                myqry = myqry + " ProductName = '" & txtProd.Text & "',"
                myqry = myqry + " Quantity= '" & txtQuan.Text & "',"
                myqry = myqry + " Quantity Unit= '" & ComboBox3.SelectedItem.ToString() & "',"
                myqry = myqry + " Category = '" & ComboBox2.SelectedItem.ToString() & "',"
                myqry = myqry + " Supplier = '" & ComboBox1.SelectedItem.ToString() & "',"
                myqry = myqry + " ReceivedDate = '" & txtDate.Text & "',"
                myqry = myqry + " ReceivedBy = '" & ComboBox4.SelectedItem.ToString() & "' "
                myqry = myqry + " WHERE "
                myqry = myqry + " ProductID = '" & txtID.Text

                mycmd = New OleDbCommand(myqry, con)
                MessageBox.Show(myqry)
                mycmd.ExecuteNonQuery()
                Call Set1()
            End If
        End If

        Call FillListview()
        Call ClearAlltextBox()

    End Su

and here is the code for

FillListview()

Sub FillListview()
        LV.Items.Clear()
        myqry = "SELECT * from tblReceive ORDER BY ProductID ASC"
        mycmd = New OleDbCommand(myqry, con)
        mydr = mycmd.ExecuteReader

        While mydr.Read
            With LV
                .Items.Add(mydr("ProductID"))
                With .Items(.Items.Count - 1).SubItems
                    .Add(mydr("ProductName"))
                    .Add(mydr("Quantity"))
                    .Add(mydr("QuantityUnit"))
                    .Add(mydr("Category"))
                    .Add(mydr("Supplier"))
                    .Add(mydr("ReceivedBy"))
                    .Add(mydr("ReceivedDate"))
                End With
            End With
        End While
        close_connection()
    End Sub

and here is the code for

ClearAlltextbox()

Sub ClearAlltextBox()
        Dim a As Control
        For Each a In Me.Controls
            If TypeOf a Is TextBox Then
                a.Text = Nothing
            End If
        Next
    End Sub

more powers guys

kingsonprisonic 42 Posting Whiz in Training · Answer 4 · 2012-02-26T17:49:44+00:00

@mitja bonka....

Now see before knowing the data types how u use quote in

myqry += "Quantity= '" + txtQuan.Text & "', "

this will give you error..

So first understand the post then help OP..

And @timothy0726

Use

myqry += "Quantity= " + txtQuan.Text & ", "

Do not use quote as quantity is a numeric value..

timothy0726 0 Newbie Poster · Answer 5 · 2012-02-26T17:57:01+00:00

timothy0726 0 Newbie Poster

12 Years Ago

i did try sir.. still error :(

kingsonprisonic 42 Posting Whiz in Training · Answer 6 · 2012-02-26T18:03:27+00:00

U can use ComboBox2.Text.Trim instead of ComboBox2.SelectedItem

By the way please post your current code once more....

timothy0726 0 Newbie Poster · Answer 7 · 2012-02-26T18:18:47+00:00

Sub FillListview()
        LV.Items.Clear()
        myqry = "SELECT * from tblReceive ORDER BY ProductID ASC"
        mycmd = New OleDbCommand(myqry, con)
        mydr = mycmd.ExecuteReader

        While mydr.Read
            With LV
                .Items.Add(mydr("ProductID"))
                With .Items(.Items.Count - 1).SubItems
                    .Add(mydr("ProductName"))
                    .Add(mydr("Quantity"))
                    .Add(mydr("QuantityUnit"))
                    .Add(mydr("Category"))
                    .Add(mydr("Supplier"))
                    .Add(mydr("ReceivedBy"))
                    .Add(mydr("ReceivedDate"))
                End With
            End With
        End While
        close_connection()
    End Sub



Sub ClearAlltextBox()
        Dim a As Control
        For Each a In Me.Controls
            If TypeOf a Is TextBox Then
                a.Text = Nothing
            End If
        Next
    End Sub




Private Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
        open_connection()
        If str = "add" Then
            ''''''ADD NEW RECORD'''''''
            If txtProd.Text = "" Or txtQuan.Text = "" Or txtDate.Text = "" Then
                MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                myqry = "INSERT INTO tblReceive(ProductName,Quantity,QuantityUnit,Category,Supplier,ReceivedDate,ReceivedBy)"
                myqry = myqry + "VALUES('" & txtProd.Text & "','" & txtQuan.Text & "','" & ComboBox3.SelectedItem & "','" & ComboBox2.SelectedItem & "','" & ComboBox1.SelectedItem & "','" & txtDate.Text & "','" & ComboBox4.SelectedItem & "')"
                mycmd = New OleDbCommand
                With mycmd
                    .CommandText = myqry
                    .Connection = con
                    .ExecuteNonQuery()
                End With
                Call Set1()
            End If


        Else
            ''''''''''UPDATE RECORD'''''''
            If txtProd.Text = "" Or txtQuan.Text = "" Or txtDate.Text = "" Then
                MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                myqry = "UPDATE tblReceive SET "
                myqry = myqry + " ProductName = '" & txtProd.Text & "',"
                myqry = myqry + " Quantity= " & txtQuan.Text & ","
                myqry = myqry + " Quantity Unit= '" & ComboBox3.SelectedItem.ToString() & "',"
                myqry = myqry + " Category = '" & ComboBox2.SelectedItem.ToString() & "',"
                myqry = myqry + " Supplier = '" & ComboBox1.SelectedItem.ToString() & "',"
                myqry = myqry + " ReceivedDate = '" & txtDate.Text & "',"
                myqry = myqry + " ReceivedBy = '" & ComboBox4.SelectedItem.ToString() & "'"
                myqry = myqry + " WHERE "
                myqry = myqry + " ProductID = " & txtID.Text & ""

                mycmd = New OleDbCommand(myqry, con)
                MessageBox.Show(myqry)
                mycmd.ExecuteNonQuery()
                Call Set1()
            End If
        End If

        Call FillListview()
        Call ClearAlltextBox()

    End Sub

this is the code..adding is ok.....editing is ok..but saving after editing is error..

kingsonprisonic 42 Posting Whiz in Training · Answer 8 · 2012-02-26T18:57:45+00:00

I dont agree with you

You said quantity is a numeric so how this code get executed????

myqry = "INSERT INTO tblReceive(ProductName,Quantity,QuantityUnit,Category,Supplier,ReceivedDate,ReceivedBy)"
                myqry = myqry + "VALUES('" & txtProd.Text & "','" & txtQuan.Text & "','" & ComboBox3.SelectedItem & "','" & ComboBox2.SelectedItem & "','" & ComboBox1.SelectedItem & "','" & txtDate.Text & "','" & ComboBox4.SelectedItem & "')"

Here you use

'" & txtQuan.Text & "'

I think this will give you an error.

If i am not clear then please debug your project and give the specific line where you r getting this error...