Error in select statement in C# code.

Question

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..

virendra a · Answer

sqlcommand="SELECT timein1,curstatus FROM report WHERE userid=" + userID + " and [date1]=" + System.DateTime.Today;

Scott Knake · Answer

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 }

Error in select statement in C# code.

Answers (2)