Error inserting SQL statement into FileMaker 8 via ODBC from Visual Basic 6

I am receiving an error when trying to insert in the following SQL statement via the FileMaker ODBC Driver from within Visual Basic 6.

The SQL Insert Statement is this:

Insert INTO timer (PassingTime, TransponderID, Hits, Strength, LocationID) VALUES (44796001, 'LK-99381', 0, 0, '123')

The Table within FileMaker appears to be correct with the following setup

Table Name: timer

Field Name Type

PassingID Number - Auto enter Serial

PassingTime Number

TransponderID Text

Hits Number

Strength Number

LocationID Text

The error i receive within Visual Basic is this:

Error -2147467259 ([DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver] The statemnet contained an escape clause not supported by this database driver.)

The code within Visual Basic looks like this...

Dim cnFM As New ADODB.Connection

Dim rsPassingInfo As New ADODB.Recordset

Dim strSQL As String



strSQL = ... Build SQL Statement here....

       

    cnFM.CursorLocation = adUseClient

    cnFM.ConnectionString = "DSN=" & ProgramVar.strFMDSNName & ";Uid=" & ProgramVar.strFMUserID & ";Pwd=" & ProgramVar.strFMPasswordID & ";"

    cnFM.Open

    cnFM.CommandTimeout = 600

   

    cnFM.Excute (strSQL)

   

    cnFM.Close

    Set cnFM = Nothing

The code errors out on the cnFM.Excute (strSQL) line.



Using this same code above and with the excute statement i can run normal Select statements just fine (i.e. Select * from timer).  



The insert statement:

Insert INTO timer (PassingTime, TransponderID, Hits, Strength, LocationID) VALUES (44796001, 'LK-99381', 0, 0, '123')

Can be excuted just fine when using Microsoft Query app.

Any help would be great!

Thanks,

Greg Blasko

Edited December 3, 200520 yr by Guest