Getting error when running SQL to search for a range of records between month start and end.
SqlDataSource1 is defined for a GridView on web page.
---------------------------------------------------------------------------
protected void Page_Load(object sender, EventArgs e)
{
string sqltmp;
if (!IsPostBack)
{
DateTime now = DateTime.Now;
DateTime firstDate = new DateTime(now.Year, now.Month, 1);
monthNumber = Now.Month;
yearNumber = Now.Year;
if (monthNumber == 12)
{
monthNumber = 1;
yearNumber = yearNumber + 1;
}
else
{
monthNumber++;
}
DateTime lastDate = new DateTime(yearNumber, monthNumber, 1);
sqltmp = "SELECT TOP 10 [Trans_Date], [CaseLast] ";
sqltmp = sqltmp + "FROM Transactions ";
sqltmp = sqltmp + "WHERE ";
sqltmp = sqltmp + "( [Trans_Date] >= " + firstDate + " AND [Trans_Date] < " + lastDate + ")";
SqlDataSource1.SelectCommand = sqltmp;
}
}
-------------------------------------------------------------------------------------
Built SQL looks like this:
"SELECT TOP 10 [Trans_Date], [CaseLast] FROM Transactions WHERE ( [Trans_Date] >= 5/1/2014 12:00:00 AM AND [Trans_Date] < 6/1/2014 12:00:00 AM)"
--------------------------------------------------------------------------------------
Error: Incorrect syntax near '12'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '12'.
---------------------------------------------------------------------------------------
What did I forget?