locked
How to insert date and time into microsoft access database RRS feed

  • Question

  • I am using vb 2008, and i have successfully connected to access 2003 database using ado data control.
    But I am unable to insert date and time and also retrieve it from data base.
    The code which I use is

    TO INSERT
    recordset.fields("EntryDate").value= DateTimePicker.value

    TO RETRIEVE
    DateTimePicker.value=recordset.fields("EntryDate") .value


    It gives me errors.
    Any suggestions will be appreciated
    Thank you in advance
    Tuesday, May 27, 2014 12:36 PM

Answers

  • Hello,

    You should be using OleDb data class to work with MS-Access databases rather than RecordSets. The following example on OneDrive is done in a later version of Visual Studio but will work with minor changes as shown below.

    There is an example of selecting data and also inserting a new row along with retrieving the new primary key value. I would be this into a VS2008 project but I only have VS2012.

    Public Class frmLesson
        WithEvents bsCustomers As New BindingSource
        Private Sub frmLesson1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Using cn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb")
                Using cmd As New OleDb.OleDbCommand("SELECT Identifier, FirstName, LastName FROM Customers", cn)
                    cn.Open()
                    Dim dt As New DataTable
                    dt.Load(cmd.ExecuteReader)
                    bsCustomers.DataSource = dt
                End Using
            End Using
    
            DataGridView1.DataSource = bsCustomers
            DataGridView1.Columns("FirstName").HeaderText = "First"
            DataGridView1.Columns("LastName").HeaderText = "Last"
            DataGridView1.Columns("LastName").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
    
            ActiveControl = txtFirstName
    
        End Sub
        Private Sub AddNewRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddRow.Click
            If Not String.IsNullOrWhiteSpace(txtFirstName.Text) AndAlso Not String.IsNullOrWhiteSpace(txtLastName.Text) Then
                Using cn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb")
    
                    Using cmd As New OleDb.OleDbCommand("INSERT INTO Customers (FirstName, LastName) VALUES (@FirstName, @LastName)", cn)
                        cmd.Parameters.AddRange(New OleDb.OleDbParameter() _
                            { _
                                New OleDb.OleDbParameter With {.ParameterName = "@FirstName", .DbType = DbType.String}, _
                                New OleDb.OleDbParameter With {.ParameterName = "@LastName", .DbType = DbType.String} _
                            } _
                        )
    
                        cmd.Parameters(0).Value = txtFirstName.Text
                        cmd.Parameters(1).Value = txtLastName.Text
    
                        cn.Open()
    
                        Dim Affected As Integer = cmd.ExecuteNonQuery
    
                        cmd.Parameters.Clear()
    
                        If Affected > 0 Then
                            cmd.CommandText = "Select @@Identity"
                            Dim Identifier As Long = CLng(cmd.ExecuteScalar)
    
                            CType(bsCustomers.DataSource, DataTable).Rows.Add(
                                New Object() {Identifier, txtFirstName.Text, txtLastName.Text})
    
                            txtFirstName.Text = ""
                            txtLastName.Text = ""
    
                            ActiveControl = txtFirstName
    
                        End If
                    End Using
                End Using
            Else
                MessageBox.Show("Both first and last name are required.")
            End If
    
        End Sub
    End Class

    Doing a date parameter (the ones above are doing string type)

    Dim DemoDate As New OleDb.OleDbParameter("@SomeDate", OleDb.OleDbType.Date)
    DemoDate.Value = Now

    The connection above is for accdb, for mdb use

    Provider=Microsoft.Jet.OLEDB.4.0;


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.


    Tuesday, May 27, 2014 12:59 PM

