Syntax error in UPDATE statement.. Help Me Please!!

General Web Coding Microsoft Development
1

Hi,

The following code generates an error and I am not sure why. The error says syntax error in UPDATE statement. Anyone have any ideas? Your help will greatly appreciated.

Dim CONNECTION_STRING As String = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\87-08\shareddocs\User.mdb”

Dim access As New OleDbConnection
access = New OleDbConnection(CONNECTION_STRING)

Dim UpdateCommand As OleDbCommand = New OleDbCommand()
UpdateCommand.Connection = access

UpdateCommand.CommandText = “Update Admin Set [AcadYr] = @Ay, [ComName] = @Cn, [Sal1] = @Sa1, [Sal2] = @Sa2, [Firstname1] = @Fn1, [Firstname2] = @Fn2, [Lastname1] = @Ln1, [Lastname2] = @Ln2, [Status1] = @s1, [Status2] = @s2, [Designation1] = @Dg1, [Designation2] = @Dg2, [Add1] = @Add1, [Pcode] = @Pc, [Otel] = @Ot, [Dline1] = @D1, [Dline2] = @D2, [Faxno] = @Fn, [Email1] = @Em1, [Email2] = @Em2, [Supervisor1] = @Sp1, [Supervisor2] = @Sp2, [ProjId] = @Pi, [ProjTitle] = @Pt, [ProjCatDes] = @Pcd, [ProjCatCode] = @Pcc, [TechAreaDes] = @Tad, [Remarks] = @Rem, [Awards] =@Aw, [PDonation] =@PDon, [Year] =@Year, [TPrizes] =@Tz, [Time] =@Tim WHERE Id = @Id”

UpdateCommand.Parameters.AddWithValue(“@Id”, IdComboBox.Text)

UpdateCommand.Parameters.AddWithValue(“@Ay”, AcadYrComboBox.Text)
UpdateCommand.Parameters.AddWithValue(“@Cn”, TextBox32.Text)
UpdateCommand.Parameters.AddWithValue(“@Sa1”, ComboBox26.Text)
UpdateCommand.Parameters.AddWithValue(“@Sa2”, ComboBox25.Text)
UpdateCommand.Parameters.AddWithValue(“@Fn1”, TextBox27.Text)
UpdateCommand.Parameters.AddWithValue(“@Fn2”, TextBox22.Text)
UpdateCommand.Parameters.AddWithValue(“@Ln1”, TextBox29.Text)
UpdateCommand.Parameters.AddWithValue(“@Ln2”, TextBox24.Text)
UpdateCommand.Parameters.AddWithValue(“@S1”, ComboBox31.Text)
UpdateCommand.Parameters.AddWithValue(“@S2”, ComboBox32.Text)
UpdateCommand.Parameters.AddWithValue(“@Dg1”, TextBox28.Text)
UpdateCommand.Parameters.AddWithValue(“@Dg2”, TextBox23.Text)
UpdateCommand.Parameters.AddWithValue(“@Add1”, TextBox33.Text)
UpdateCommand.Parameters.AddWithValue(“@Pc”, TextBox34.Text)
UpdateCommand.Parameters.AddWithValue(“@Ot”, TextBox35.Text)
UpdateCommand.Parameters.AddWithValue(“@D1”, TextBox30.Text)
UpdateCommand.Parameters.AddWithValue(“@D2”, TextBox25.Text)
UpdateCommand.Parameters.AddWithValue(“@Fn”, TextBox36.Text)
UpdateCommand.Parameters.AddWithValue(“@Em1”, TextBox31.Text)
UpdateCommand.Parameters.AddWithValue(“@Em2”, TextBox26.Text)
UpdateCommand.Parameters.AddWithValue(“@Sp1”, ComboBox23.Text)
UpdateCommand.Parameters.AddWithValue(“@Sp2”, ComboBox22.Text)
UpdateCommand.Parameters.AddWithValue(“@Pi”, ComboBox21.Text)
UpdateCommand.Parameters.AddWithValue(“@Pt”, TextBox1.Text)
UpdateCommand.Parameters.AddWithValue(“@Pcd”, ComboBox18.Text)
UpdateCommand.Parameters.AddWithValue(“@Pcc”, ComboBox19.Text)
UpdateCommand.Parameters.AddWithValue(“@Tad”, ComboBox20.Text)
UpdateCommand.Parameters.AddWithValue(“@Rem”, TextBox19.Text)
UpdateCommand.Parameters.AddWithValue(“@Aw”, ComboBox17.Text)
UpdateCommand.Parameters.AddWithValue(“@Id”, IdComboBox.Text)
UpdateCommand.Parameters.AddWithValue(“@PDon”, ComboBox30.Text)
UpdateCommand.Parameters.AddWithValue(“@Year”, ComboBox33.Text)
UpdateCommand.Parameters.AddWithValue(“@Tz”, ComboBox24.Text)
UpdateCommand.Parameters.AddWithValue(“@Tim”, Time.Text)

access.Open()
UpdateCommand.ExecuteNonQuery() [COLOR=“Red”]<== Error Here
access.Close()

2

what error you get?
common mistake is null values on field with not null allowed.

martin

3

syntax error in UPDATE statement which points to the ExecuteNonQuery that part. Nope.. i doesnt have that null is not allowed fields..

4

Little chance of us picking the error without seeing the data. Remember also, that in Access, there is a field property called Allow Zero Length - if this is set to False, then assigning an empty string (“”) will cause an error.

If you’re still stumped, the best approach is often to go back to basics. In this case, REM out all your “AddWithValue” lines - create a new set which supplies literal strings/values that you know will work when it adds the record.

Once you have it running like this without error, start replacing each copied line with its original, running it each time until you get the error - then you’ll know which Textbox value is causing you grief.

5

i’ll give it a try.. thanks.. so, nothing is wrong with my sql statement??

6

Well not that I can see, but that doesn’t often mean much. Also, Access (the Jet driver specifically) has it’s own quirks, and doesn’t always interperet SQL the same way SQL Server does.

All the more reason to simplify your statement until it runs, then build it up slowly until it crashes, then you’ll know which bit it doesn’t like. Basic troubleshooting. :slight_smile:

7

Thanks alot.. your help is greatly appreciated.. I’ve solved my problem.. =)

8

Excellent. What was it?

9

I tried to read it from the combobox first before updating it and it works.. =)

10

Great information