Hi!
I have to do an ASP script that updates some columns in four MS Excel files, based on the SQL Server data. For 2 days I have an error :

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] Syntax error in UPDATE statement.

This is the code I use for the update statement:

while not objRS.EOF


obj1RS.Source = "Select CECAND_PACODE, CURR_BUNDLEID, CURR_BUNDLEDDEALER, CECAND_LAST_AUTH FROM CE_CANDEALER where CECAND_PACODE ='" & objRS.Fields.Item(1).Value & "'"
obj1RS.Open

if objRS.Fields(1).Value=obj1RS.Fields(0).Value then
objRS.Fields(1).Value=obj1RS.Fields(0).Value

if obj1RS.Fields(1).Value= "3" then
objRS.Fields(3).Value="S"

else
objRS.Fields(3).Value=obj1RS.Fields(1).Value
end if
response.Write("aici1")
if obj1RS.Fields(2).Value="1" then
objRS.Fields(5).Value="xx"
elseif obj1RS.Fields(2).Value="0" then
objRS.Fields(5).Value="ww"
else
objRS.Fields(5).Value="ss"
end if

'if obj1RS.Fields(3)<>"" then
'objRS.Fields(4)="y"
'else
'objRS.Fields(4).Value=obj1RS.Fields(3).Value
'end if


end if


objRS.Update
objRS.MoveNext
obj1RS.Close
wend

In my opinion everything should be ok, but obviously is not.

I changed the permissions for IUSR_x(full control) and I did many other things that I found on google.
If there is anybody who had the same problem and knows an answeer, please email me:(
Thanks

Are you updating an excel sheet with this code ?

If yes, the excel tables (equivalent to worksheets) start with $ symbol. $Sheet1 etc. If we omit that, it will give a similar error.

The other reason may be that one of the fields of excel sheet has a keyword as the header (field name). Password, name, date etc. are keywords.

Please post the portion of the code which sets the recordset objRS. And also the data types of fields. This will help us give you more idea.

Hi again!
This is the code for recordset objRS:
 Set objConn = Server.CreateObject("ADODB.Connection")
 objConn.Open "Ram2"
 Set objRS = Server.CreateObject("ADODB.Recordset")
 objRS.ActiveConnection = objConn
Everything is fine here, the select statement from Excel and SQL works and in Excel I don't have keyword names:(
Yesterday I tried the following version for the update, but also didn't work:
While Not objRS.EOF
         Response.Write("<TR>")
        'Response.Write " Select CECAND_PACODE, CURR_BUNDLEID, CURR_BUNDLEDDEALER, CECAND_LAST_AUTH FROM CE_CANDEALER where CECAND_PACODE =" & objRS.Fields.Item(1).Value
             Set obj1RS = Server.CreateObject("ADODB.Recordset")
             obj1RS.ActiveConnection=obj1
            'obj1RS.Source = "Select CURR_BUNDLEID, CURR_BUNDLEDDEALER, CECAND_LAST_AUTH FROM CE_CANDEALER where CECAND_PACODE ='" & objRS.Fields.Item(1).Value & "'"
            obj1RS.Open
       'this works

                Response.write(" - 1) " & obj1RS.Fields.Item(0).Value)
                'Response.write(" - 2) " & obj1RS.Fields.Item(1).Value)
                'Response.write(" - 3) " & obj1RS.Fields.Item(2).Value)

        'update
        dim d

        if(obj1RS.Fields(0).Value = "3") then
            d="S"
        else
            d = obj1RS.Fields(0).Value

        end if
       ' if(obj1RS.Fields(1).Value="1") then
            objRS.Fields(6).Value="Y"
                    '
        elseif(obj1RS.Fields(1).Value="0") then
            objRS.Fields(6).Value="N"

        else
            objRS.Fields(6).Value=" "
        end if
          'this doesn't work
             excelString="Update Ram2 Set [$YES/NO]='" & d & "' "

             objConn.Execute(excelString)
             objrs.Update
             obj1RS.Close
         set obj1RS= nothing
          Response.write("<br /></TD>")

         objRS.MoveNext
         Response.Write("</TR>")
         Wend
As you can see I put $ sign.
Do you have any ideea what is happening?

The query which sets objRS is not there. May be you have some security problems in posting it ?

Please use $ only for tablename (worksheet name) of excel.

i.e. excelString=="Update $Ram2 Set [YES/NO]='" & d & "' ". This also will not work as [YES/NO] is not a valid field.

Here is the correct connection string to an excel file.

"Data Source=<absolute path to excel file>\sample.xls" & ";" & _
                "Extended Properties=""Excel 8.0;"""

And I found that you are not checking obj1RS has got any rows. Please check that also.

It is always better to do updation through direct SQL statement rather than using .update method of ADO object. Then you can print the query and check whether there is anything wrong there.