ERROR [42000] [Microsoft][ODBC Visual FoxPro Driver]Syntax error

Question

Hy
I want to insert into ODBC and I have the error: ERROR [42000] [Microsoft][ODBC Visual FoxPro Driver]Syntax error.
My code is:

string number;

insertStatement = "INSERT INTO " + tabela + " (Data, Fetr, Fldo, Nrdo, Dii) " +
        " VALUES ( "+ "@data" +", 'cc','CD', " +  number + ","+ dii + ")";

       OdbcCommand cmd = new OdbcCommand(insertStatement, this.connection);             

        cmd.Parameters.Add("@data",OdbcType.DateTime).Value = data;

        cmd.ExecuteNonQuery();

The problem is with the data, but I cannot figure out what is the problem.
Can someone help me?
Thanks

Could you specify the field data type for each of the field & data type for each of the variable (number, dii)? — shahkalpesh
DateTime data, double dii, string number - and in the table number is of type number..only I have problems at data...because if I insert without data it works, but when I put data in my insert statement gives me errors — user599977

Paolo Falabella Paolo Falabella · Accepted Answer · 2011-03-10T07:48:04

try with a ? instead of @data in the query, like this:

insertStatement = "INSERT INTO " + tabela + " (Data, Fetr, Fldo, Nrdo, Dii) " +
        " VALUES ( ? , 'cc','CD', " +  number + ","+ dii + ")";

Msdn says:

When CommandType is set to Text, the .NET Framework Data Provider for ODBC does not support passing named parameters to an SQL statement or to a stored procedure called by an OdbcCommand. In either of these cases, use the question mark (?) placeholder.

UPDATE you could try concatenating your date directly in the insert in this format { d '2011-03-10' } (see ODBC Datetime Format for reference) and drop the parameter.

insertStatement = "INSERT INTO " + tabela + " (Data, Fetr, Fldo, Nrdo, Dii) " +
            " VALUES ( { d '" +
            data.ToString("yyyy-MM-dd") 
             + "' } , 'cc','CD', " +  number + ","+ dii + ")";

ERROR [42000] [Microsoft][ODBC Visual FoxPro Driver]Syntax error

1 Answers