problem with paramters on select statement

tforrester

I have just started learning C#, coming from Java. I am trying to do a select statement in the way of a prepared statement which is the kind of thing done in Java all the time. I am not sure if what I am doing is legal, or if it is just the usage I have got wrong: The Error is: Error getting Ticknum: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ':'. The code snipits are: dbConn = new OdbcConnection(); dbConn.ConnectionString = connectionString; dbConn.Open(); string query = "select convert(varchar,TICKNUM) TICKNUM from TICKET where convert(datetime,DATEVAL) >= :lowDate and convert(datetime,DATEVAL) <= :hiDate"; IDbCommand command = dbConn.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = query; IDbDataParameter lowDate = command.CreateParameter(); lowDate.ParameterName = ":lowDate"; lowDate.DbType = DbType.DateTime; lowDate.Value = param1; command.Parameters.Add(lowDate); IDbDataParameter hiDate = command.CreateParameter(); hiDate.ParameterName = ":hiDate"; hiDate.DbType = DbType.DateTime; hiDate.Value = param2; command.Parameters.Add(hiDate); command.Prepare(); IDataReader reader = command.ExecuteReader(); Many thanks in advance for your help.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

swilkinson21y ago
We use the oledb type for sybase and still point it at a system dsn. You can use the oledb to point at SQL Server but it wont be as quick as the sqlclient types as I think sqlclient is a optimized version of oledb for SQL server. We could not get at the sybase .NET namespace as we only use sysbase7 and it was only available in version 8 onwards. Cheers, Si
tforrester21y ago
Thanks Mike That worked fine. I guess trying to make the code more generic this way is going to be problematic. What is the conventional way. Its seems that most people don't use the ODBC provider and use the Sql one instead, and then perhaps one from Sybase. However I want to keep as much of the code the same, whether I am calling sybase or SQl server.
miker21y ago
Here is the answer from MSDN. 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. For example: SELECT * FROM Customers WHERE CustomerID = ? Look in MSDN for OdbcCommand.Parameters property.
tforrester21y ago
I had already tried that, if I try: select convert(varchar,TICKNUM) TICKNUM from TICKET where convert(datetime,DATEVAL) >= @lowDate and convert(datetime,DATEVAL) <= @hiDate I get: Error getting Ticknum: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Ser ver]Must declare the variable '@lowDate'. IDbDataParameter is syntatically correct, I don't know why I should use IDataParameter instead? I am not calling the interfaces, I am just typing the classes to the Interface type. The reason is that if I where to creat a connection of a different type, then I couls still use the same code for the rest. Since my applications will use SQL and Sybase databases and I am not sure if I will ODBC or a mixture of Data providers.
miker21y ago
First of all, why are you calling interfaces istead of classes? It suppose to be OdbcCommand instead of IDbCommand, OdbcParameter instead of IDataParameter (I think IdbDataParameter is just a typo), OdbcDataReader instead of IDataReader. Second, I think ODBC paramerets must be named like @parameter. So, in your example ParameterName property would be set to "@lowDate" and "@hiDate". And your query would be: string query = "select convert(varchar,TICKNUM) TICKNUM from TICKET where convert(datetime,DATEVAL) >= :"@lowDate and convert(datetime,DATEVAL) <= @hiDate";
tforrester21y ago
I should have added that: param1=new DateTime(2004,8,26); param2=new DateTime(2004,8,27);