All replies

  • Hello,

    You should be using OleDb data class to work with MS-Access databases rather than RecordSets. The following example on OneDrive is done in a later version of Visual Studio but will work with minor changes as shown below.

    There is an example of selecting data and also inserting a new row along with retrieving the new primary key value. I would be this into a VS2008 project but I only have VS2012.

    Public Class frmLesson
        WithEvents bsCustomers As New BindingSource
        Private Sub frmLesson1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Using cn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb")
                Using cmd As New OleDb.OleDbCommand("SELECT Identifier, FirstName, LastName FROM Customers", cn)
                    cn.Open()
                    Dim dt As New DataTable
                    dt.Load(cmd.ExecuteReader)
                    bsCustomers.DataSource = dt
                End Using
            End Using
    
            DataGridView1.DataSource = bsCustomers
            DataGridView1.Columns("FirstName").HeaderText = "First"
            DataGridView1.Columns("LastName").HeaderText = "Last"
            DataGridView1.Columns("LastName").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
    
            ActiveControl = txtFirstName
    
        End Sub
        Private Sub AddNewRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddRow.Click
            If Not String.IsNullOrWhiteSpace(txtFirstName.Text) AndAlso Not String.IsNullOrWhiteSpace(txtLastName.Text) Then
                Using cn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb")
    
                    Using cmd As New OleDb.OleDbCommand("INSERT INTO Customers (FirstName, LastName) VALUES (@FirstName, @LastName)", cn)
                        cmd.Parameters.AddRange(New OleDb.OleDbParameter() _
                            { _
                                New OleDb.OleDbParameter With {.ParameterName = "@FirstName", .DbType = DbType.String}, _
                                New OleDb.OleDbParameter With {.ParameterName = "@LastName", .DbType = DbType.String} _
                            } _
                        )
    
                        cmd.Parameters(0).Value = txtFirstName.Text
                        cmd.Parameters(1).Value = txtLastName.Text
    
                        cn.Open()
    
                        Dim Affected As Integer = cmd.ExecuteNonQuery
    
                        cmd.Parameters.Clear()
    
                        If Affected > 0 Then
                            cmd.CommandText = "Select @@Identity"
                            Dim Identifier As Long = CLng(cmd.ExecuteScalar)
    
                            CType(bsCustomers.DataSource, DataTable).Rows.Add(
                                New Object() {Identifier, txtFirstName.Text, txtLastName.Text})
    
                            txtFirstName.Text = ""
                            txtLastName.Text = ""
    
                            ActiveControl = txtFirstName
    
                        End If
                    End Using
                End Using
            Else
                MessageBox.Show("Both first and last name are required.")
            End If
    
        End Sub
    End Class

    Doing a date parameter (the ones above are doing string type)

    Dim DemoDate As New OleDb.OleDbParameter("@SomeDate", OleDb.OleDbType.Date)
    DemoDate.Value = Now

    The connection above is for accdb, for mdb use

    Provider=Microsoft.Jet.OLEDB.4.0;


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.


    Tuesday, May 27, 2014 12:59 PM
  • Thank you for your replyActually I have a vb book in that there is explanation about ADO data control instead of OLEDB, So I had to forcibly choose ADO instead of ODBC and OLEDB. So if you have code to insert Date into recordset please give me.

    Thank you

    Tuesday, May 27, 2014 1:54 PM
  • Thank you for your replyActually I have a vb book in that there is explanation about ADO data control instead of OLEDB, So I had to forcibly choose ADO instead of ODBC and OLEDB. So if you have code to insert Date into recordset please give me.

    Thank you

    Hello,

    I highly advise not going down this path using Recordsets and use OleDb as learning old methods does you no good in the long run.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Tuesday, May 27, 2014 2:11 PM
  • Thank you for your replyActually I have a vb book in that there is explanation about ADO data control instead of OLEDB, So I had to forcibly choose ADO instead of ODBC and OLEDB. So if you have code to insert Date into recordset please give me.

    Thank you

    Ado data controls are not in VB 2008 and therefore you have probably the wrong book. The recordset  (DAO/ADO) is and was available as a kind of step from VB language version 6 to 7, VB2008 is already language version 2009 (while 2002/2003 although quite different where counted as one). 

    In newer OS systems you get only trouble with the old recordset.

    The counterpart of the recordset in .Net is the DataTable (not the DataSet), there are unlike the recordset thousands of ways you can update it and then often with less then 4 rows of code. But it is dependent how you use it. Therefore no code but the advice to look for how you want to get the datatable first. A modern (with underscore m) is the dataadapterManager

    http://msdn.microsoft.com/en-us/library/bb384426.aspx


    Success
    Cor


    Tuesday, May 27, 2014 2:29 PM