[02/03] MS Access Update Statement

I posted this question in the Database section, but that forum is like a ghost town.

This Update statement stopped working after I added the second and third parameters from the bottom. I get an error message that says there is something wrong with my Update syntax. I checked the db table and everything is ok on that end. Any ideas? I wish Access used T-SQL.

Code:

---

' CREATE COMMAND AND SET PARAMETERS
        invCmd = New OleDbCommand("UPDATE invMain SET Description2=@desc2, Type=@type, " & _
            "Inventory=@inv, Trigger=@trig, LeadTime=@lead, Size=@size, Cartridge=@cart " & _
            "WHERE (Description1=@part)", invCon)
        invCmd.Parameters.Add("@desc2", OleDb.OleDbType.Char)
        invCmd.Parameters.Add("@type", OleDb.OleDbType.Char)
        invCmd.Parameters.Add("@inv", OleDb.OleDbType.Char)
        invCmd.Parameters.Add("@trig", OleDb.OleDbType.Char)
        invCmd.Parameters.Add("@lead", OleDb.OleDbType.Char)
        invCmd.Parameters.Add("@size", OleDb.OleDbType.Char)
        invCmd.Parameters.Add("@cart", OleDb.OleDbType.Char)
        invCmd.Parameters.Add("@part", OleDb.OleDbType.Char)
        invCmd.Parameters("@desc2").Value = "d2" & Me.uiDescriptionTextBox.Text
        invCmd.Parameters("@type").Value = "ty" & Me.uiTypeTextBox.Text
        invCmd.Parameters("@inv").Value = Me.uiOnHandTextBox.Text
        invCmd.Parameters("@trig").Value = Me.uiTriggerTextBox.Text
        invCmd.Parameters("@lead").Value = Me.uiLeadTextBox.Text
        invCmd.Parameters("@size").Value = Me.uiSizeTextBox.Text
        invCmd.Parameters("@cart").Value = Me.uiCartridgeComboBox.Text
        invCmd.Parameters("@part").Value = "d1" & Me.uiPartNumberLabel.Text

---

it looks like its right after your where clause
See Below:

"UPDATE invMain SET Description2=@desc2, Type=@type, Inventory=@inv, " &_
"Trigger=@trig, LeadTime=@lead, Size=@size, Cartridge=@cart " & _
"WHERE Description1=@part",

I think that should be right

I tried removing the parenthesis in the WHERE clause like you did, but still got the same error.

after taking a close look i don't know if i wouldn't just declare variables and set them equal to the textboxes

VB Code:

dim @description as string = me.txtdescription.text
 
dim sql as string = "update table set description = @description where id = @id"

i think that should work

I tried that too, except when you use variables you have to concatenate them into your string. In your example, @description would throw an exception because it is not identified as a parameter or a value.

When you get a syntax error in what looks like a good SQL statement it is usually because one of your column names is a reserved word. There are several likely candidates in there including Trigger, Size and Type. These are just the sort of words that are likely to have special meaning in a programming language, especially Trigger in SQL. Try putting square brackets around the suspicious column names.

Also, you're writing considerably more code than is necessary. This:

VB Code:

invCmd.Parameters.Add("@desc2", OleDb.OleDbType.Char)
invCmd.Parameters("@desc2").Value = "d2" & Me.uiDescriptionTextBox.Text

can be reduced to this:

VB Code:

invCmd.Parameters.Add("@desc2", OleDb.OleDbType.Char).Value = "d2" & Me.uiDescriptionTextBox.Text

because Add returns a reference to the parameter just added. Having said that, there's no need to specify the parameter type because it will implicitly take on the appropriate type for the value you specify. As you're specifying a string for each Value and each parameter is a text type there's no need to specify a type. Your code then reduces further to this:

VB Code:

invCmd.Parameters.Add("@desc2", "d2" & Me.uiDescriptionTextBox.Text)

Thanks JM. I'll give this a shot when I get back in the office. I appreciate the optimized code example.