none
Set a date/time in an MS Access Database from VB.net RRS feed

  • Question

  • Hi

    Im constatly running into problems concerning database handeling. But here goes:

    I have a field that is on a date/time format in the Access database. I have read it as a string from the database and stored the value. Now I want to update the date, and it gives me an error saying that it has a datatype mismatch.

    So here is how I assign the value:

     OleDbupd.Parameters.AddWithValue("@Dato", If(Not obj.Dato = Nothing, GetDateFromString(obj.Dato).ToOADate, DBNull.Value))

    But that dosent work. Does anybody know how to assign the Date in VB to a Date/Time in Accsess?

    I have tried to just write the string also, but same result.

    The format in the database is 01.01.2000

     


    Kenneth
    • Moved by VMazurModerator Wednesday, August 17, 2011 10:06 PM (From:ADO.NET DataSet)
    Monday, August 15, 2011 1:18 PM

Answers

  • I used the following C# (I forgot you were using VB -- I'll try to convert it later if necessary) code to modify a date/time field in the Nortwind database without issue.

    public void ChangeACEdata()
    {
      const string cnnString = "Provider=Microsoft.ACE.OLEDB.12.0" 
                    + ";Mode=Share Deny None" 
                    + @";Data Source=|DataDirectory|\Northwind 2010.accdb";
          
      DataTable dt = new DataTable();
      OleDbConnection cnn = new OleDbConnection(cnnString);
          
      //Read in the data
      using (OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Orders WHERE [Order Id] = 30", cnn))
      {
        da.Fill(dt);
      }
    
      // do what ever it is you want to do with your data, but store it in a string of the same format
      // you specified.
      String myDateString = ((DateTime)dt.Rows[0]["Order Date"]).ToString("MM.dd.yyyy");
    
      //Write a change to the date
      try
      {
        using (OleDbCommand cmd = new OleDbCommand())
        {
          cmd.Connection = cnn;
          cmd.CommandText = "UPDATE Orders SET [Order Date] = @OrderDate WHERE [Order ID]=@OrderId";
          cmd.Parameters.AddWithValue("@OrderDate", DateTime.Parse(myDateString.Replace('.','/')).Date);
          cmd.Parameters.AddWithValue("@OrderId", dt.Rows[0]["Order Id"]);
    
          cmd.Connection.Open();
          cmd.ExecuteNonQuery();
          cmd.Connection.Close();
        }
      }
      catch (Exception e)
      {
        throw e;
      }
      finally
      {
        cnn.Dispose();
      }
    }
    


     


    Brent Spaulding | Access MVP
    Tuesday, August 16, 2011 3:22 PM

All replies

  • Hmm...

     

    I have search and search on the web, it seems to be a nightmare to get this to work...

    I have also tried this, but got no result:  

    Dim f As New OleDbParameter
                        f.ParameterName = "@Dato"
                        f.OleDbType = OleDbType.Date
                        f.Value = obj.GetDateFromString(obj.Dato).ToOADate
                        OleDbupd.Parameters.Add(f)

    One would presum that ToOADate would return the correct representation but it does not seem to be true.


    Kenneth
    Monday, August 15, 2011 3:09 PM
  • https://connect.microsoft.com/VisualStudio/feedback/details/94377/oledbparameter-with-dbtype-datetime-throws-data-type-mismatch-in-criteria-expression#

    One problem, I cant get the workaroumd to work either...


    Kenneth
    Monday, August 15, 2011 3:28 PM
  •          Dim f As New Date
                    f = obj.GetDateFromString(obj.Dato)
                    Dim org As New DateTime(f.Year, f.Month, f.Day)
                    
                    Dim ff As New OleDbParameter
                    ff = CreateParameter(OleDbupd, "@Date", org)

    with:

     Public Shared Function CreateParameter(ByVal command As IDbCommand, ByVal name As String, ByVal value As DateTime) As IDbDataParameter
            If command Is Nothing Then
                Throw New ArgumentNullException("command")
            End If

            Dim parameter As IDbDataParameter = command.CreateParameter()
            If name IsNot Nothing AndAlso 0 <> name.Length Then
                parameter.ParameterName = name
            End If

            Dim p As OleDbParameter = TryCast(parameter, OleDbParameter)
            If p Is Nothing Then
                parameter.DbType = DbType.DateTime
            Else
                p.OleDbType = OleDbType.[Date]
            End If

            parameter.Value = value

            command.Parameters.Add(parameter)
            Return parameter
        End Function

    This woudnt work either....
    Kenneth
    Monday, August 15, 2011 3:42 PM
  • Some of these issues you are dealing with is why I have often chose to build the CommanText in-line, especially with dealing with strong data types that are not String as the source of your parameter values.  Here is an example of using a date in the criteria of CommandText.

    .CommandText = "SELECT * FROM someTable WHERE someDate = #" & DateTime.Parse(obj.Dato).ToString("yyyy-MM-dd") & "#"

    It is important to note that the Jet/ACE database engine must have literal dates (the string between the octothorpse) in an unambiguous form, if the date is ambiguous, the US format is assumed.


    Brent Spaulding | Access MVP

    • Edited by datAdrenalineMVP Monday, August 15, 2011 6:16 PM modified SQL statement a bit
    Monday, August 15, 2011 4:07 PM
  • >> I have a field that is on a date/time format in the Access database.

    .... The format in the database is 01.01.2000 <<

    So ... What is the datatype of the field in the Jet/ACE table? (is it Text or Date/Time). Note that a display Format is much much different than a datatype.  Actually the Format has nothing to do with the value that is stored in a date/time datatyped field.

    Also, what is the code from GetDateTimeFromString() returning?  I assume it is a DateTime object right?


    Brent Spaulding | Access MVP

    Monday, August 15, 2011 6:06 PM
  • Side note ... ToOADate returns a floating point numeric value (Double), so I *think* you would want to set your parameter type to OleDbType.Double.  With that, note that Date/Time datatyped columns in Jet/ACE store numeric values that are of the Double structure.

    Dim f As New OleDbParameter
                        f.ParameterName = "@Dato"
                        f.OleDbType = OleDbType.Double
                        f.Value = obj.GetDateFromString(obj.Dato).ToOADate
                        OleDbupd.Parameters.Add(f)


    Brent Spaulding | Access MVP
    Monday, August 15, 2011 8:01 PM
  • Nop

    Non of the code vorked, and the value in the database is on Date/Time format.

     Dim f As String = "#" & DateTime.Parse(obj.Dato).ToString("yyyy-MM-dd HH:mm:ss") & "#"
     Dim f As String = "#" & DateTime.Parse(obj.Dato).ToString("yyyy-MM-dd") & "#"
     Dim f As String = "#" & DateTime.Parse(obj.Dato).ToString("dd-MM-yyyy HH:mm:ss") & "#"
    Dim f As String = "#" & DateTime.Parse(obj.Dato).ToString("dd-MM-yyyy HH:mm:ss") & " AM #"
    Dim f As String = "# " & DateTime.Parse(obj.Dato).ToString("MM-dd-yyyy") & " #"

    And you can also do this:

                        OleDbupd.Parameters.AddWithValue("@Date", Convert.ToDateTime(obj.Dato).ToString("dd.MM.yyyy HH:mm:ss"))


    But non helps... Can anybody produce a code that passes Date/Time to a database in VB.net?

    These all produced readeble dates with the line : Debug.Writeline(f) but the database keeps complaining that it is not a DateTime object.

    Im running out of things to test now. I can ofcourse change the data to text in the database. But Id still like to know how to pass a Date/Time object to the database.


    Kenneth

    Tuesday, August 16, 2011 7:50 AM
  • I used the following C# (I forgot you were using VB -- I'll try to convert it later if necessary) code to modify a date/time field in the Nortwind database without issue.

    public void ChangeACEdata()
    {
      const string cnnString = "Provider=Microsoft.ACE.OLEDB.12.0" 
                    + ";Mode=Share Deny None" 
                    + @";Data Source=|DataDirectory|\Northwind 2010.accdb";
          
      DataTable dt = new DataTable();
      OleDbConnection cnn = new OleDbConnection(cnnString);
          
      //Read in the data
      using (OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Orders WHERE [Order Id] = 30", cnn))
      {
        da.Fill(dt);
      }
    
      // do what ever it is you want to do with your data, but store it in a string of the same format
      // you specified.
      String myDateString = ((DateTime)dt.Rows[0]["Order Date"]).ToString("MM.dd.yyyy");
    
      //Write a change to the date
      try
      {
        using (OleDbCommand cmd = new OleDbCommand())
        {
          cmd.Connection = cnn;
          cmd.CommandText = "UPDATE Orders SET [Order Date] = @OrderDate WHERE [Order ID]=@OrderId";
          cmd.Parameters.AddWithValue("@OrderDate", DateTime.Parse(myDateString.Replace('.','/')).Date);
          cmd.Parameters.AddWithValue("@OrderId", dt.Rows[0]["Order Id"]);
    
          cmd.Connection.Open();
          cmd.ExecuteNonQuery();
          cmd.Connection.Close();
        }
      }
      catch (Exception e)
      {
        throw e;
      }
      finally
      {
        cnn.Dispose();
      }
    }
    


     


    Brent Spaulding | Access MVP
    Tuesday, August 16, 2011 3:22 PM
  • Yes

     

    You are right It is working, thank you


    Kenneth
    Wednesday, August 17, 2011 10:25 AM
  • Awesome!!! I am thrilled to here of your success!
    Brent Spaulding | Access MVP
    Wednesday, August 17, 2011 11:54 AM