[RESOLVED] Can somebody PLEASE help

[Newtester]

Hello,

I have the following query that is giving me error:

Microsoft JET Database Engine
-2147217900 Syntax error in UPDATE statement.

UPDATE Employees SET First_name = "Faaez", Last_name = "Kamaal", Middle = 'A', Address = "5835 S. Blucher Drive", City = "Tucson", State = 'AZ', Zip = '85746', Phone = '5207417099', Alt_phone = '5207410715', Type = 'S', User_id = 'FAAEZ', Password = '123456', Status = 'W', Commission_p = 50, Commission_f = 100, Commission_m = 250, DLN = 'D12345678', DL_state = 'AZ', DL_exp = '7/1/2006', SSN = '000000000', Notes = "This is a test.", Start_date = '7/1/2006', End_date = '7/1/2006', Salary_chk = 0, Comn_chk = 1, M_salary = 0 WHERE Emp_id = 1

I do not understand the error. Waisted half a day but enable to find any errors. When I paste this same thing in VISDATA it works just fine without errors but thru my application it is coming up with this error.

some dat has single quote arround it and some has double quotes. It is only because I am expecting an apostrophy s in that field. And ofcourse numbers are without any quotes arround them.

Any and all help will be greatly appreciated.

[rory]

What type of database is this ?
And are the date strings going in Date Fields or Text Fields ?

[Newtester]

It is MS Access and this is my connection string:

Set cn = New ADODB.Connection 'weve declared it as a ADODB connection lets set it.
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & App.Path & "\Vehicle_sales.mdb" 'this is the connection string.
cn.Open

Call cn.Execute(sSQL)

cn.Close
Set cn = Nothing

Dates are going into date fields for sure cuz it isa working fine in the VISDATA so must be right. Only thru the application it is coming up with this error.

Also if the data type is wrong the there will not be a syntax error I think.

[rory]

Did you try the date fields like this ..

#01/06/2006#

When working with a date in Access always use the # in your SQL statement.

eg.
SQL="INSERT INTO myTable (myDateFld) VALUES (#" & FormatDateTime(Date, 2) & "#)"

[rory]

also,use single quotes on the Text Fields.

Replace the fields before or when you enter them by using a function like this ..

'// CLEAN DATABASE TEXT STRING
Public Function Correct_String(ByVal rStr As String)
    Correct_String = Replace$(rStr, "'", "''")
End Function

[Newtester]

Rory,

Thanks for your quick response. I tried what you have suggested but still same error

[rory]

did you update it without the single colon on the date field ..?

Eg . ,, with Corrected Text and Date Fields ..

UPDATE Employees SET First_name = 'Faaez', Last_name = 'Kamaal', Middle = 'A', Address = '5835 S. Blucher Drive', City = 'Tucson', State = 'AZ', Zip = '85746', Phone = '5207417099', Alt_phone = '5207410715', Type = 'S', User_id = 'FAAEZ', Password = '123456', Status = 'W', Commission_p = 50, Commission_f = 100, Commission_m = 250, DLN = 'D12345678', DL_state = 'AZ', DL_exp = #7/1/2006#, SSN = '000000000', Notes = 'This is a test.', Start_date = #7/1/2006#, End_date = #7/1/2006#, Salary_chk = 0, Comn_chk = 1, M_salary = 0 WHERE Emp_id = 1

Also when i copied and pasted your SQL I got a Double Quote instead of 2 single quotes .. which will probably give an error.

Edit: missed another date field.

Also is SSN a Text Field?

[Newtester]

Rory,

I tried single quotes and Chr(34) which is a double quote but no difference. Yes the SSN is a text field.

What I am puzzled about is that this same query is working just fine in the VISDATA.exe which is the interface to MS Access thru VB6.

I am so fruastrated now. I have a huge application and this is the first time this SIMPLE UPDATE is coming up with so hiden type of error.

I really do appreciate all your help.

[rory]

