Must declare the scalar variable "@P"

[EDN Admin]

I am trying to get an output with the code below and it is saying must declare scalar variable @P

<span style="color:#666666; font-family:Segoe UI,Helvetica,Garuda,Arial,sans-serif; font-size:14px; line-height:21px This code below is supposed to bring out results based on the items in ClassTitles. ClassTitles can contain more than one items. But the
code only works when there is only one item in ClassTitles, and shows no results when ClassTitles contains more than one item.
Can anyone help me figure out the problem in my code?

<pre class="prettyprint lang-vb Dim instSQL As String = "SELECT DISTINCT ClassInstructors.InstructorID FROM classes INNER JOIN ClassInstructors ON classes.ClassID = ClassInstructors.ClassID" & _ " WHERE classes.CourseTitle =@P0 GROUP BY ClassInstructors.InstructorID HAVING COUNT(DISTINCT classes.CourseTitle) = " & ClassTitles.Count & "" For i = 1 To ClassTitles.Count - 1 instSQL &= " Union SELECT DISTINCT ClassInstructors.InstructorID FROM classes INNER JOIN ClassInstructors ON classes.ClassID = ClassInstructors.ClassID" & _ " WHERE classes.CourseTitle =@P GROUP BY ClassInstructors.InstructorID HAVING COUNT(DISTINCT classes.CourseTitle) = " & ClassTitles.Count & "" & i.ToString Next Dim assCMD As New SqlCommand(instSQL) For i = 0 To ClassTitles.Count - 1 assCMD.Parameters.Add(New SqlParameter With {.ParameterName = "@P" & i.ToString, .SqlDbType = SqlDbType.VarChar, .Size = 50, .Value = ClassTitles(i)}) Next Dim sqlConn As SqlClient.SqlConnection = CType(objgen.dbConnect(), SqlConnection) assCMD.Connection = sqlConn sqlConn.Open() Dim Assdr As SqlDataReader = assCMD.ExecuteReader If Assdr.HasRows Then While Assdr.Read Instructs.Add(Assdr.GetInt32(0)) End While End If Assdr.Close() assCMD.Dispose() sqlConn.Close() [/code]
<br/>
<p style="font-size:14px; vertical-align:baseline; list-style-type:none; color:#666666; font-family:Segoe UI,Helvetica,Garuda,Arial,sans-serif; line-height:21px
Classtitles is a list of string that is populated with the code below.
<pre class="prettyprint lang-vb prettyprinted" style="font-size:14px; vertical-align:baseline; list-style-type:none; word-wrap:normal; overflow-y:hidden; overflow-x:auto; color:#666666; line-height:21px <span class="kwd" style="margin:0px; padding:0px; border:0px; vertical-align:baseline For<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline i <span class="pun" style="margin:0px; padding:0px; border:0px; vertical-align:baseline =<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline <span class="lit" style="margin:0px; padding:0px; border:0px; vertical-align:baseline 0<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline <span class="kwd" style="margin:0px; padding:0px; border:0px; vertical-align:baseline To<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline ClassesListBox1<span class="pun" style="margin:0px; padding:0px; border:0px; vertical-align:baseline .<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline SelectedItems<span class="pun" style="margin:0px; padding:0px; border:0px; vertical-align:baseline .<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline Count <span class="pun" style="margin:0px; padding:0px; border:0px; vertical-align:baseline -<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline <span class="lit" style="margin:0px; padding:0px; border:0px; vertical-align:baseline 1<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline
ClassTitles<span class="pun" style="margin:0px; padding:0px; border:0px; vertical-align:baseline .<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline Add<span class="pun" style="margin:0px; padding:0px; border:0px; vertical-align:baseline (<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline ClassesListBox1<span class="pun" style="margin:0px; padding:0px; border:0px; vertical-align:baseline .<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline SelectedItems<span class="pun" style="margin:0px; padding:0px; border:0px; vertical-align:baseline (<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline i<span class="pun" style="margin:0px; padding:0px; border:0px; vertical-align:baseline ).<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline ToString<span class="pun" style="margin:0px; padding:0px; border:0px; vertical-align:baseline )<span class="pln" style="margin:0px; padding:0px; border:0px; vertical-align:baseline
<span class="kwd" style="margin:0px; padding:0px; border:0px; vertical-align:baseline Next[/code]


View the full article