Question Issue passing Now as OleDbParameter in Update Query

[VBobCat]

Hello people, I'd like you to help me figure out this.

I've added a field to one given table of my database. Its datatype is DateTime, for I want to store in it the date/time of each record's last update.

These updates were being successfully done with a SQL "UPDATE TABLE" query, passed with OleDbCommand class and OleDbParameters for each field to be updated.

When I added a new parameter, and set its value with Now (Public ReadOnly Property Now As Date, Member of Microsoft.VisualBasic.DateAndTime), my query started to fail, triggering this exception (sorry, I don't know how to make VS.IDE to send this in English, but the message says "Incompatible data type in the expression of criterium"):



The weird thing (though I'm satisfied because it solved the error) is that if I set OleDbParameter's value with Date.Parse(Now.ToString), it works fine.

I could keep this and think no more about it, but I'd rather understand what causes this behavior. It is quite peculiar, for both expressions, Now and Date.Parse() are typed as Date, and both show the same result when printed in VS.IDE's Immediate Window.

Thank you very much for your attention.

 

[Dunfiddlin]

VB's definition of the Date type is not the same as the database definition, the latter being literally a date without any time information. The compatible property is Now.Date

 

[jmcilhinney]

VB's definition of the Date type is not the same as the database definition, the latter being literally a date without any time information. The compatible property is Now.Date

Given that the OP said that the database data type is DateTime then I don't think that that's the issue, especially when the column is designed to store dates and times. Can you show us a screenshot of the table definition and also show us the code that is failing?

 

[VBobCat]

Ok, though I must warn that my database approach may seem strange (I use a dictionary to pass data to a custom class that handles the database). It works, however, and allows me to write code fast.

This is a test table, but I reproduced both the error and the workaround:



Now this is the code that commands data insertion. It inserts a new record, and stores the present date and time in field "DAT":

Private Sub Teste(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim R_Tst As New Dictionary(Of String, Object)
        'R_Tst.Add("DAT", Now)                      ' this fails
        R_Tst.Add("DAT", Date.Parse(Now.ToString))  ' this works
        T_Tst.Inserir(R_Tst)
    End Sub

It must be said that T_Tst is an instance of my custom class Tabela, which has this code:

Friend Class Tabela
    Friend Nome As String
    Friend Dados As DataTable
    Friend Campos As Dictionary(Of String, DataColumn)
    Private _preencher As Boolean

    Friend Sub New(ByVal nome_tabela As String, Optional ByVal preencher As Boolean = False)
        Nome = nome_tabela.Trim
        _preencher = preencher
        Refresh()
        Campos = New Dictionary(Of String, DataColumn)
        For Each dc As DataColumn In Dados.Columns
            Campos.Add(dc.ColumnName, dc)
        Next
    End Sub

    Friend Sub Refresh()
        Dados = BD.Consultar("SELECT * FROM " & Nome & " WHERE " & IIf(_preencher, "TRUE;", "FALSE;"))
    End Sub

    Friend Function Inserir(dic_campos_valores As Dictionary(Of String, Object)) As Integer
        Dim fields_list, values_list As New List(Of String), parametros As New List(Of OleDbParameter)
        For Each campo_existente In Campos
            If dic_campos_valores.ContainsKey(campo_existente.Key) AndAlso dic_campos_valores(campo_existente.Key) IsNot Nothing Then
                fields_list.Add(campo_existente.Key)
                values_list.Add("@" & campo_existente.Key)
                parametros.Add(New OleDbParameter("@" & campo_existente.Key, dic_campos_valores(campo_existente.Key)))
            End If
        Next
        BD.Executar("INSERT INTO " & Nome & " (" & Join(fields_list.ToArray, ",") & ") VALUES (" & Join(values_list.ToArray, ",") & ");", parametros.ToArray)
        Dim _result As Integer = BD.LerValor("SELECT @@IDENTITY")
        If _preencher Then Refresh()
        Return _result
    End Function

    Friend Function Alterar(ByVal primary_key As Integer, dic_campos_valores As Dictionary(Of String, Object)) As Integer
        Dim sets_list As New List(Of String), parametros As New List(Of OleDbParameter)
        For Each campo_existente In Campos
            If dic_campos_valores.ContainsKey(campo_existente.Key) Then
                sets_list.Add(campo_existente.Key & "=@" & campo_existente.Key)
                Dim valor As Object = DBNull.Value
                If dic_campos_valores(campo_existente.Key) IsNot Nothing Then valor = dic_campos_valores(campo_existente.Key)
                parametros.Add(New OleDbParameter("@" & campo_existente.Key, valor))
            End If
        Next
        Dim _result As Integer = BD.Executar("UPDATE " & Nome & " SET " & Join(sets_list.ToArray, ",") & " WHERE PK=" & primary_key.ToString & ";", parametros.ToArray)
        If _preencher Then Refresh()
        Return _result
    End Function

    Friend Function Selecionar(ByVal cumulativo As Boolean, dic_campos_valores As Dictionary(Of String, Object), Optional ByVal conexao As OleDbConnection = Nothing) As DataTable
        Dim operador As String = " OR "
        If cumulativo Then operador = " AND "
        Dim sets_list As New List(Of String), parametros As New List(Of OleDbParameter)
        For Each campo_existente In Campos
            If dic_campos_valores.ContainsKey(campo_existente.Key) Then
                sets_list.Add(campo_existente.Key & "=@" & campo_existente.Key)
                Dim valor As Object = DBNull.Value
                If dic_campos_valores(campo_existente.Key) IsNot Nothing Then valor = dic_campos_valores(campo_existente.Key)
                parametros.Add(New OleDbParameter("@" & campo_existente.Key, valor))
            End If
        Next
        Return BD.Consultar("SELECT * FROM " & Nome & " WHERE " & Join(sets_list.ToArray, operador) & ";", parametros.ToArray)
    End Function
End Class

Finally, this class takes its core operations from another custom class, from which BD is is the single instance along whole application's execution.

Friend Class DataBaseClass
    Friend WithEvents Conexao As OleDbConnection
    Friend WithEvents TimeOut As System.Timers.Timer

    Friend Sub New(ByVal arquivo As String)
        Dim caminhoDB As String = ""
        If FileIO.FileSystem.GetParentPath(arquivo) = "" Then
            Dim Pasta As String = My.Application.Info.DirectoryPath, PastaMae As String = ""
            Do
                PastaMae = FileIO.FileSystem.GetParentPath(Pasta)
                If FileIO.FileSystem.FileExists(PastaMae & "\" & arquivo) Then caminhoDB = PastaMae & "\" & arquivo : Exit Do
                Pasta = PastaMae
            Loop Until Pasta = System.IO.Path.GetPathRoot(Pasta)
        Else
            caminhoDB = arquivo
        End If
        Conexao = New OleDbConnection With {.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & caminhoDB & ";Jet OLEDB:Database Password=XXXXXXXXXXXXX;"}
        TimeOut = New System.Timers.Timer(10000)
    End Sub

    Friend Function Consultar(ByVal comando_SQL As String, ParamArray parametros As OleDbParameter()) As DataTable
        Dim Comando As OleDbCommand = GerarComando(comando_SQL, parametros), Tabela As New DataTable
        TimeOut.Stop() : TimeOut.Start()
        If Comando.Connection.State = ConnectionState.Closed Then Comando.Connection.Open()
        Try
            Tabela.Load(Comando.ExecuteReader)
            Return Tabela
        Catch ex As Exception
            Falha(comando_SQL, parametros, ex)
        End Try
        Return Nothing
    End Function

    Friend Function LerValor(ByVal comando_SQL As String, ParamArray parametros As OleDbParameter()) As Object
        Dim Comando As OleDbCommand = GerarComando(comando_SQL, parametros)
        TimeOut.Stop() : TimeOut.Start()
        If Comando.Connection.State = ConnectionState.Closed Then Comando.Connection.Open()
        Try
            Return Comando.ExecuteScalar
        Catch ex As Exception
            Falha(comando_SQL, parametros, ex)
        End Try
        Return Nothing
    End Function

    Friend Function Executar(ByVal comando_SQL As String, ParamArray parametros As OleDbParameter()) As Integer
        Dim Comando As OleDbCommand = GerarComando(comando_SQL, parametros)
        TimeOut.Stop() : TimeOut.Start()
        If Comando.Connection.State = ConnectionState.Closed Then Comando.Connection.Open()
        Try
            Return Comando.ExecuteNonQuery
        Catch ex As Exception
            Falha(comando_SQL, parametros, ex)
        End Try
        Return 0
    End Function

    Private Sub Falha(ByVal comando_SQL As String, ByRef parametros As OleDbParameter(), ByVal excecao As Exception)
        Stop
        End
        Exit Sub
    End Sub

    Private Function GerarComando(ByVal comando_SQL As String, ByRef parametros() As OleDbParameter) As OleDbCommand
        Dim comando As New OleDbCommand With {.Connection = Conexao, .CommandText = comando_SQL} 
For Each parametro As OleDbParameter In parametros
            If parametro IsNot Nothing Then comando.Parameters.Add(parametro)
        Next
        Return comando
    End Function

    Private Sub Conexao_StateChange(sender As Object, e As System.Data.StateChangeEventArgs) Handles Conexao.StateChange
        If Conexao.State = ConnectionState.Open Then
            TimeOut.Start()
        ElseIf Conexao.State = ConnectionState.Closed Then
            TimeOut.Stop()
        End If
    End Sub

    Private Sub TimeOut_Elapsed(sender As Object, e As System.Timers.ElapsedEventArgs) Handles TimeOut.Elapsed
        If Conexao.State = ConnectionState.Open Then
            Conexao.Close()
            TimeOut.Stop()
        End If
    End Sub
End Class

Ok, I'm almost sure that this code may seem outrageous for you who are far more experienced than I. But that was my best effort in order to have some code that could be wrote once and used many times without adaptations, so that I can write the rest of the application with little effort on commanding CRUD operations.

I am happy that it is working well, except for the issue with the Now object. Nevertheless, I will accept any criticism, of course. But please don't be so harsh on me... I'm struggling with all this the best I can.

Sorry that some objects have names in Portuguese. It would be a doubtful and painful process to translate all them. But it's only their names.

And finally, thank you very much for you kind attention.

 

[VBobCat]

Above all this, what I can't understand is why Now fails while Date.Parse(Now.ToString) succeeds, if they should expose the same type and data, as I tested on Immediate Window with these results:

? System.DateTime.Now
#9/12/2012 10:05:20 AM#

? System.DateTime.Now.GetType
{Name = "DateTime" FullName = "System.DateTime"}
    System.RuntimeType: {Name = "DateTime" FullName = "System.DateTime"}

? date.Parse(System.DateTime.Now.ToString)
#9/12/2012 10:05:30 AM#

? date.Parse(System.DateTime.Now.ToString).GetType
{Name = "DateTime" FullName = "System.DateTime"}
    System.RuntimeType: {Name = "DateTime" FullName = "System.DateTime"}

 

[cjard]

Yeah, i can't really think why it would be a problem, but I'm curious to know if it goes away when you create the parameter and also specify it to be a datetime, something like:

Friend Function Inserir(dic_campos_valores As Dictionary(Of String, Object)) As Integer
Dim fields_list, values_list As New List(Of String), parametros As New List(Of OleDbParameter)
For Each campo_existente In Campos
If dic_campos_valores.ContainsKey(campo_existente.Key) AndAlso dic_campos_valores(campo_existente.Key) IsNot Nothing Then
fields_list.Add(campo_existente.Key)
values_list.Add("@" & campo_existente.Key)

If TypeOf( dic_campos_valores(campo_existente.Key) ) Is DateTime Then
Dim odbp as New OleDbParameter("@" & campo_existente.Key[B],  system.data.oledb.oledbtype.dbdate [/B] ))
odbp.Value = DirectCast(dic_campos_valores(campo_existente.Key), DateTime)
parametros.add(odbp)
Else
parametros.Add(New OleDbParameter("@" & campo_existente.Key, dic_campos_valores(campo_existente.Key)))
End If



End If
Next
BD.Executar("INSERT INTO " & Nome & " (" & Join(fields_list.ToArray, ",") & ") VALUES (" & Join(values_list.ToArray, ",") & ");", parametros.ToArray)
Dim _result As Integer = BD.LerValor("SELECT @@IDENTITY")
If _preencher Then Refresh()
Return _result
End Function

 

[VBobCat]

Yeah, i can't really think why it would be a problem, but I'm curious to know if it goes away when you create the parameter and also specify it to be a datetime (...)

Yes, thank you, I will try that, and post the result as soon as possible.

 

[VBobCat]

[SOLVED] Issue passing Now as OleDbParameter in Update Query

It did not fail, indeed. But it cropped out the hourly part of Now, which I also need.

But when I replaced OleDbType.DBDate with OleDbType.Date as DataType of the new parameter, it started working all right.

So in this case (values of type DateTime) it does not seem safe to let the parameter to be created without specifying its type. In other words, it seems parameter type must be explicitly mapped to OleDbType.Date to correspond the closest to System.DateTime value type.

Thank you all folks!