can you PM me the Access DB and Ill check it out ..
Thanks
Rory

[Newtester]

Can you please tell me how to PM you the DB? I don't know how to do it.

Thanks.

[BrailleSchool]

Have a look at the highlighted (yellow), change yellow to single quotes
Then the dates need to be surrounded by #

Hello,

I have the following query that is giving me error:

Microsoft JET Database Engine
-2147217900 Syntax error in UPDATE statement.

UPDATE Employees SET First_name = "Faaez", Last_name = "Kamaal", Middle = 'A', Address = "5835 S. Blucher Drive", City = "Tucson", State = 'AZ', Zip = '85746', Phone = '5207417099', Alt_phone = '5207410715', Type = 'S', User_id = 'FAAEZ', Password = '123456', Status = 'W', Commission_p = 50, Commission_f = 100, Commission_m = 250, DLN = 'D12345678', DL_state = 'AZ', DL_exp = '#7/1/2006#', SSN = '000000000', Notes = "This is a test.", Start_date = '#7/1/2006#', End_date = '#7/1/2006#', Salary_chk = 0, Comn_chk = 1, M_salary = 0 WHERE Emp_id = 1

I do not understand the error. Waisted half a day but enable to find any errors. When I paste this same thing in VISDATA it works just fine without errors but thru my application it is coming up with this error.

some dat has single quote arround it and some has double quotes. It is only because I am expecting an apostrophy s in that field. And ofcourse numbers are without any quotes arround them.

Any and all help will be greatly appreciated.

[Newtester]

BrailleSchool,

Thanks for your help but I have tried that already still getting the same error.

[rory]

sent you a PM

[BrailleSchool]

What I would do is make it easier to read the SQL statement because its kinda long, do it like so:

Dim sSQL As String
 
sSQL = "UPDATE Employees "
sSQL = sSQL & "SET First_name = 'Faaez', "
sSQL = sSQL & "Last_name = 'Kamaal', "
sSQL = sSQL & "Middle = 'A', "
sSQL = sSQL & "Address = '5835 S. Blucher Drive', "
sSQL = sSQL & "City = 'Tucson', "
sSQL = sSQL & "State = 'AZ', "
sSQL = sSQL & "Zip = '85746', "
sSQL = sSQL & "Phone = '5207417099', "
sSQL = sSQL & "Alt_phone = '5207410715', "
sSQL = sSQL & "Type = 'S', "
sSQL = sSQL & "User_id = 'FAAEZ', "
sSQL = sSQL & "Password = '123456', "
sSQL = sSQL & "Status = 'W', "
sSQL = sSQL & "Commission_p = 50, "
sSQL = sSQL & "Commission_f = 100, "
sSQL = sSQL & "Commission_m = 250, "
sSQL = sSQL & "DLN = 'D12345678', "
sSQL = sSQL & "DL_state = 'AZ', "
sSQL = sSQL & "DL_exp = '#7/1/2006#', "
sSQL = sSQL & "SSN = '000000000', "
sSQL = sSQL & "Notes = 'This is a test.', "
sSQL = sSQL & "Start_date = '#7/1/2006#', "
sSQL = sSQL & "End_date = '#7/1/2006#', "
sSQL = sSQL & "Salary_chk = 0, "
sSQL = sSQL & "Comn_chk = 1, "
sSQL = sSQL & "M_salary = 0 "
sSQL = sSQL & "WHERE Emp_id = 1"

I just put the above code into a code window (command click) and I get no errors for syntax problems

[Newtester]

These are the routine I am using:

