Must declare scalar variable @resident

Question

I have declared the variable and it is returning the corrent value however I dont understand why I am still getting this error.

  sqlString.Append( " SELECT  Medication.Description + ' - ' + Medication.Strength + ' - ' + Medication.MedicationCode as DrugName, Resident.Title + ' ' + Resident.firstName + ' ' + Resident.Surname AS Resident, MedicationDispenseTimeslot.MedicationDosageBarcode, 0 AS checkBox " );
             sqlString.Append( " FROM Medication INNER JOIN MedicationDispense ON Medication.MedicationID = MedicationDispense.MedicationID INNER JOIN Resident ON MedicationDispense.ResidentID = Resident.ResidentID INNER JOIN MedicationDispenseStatus ON MedicationDispense.MedicationDispenseStatusID = MedicationDispenseStatus.MedicationDispenseStatusID INNER JOIN MedicationDispenseTimeslot ON MedicationDispense.MedicationDispenseID = MedicationDispenseTimeslot.MedicationDispenseID WHERE     (MedicationDispenseStatus.MedicationDispenseStatusID = 2) and  MedicationDispense.MedicationDispenseID in (select MedicationDispenseID from dbo.MedicationDispenseHistory where DATEDIFF(d, 0, DateTimeStamp) =DATEDIFF(d, 0, GETDATE()) and MedicationDispenseStatusID=2) AND MedicationDispense.ResidentID = @residentID " );
             sqlString.Append( " AND   (MedicationDispense.MedicationDispenseStatusID > 1) AND ");
             sqlString.Append( "    1 = (CASE WHEN MedicationDispense.StopDate IS NULL " );
             sqlString.Append( "                 THEN (CASE WHEN MedicationDispense.StartDate <= @periodEndDate THEN 1 ELSE 0 END) " );
             sqlString.Append( "            ELSE " );
             sqlString.Append( "                CASE WHEN (MedicationDispense.StartDate between @periodStartDate and @periodEndDate) " );
             sqlString.Append( "                                OR " );
             sqlString.Append( "                          (MedicationDispense.StopDate between @periodStartDate and @periodEndDate) " );
             sqlString.Append( "                                OR " );
             sqlString.Append( "                          (MedicationDispense.StartDate < @periodStartDate and MedicationDispense.StopDate > @periodEndDate) " );
             sqlString.Append( "    GROUP BY Medication.Description + ' - ' + Medication.Strength + ' - ' + Medication.MedicationCode, Resident.Title + ' ' + Resident.firstName + ' ' + Resident.Surname , MedicationDispenseTimeslot.MedicationDosageBarcode " );

             command = new SqlCommand(sqlString.ToString(), connection);
             command.Parameters.Add( "@residentID", SqlDbType.Int ).Value = residentID.ToString();
             command.Parameters.Add("@periodStartDate", SqlDbType.DateTime).Value = Helper.GetValue(periodStartDate);
             command.Parameters.Add("@periodEndDate", SqlDbType.DateTime).Value = Helper.GetValue(periodEndDate);

EDIT

  command = new SqlCommand( sqlString.ToString(), connection );

     SqlDataAdapter dataAdapter = new SqlDataAdapter( command );
     dataAdapter.Fill( ds, "dataTable" );

     return ds;

What error message are you getting? It seems that you are assigning a string to the @residentID parameter: command.Parameters.Add( "@residentID", SqlDbType.Int ).Value = residentID.ToString(); — Dan, Aug 14 '13 at 10:36
You are adding string (`residentID.ToString()`) to `SqlDbType.Int` ? That's just wrong.. — Soner Gönül, Aug 14 '13 at 10:37
Use verbatim string literals to build your formatted sql query instead of this ugly `StringBuilder`. That will also force you to always use sql-parameters. — Tim Schmelter, Aug 14 '13 at 10:37
It was still giving me the same error before i added the .ToString() — Mark, Aug 14 '13 at 10:53
I would bin this - writing sql in a concatenated string is ugly, unreadable and unmaintainable. Why aren't you using a stored procedure? Your error suggests something isn't being escaped properly perhaps? again another reason to avoid doing this kind of thing — wysinawyg, Aug 14 '13 at 10:55
You should insert a `MessageBox.Show(sqlString.ToString());` before you create your command object to see what you have constructed. For me this doesn`t look like a valid sql statement. — Heslacher, Aug 14 '13 at 11:03
Have you checked your query ??Run your query and check if you are getting the desired result ,, One suggestion don't get complications use **AddWithValue** Instead of Add. — Suraj Singh, Aug 14 '13 at 11:17
The problem resides on the second and longest line where the 'From' statement is. at the end of that is the @residentID — Mark, Aug 14 '13 at 11:43
@Mark, Can you show the rest of the code where you are executing the command ( edit your post ) and also in the title you are reffering to the variable `@resident`: is this a typo ? — Heslacher, Aug 14 '13 at 11:55

score 1 · Answer 1 · edited May 23 '17 at 12:23

1

if you writing sql in a concatenated string this problem will occurs, you can write static query as concatenated string because it will go to SQL and execute directly but here what i can understand , if you see output of sqlString.ToString() it contains some variable like @residentID , @periodEndDate etc, when it goes for execution it is not available in contextvand also not defined as parameters, that's why you getting error Must declare scalar variable @resident even you are passing it as parameter but i think in case of concatenated string you can't pass parameters,.

So better use a stored procedure, this is made for executing dynamic query and multiple query in once. In stored parameter you can define parameters and pass values for them.

Check to know how stored procedure works using c#

How to execute stored procedure using C#

edited May 23 '17 at 12:23

Community

1
1

answered Aug 14 '13 at 11:21

CodeGuru

2,722
6
36
52

`if you see output of sqlString.ToString() it contains some variable like @residentID , @periodEndDate etc, when it goes for execution it is not available in context` this assumption is false. – Heslacher Aug 14 '13 at 11:25
In case of SP we define these are the parameters for Sp, but how we will know what are the parameters here in case of concatenated string? – CodeGuru Aug 14 '13 at 11:28
This is done by the command object. It parses the command text find the defined parameternames and replaces them with the values of the parameter objects which will match the names ( and for sure is escaping them ). – Heslacher Aug 14 '13 at 11:31
@Mark you created stored procedure? – CodeGuru Aug 14 '13 at 11:43

score 1 · Answer 2 · answered Aug 14 '13 at 12:07

This is an example of not seeing the wood for the trees:

You are building your SqlCommand command just fine , but you are never using it. Instead you create a new SqlCommand command which you are using. The parameters aren`t added to this SqlCommand object.

Must declare scalar variable @resident

2 Answers2