[RESOLVED] [2005] parameters in ODBC provider

[vbdotnetboy]

how do i declare a parameter for a ODBC provider


for instance i have the following for a update statement


UPDATE [Table1] SET [Col1]=@p1, [Col2]=@p2 WHERE [Col3]=@p3


however when i try and run the code i get an error about declaring @p1. so how exactly do i set up parameters for ODBC do i just use a question mark or what? is it data provider specific meaning do i have to use something different it the ODBC is for MSSQL compared to that of say Oracle or MySQL.

Thanks in advance

[jmcilhinney]

Parameters can be specified differently depending on the database. SQL Server uses the "@" prefix and I believe MySQL uses a "?" prefix and Oracle uses ":". I've never used MySQL or Oracle so I'm not 100% sure about that. Maybe you should be specific about what code you're using, what ODBC driver and what the error message was. Errors are often very specific so if your question is vague we can't really help.

[vbdotnetboy]

ok the odbc driver is SQL Server

here's an error i got when i had just a question mark for the parameter

ERROR [07S01] [Microsoft][ODBC SQL Server Driver]Invalid use of default parameter

then i used as @ simble and this is the error i got
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@p1'.

[vbdotnetboy]

ok i found a little mistake but now i'm getting this error

ERROR [22001] [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

[vbdotnetboy]

as it seems always happens i got it

[jmcilhinney]

Any particular reason you're using OdbcClient for SQL Server rather than the more efficient and more functional SqlClient? You should generally use a database-specific data connector if one is available. If there isn't one but there is an OLEDB provider available I believe that OLEDB is more efficient than ODBC too. You should generally only choose ODBC if an ODBC driver is the only option available.

[vbdotnetboy]

Oh I totally agree with you jmcilhinney however for the class that I was to create it had to have the ability to have an ODBC, OLE, and SqlClient capability. Then all the user has to do is just simply setup the config file for the connection string and provider type. Since I’m using the IDbDataConnection and so forth. Hopefully they can use the SqlClient or OLE provider and in a last case scenario use the ODBC.