Error in writing statement for UPDATE and DELETE

[manutd4life]

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.

[Teme64]

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="

[manutd4life]

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

[Teme64]

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?

[manutd4life]

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

[Teme64]

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.

[manutd4life]

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

[Teme64]

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

[manutd4life]

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

[Teme64]

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