Question Update Syntax Error?

[Antone.evans]

I'm getting a "Syntax error in UPDATE statement."
Any help would be great!

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim cb As New OleDb.OleDbCommandBuilder(dAdapter)

        dSet.Tables("Db").Rows(Inc).Item("Ticket_Number") = txtTicket.Text
        dSet.Tables("Db").Rows(Inc).Item("Customer") = txtCustomer.Text
        dSet.Tables("Db").Rows(Inc).Item("Phone") = txtPhone.Text
        dSet.Tables("Db").Rows(Inc).Item("In_Date") = dtIn.Value
        dSet.Tables("Db").Rows(Inc).Item("Fixed_Date") = dtReady.Value
        dSet.Tables("Db").Rows(Inc).Item("Paid_Date") = dtPaid.Value
        dSet.Tables("Db").Rows(Inc).Item("Location") = txtLocation.Text
        dSet.Tables("Db").Rows(Inc).Item("Do") = cmbDo.Text
        dSet.Tables("Db").Rows(Inc).Item("Repair_Diff") = cmbDifficulty.Text
        dSet.Tables("Db").Rows(Inc).Item("Rush") = txtRush.Text
        dSet.Tables("Db").Rows(Inc).Item("Go") = cbGo.Checked
        dSet.Tables("Db").Rows(Inc).Item("Make") = txtMake.Text
        dSet.Tables("Db").Rows(Inc).Item("Model") = txtModel.Text
        dSet.Tables("Db").Rows(Inc).Item("Type") = txtType.Text
        dSet.Tables("Db").Rows(Inc).Item("Code") = cmbClass.Text
        dSet.Tables("Db").Rows(Inc).Item("How_paid") = cmbHow.Text
        dSet.Tables("Db").Rows(Inc).Item("Symptom") = txtSymptoms.Text
        dSet.Tables("Db").Rows(Inc).Item("Estimate") = txtEstimate.Text
        dSet.Tables("Db").Rows(Inc).Item("Estimate_Notes") = txtEstNote.Text
        dSet.Tables("Db").Rows(Inc).Item("Received") = txtRecieved.Text
        dSet.Tables("Db").Rows(Inc).Item("Materials") = txtMats.Text
        dSet.Tables("Db").Rows(Inc).Item("Tax") = txtTax.Text
        dSet.Tables("Db").Rows(Inc).Item("Total") = txtTotal.Text
        dSet.Tables("Db").Rows(Inc).Item("Labor") = txtLabor.Text
        dSet.Tables("Db").Rows(Inc).Item("Advances") = txtAdvance.Text
        dSet.Tables("Db").Rows(Inc).Item("Balance") = txtBalance.Text
        dSet.Tables("Db").Rows(Inc).Item("Proof") = txtProof.Text
        dSet.Tables("Db").Rows(Inc).Item("Advance_Type") = txtAdvType.Text
        dSet.Tables("Db").Rows(Inc).Item("Note") = txtNotes.Text
        dSet.Tables("Db").Rows(Inc).Item("Flag") = cbFlag.Checked

        dAdapter.Update(dSet, "Db")

    End Sub

Error is pointing to the "dAdapter.Update(dSet, "Db")" line.

 

[jmcilhinney]

The usual cause of this issue is having a SELECT statement with a * for the column list and at least one column name that is either a reserved word or contains illegal characters, e.g. spaces. In that case, the solutions are:

1. Change the offending column names in the database.
2. Don't use a CommandBuilder and write your action statements yourself, escaping the offending column names.
3. Don't use a wildcard in your SELECT statement and write the column list out in full, escaping the offending column names.

 

[Antone.evans]

I've got a column named Do. I'm assuming that's the culprit. I'll just change it in the Db, but for future reference; what does "escaping" mean?

 

[jmcilhinney]

for future reference; what does "escaping" mean?

In general, it means using one or more extra characters to provide special meaning to one or more original characters. In the case of database identifiers, it generally means wrapping column names that are reserved words or contain special characters in brackets to force them to be interpreted as single identifiers, e.g.

SELECT Name, [Date] FROM MyTable

Date is a reserved word in many databases so you must escape it if you use it as a column name.

 

[Antone.evans]

ah, okay. Well I went in and added an underscore to the front of all of my columns. but now I'm getting this.

Syntax error in query expression '(((? = 1 AND _do IS NULL) OR (_do = ?)) AND ((? = 1 AND Repair_Diff IS NULL) OR (Repair_Diff = ?)) AND ((? = 1 AND _rush IS NULL) OR (_rush = ?)) AND ((? = 1 AND _go IS NULL) OR (_go = ?)) AND ((? = 1 AND _location IS NULL) OR (_location = ?)) AND ((? = 1'.

uhg...

 

[jmcilhinney]

Just use sensible names for your columns. Prefixing every column with an underscore is a bad idea. I'm quite sure that it's not hard to find a list of reserved words for the database you're using, which I'm guessing is Access but may not be.

 

[totalbiz]

Hello,

I am a newbie to VB.NET and I am trying to change someone else's code. I am also having a problem with an update. Here is the code:

' clears commission field in brokers for accumulation
Dim sql As OleDbCommand
Dim da As OleDbDataAdapter = New OleDbDataAdapter
Dim dt As New DataTable
Dim ds As DataSet = New DataSet
Dim a As Integer
Dim test As Integer
Dim sql2 As OleDbCommand

sql = New OleDbCommand("SELECT id,commission FROM brokers", CalcForex.ForexDB)
da.SelectCommand = sql
da.Fill(ds, "Brokers")
dt = ds.Tables(0)

If dt.Rows.Count = 0 Then
MsgBox("Brokers table is empty?")
Exit Sub
End If

For a = 0 To dt.Rows.Count - 1
dt.Rows(a)("commission") = 0
Next a
test = da.Update(dt)

sql.Dispose()
da.Dispose()
ds.Dispose()
dt.Dispose()

When this runs it get an error message pointing to the "update" statement which says:

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

Any help would be appreciated. Thank you.

 

[jmcilhinney]

A DataAdapter encapsulates four Commands: SelectCommand, DeleteCommand, InsertCommand and UpdateCommand. The names should pretty much tell you what they do.

When you call Fill, it executes the SelectCommand to retrieve data and populate a DataTable. When you call Update, it executes the DeleteCommand, InsertCommand and UpdateCommand as needed to save all the changes in the DataTable back to the database. If you have modified rows and no UpdateCommand then the changes can't be saved and your call to Update fails. You need to either use a CommandBuilder to generate the Commands for you, or else create them yourself.