locked
Paramaterizing Date in Access Insert query RRS feed

  • Question

  • User224181609 posted

    This is driving me crazy. I have an Access Insert query that I have paramaterized  and everything works except the Date paramaterization.

    I am showing the code below.  The last parameter assigned is a date called "RegDate". In the code below I show two paramaterizations for RegDate, with one of them commented-out

    When I run the code as shown. with an attempt to put in the specific date 1/12/2016, the code runs and a database record is inserted with all the paramaterized values inserted as they should be except the RegDate value, which shows a value of 12/30/1899 12:00:03 AM which is clearly wrong.  But at least all the other data is inserted and correct.

    But I instead run the code with the commented-out parameterization for RegDate of "Date.Now.ToOADate". nothing at all happens, no record is inserted.

    I am using an Access 2000 database, and with Date/Time for the datatype.

    Can someone point me to the problem?  Here is the code:

     Dim SQLEmail As String
            Dim Con As New OleDb.OleDbConnection
            SQLEmail = "insert into registrations (FName, BadgeName, LName, Classification, Club, District, Street, City, State, ZIP, Choir, CasinoNight, Golf, Interpreter, Title, DietNeed, Code,AmtPaidChips, AmtPaidRegistration, RegDate) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )"
    
            Dim cmd As New OleDb.OleDbCommand
            cmd = New OleDb.OleDbCommand(SQLEmail, Con)
    
            cmd.Parameters.AddWithValue("@FName", TxtBxFName.Text)
            cmd.Parameters.AddWithValue("@BadgeNane", TxtBxBadgeName.Text)
            cmd.Parameters.AddWithValue("@LName", TxtBxLName.Text)
            cmd.Parameters.AddWithValue("@Classification", TxtBxClassification.Text)
            cmd.Parameters.AddWithValue("@Club", DDLClubs.SelectedValue)
            cmd.Parameters.AddWithValue("@District", TxtBxDistrict.Text)
            cmd.Parameters.AddWithValue("@Street", TxtBxStreet.Text)
            cmd.Parameters.AddWithValue("@City", TxtBxCity.Text)
            cmd.Parameters.AddWithValue("@State", TxtBxState.Text)
            cmd.Parameters.AddWithValue("@ZIP", TxtBxZip.Text)
            cmd.Parameters.AddWithValue("@Choir", LblChoir.Text)
            cmd.Parameters.AddWithValue("@CasinoNight", LblCasino.Text)
            cmd.Parameters.AddWithValue("@Golf", LblGolf.Text)
            cmd.Parameters.AddWithValue("@Interpreter", LblInterpreter.Text)
            cmd.Parameters.AddWithValue("@Title", DDLTitles.SelectedValue)
            cmd.Parameters.AddWithValue("@DietNeed", TxtBxDietNeed.Text)
            cmd.Parameters.AddWithValue("@Code", TxtBxDignitaryCode.Text)
            cmd.Parameters.AddWithValue("@AmtPaidChips", LblChipsCost.Text)
            cmd.Parameters.AddWithValue("@AmtPaidRegistration", LblRegistrationCost.Text)
            'cmd.Parameters.AddWithValue("@RegDate", Date.Now.ToOADate)
            cmd.Parameters.AddWithValue("@RegDate", 1 / 12 / 2016)
    
            Con.ConnectionString = "PROVIDER = Microsoft.Jet.OLEDB.4.0; Data Source = |DataDirectory|Conference2017Registration.mdb"
    
            Con.Open()
    
            cmd.ExecuteNonQuery()
    
            Con.Close()

    Saturday, October 1, 2016 10:13 PM

Answers

  • User-1716253493 posted
    cmd.Parameters.AddWithValue("@RegDate", New DateTime(2016, 12, 1))
    cmd.Parameters.AddWithValue("@RegDate", DateTime.Parse("1/12/2016"))



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 2, 2016 1:11 AM
  • User224181609 posted

    Thank you for taking the time to reply.

    I am trying to add the current date, and a slight modification of your suggestion worked:

    cmd.Parameters.AddWithValue("@RegDate", Date.Parse(Date.Now))

    Thank you, that solved the problem.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 2, 2016 12:03 PM

All replies

  • User-1716253493 posted
    cmd.Parameters.AddWithValue("@RegDate", New DateTime(2016, 12, 1))
    cmd.Parameters.AddWithValue("@RegDate", DateTime.Parse("1/12/2016"))



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 2, 2016 1:11 AM
  • User224181609 posted

    Thank you for taking the time to reply.

    I am trying to add the current date, and a slight modification of your suggestion worked:

    cmd.Parameters.AddWithValue("@RegDate", Date.Parse(Date.Now))

    Thank you, that solved the problem.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 2, 2016 12:03 PM
  • User-1716253493 posted

    Hi, you don't need to parse it if datetime type already

    Ensure you pass datetime value, because 1/12/2016 is not date type, it is integer or double, "1/12/2016" is string. etc

    Date.Now is already datetime type i guess.

    Sunday, October 2, 2016 1:13 PM
  • User224181609 posted

    Well I already tried Date.Now and Date.Now.ToOADate (suggested in another thread) and could never get either to work.

    Whatever the reason, parsing Date.Now makes it work.  I agree with you that it seems just Date.Now should work. But it doesn't, so I will use the Parse.

    Sunday, October 2, 2016 11:35 PM