C#NET Sql striing using Date Between

[LennieKuah]

Hullo Good Friends,
I need your help. Please Help me. Thank You.

I encounter a very interesting problem regarding Window Application using C#NET3.5, SqlServer2000 with SQL string using Where Statement with DATE BETWEEN statements

I am using date parameters from 2 input textbox to retrieve Customer Invoice table to fill the DataGridView and it’s not working as the problem seem
to be caused my the
SQL String Statement : OrderDate Between DateFrom and DateTo


Error message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value


private void FFillDataGridView()
{
DateTime DateFrom =Convert.ToDateTime(this.txtDateFrom.Text);<-causing error
DateTime DateTo =Convert.ToDateTime(this.txtDateTo.Text); <- causing errror message
string strsql = " Select OrderID, ProductID, ";
strsql += " Convert(varchar(10), OrderDate, 103) as [OrderDate], ";
strsql += " Convert(Numeric(10,2), unitprice, 2) as [UnitPrice], ";
strsql += " From tblInvoices ";
strsql += " Where (CustomerID = '" + strCustID + "')";
strsql += " AND (OrderDate between DateFrom + "'"; <-- not working
strsql += " AND '" + DateTo + "')"; <-- not working

try
{
sqlconn = new SqlConnection(connstr);
sqlconn.Open();
DA = new SqlDataAdapter(strsql, sqlconn);
DS = new System.Data.DataSet();
DS.Clear();
DA.Fill(DS, "Invoice");
this.dataGridView1.DataSource = DS.Tables["Invoice"];
}
Catch (Exceptional Ex)
{
messagebox.show(ex.message);
}
}

Thank you for helping me.
Cheerws,
Lennie

[Talikag]

you need to concatinate DateFrom to the string. plus, you missed a ' character in the beginning of the date.
try this:

//...
strsql += " AND (OrderDate BETWEEN '" + DateFrom + "'";
strsql += " AND '" + DateTo + "')";

by the way, you might want to consider using parameters instead of concatenating values.

[careboy]

Thanks man.

[Lennie]

Hullo Talikag,
Prior to my original posting here I have use the sample coding that you share with me and it was not working. So I tried different technic and I am very confused that's why I post my problem here seeking for help

//...
strsql += " AND (OrderDate BETWEEN '" + DateFrom + "'";
strsql += " AND '" + DateTo + "')";

[Arjay]

Use DateTime.Parse() instead of Convert.ToDateTime().