Hi again my problem is i always make mistake in writing statement
for update, here's the code

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
        Try
            Dim com As New OleDbCommand
            com.Connection = con
            com.CommandText = "update Watches set WatchBrand='" & txtWatchBrand.Text & "',WatchModel='" & txtWatchModel.Text & _
                                    "',Manufacturer='" & txtManufacturer.Text & "',CountryOfOrigin='" & txtCountryOfOrigin.Text & "',Type='" & _
                                    cmbType.Text & "',DescriptionOfWatch='" & txtDescription.Text & _
                                    ",Price=" & txtPrice.Text & ",PriceInclVat=" & txtPriceInclVat.Text & " Where WatchID='" & cmbWatchId.Text
            com.ExecuteNonQuery()
            MsgBox("Record Updated")
            Call populate()
        Catch
            MsgBox("Error")
        End Try
    End Sub

For update i got syntax error in update statement


For my Delete statement, here's the code

Try
            Dim com As New OleDbCommand
            com.Connection = con
            com.CommandText = "delete from Watches where WatchID='" & cmbWatchId.Text
            com.ExecuteNonQuery()
            MsgBox("Record Deleted")
            Call populate()
            Call clear()
        Catch exp As Exception
            MsgBox(exp.ToString())
        End Try

i attached the program, so please help as quick as possible
Please help.

You're at least missing the ending '-character at both SQL clauses.

Your code

" Where WatchID='" & cmbWatchId.Text

should be

" Where WatchID='" & cmbWatchId.Text & "'"

assuming WatchID is textual value. Otherwise remove '-character from WatchID='"

In your update clause

"',DescriptionOfWatch='" & txtDescription.Text & ",Price="

you're also missing one '-character. It should be

"',DescriptionOfWatch='" & txtDescription.Text & "',Price="

For delete its working but Update, syntax error in satatement
please help

Dump this SQL statement to immediate window

Dim a As String
    a = "update Watches set WatchBrand='" & txtWatchBrand.Text & "',WatchModel='" & txtWatchModel.Text & _
                            "',Manufacturer='" & txtManufacturer.Text & "',CountryOfOrigin='" & txtCountryOfOrigin.Text & "',Type='" & _
                            cmbType.Text & "',DescriptionOfWatch='" & txtDescription.Text & _
                            "',Price=" & txtPrice.Text & "',PriceInclVat=" & txtPriceInclVat.Text & " Where WatchID='" & cmbWatchId.Text & "'"
    Debug.Write(a)

and post the result.

You can also check yourself that none of the text variables contains '-character (apostrophe). In that case you have to add an extra '-char, like txtDescription.Text.Replace("'", "''").

BTW, do you use comma or dot as decimal separator?

When i use this code

Dim a As String
    a = "update Watches set WatchBrand='" & txtWatchBrand.Text & "',WatchModel='" & txtWatchModel.Text & _
                            "',Manufacturer='" & txtManufacturer.Text & "',CountryOfOrigin='" & txtCountryOfOrigin.Text & "',Type='" & _
                            cmbType.Text & "',DescriptionOfWatch='" & txtDescription.Text & _
                            "',Price=" & txtPrice.Text & "',PriceInclVat=" & txtPriceInclVat.Text & " Where WatchID='" & cmbWatchId.Text & "'"
    Debug.Write(a)

nothing happen

i attached my program below , please help

Then dump it to immediate window:
- move cursor to line Debug.Write(a) - press F9 to toggle breakpoint on to that line
- run the app until it stops at that breakpoint
- open immediate window (press Ctrl+G if it isn't open)
- type to immediate window: ? a
- and press Enter

Now you should see your update statement. Check apostrophes and commas like I told in my previous post. If you can't spot any errors, copy update statement from the immediate window and post it here.

i cant figure anything out
Here's the code:

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
        Try
            Dim com As New OleDbCommand
            com.Connection = con
            com.CommandText = "update Watches set WatchBrand='" & txtWatchBrand.Text & "',WatchModel='" & txtWatchModel.Text & _
                                    "',Manufacturer='" & txtManufacturer.Text & "',CountryOfOrigin='" & txtCountryOfOrigin.Text & "',Type='" & _
                                    cmbType.Text & "',DescriptionOfWatch='" & txtDescription.Text & _
                                    "',Price=" & txtPrice.Text & ",PriceInclVat=" & txtPriceInclVat.Text & " Where WatchID='" & cmbWatchId.Text
            com.ExecuteNonQuery()
            MsgBox("Record Updated")
            Call populate()
        Catch
            MsgBox("Error")
        End Try
    End Sub

Please help, try to use the attachment (New Bourgeon Watch.zip) and post a helpful respond.
Please help me, its very important

post a helpful respond.

I already did :D I posted

You can also check yourself that none of the text variables contains '-character (apostrophe). In that case you have to add an extra '-char, like txtDescription.Text.Replace("'", "''").

You did have apostrophes in description field. Change ..."',DescriptionOfWatch='" & txtDescription.Text &... to ..."',DescriptionOfWatch='" & txtDescription.Text.Replace("'", "''") &... . If you have apostrophes in some other field too, use the same replace method.

HTH

Thanks i got it, everything is working
Here's the code:

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
        Try
            Dim com As New OleDbCommand
            com.Connection = con
            com.CommandText = "Update Watches set WatchBrand='" & txtWatchBrand.Text & "',WatchModel='" & txtWatchModel.Text & _
            "',Manufacturer='" & txtManufacturer.Text & "',CountryOfOrigin='" & txtCountryOfOrigin.Text & "',Type='" & cmbType.Text & "',DescriptionOfWatch='" & txtDescription.Text & _
            "',Price='" & txtPrice.Text & "',PriceInclVat='" & txtPriceInclVat.Text & "' Where WatchID='" & cmbWatchId.Text & "'"
            com.ExecuteNonQuery()
            MsgBox("Record Updated")
            Call populate()
        Catch
            MsgBox("Error")
        End Try
    End Sub

thanks

Hi! Nice to hear that you got answer to your problem. Could you please mark the thread as solved. Thank you!

This article has been dead for over six months. Start a new discussion instead.