Results 1 to 5 of 5

Thread: [RESOLVED] Error when trying to save date from date time picker to access database?

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2004
    Location
    U.K
    Posts
    771

    Resolved [RESOLVED] Error when trying to save date from date time picker to access database?

    Hi,

    I want to save the date from a date/time picker into my table where the DTPJobID = 1.

    I'm using VB2010 and Microsoft Access 2003. My table contains the following

    Code:
    Table Name: DTP
    Fields
    ID: Auto number
    DTPJobID: Number
    DTPDate: Date/Time with format of dd/mm/yyyy hh:nn
    I use the following coding:-

    Code:
            Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\DB1.mdb")
            Dim cmd As New OleDbCommand
            With cmd
                .CommandType = CommandType.Text
                .Connection = conn
                .CommandText = "UPDATE [DTP] SET DTPDate =" & dtp.Text & " WHERE DTPJobID = 1"
            End With
            conn.Open()
            cmd.ExecuteNonQuery()
    However, I get the following error:-

    Code:
    An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
    
    Additional information: Syntax error (missing operator) in query expression '22/01/2015 10:10'.
    Any ideas where i might have gone wrong?
    Please mark threads as resolved once the problem has been solved.
    I apprecaite all your help/advice given

  2. #2
    Frenzied Member CoachBarker's Avatar
    Join Date
    Aug 2007
    Location
    Central NY State
    Posts
    1,120

    Re: Error when trying to save date from date time picker to access database?

    One thing you should dois be using parameters in your query and I think you want to use dtp.value, not dtp.text. You are trying to convert a text value to a Date"Time value.
    Thanks
    CoachBarker

    Code Bank Contribution
    Login/Manage Users/Navigate Records
    VB.Net | C#

    Helpful Links: VB.net Tutorial | C Sharp Tutorial | SQL Basics

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2004
    Location
    U.K
    Posts
    771

    Re: Error when trying to save date from date time picker to access database?

    Ok thanks,

    Using parameters (assuming I have done it correct) I still get an error?

    New code:-

    Code:
                .CommandText = "UPDATE [DTP] SET DTPDate = @p1 WHERE DTPJobID = @p2"
                .Parameters.AddWithValue("@p1", Me.dtp.Value)
                .Parameters.AddWithValue("@p2", 1)
    The error is

    Code:
    Additional information: Data type mismatch in criteria expression.
    Please mark threads as resolved once the problem has been solved.
    I apprecaite all your help/advice given

  4. #4
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,535

    Re: Error when trying to save date from date time picker to access database?

    Here is a working example, would upload the project but I am using VS2013

    Code:
    Imports System.Data.OleDb
    
    Public Class Form1
    
        Public Function UpdateRow(ByVal Identifier As Integer, ByVal TheDate As Date) As Boolean
            Dim Builder As New OleDbConnectionStringBuilder With
                {
                    .Provider = "Microsoft.ACE.OLEDB.12.0",
                    .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
                }
            Using cn As New OleDb.OleDbConnection With
                {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            UPDATE Table1 SET DTPDate = @TheDate WHERE Identifier = @Identifier
                        </SQL>.Value
    
                    cmd.Parameters.Add(New OleDb.OleDbParameter With
                            {
                                .DbType = DbType.Date,
                                .ParameterName = "@TheDate",
                                .Value = TheDate
                            }
                        )
                    cmd.Parameters.Add(New OleDb.OleDbParameter With
                            {
                                .DbType = DbType.Int32,
                                .ParameterName = "@Identifier",
                                .Value = Identifier
                            }
                        )
    
                    cn.Open()
    
                    Return cmd.ExecuteNonQuery() = 1
    
                End Using
            End Using
        End Function
    
        Private Sub cmdUpdate_Click(sender As Object, e As EventArgs) Handles cmdUpdate.Click
            If UpdateRow(1, DateTimePicker1.Value) Then
                MessageBox.Show("Updated")
            Else
                MessageBox.Show("Update failed")
            End If
        End Sub
    End Class
    Name:  qqqqq.png
Views: 480
Size:  5.4 KB

  5. #5
    Registered User
    Join Date
    Feb 2016
    Posts
    1

    Re: Error when trying to save date from date time picker to access database?

    Hello kevininstructor, I found this advice and tried to adapt it for my own use. However I receive an error when I try to run the line "cn.Open()":
    Can you please assist. Many thanks

    ERROR MSG
    An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
    Additional information: Not a valid file name.

    I have checked the path and name of my dbase

    Here is my code (amended from yours shown below)

    Code:
     Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
            '   Save button clicked - so save the record to the Table ->Transactions
    
            Dim Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = "Data Source=C:\Users\Peter R\Documents\MyPortfolio\Portfolio.accdb;"
        }
            Using cn As New OleDb.OleDbConnection With
                {.ConnectionString = Builder.ConnectionString}
    
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            INSERT INTO [Transactions] Transaction_Date VALUES (@Transaction_Date)
                        </SQL>.Value
    
                    cmd.Parameters.Add(New OleDb.OleDbParameter With
                            {
                                .DbType = DbType.Date,
                                .ParameterName = "@TheDate",
                                .Value = Me.DateTimePicker1.Value
                            }
                    )
    
                    cn.Open()
    
                    cmd.ExecuteNonQuery()
    
                End Using
            End Using
    
        End Sub
    [QUOTE=kevininstructor;4824037]Here is a working example, would upload the project but I am using VS2013

    Code:
    Imports System.Data.OleDb
    
    Public Class Form1
    
        Public Function UpdateRow(ByVal Identifier As Integer, ByVal TheDate As Date) As Boolean
            Dim Builder As New OleDbConnectionStringBuilder With
                {
                    .Provider = "Microsoft.ACE.OLEDB.12.0",
                    .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
                }
            Using cn As New OleDb.OleDbConnection With
                {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            UPDATE Table1 SET DTPDate = @TheDate WHERE Identifier = @Identifier
                        </SQL>.Value
    
                    cmd.Parameters.Add(New OleDb.OleDbParameter With
                            {
                                .DbType = DbType.Date,
                                .ParameterName = "@TheDate",
                                .Value = TheDate
                            }
                        )
                    cmd.Parameters.Add(New OleDb.OleDbParameter With
                            {
                                .DbType = DbType.Int32,
                                .ParameterName = "@Identifier",
                                .Value = Identifier
                            }
                        )
    
                    cn.Open()
    
                    Return cmd.ExecuteNonQuery() = 1
    
                End Using
            End Using
        End Function
    
        Private Sub cmdUpdate_Click(sender As Object, e As EventArgs) Handles cmdUpdate.Click
            If UpdateRow(1, DateTimePicker1.Value) Then
                MessageBox.Show("Updated")
            Else
                MessageBox.Show("Update failed")
            End If
        End Sub
    End Class

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width