Error in select statement in C# code.

moon lal

Hello Friends, Rite now i am developing an application for maintaining employee login,logout information. I am using a select statement where the condition is userid & systemtime. below is the code. sqlcommand="SELECT timein1,curstatus FROM report WHERE userid=" + userID; sqlcommand += " and [date1]="; sqlcommand+=System.DateTime.Today; commandSelect.CommandText = sqlcommand; readerSelect = commandSelect.ExecuteReader(CommandBehavior.CloseConnection); This is the exception iam getting. Regards, moon0315 ex = {"Syntax error (missing operator) in query expression 'userid=31 and [date1]=6/13/2009 12:00:00 AM'."} I have spent almost 7 hrs in figuring out the error.but no use. i guess the error is in system.datetime.today; Rite now i am clueless.. It would be so great if some one helps me... Pls help me..

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

virendra a17y ago
sqlcommand="SELECT timein1,curstatus FROM report WHERE userid=" + userID + " and [date1]=" + System.DateTime.Today;
Scott Knake17y ago
Building queries with values like that is dangerous and has a negative performance impact. Switch over to using parameterized queries and this will solve a lot of issues. Rename the columns and tables and this code should work fine for you:

public static string BuildSqlNativeConnStr(string server, string database)
{
return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
}
private void simpleButton1_Click(object sender, EventArgs e)
{
string query =
"Select *" + Environment.NewLine +
"From UserTable" + Environment.NewLine +
"Where RepNumber = @RepNumber and HireDate = @HireDate";

string connStr = BuildSqlNativeConnStr("apex2006sql", "Leather");
DataTable dt;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add(new SqlParameter("@RepNumber", 50));
cmd.Parameters.Add(new SqlParameter("@HireDate", DateTime.Today));
SqlDataReader dr = cmd.ExecuteReader();
dt = new DataTable();
dt.Load(dr);
}
}
System.Diagnostics.Debugger.Break(); //At this point you have the populated datatable
}