Need some assistance with parameterized queries in visual studio.

[sock1992]

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

 {
                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();
              
            }

 

[jmcilhinney]

Firstly, @dateToo and @travelToo should be @dateTo and @travelTo. "Too" has only one meaning and that is "as well".

 

[jmcilhinney]

As for the issue, you're adding parameters to a command but then you don't execute that command. Do this:

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:

SqlDataAdapter adapter = new SqlDataAdapter(query, connection);

should be this:

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:

var cmd = adapter.SelectCommand;

 

[sock1992]

As for the issue, you're adding parameters to a command but then you don't execute that command. Do this:

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:

SqlDataAdapter adapter = new SqlDataAdapter(query, connection);

should be this:

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:

var cmd = adapter.SelectCommand;

ahhhh i see. Yeah that makes sense, I cant believe I never noticed that myself. Thank you dude works perfectly