OdbcConnection - parameter problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi;

I have an OdbcConnection (ADO.NET) to Sql Server (I know I can use the
SqlConnection - I'm testing our code when it uses ODBC) with:

protected DbProviderFactory provider;
protected DbCommand cmd;

....

cmd.CommandText = "select lastname from employees where (employeeid < 8)
and (lastname = @p0)";
DbParameter param = provider.CreateParameter();
param.ParameterName = "@p0";
param.Value = "thi";
cmd.Parameters.Add(param);
cmd.ExecuteReader();

And I get:
$exception {"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Must declare the variable '@p0'."} System.Exception
{System.Data.Odbc.OdbcException}

Any ideas? This works great for the SqlServerConnection.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
With the OLE DB providers you need to build a matching Parameters
collection--one for each parameter marker (?) in the SQL. These are
positional (not named as they are in SqlClient) so they must be added to the
Parameters collection in the correct order.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

David Thielen said:
Apparently the trick is to use a ? in the select as:

cmd.CommandText = "select lastname from employees where (employeeid < 8)
and (lastname = ?)";

But how does it know what ? to sub for each parameter?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




David Thielen said:
Hi;

I have an OdbcConnection (ADO.NET) to Sql Server (I know I can use the
SqlConnection - I'm testing our code when it uses ODBC) with:

protected DbProviderFactory provider;
protected DbCommand cmd;

...

cmd.CommandText = "select lastname from employees where (employeeid < 8)
and (lastname = @p0)";
DbParameter param = provider.CreateParameter();
param.ParameterName = "@p0";
param.Value = "thi";
cmd.Parameters.Add(param);
cmd.ExecuteReader();

And I get:
$exception {"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Must declare the variable '@p0'."} System.Exception
{System.Data.Odbc.OdbcException}

Any ideas? This works great for the SqlServerConnection.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm
Click to expand...
Click to expand...
 
Hi Dave,
Thanks for Bill's reply.

I agree with Bill. I just want to check if you have any further questions.
Please feel free to let me know if there is anything we can help with.

Have a great day.
Sincerely,
Wen Yuan
 
Oh, like JDBC. Got it.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




William (Bill) Vaughn said:
With the OLE DB providers you need to build a matching Parameters
collection--one for each parameter marker (?) in the SQL. These are
positional (not named as they are in SqlClient) so they must be added to the
Parameters collection in the correct order.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

David Thielen said:
Apparently the trick is to use a ? in the select as:

cmd.CommandText = "select lastname from employees where (employeeid < 8)
and (lastname = ?)";

But how does it know what ? to sub for each parameter?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




David Thielen said:
Hi;

I have an OdbcConnection (ADO.NET) to Sql Server (I know I can use the
SqlConnection - I'm testing our code when it uses ODBC) with:

protected DbProviderFactory provider;
protected DbCommand cmd;

...

cmd.CommandText = "select lastname from employees where (employeeid < 8)
and (lastname = @p0)";
DbParameter param = provider.CreateParameter();
param.ParameterName = "@p0";
param.Value = "thi";
cmd.Parameters.Add(param);
cmd.ExecuteReader();

And I get:
$exception {"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Must declare the variable '@p0'."} System.Exception
{System.Data.Odbc.OdbcException}

Any ideas? This works great for the SqlServerConnection.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm
Click to expand...
Click to expand...
Click to expand...
 
Back
Top