Private Sub Set_tf(ByRef Ta() As String)
   Ta(0) = "'" & Trim$(TBx(1).Text) & "'"
   Ta(1) = "'" & Trim$(TBx(0).Text) & "'"
   Ta(2) = "'" & Trim$(TBx(2).Text) & "'"
   Ta(3) = "'" & Trim$(TBx(3).Text) & "'"
   Ta(4) = "'" & Trim$(TBx(4).Text) & "'"
   Ta(5) = "'" & Trim$(TBx(5).Text) & "'"
   Ta(6) = "'" & Trim$(TBx(6).Text) & "'"
   Ta(7) = "'" & Trim$(MBx(0).Text) & "'"
   Ta(8) = "'" & Trim$(MBx(1).Text) & "'"
   Ta(9) = "'" & Left$(Combo(0).Text, 1) & "'"
   Ta(10) = "'" & Trim$(TBx(9).Text) & "'"
   Ta(11) = "'" & Trim$(TBx(10).Text) & "'"
   Ta(12) = "'" & Left$(Combo(1).Text, 1) & "'"
   Ta(13) = Val(TBx(12).Text)
   Ta(14) = Val(TBx(13).Text)
   Ta(15) = Val(TBx(14).Text)
   Ta(16) = "'" & Trim$(TBx(7).Text) & "'"
   Ta(17) = "'" & Trim$(TBx(8).Text) & "'"
   Ta(18) = "'#" & DTPik(0).Value & "#'"
   Ta(19) = "'" & Trim$(MBx(2).Text) & "'"
   Ta(20) = "'" & Trim$(RTBx.Text) & "'"
   Ta(21) = "'#" & DTPik(1).Value & "#'"
   Ta(22) = "'#" & DTPik(2).Value & "#'"
   Ta(23) = Chk(0).Value
   Ta(24) = Chk(1).Value
   Ta(25) = Val(TBx(11).Text)
End Sub
 
Private Sub Update_employee()
   On Error GoTo DispError
   
   Dim sSQL As String, cn As ADODB.Connection, I As Integer, Tf(25) As String
   
   Me.MousePointer = 11
   
   Call Set_tf(Tf())
   
   sSQL = "UPDATE Employees SET "
   sSQL = sSQL & "First_name = " & Tf(0) & ", "
   sSQL = sSQL & "Last_name = " & Tf(1) & ", "
   sSQL = sSQL & "Middle = " & Tf(2) & ", "
   sSQL = sSQL & "Address = " & Tf(3) & ", "
   sSQL = sSQL & "City = " & Tf(4) & ", "
   sSQL = sSQL & "State = " & Tf(5) & ", "
   sSQL = sSQL & "Zip = " & Tf(6) & ", "
   sSQL = sSQL & "Phone = " & Tf(7) & ", "
   sSQL = sSQL & "Alt_phone = " & Tf(8) & ", "
   sSQL = sSQL & "Type = " & Tf(9) & ", "
   sSQL = sSQL & "User_id = " & Tf(10) & ", "
   sSQL = sSQL & "Password = " & Tf(11) & ", "
   sSQL = sSQL & "Status = " & Tf(12) & ", "
   sSQL = sSQL & "Commission_p = " & Tf(13) & ", "
   sSQL = sSQL & "Commission_f = " & Tf(14) & ", "
   sSQL = sSQL & "Commission_m = " & Tf(15) & ", "
   sSQL = sSQL & "DLN = " & Tf(16) & ", "
   sSQL = sSQL & "DL_state = " & Tf(17) & ", "
   sSQL = sSQL & "DL_exp = " & Tf(18) & ", "
   sSQL = sSQL & "SSN = " & Tf(19) & ", "
   sSQL = sSQL & "Notes = " & Tf(20) & ", "
   sSQL = sSQL & "Start_date = " & Tf(21) & ", "
   sSQL = sSQL & "End_date = " & Tf(22) & ", "
   sSQL = sSQL & "Salary_chk = " & Tf(23) & ", "
   sSQL = sSQL & "Comn_chk = " & Tf(24) & ", "
   sSQL = sSQL & "M_salary = " & Tf(25) & " "
   sSQL = sSQL & "WHERE Emp_id = " & Val(Lbl1.Caption)
  
   Set cn = New ADODB.Connection 'weve declared it as a ADODB connection lets set it.
   cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= " & App.Path & "\Vehicle_sales.mdb" 'this is the connection string.
   cn.Open
  
   Call cn.Execute(sSQL)
   
   cn.Close
   Set cn = Nothing
   
   Me.MousePointer = 0
   Exit Sub
   
