none
Date condition is not working in command text parameter please help urgent RRS feed

  • Question

  • I have  given the following command in sql query

    DateTime todaysdate = new DateTime(2012, 09, 05);
    SqlCommand objCommand = objconnection.CreateCommand();
    objCommand.Parameters.AddWithValue("@EMPREF", empref);
    objCommand.Parameters.AddWithValue("@STARTDATE", todaysdate);
    objCommand.CommandText = "select EMPREF,STARTDATE from TMSFC Where EMPREF = @EMPREF and STARTDATE >= @STARTDATE";

    But when I run the query is  showing as follows . Please help how can I fix the error

    select EMPREF,STARTDATE  from TMSFC  Where EMPREF = 10472 and  STARTDATE >= @STARTDATE


    polachan

    Thursday, November 2, 2017 8:20 PM

All replies

  • Edit, I missed the first part where the first part of the where is not right. Have not seen this before.

    I ran this

    private void button2_Click(object sender, EventArgs e)
    {
        var empref = 10472;
    
        var objconnection = new SqlConnection();
        DateTime todaysdate = new DateTime(2012, 09, 05);
        SqlCommand objCommand = objconnection.CreateCommand();
        objCommand.Parameters.AddWithValue("@EMPREF", empref);
        objCommand.Parameters.AddWithValue("@STARTDATE", todaysdate);
        objCommand.CommandText = "select EMPREF,STARTDATE from TMSFC Where EMPREF = @EMPREF and STARTDATE >= @STARTDATE";
        Console.WriteLine(objCommand.CommandText);
    }

    Got this output

    select EMPREF,STARTDATE from TMSFC Where EMPREF = @EMPREF and STARTDATE >= @STARTDATE


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Thursday, November 2, 2017 9:04 PM
    Moderator

  • Hi polachan,

    You can use the following code to query MSSQL with objCommand.Parameters. I tested the code below and executed successfully.

                SqlConnection conn = new SqlConnection(strconn);
                conn.Open();
                int empref = 22;
                DateTime todaysdate = new DateTime(2012, 09, 05);
                SqlCommand objCommand = conn.CreateCommand();
                objCommand.CommandType = CommandType.Text;
                objCommand.CommandText = "select Name from Students Where Age = @EMPREF and STARTDATE >= @STARTDATE";
                var salaryParam = new SqlParameter("@EMPREF", SqlDbType.Int);
                salaryParam.Value = empref;
                objCommand.Parameters.Add(salaryParam);
                var datetimeParam = new SqlParameter("@STARTDATE", SqlDbType.DateTime);
                datetimeParam.Value = todaysdate;
                objCommand.Parameters.Add(datetimeParam);
                // you can run the code to test whether it can be executed successfully
                var ds = objCommand.ExecuteScalar();
                MessageBox.Show(ds.ToString());
    


    Your case is relate to the ADO.NET. If you have question about ADO.NET, please go to the ADO.NET Forum for getting suitable help.

    Best Regards,

    Yohann Lu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 3, 2017 6:53 AM
    Moderator
  • I have a hard time believing that the above code generated the query you're posting. There are variants of the above code that could do that though. Can you provide a screenshot showing the code running in the debugger along with a screenshot of the actual query text that was sent and what tool said you're getting that text?

    One of the reasons I'm questioning the query text from the code is that there is also extra spaces before STARTDATE which isn't in the query. Did this code happen to get changed and, perchance, isn't compiling but because you accidentally checked the option to run anyway (appears if you use F5 to build and run instead of compiling first) you're not seeing the error and still running the old code?


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, November 3, 2017 3:07 PM
    Moderator