locked
Help with date Mismatch RRS feed

  • Question

  • User524898530 posted

    Hello People,
     I am developing web application using csharp. I have writtern a sql that is pulling data from
    Microsoft Access Database. Iam subitting two dates to an sql query. The dates are in format dateg.string(dd-MMM-yyy) . I am getting the error that date Mismatch. How can I solve this problem or avoid
    this problem.
    Please help

    Wednesday, April 21, 2010 9:43 AM

Answers

  • User1633968479 posted

    use # for datetime values and ' for string

    your sql should look like this: "...BETWEEN #" & MyDate1 & "# AND #" & MyDate2 & "# ..." 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 22, 2010 7:01 AM
  • User614805505 posted

    Dear Mobzam,

    It gv a way for u to add parameter to a command text (sql statement).

    If u assign the parameter through this way with the correct datatype define for it, u will not hv those formatting problem.

    Pls refer:

    http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter%28v=VS.80%29.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 22, 2010 7:02 AM
  • User-1561814533 posted

    By adding the parameters with .Add you can specify the type, i.e. OleDbType.Date

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 22, 2010 7:07 AM
  • User-1199946673 posted

    what are Use parameterized queries?


     

    It would be nice if you would read if someone is answering your question! Once again:

     

    Read the article and you'll understand what parameterized queries are, why you should use them and what the benefits are

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 22, 2010 1:07 PM

All replies

  • User614805505 posted

    Dear Mobzam,

    Iam subitting two dates to an sql query. The dates are in format dateg.string(dd-MMM-yyy)

    ^^. Can show ur code?

    Wednesday, April 21, 2010 10:32 AM
  • User-1199946673 posted

    Use parameterized queries:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    Dim ConnString As String = Utils.GetConnString()
    Dim SqlString As String = "Update tablename Set fieldname = ?"
    Using conn As New OleDbConnection(ConnString)
      Using cmd As New OleDbCommand(SqlString, conn)
        cmd.Parameters.AddWithValue("Fieldame", Now.ToOADate)
        conn.Open()
        cmd.ExecuteNonQuery()
      End Using
    End Using
    


    As you see, I changed the datetime to an Ole Automation Date, because Access cannot deal with the miliseconds

    http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET 

    Wednesday, April 21, 2010 11:42 AM
  • User524898530 posted

    This the code with a query I am using

      resortdbconn.Open();
    
            // query for insertion 
            // declaring dates
           String Fromdate = BBFIRSTDATE.Text;
           String Todate = BBSECONDDATE.Text;
          DateTime vaFromdate = DateTime.Parse(DateTime.Today.ToString());
           DateTime vaTodate = DateTime.Parse(DateTime.Today.ToString());
    
            ////linking string to date valuables
          vaFromdate = DateTime.Parse(Fromdate);
         vaTodate = DateTime.Parse(Todate);
          DateTime finalvaFromdate = DateTime.Parse(vaFromdate.ToString("dd-MMM-yy"));
         DateTime finalvaTodate = DateTime.Parse(vaTodate.ToString("dd-MMM-yy"));
    
            //String query = "SELECT * FROM BOOKING";
           String query = "SELECT * FROM BOOKING  WHERE TRANSACTIONDATE BETWEEN '" + finalvaFromdate + "' AND '" + finalvaTodate + "'";
           
            OleDbCommand cmdSELECT = new OleDbCommand(query,resortdbconn);
            if (resortdbconn.State == ConnectionState.Closed)
                resortdbconn.Open();
            GridView1.DataSource = cmdSELECT.ExecuteReader(CommandBehavior.CloseConnection);
            GridView1.DataBind();




    Thursday, April 22, 2010 5:07 AM
  • User614805505 posted

    Dear Mobzam,

  •      String Fromdate = BBFIRSTDATE.Text;  
  •      String Todate = BBSECONDDATE.Text;  


Change this 2 control to other control like datetime edit. It just save a lot of time on formatting.... and it should be in this way....

the Use parameterized queries:

suggestted at previous post is the way that u should go for.


Thursday, April 22, 2010 6:07 AM
  • User524898530 posted

    what are Use parameterized queries?


    Thursday, April 22, 2010 6:44 AM
  • User1633968479 posted

    use # for datetime values and ' for string

    your sql should look like this: "...BETWEEN #" & MyDate1 & "# AND #" & MyDate2 & "# ..." 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 22, 2010 7:01 AM
  • User614805505 posted

    Dear Mobzam,

    It gv a way for u to add parameter to a command text (sql statement).

    If u assign the parameter through this way with the correct datatype define for it, u will not hv those formatting problem.

    Pls refer:

    http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter%28v=VS.80%29.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 22, 2010 7:02 AM
  • User-1561814533 posted

    By adding the parameters with .Add you can specify the type, i.e. OleDbType.Date

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 22, 2010 7:07 AM
  • User-1199946673 posted

    what are Use parameterized queries?


     

    It would be nice if you would read if someone is answering your question! Once again:

     

    Read the article and you'll understand what parameterized queries are, why you should use them and what the benefits are

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 22, 2010 1:07 PM