DispError:
   Call ShowError(sSQL, "ADEEmployeeP1", "Update_employee", Err.Number, Err.Description, Err.Source)
   Me.MousePointer = 0
   cn.Close
   Set cn = Nothing
End Sub

and here is the error I am getting back:

Microsoft JET Database Engine
-2147217900 Syntax error in UPDATE statement.

UPDATE Employees SET First_name = 'Faaez', Last_name = 'Kamaal', Middle = 'A', Address = '5835 S. Blucher Drive', City = 'Tucson', State = 'AZ', Zip = '85746', Phone = '5207417099', Alt_phone = '5207410715', Type = 'S', User_id = 'FAAEZ', Password = '123456', Status = 'W', Commission_p = 50, Commission_f = 100, Commission_m = 250, DLN = 'D12345678', DL_state = 'AZ', DL_exp = '#7/1/2006#', SSN = '000000000', Notes = 'This is a test.', Start_date = '#7/1/2006#', End_date = '#7/1/2006#', Salary_chk = 0, Comn_chk = 1, M_salary = 0 WHERE Emp_id = 1

[BrailleSchool]

sSQL = "UPDATE Employees SET "
   sSQL = sSQL & "First_name = [hl]'[/hl]" & Tf(0) & "[hl]'[/hl], "
   sSQL = sSQL & "Last_name = [hl]'[/hl]" & Tf(1) & "[hl]'[/hl], "
   sSQL = sSQL & "Middle = [hl]'[/hl]" & Tf(2) & "[hl]'[/hl], "
   sSQL = sSQL & "Address = [hl]'[/hl]" & Tf(3) & "[hl]'[/hl], "
   sSQL = sSQL & "City = [hl]'[/hl]" & Tf(4) & "[hl]'[/hl], "
   sSQL = sSQL & "State = [hl]'[/hl]" & Tf(5) & "[hl]'[/hl], "
   sSQL = sSQL & "Zip = [hl]'[/hl]" & Tf(6) & "[hl]'[/hl], "
   sSQL = sSQL & "Phone = [hl]'[/hl]" & Tf(7) & "[hl]'[/hl], "
   sSQL = sSQL & "Alt_phone = [hl]'[/hl]" & Tf(8) & "[hl]'[/hl], "
   sSQL = sSQL & "Type = [hl]'[/hl]" & Tf(9) & "[hl]'[/hl], "
   sSQL = sSQL & "User_id = [hl]'[/hl]" & Tf(10) & "[hl]'[/hl], "
   sSQL = sSQL & "Password = [hl]'[/hl]" & Tf(11) & "[hl]'[/hl], "
   sSQL = sSQL & "Status = [hl]'[/hl]" & Tf(12) & "[hl]'[/hl], "
   sSQL = sSQL & "Commission_p = [hl]'[/hl]" & Tf(13) & "[hl]'[/hl], "
   sSQL = sSQL & "Commission_f = [hl]'[/hl]" & Tf(14) & "[hl]'[/hl], "
   sSQL = sSQL & "Commission_m = [hl]'[/hl]" & Tf(15) & "[hl]'[/hl], "
   sSQL = sSQL & "DLN = [hl]'[/hl]" & Tf(16) & "[hl]'[/hl], "
   sSQL = sSQL & "DL_state = [hl]'[/hl]" & Tf(17) & "[hl]'[/hl], "
   sSQL = sSQL & "DL_exp = [hl]'[/hl]" & Tf(18) & "[hl]'[/hl], "
   sSQL = sSQL & "SSN = [hl]'[/hl]" & Tf(19) & "[hl]'[/hl], "
   sSQL = sSQL & "Notes = [hl]'[/hl]" & Tf(20) & "[hl]'[/hl], "
   sSQL = sSQL & "Start_date = [hl]'[/hl]" & Tf(21) & "[hl]'[/hl], "
   sSQL = sSQL & "End_date = [hl]'[/hl]" & Tf(22) & "[hl]'[/hl], "
   sSQL = sSQL & "Salary_chk = [hl]'[/hl]" & Tf(23) & "[hl]'[/hl], "
   sSQL = sSQL & "Comn_chk = [hl]'[/hl]" & Tf(24) & "[hl]'[/hl], "
   sSQL = sSQL & "M_salary = [hl]'[/hl]" & Tf(25) & "[hl]'[/hl] "
   sSQL = sSQL & "WHERE Emp_id = " & Val(Lbl1.Caption)

