none
SqlDataAdapter datetime error RRS feed

  • Question

  • Problem is SQLDataAdapter is parsing a date time parameter incorrectly

     

    example code

    c.Connection = cn;

    c.CommandType = CommandType.StoredProcedure;

    c.CommandText = "up_Usage_Browse";

    c.Parameters.Add("@datefrom", SqlDbType.DateTime);

    c.Parameters["@datefrom"].Value = DateTime.Now;

    SqlDataAdapter da = new SqlDataAdapter(c);

    DataSet ds = new DataSet();

     

    from SQL profiler this normally parses (this is done by ADO.net)

    exec up_Usage_Browse @datefrom = 'Jul 30 2007  8:08:27:530AM'

     

    on another database (same instance of SQL server) it parses

    exec up_Usage_Browse @datefrom = '30/07/2007  8:08:27:530AM'

    Which fails.

     

    Why is ADO.net using a different date time format?

     

     

     

     

     

    Sunday, July 29, 2007 10:15 PM

All replies

  • Do you have only one place in your application that makes that call to the stored procedure or you have multiple places?

    Monday, July 30, 2007 10:55 AM
    Moderator
  • A Single location

    The sample code is essentially the same but the in the real code it is in a class that implements the IDataServiceProvider interface. This is well developed and has been in production for +2 years and is run on different databases with a different connection string without any problems.


    Monday, July 30, 2007 9:13 PM
  • No concatenation of the date value to the query? I see that you are using parameter in this specific case, but maybe other place concatenates the value? Do you switch between the databases inside of same application? If yes, how do you switch between them, opening new connection with new connection string?

    Tuesday, July 31, 2007 10:26 AM
    Moderator
  • There is only one class that runs the get dataset command this is called using reflection and the database connection string is a property of the derived class. 

     

    All SQL statements are via parameterised SQL statements and are mainly stored procedures.

     

    The problem seems to have been fixed by droping and re-creating the stored procedure.

     

    Tuesday, July 31, 2007 11:47 AM