2

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)
mehdi lotfi
  • 11,194
  • 18
  • 82
  • 128
sdouble
  • 1,055
  • 3
  • 11
  • 28
  • Can you post the resulting SQL? Since an exception saying that you didn't specify parameter `@SimpleID0`, there seems to be something fishy going on, as it looks like you're adding that parameter just fine. – Lasse V. Karlsen Jan 03 '14 at 20:24
  • 1
    You say "sql server" in the tag. Why are you using OleDbConnection? Could it be that parameter handling through those classes are different? To be honest I've never used them with SQL server. Can you try, when adding the parameter-values, to drop the `@` (string.Format). – Lasse V. Karlsen Jan 03 '14 at 20:29
  • When looking at the documentation for OleDbCommand, it seems it uses positional parameters, and not named ones. http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters(v=vs.110).aspx - Could you try using question marks for the parameters in the SQL instead? This might mean that you need to add a parameter more than once in the `.Parameters` collection, if you use it more than once in the SQL. – Lasse V. Karlsen Jan 03 '14 at 20:30
  • @LasseV.Karlsen I don't need to use OleDb, I'm just used to using OleDb. Switching to SqlClient works and I'll keep it with that. I'll look into using parameters with OleDb. – sdouble Jan 03 '14 at 20:58
  • Did you try what I suggested? ? instead of @? – Piotr Perak Jan 03 '14 at 22:28

2 Answers2

1

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

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

Piotr Perak
  • 10,718
  • 9
  • 49
  • 86
0

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

Community
  • 1
  • 1
Matt
  • 2,682
  • 1
  • 17
  • 24
  • This does not work. The accepted answer on the linked question is incorrect. The SQL will still be `where ... in (1 value)`, the 1 value in question will be a string, being "10,11,12,13" and so on. The only way you're going to get a hit is if you're actually having a row that has a primary key value that is "10,11,12,13" (ie. not *one* of those values, but a string with all those values, with commas inbetween). – Lasse V. Karlsen Jan 03 '14 at 20:21
  • Fair enough, updated with potential solution if OP is actually using SQL server as tagged (though still with OleDb) – Matt Jan 03 '14 at 20:34
  • No SQL-based database engine allows you to pass in 1 parameter and use it as a multi-valued list of a IN-clause if you pass in a string. SQL Server or no, that makes no difference. There might be a parameter type other than string, like table-valued parameters for SQL server, that would fit the bill, but if you pass in 1 parameter with 1 string, it's going to match at most 1 value. – Lasse V. Karlsen Jan 03 '14 at 20:37