problem with paramters on select statement

Question

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(varcharTICKNUM) TICKNUM from TICKET where convert(datetimeDATEVAL) >= :lowDate and convert(datetimeDATEVAL) <= :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.

swilkinson · Answer

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

tforrester · Answer

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.

miker · Answer

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.

tforrester · Answer

I had already tried that if I try: select convert(varcharTICKNUM) TICKNUM from TICKET where convert(datetimeDATEVAL) >= @lowDate and convert(datetimeDATEVAL) <= @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.

miker · Answer

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(varcharTICKNUM) TICKNUM from TICKET where convert(datetimeDATEVAL) >= :@lowDate and convert(datetimeDATEVAL) <= @hiDate;

tforrester · Answer

I should have added that: param1=new DateTime(2004826); param2=new DateTime(2004827);