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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.