pass now() as parameter

Hi,
I"m using VB.Net w/ VS2005 and an Access 2000 db.

I was getting a data type mismatch error when I tried set a datetime variable = Now(). Why does this work:

        Dim sqlClose As String = "INSERT INTO tbl_close_open (pc_name,close_tm,sortid) VALUES (@pc_name,@close_tm,@sortid)"
        Dim cmdClose As OleDb.OleDbCommand = New OleDb.OleDbCommand(sqlClose, New OleDb.OleDbConnection(sDbPath))

        Dim sClose As String = CStr(Now())
        Dim dtClose As DateTime = CDate(sClose)


        With cmdClose.Parameters
            .Add(New OleDb.OleDbParameter("@pc_name", psPCName))
            .Add(New OleDb.OleDbParameter("@close_tm", dtClose))
            .Add(New OleDb.OleDbParameter("@sortid", sSorter))
        End With

        cmdClose.Connection.Open()
        cmdClose.ExecuteNonQuery()
        cmdClose.Connection.Close()

And why does this Not work:

        Dim sqlClose As String = "INSERT INTO tbl_close_open (pc_name,close_tm,sortid) VALUES (@pc_name,@close_tm,@sortid)"
        Dim cmdClose As OleDb.OleDbCommand = New OleDb.OleDbCommand(sqlClose, New OleDb.OleDbConnection(sDbPath))

        Dim dtClose As DateTime =  Now()

        With cmdClose.Parameters
            .Add(New OleDb.OleDbParameter("@pc_name", psPCName))
            .Add(New OleDb.OleDbParameter("@close_tm", dtClose))  'error here.           .Add(New OleDb.OleDbParameter("@sortid", sSorter))
        End With

        cmdClose.Connection.Open()
        cmdClose.ExecuteNonQuery()
        cmdClose.Connection.Close()
coperations07Asked:
Who is Participating?
 
x77Commented:
The problem is the precision on VbNet DateTime (milliseconds).

You can trunc the date to seconds or minutes.

  Private Function TruncToSeconds(ByVal f As Date) As Date
      Return New Date(f.Ticks - f.Ticks Mod TimeSpan.TicksPerSecond)
  End Function

  cmd.Parameters.AddWithValue(Nothing, TruncToSeconds(Now))

Note: Oledb does a Type conversion from VbNet to Ado.Date type (a Double).
When precission on parameter to converts exceds the precision on destination type, throws a exception.

By Sample:
   On Oracle I store a value  1/3 on a column type Number (About 28 digits precission).
   When I try to get a data from database, it trhows a exception if I try to get a Double or Decimal Value.
0
 
käµfm³d 👽Commented:
Actually, I'm surprised any of them work, since the .NET OleDb implementation using question marks as placeholders rather than named parameters.

If you look at the Intellisense for the two-argument overload to Add(), you will see that the second parameter should be of type OleDbType (an enumeration). In truth, neither should work.
untitled.PNG
0
 
käµfm³d 👽Commented:
Shoot. Disregard the above. I was adding the parameter differently than you  : (
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
käµfm³d 👽Commented:
There's no reason why it shouldn't work, that I can think of. Are you getting the error during the addition of the parameter, or during execution of the query itself?
0
 
x77Commented:
You can also use the Access Function Now to avoid the Now Parameter:

          Dim cmd As New OleDb.OleDbCommand( _
              "Insert into Retenes(id,desde,hasta,emp) values (?,now,?,?)", cn)

On Oracle, the equivalent function is SysDate.
0
 
coperations07Author Commented:
Thanks for the replies guys!  You answered my question x77 and passing now like that is cool. I didn't know that could be done.  Less code, same results I like it a lot. Bonus points for you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.