none
Passing Date/Time to an Access DB with Visual C++ RRS feed

  • Question

  • I have an access database that I'd like to send the current date and time too. I'd also like the ability to send a specfic date generated by the program. What I've tried to do is as follow:

    Code Block

    String* newAlgorithmQuery = String::Concat(S"INSERT INTO Algorithms(CompanyName, AlgorithmName, DateReceived) VALUES ('", theCompanyName, S"', '", AlgorithmInfo, S"', ????????)");


    That's all one line of code.

    I know the first parts work, but I can't get the date to send correctly. Can anyone help me out with what I should put in place of the ?????

    The only thing I've tried thus far is a string:
    '1/1/2001 1:11 PM'

    and the same thing with #'s:
    #1/1/2001 1:11 PM#


    Also, I'd like to use a DateTimePicker control to return a DateTime and send it as well. Any ideas?

    Thanks in advance,
    Jesse Musgrove
    Wednesday, January 9, 2008 6:36 PM

Answers

  • Are you receiving an error when you execute the last example you posted. The syntax looks OK to me.

     

    The recommended method is to use a Command object with Parameters. This way you don't have to be concerned with syntax quirks when using variable inserted parameters. Below is an example which uses a Command object w/Parameters (sorry I don't have a C++ example). I believe the DateTimePicker returns text so you will want to convert that to a date/time data type when using parameters.

     

    Code Block

    Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=C:\Test Files\db1 XP.mdb")

     

    Dim AccessCommand As New OleDbCommand("SELECT * FROM Table1 WHERE [Date Field] BETWEEN ? AND ?", AccessConnection)

     

    AccessConnection.Open()

     

    Dim StartDate As New System.DateTime(2005, 1, 25)

    Dim EndDate As New System.DateTime(2006, 5, 5)

     

    AccessCommand.Parameters.Add("@DateValStart", System.Data.OleDb.OleDbType.DBDate).Value = StartDate

    AccessCommand.Parameters.Add("@DateValEnd", System.Data.OleDb.OleDbType.DBDate).Value = EndDate

     

    Dim dr As OleDbDataReader = AccessCommand.ExecuteReader(CommandBehavior.SequentialAccess)

     

    While dr.Read()

    Console.WriteLine(dr.Item("record id").ToString)

    End While

     

    dr.Close()

    AccessConnection.Close()

     

     

    Friday, January 11, 2008 1:47 PM

All replies

  • Just to clarify, since I've had no responses; the String:Concat peices together a SQL statement. What I can't figure out is how to send a date/time to Access using a SQL statement. I've tried sending a string of text, but it returns an error.

    For example:

    INSERT INTO People (FirstName) VALUES ('Peter')

    That works. But,

    INSERT INTO People (DateOfBirth) Values ('1/1/1984')

    That doesn't work if DateOfBirth is stored as a Date/Time. Now I've tried replacing the apostrophes like:


    INSERT INTO People (DateOfBirth) Values (#1/1/1984#)

    because that is how I saw it done in one example online, but it doesn't work.

    Does anyone have any idea?

    And also, does anyone know the data type that is returned from the DateTimePicker? Or can I just take the 'Value' of the object and ->ToString() it?
    Friday, January 11, 2008 1:07 PM
  • Are you receiving an error when you execute the last example you posted. The syntax looks OK to me.

     

    The recommended method is to use a Command object with Parameters. This way you don't have to be concerned with syntax quirks when using variable inserted parameters. Below is an example which uses a Command object w/Parameters (sorry I don't have a C++ example). I believe the DateTimePicker returns text so you will want to convert that to a date/time data type when using parameters.

     

    Code Block

    Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=C:\Test Files\db1 XP.mdb")

     

    Dim AccessCommand As New OleDbCommand("SELECT * FROM Table1 WHERE [Date Field] BETWEEN ? AND ?", AccessConnection)

     

    AccessConnection.Open()

     

    Dim StartDate As New System.DateTime(2005, 1, 25)

    Dim EndDate As New System.DateTime(2006, 5, 5)

     

    AccessCommand.Parameters.Add("@DateValStart", System.Data.OleDb.OleDbType.DBDate).Value = StartDate

    AccessCommand.Parameters.Add("@DateValEnd", System.Data.OleDb.OleDbType.DBDate).Value = EndDate

     

    Dim dr As OleDbDataReader = AccessCommand.ExecuteReader(CommandBehavior.SequentialAccess)

     

    While dr.Read()

    Console.WriteLine(dr.Item("record id").ToString)

    End While

     

    dr.Close()

    AccessConnection.Close()

     

     

    Friday, January 11, 2008 1:47 PM
  • Thanks for the help. You were right, my problem was a few lines higher in my code, it just gave me the error in the SQL line because it hates me.

    It's working now. All I have to do now is get Crystal Reports to turn it into a nice looking report. Big Smile
    Friday, January 11, 2008 3:38 PM