[rory]

get rid of single quotes around date ..

Private Sub Set_tf(ByRef Ta() As String)
   Ta(0) = "'" & Trim$(TBx(1).Text) & "'"
   Ta(1) = "'" & Trim$(TBx(0).Text) & "'"
   Ta(2) = "'" & Trim$(TBx(2).Text) & "'"
   Ta(3) = "'" & Trim$(TBx(3).Text) & "'"
   Ta(4) = "'" & Trim$(TBx(4).Text) & "'"
   Ta(5) = "'" & Trim$(TBx(5).Text) & "'"
   Ta(6) = "'" & Trim$(TBx(6).Text) & "'"
   Ta(7) = "'" & Trim$(MBx(0).Text) & "'"
   Ta(8) = "'" & Trim$(MBx(1).Text) & "'"
   Ta(9) = "'" & Left$(Combo(0).Text, 1) & "'"
   Ta(10) = "'" & Trim$(TBx(9).Text) & "'"
   Ta(11) = "'" & Trim$(TBx(10).Text) & "'"
   Ta(12) = "'" & Left$(Combo(1).Text, 1) & "'"
   Ta(13) = Val(TBx(12).Text)
   Ta(14) = Val(TBx(13).Text)
   Ta(15) = Val(TBx(14).Text)
   Ta(16) = "'" & Trim$(TBx(7).Text) & "'"
   Ta(17) = "'" & Trim$(TBx(8).Text) & "'"
   Ta(18) = "#" & DTPik(0).Value & "#"
   Ta(19) = "'" & Trim$(MBx(2).Text) & "'"
   Ta(20) = "'" & Trim$(RTBx.Text) & "'"
   Ta(21) = "#" & DTPik(1).Value & "#"
   Ta(22) = "#" & DTPik(2).Value & "#"
   Ta(23) = Chk(0).Value
   Ta(24) = Chk(1).Value
   Ta(25) = Val(TBx(11).Text)
End Sub

[Newtester]

Rory,

I just sent you an email with my DB.

[rory]

