Need some assistance with parameterized queries in visual studio.

sock1992

Well-known member
Joined
May 20, 2020
Messages
107
Programming Experience
Beginner
I'm trying to execute the following query, but its throwing an exception "Must declare the scalar variable \@dateFrom\ "." Does anyone maybe know why this is occurring? the dataType for dateOfDeparture is date. when i was using concatenation i was providing the format of the date "dateFromPicker.Value.ToString("MM/dd/yyyy")" but now changed it to dateFromPicker.Value as i assumed that i wouldn't need to specify the format.

Either way both of these inputs do not work so i don't really know what I'm doing wrong here

C#:
 {
                string query = " SELECT cs.coachScheduleId, cs.stationDeparture, cs.stationArrival , cs.timeOfDeparture, cs.timeOfArrival, cs.dateOfDeparture, c.numberOfSeats FROM coachSchedule cs JOIN Coach c ON cs.coachId = c.CoachId WHERE cs.dateOfDeparture BETWEEN @dateFrom AND @dateToo AND cs.stationDeparture= @travelFrom AND cs.stationArrival= @travelToo";
              
                              
                cmd = new SqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@dateFrom", dateFromPicker.Value);
                cmd.Parameters.AddWithValue("@dateToo", dateTooPicker.Value);
                cmd.Parameters.AddWithValue("@travelFrom", comboBoxTravelFrom.SelectedValue);
                cmd.Parameters.AddWithValue("@travelToo", comboBoxTravelTo.SelectedValue);
                
                connection.Open();               
                SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                dataGrid.DataSource = dt;
                connection.Close();
              
            }
 
As for the issue, you're adding parameters to a command but then you don't execute that command. Do this:
C#:
cmd = new SqlCommand(query, connection);

// ...

SqlDataAdapter adapter = new SqlDataAdapter(query, connection);

if (adapter.SelectCommand == cmd)
{
    Console.WriteLine("You're executing the one and only command.")
}
else
{
    Console.WriteLine("Oops! You have two different commands.")
}
If you're not getting it yet, this:
C#:
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
should be this:
C#:
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
Either that or you should create the data adapter as you are and then get the command from it to add the parameters:
C#:
var cmd = adapter.SelectCommand;
 
As for the issue, you're adding parameters to a command but then you don't execute that command. Do this:
C#:
cmd = new SqlCommand(query, connection);

// ...

SqlDataAdapter adapter = new SqlDataAdapter(query, connection);

if (adapter.SelectCommand == cmd)
{
    Console.WriteLine("You're executing the one and only command.")
}
else
{
    Console.WriteLine("Oops! You have two different commands.")
}
If you're not getting it yet, this:
C#:
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
should be this:
C#:
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
Either that or you should create the data adapter as you are and then get the command from it to add the parameters:
C#:
var cmd = adapter.SelectCommand;
ahhhh i see. Yeah that makes sense, I cant believe I never noticed that myself. Thank you dude works perfectly :)
 

Latest posts

Back
Top Bottom