CODEWITHSUNDEEP
c#sql-server
sdouble
sdouble

Reputation: 1095

"Must declare the scalar variable" when trying to pass parameters to OleDbCommand/Connection from C#

I'm trying to use Parameters with my query for the first time and I keep receiving this error

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Must declare the scalar variable "@SampleID0".

I've never used parameters and am trying to figure them out. I believe the part that says dbcmd.Parameters.AddWithValue(...) should be adding the parameter with the value to the command, is there some other way I should "declare the scalar variable?" listItems will potentially be thousands of entries, so if there is a more efficient way to do this, I'm all ears.

OleDbConnection dbconn = new OleDbConnection();
OleDbCommand dbcmd = new OleDbCommand();
dbconn.ConnectionString = connectionString;

string[] listItems = new string[2];
listItems[0] = "P00000000683634820055041";
listItems[1] = "P00000000683063257726977";

var parameters = new string[listItems.Length];
for (int i = 0; i < listItems.Length; i++)
{
    parameters[i] = string.Format("@SampleID{0}", i);
    dbcmd.Parameters.AddWithValue(parameters[i], listItems[i]); 
}

dbcmd.CommandText = string.Format("select Analysis.SampleID, Analysis.SampleDateTime, AnalysisResult.ParameterName, AnalysisResult.Slope, AnalysisResult.Bias, " +
"AnalysisResult.ResultValue, AnalysisResult.CalibrationFileName FROM Analysis INNER JOIN AnalysisResult ON (Analysis.AnalysisGUID = AnalysisResult.AnalysisGUID) " +
"where Analysis.SampleID IN ({0})", string.Join(", ", parameters));

dbcmd.Connection = dbconn;
dba = new OleDbDataAdapter(dbcmd);
dba.Fill(dt);
dataGridView1.DataSource = dt;

Resulting dbcmd.CommandText: 

select Analysis.SampleID, Analysis.SampleDateTime, AnalysisResult.ParameterName, AnalysisResult.Slope, AnalysisResult.Bias, AnalysisResult.ResultValue, AnalysisResult.CalibrationFileName 
FROM Analysis 
INNER JOIN AnalysisResult ON Analysis.AnalysisGUID = AnalysisResult.AnalysisGUID
where Analysis.SampleID IN (@SampleID0, @SampleID1)

Upvotes: 2

Views: 2580

Answers (2)

Matt
Matt

Reputation: 2682

Instead of providing a list of several different parameters, try just passing a single parameter that contains all of them, like this question

dbcmd.Parameters.AddWithValue("SampleIDs", String.Join(",",listItems.ToArray()))

Then change the query:

dbcmd.CommandText = "select Analysis.SampleID, Analysis.SampleDateTime, AnalysisResult.ParameterName, AnalysisResult.Slope, AnalysisResult.Bias, " + "AnalysisResult.ResultValue, AnalysisResult.CalibrationFileName FROM Analysis INNER JOIN AnalysisResult ON (Analysis.AnalysisGUID = AnalysisResult.AnalysisGUID) " + "where Analysis.SampleID IN (@SampleIDs)";

EDIT:

As pointed out in the comments, this may not work, but I don't have an OleDB to test with (or SQL handy this second). However, since this is tagged as SQL Server, if it is such you could look at Table Value Parameters

Upvotes: 0

Piotr Perak
Piotr Perak

Reputation: 11088

You should change @ to ? in parameter name if you use OleDbConnection

parameters[i] = string.Format("?SampleID{0}", i);

Upvotes: 1

Related Questions