Got it .. why is Comn_chk set to a byte format?
The others are doubles also .. by default a number in Access is a Long Integer..
This is an old version of Access BTW (Access 95) so i cant edit it .. :-(

[BrailleSchool]

how about this:

Dim sSQL As String
 
   sSQL = "UPDATE Employees SET "
   sSQL = sSQL & "First_name = '" & Trim$(TBx(1).Text) & "', "
   sSQL = sSQL & "Last_name = '" & Trim$(TBx(0).Text) & "', "
   sSQL = sSQL & "Middle = '" & Trim$(TBx(2).Text) & "', "
   sSQL = sSQL & "Address = '" & Trim$(TBx(3).Text) & "', "
   sSQL = sSQL & "City = '" & Trim$(TBx(4).Text) & "', "
   sSQL = sSQL & "State = '" & Trim$(TBx(5).Text) & "', "
   sSQL = sSQL & "Zip = '" & Trim$(TBx(6).Text) & "', "
   sSQL = sSQL & "Phone = '" & Trim$(MBx(0).Text) & "', "
   sSQL = sSQL & "Alt_phone = '" & Trim$(MBx(1).Text) & "', "
   sSQL = sSQL & "Type = '" & Left$(Combo(0).Text, 1) & "', "
   sSQL = sSQL & "User_id = '" & Trim$(TBx(9).Text) & "', "
   sSQL = sSQL & "Password = '" & Trim$(TBx(10).Text) & "', "
   sSQL = sSQL & "Status = '" & Left$(Combo(1).Text, 1) & "', "
   sSQL = sSQL & "Commission_p = '" & Val(TBx(12).Text) & "', "
   sSQL = sSQL & "Commission_f = '" & Val(TBx(13).Text) & "', "
   sSQL = sSQL & "Commission_m = '" & Val(TBx(14).Text) & "', "
   sSQL = sSQL & "DLN = '" & Trim$(TBx(7).Text) & "', "
   sSQL = sSQL & "DL_state = '" & Trim$(TBx(8).Text) & "', "
   sSQL = sSQL & "DL_exp = " '#' & DTPik(0).Value & '#',
   sSQL = sSQL & "SSN = '" & Trim$(MBx(2).Text) & "', "
   sSQL = sSQL & "Notes = '" & Trim$(RTBx.Text) & "', "
   sSQL = sSQL & "Start_date = " '#' & DTPik(1).Value & '#',
   sSQL = sSQL & "End_date = " '#' & DTPik(2).Value & '#',
   sSQL = sSQL & "Salary_chk = '" & Chk(0).Value & "', "
   sSQL = sSQL & "Comn_chk = '" & Chk(1).Value & "', "
   sSQL = sSQL & "M_salary = '" & Val(TBx(11).Text) & "' "
   sSQL = sSQL & "WHERE Emp_id = " & Val(Lbl1.Caption)

[BrailleSchool]

Got it .. why is Comn_chk set to a byte format?
The others are doubles also .. by default a number in Access is a Long Integer..
This is an old version of Access BTW (Access 95) so i cant edit it .. :-(

so it isnt his code but his db setup?

[Newtester]

Rory,

it is a BYTE cuz I will store only a 0 or a 1 in it so why declare it any other way? I am using thwe same format in other tables as well and it is working just fine in all other tables.

[Newtester]

BrailleSchool,

I do not understand why you want me to put single quotes arround NUMERIC fields? I think it will definitely genrate an error.

[rory]

so it isnt his code but his db setup?

Well i know with Access you 'should' use # around dates and no single quotes ... but also the DB is Access 95 .. plus the formats are bytes on one number field, and double on some others. ... if i edit the fields it would be in Office 2003 so he wouldnt be able to use it ... at least not manually .. though it may work in the application?

[Newtester]

Rroy,

When I installed VB6 Professional and created the DB this is the version it was there. I have OFFICE 2003 that includes the new version of MS ACCESS but I don't know how to upgrade my existing DB. ALso not sure if I upgrade it then my application will be able to access it or not?

[Newtester]

Rroy,

So you think it is the BYTE format that is causing this error? Cuz if you see some other tables they have the same BYTE format and working just fine thru the same application.

[Newtester]

What ever the error is it is well hidden. Cuz the direct interface to the DB (Visdata.exe) gets no errors for the same exact uesry. The error is just thru the application.

So the Table and the field format must be correct in order for Visdata.exe to accept the same exact quesry without any problems.

What do you think?

[rory]

if you dont have Access Installed the program can still work with the Database File .. no prob. ..

Also, you have set field sizes on the Text Fields ...

ill make some changes and send it back .. also in your Update make surte date fields only have # around it and no '

[Newtester]

Rory,

Thanks, got the DB. It is coming up with the SAME EXACT error. No difference.

[Newtester]

Do you think there is any RESERVED word may be as a field name?

[Newtester]

I found the problem THANK GOD.

Field name Password. It is a reserved word.

[rory]

LOL .. you serious.. ? Where is the error ..?

Anyway i always name my Databases like this ..

If the table name is Website

Website_ID
Website_Name
Website_Page

etc ...