none
Dates / MS access and Sql Parameter Passing RRS feed

  • Question

  •  

    I am having difficulty passing a Visual Studio Visual Basic date data type as a parameter during a sql call using OleDbCommand to a MS Access database.

     

    I have no difficulty with Decimals, Strings and Currency data types. It seems there is something different when passing date data types.

     

    Can anyone give me a little help on this one.

     

    Paul

     

    Thursday, June 7, 2007 10:18 PM

All replies

  • What kind of difficulty are you experiencing? Can you show some code how you pass the date parameter?

     

     

    Thursday, June 7, 2007 10:50 PM
  •  

    Here is my code..... everything works except date data types  (Field names: Year, Acquired_on, last_Odometer_update and DOT_expiration_date). When I remove them from the code the update is successful.

     

     

    Return_code = 0

    Return_msg = ""

    Dim sqlconn As New OleDbConnection

    Dim sqlcommand As New OleDbCommand

    Dim transaction As OleDbTransaction

    sqlconn.ConnectionString = G_Connection_String

    sqlcommand.Connection = sqlconn

    sqlcommand.Connection.Open()

    sqlcommand.Parameters.Clear()

    transaction = sqlconn.BeginTransaction()

    sqlcommand.Transaction = transaction

     

    Dim SQL_Query_String As String

    Try

    SQL_Query_String = "UPDATE Trucks SET Asset_id = ?, VIN = ?, Truck_id = ?, Make = ?, Year = ?"

    SQL_Query_String = SQL_Query_String + ", Acquisition_cost = ?, Odometer = ?, Acquired_On = ?"

    SQL_Query_String = SQL_Query_String + ", Last_Odometer_Update = ?, Dot_Expiration_Date = ?"

    SQL_Query_String = SQL_Query_String + " WHERE id = " + Truck_table.Rows(G_curr_index)("Id") + ";"

    sqlcommand.Parameters.AddWithValue("asset_id", Truck_table.Rows(G_curr_index)("Asset id").ToString)

    sqlcommand.Parameters.AddWithValue("VIN", Truck_table.Rows(G_curr_index)("VIN").ToString)

    sqlcommand.Parameters.AddWithValue("Truck_id", Truck_table.Rows(G_curr_index)("Truck id").ToString)

    sqlcommand.Parameters.AddWithValue("Make", Truck_table.Rows(G_curr_index)("Make").ToString)

    sqlcommand.Parameters.AddWithValue("Year"Truck_table.Rows(G_curr_index)("Year"))

    sqlcommand.Parameters.AddWithValue("Acquisition_Cost", Truck_table.Rows(G_curr_index)("Acquisition Cost"))

    sqlcommand.Parameters.AddWithValue("Odometer", Truck_table.Rows(G_curr_index)("Odometer"))

    sqlcommand.Parameters.AddWithValue("Acquired_On", Truck_table.Rows(G_curr_index)("Acquired On"))

    sqlcommand.Parameters.AddWithValue("Last_Odometer_Update", Truck_table.Rows(G_curr_index)("Last Odometer Update"))

    sqlcommand.Parameters.AddWithValue("Dot_Expiration_Date", Truck_table.Rows(G_curr_index)("DOT Expiration Date"))

     

    sqlcommand.CommandText = SQL_Query_String

    Dim introwsaffected As Integer = sqlcommand.ExecuteNonQuery()

    sqlcommand.Transaction.Commit()

    Catch ex As Exception

    Return_code = 99

    Return_msg = "Error Trucks Module - Updating Truck Table" + ex.Message

    sqlcommand.Transaction.Rollback()

    sqlconn.Close()

    End Try

    sqlconn.Close()

    Thursday, June 7, 2007 11:20 PM
  • AddWithValue doesn't explicitly specify the type and you might consider adding the (DateTime) parameters with explicit type.

     

    sqlcommand.Parameters.Add(new OleDbParameter("Dot_Expiration_Date", OleDbType.Date);

    sqlcommand.Parameters["Dot_Expiration_Date"].Value = Truck_table.Rows(G_curr_index)("DOT Expiration Date");

    Thursday, June 7, 2007 11:44 PM
  • Andreas, Thank you for helping!!! I made the change as suggested and I examined the value of the parameter at run time and I found the following DBTYPE = System.Data.DbType.DateTime {6} OleDbType = System.Data.OleDb.OleDbType.DBTimeStamp {135} Value = #12/30/2007# It seems right but I am still getting an "syntax error in update statement". What am I missing?
    Friday, June 8, 2007 4:11 PM
  •  

     

    Just to see what happens, I tried to use your code suggestion with a type modification to Char 

     

    sqlcommand.Parameters.Add(new OleDbParameter("Dot_Expiration_Date", OleDbType.Char);

     

    At runtime I examined the code and it showed DBtype.datetime.... It seems to ignore they type and makes its own decision based upon the type of the input data. Is this true?

     

     

     

    Friday, June 8, 2007 4:23 PM
  • Do you have any possibility to monitor the query that gets sent to the database? If you use SQL Server you can use SQL Server Profiler to see the question that gets sent to the server.

     

    EDIT: Just noticed it was ms access so not sure if it is possible, I will do some testing to see if I can figure it out.

     

    I might be incorrect that you need to set the query with specified type and there is something else going on.

    Saturday, June 9, 2007 7:11 PM
  • I was about to post that I was able to reproduct this problem and I was but a final look at other postings about this I found the issue. You should set the parameter to be type OleDbType.Date.

     

    I added a table to an access database with a column named DateValue of type Date/Time and this is how I was able to insert values to it with parameterized query.

     

    Code Snippet

    OleDbConnection cnn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb;Persist Security Info=False;");

    cnn.Open();

    OleDbCommand cmd = cnn.CreateCommand();

    cmd.CommandText = "insert into Table1 (DateValue) values (?)";

    OleDbParameter param = cmd.Parameters.Add("@p1", OleDbType.Date);

    param.Value = DateTime.Now;

    try

    {

      int result = cmd.ExecuteNonQuery();

    }

    catch (OleDbException e2)

    {

      System.Diagnostics.Debug.WriteLine(e2.Message);

    }

     

    I hope this helps you further.

     

     

    Saturday, June 9, 2007 7:59 PM