Binding data from dateTimePicker to a variable

[beginerC#]

Hi!
I have a form with two dateTimePickers (first with date of one month ago, and the secod with current date), a dataGridView and a button.
When i click the button i want to populate the dataGridView, but only between date1 and date2

mySqlCommand = con.CreateCommand();
mySqlCommand.CommandText = "SELECT * FROM MyTable WHERE (MyDate BETWEEN @date1 AND @date2)";
mySqlCommand.Parameters.Add("@date1", SqlDbType.Char);
mySqlCommand.Parameters["@date1"].Value = dateTimePicker1.Text.ToString();
mySqlCommand.Parameters.Add("@date2", SqlDbType.Char);
mySqlCommand.Parameters["@date2"].Value = dateTimePicker2.Text.ToString()

I get this error "Must declare the scalar variable "@date1".
Can anyone help me, please?

[sknake]

What type of SQL Connection is that? MSSQL? MySQL? Why are you declaring the command parameter as a char if it is a date value?

[beginerC#]

What type of SQL Connection is that? MSSQL? MySQL? Why are you declaring the command parameter as a char if it is a date value?

* i use MSSQL
* in the table mydate is declared as varchar(10) - i think i can change it

[sknake]

You need to change it. Using the >= and <= operators on strings doesn't always work the same as dates...

[beginerC#]

You need to change it. Using the >= and <= operators on strings doesn't always work the same as dates...

OK, i change it
but i get the same error: Must declare the scalar variable "@date1".

[sknake]

With mySQL you need to use "?" for parameter names. Try changing this line:

mySqlCommand.CommandText = "SELECT * FROM MyTable WHERE (MyDate BETWEEN @date1 AND @date2)";

To:

mySqlCommand.CommandText = "SELECT * FROM MyTable WHERE (MyDate BETWEEN ? AND ?)";

[beginerC#]

With mySQL you need to use "?" for parameter names. Try changing this line:

mySqlCommand.CommandText = "SELECT * FROM MyTable WHERE (MyDate BETWEEN @date1 AND @date2)";

To:

mySqlCommand.CommandText = "SELECT * FROM MyTable WHERE (MyDate BETWEEN ? AND ?)";

ERROR: Incorrect syntax near '?'.

[sknake]

I give up :(

I don't know anything about mySQL. Sorry I couldn't be of more help. I try to stick with MSSQL.

[beginerC#]

I give up :(

I don't know anything about mySQL. Sorry I couldn't be of more help. I try to stick with MSSQL.

:(

[beginerC#]

I use MSSQL ... not MySQL

[sknake]

Actually post the complete code. Are you using an OleDb or ODBC driver? OleDb supports named parameters depending on the driver but ODBC does not. Post where you are instantiating the database connection.

[sknake]

Oh, try something like this:

private void button2_Click(object sender, EventArgs e)
    {
      const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      const string query = "Select * From Invoice Where (InHomeDate BETWEEN @Date1 and @Date2)";
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.Parameters.Add(new SqlParameter("@Date1", SqlDbType.DateTime)).Value = DateTime.Today.AddDays(-180);
          cmd.Parameters.Add(new SqlParameter("@Date2", SqlDbType.DateTime)).Value = DateTime.Today;
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            DataTable dt = new DataTable();
            dt.Load(dr);
            dataGridView1.DataSource = dt;
          }
        }
      }
    }

[beginerC#]

Oh, try something like this:

private void button2_Click(object sender, EventArgs e)
    {
      const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      const string query = "Select * From Invoice Where (InHomeDate BETWEEN @Date1 and @Date2)";
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.Parameters.Add(new SqlParameter("@Date1", SqlDbType.DateTime)).Value = DateTime.Today.AddDays(-180);
          cmd.Parameters.Add(new SqlParameter("@Date2", SqlDbType.DateTime)).Value = DateTime.Today;
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            DataTable dt = new DataTable();
            dt.Load(dr);
            dataGridView1.DataSource = dt;
          }
        }
      }
    }

Hey!! Thanks a lot, it works! \:d/ - I get no error

[beginerC#]

the code was

mySqlCommand.Parameters.Add(new SqlParameter("@date1", SqlDbType.DateTime));
mySqlCommand.Parameters["@date1"].Value = dateTimePicker1.Value;

for each one @date

[DdoubleD]

Please mark as SOLVED unless you are still having trouble.

[beginerC#]

Sorry, i forgot to mark this as solved.
Thanks a lot, sknake!