Using parameters in a SQL OleDb provider leads to "must declare variable error"

Hi I am using the code below to run a simple query using the SQlOledb
provider but for some reason, I get the error "must declare the
variable @BamDestination" when the SQL is executed. This works fine if
I use the SQL Server provider. Anyone any ideas ?


//Using oledb to support sybase as well as SQL server
OleDbConnection conn = null;

try
{
conn = new OleDbConnection(connectionString);
conn.Open();

string
sSQL = @"SELECT MESSAGEDATE, MESSAGEREF, RESPONSE FROM
GENERATEDRESPONSES WHERE DESTINATION = @BamDestination ORDER BY
GENERATEDDATE";

OleDbParameter [] arParms = new OleDbParameter[1];
string bamDestination = "BAM";
arParms[0] = new
OleDbParameter("@BamDestination",OleDbType.VarWChar);
arParms[0].Value = bamDestination;

OleDb oleDb = new OleDb();
return oleDb.ExecuteDataset(conn, CommandType.Text, sSQL,arParms);

NoSpamMgbworld
Fri Jun 24 09:13:03 CDT 2005

Try this:

sSQL = @"SELECT MESSAGEDATE, MESSAGEREF, RESPONSE FROM
GENERATEDRESPONSES WHERE DESTINATION = ? ORDER BY
GENERATEDDATE";


Some OleDB providers do not accept named parameters. For this reason, the
OleDb command object takes placeholders (?) to hold params. You have to make
sure you bind multiple params in order, however (only 1 here, so no problem).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************


"desmcc@gmail.com" wrote:

> Hi I am using the code below to run a simple query using the SQlOledb
> provider but for some reason, I get the error "must declare the
> variable @BamDestination" when the SQL is executed. This works fine if
> I use the SQL Server provider. Anyone any ideas ?
>
>
> //Using oledb to support sybase as well as SQL server
> OleDbConnection conn = null;
>
> try
> {
> conn = new OleDbConnection(connectionString);
> conn.Open();
>
> string
> sSQL = @"SELECT MESSAGEDATE, MESSAGEREF, RESPONSE FROM
> GENERATEDRESPONSES WHERE DESTINATION = @BamDestination ORDER BY
> GENERATEDDATE";
>
> OleDbParameter [] arParms = new OleDbParameter[1];
> string bamDestination = "BAM";
> arParms[0] = new
> OleDbParameter("@BamDestination",OleDbType.VarWChar);
> arParms[0].Value = bamDestination;
>
> OleDb oleDb = new OleDb();
> return oleDb.ExecuteDataset(conn, CommandType.Text, sSQL,arParms);
>
>