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

Recommended Answers

All 11 Replies

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 & "'"
commented: error Solution : myqry += "Quantity= " + txtQuan.Text & "', " -1
commented: Good effort to help improve someone else's vague post +1

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

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

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.

The table structure has nothing to do with this code.

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

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

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

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

i did try sir.. still error :(

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

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

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

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

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.