SQL Update wnt work

levyuk

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
Can anyone tell me why I get an error saying UPDATE syntax incorrect. Here is my code and if anyone can see whats wrong please let me know

VB.NET:
[size=2][color=#0000ff]Private[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] ButtonUpdate_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] ButtonUpdate.Click

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] sqlreturn [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Integer[/color][/size]
[size=2][color=#0000ff]Try[/color][/size]
[size=2]OleDbConnection1.Open()
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] cmd [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbCommand = [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand[/size][size=2]
cmd.Connection = OleDbConnection1
cmd.CommandType = CommandType.Text
cmd.CommandText = "UPDATE Company SET CompanyAddress = " & [/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxCompanyAddress.Text & ", CompanyName = " & [/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxCompanyName.Text & ", ContactEmail = " & [/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxContactEmail.Text & ", ContactName = " & [/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxContactName.Text & ", ContactNumber = " & [/size][size=2][color=#0000ff]CInt[/color][/size][size=2]([/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxContactNumber.Text) & ", JobType = " & [/size][size=2][color=#0000ff]Me[/color][/size][size=2].ComboBoxJobType.SelectedItem & ", Location = " & [/size][size=2][color=#0000ff]Me[/color][/size][size=2].ComboBoxLocation.SelectedItem & " WHERE (CompanyID = " & [/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxCompanyID.Text & ")"[/size]
[size=2]sqlreturn = cmd.ExecuteNonQuery()
[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] ex [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception[/size]
[size=2]MessageBox.Show(ex.ToString)
StatusBar1.Text = "Error: " & ex.ToString
[/size][size=2][color=#0000ff]Finally[/color][/size]
[size=2]OleDbConnection1.Close()
[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Try[/color][/size]
[size=2][color=#0000ff]If[/color][/size][size=2] sqlreturn <> 1 [/size][size=2][color=#0000ff]Then[/color][/size]
[size=2]StatusBar1.Text = "Failed"
[/size][size=2][color=#0000ff]Else[/color][/size]
[size=2]StatusBar1.Text = "Success"
[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If[/color][/size]
[size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size]
[size=2][color=#0000ff]
[/color][/size]
 
You need to enclose the values in single quotes:
VB.NET:
cmd.CommandText = "UPDATE Company SET CompanyAddress='" & Me.TextBoxCompanyAddress.Text & "', CompanyName='"...
 
Right I tried that but now I get a new error message saying

FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
 
Back
Top