locked
Filtering betwen two dates and adding date to database RRS feed

  • Question

  • I used example file of database. 

    In access file I added a Date column in data type of this column changed to Date/Time

    I add a date field using DateTimePicker and when I trying to save data, nothing happens...

    And second filtering between 2 dates (Private Sub Search_Click) :

    Public Class frmMainForm
    
        WithEvents bsCustomers As New BindingSource
        Dim datatable As New DataTable
        Private mTitleList As List(Of String)
        Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            bsCustomers.DataSource = ops.LoadCustomers()
            mTitleList = ops.LoadContactTitles()
            BindingNavigator1.BindingSource = bsCustomers
    
            DataGridView1.AllowUserToAddRows = False
            DataGridView1.DataSource = bsCustomers
    
            DataGridView1.Columns("Identifier").HeaderText = "ID"
            DataGridView1.Columns("CompanyName").HeaderText = "Company"
            DataGridView1.Columns("Date").HeaderText = "Date"
            DataGridView1.Columns("ContactName").HeaderText = "Contact"
            DataGridView1.Columns("ContactTitle").HeaderText = "Contact Title"
            DataGridView1.Columns("Address").HeaderText = "Address"
            DataGridView1.Columns("City").HeaderText = "City"
            DataGridView1.Columns("PostalCode").HeaderText = "PostalCode"
            DataGridView1.Columns("Country").HeaderText = "Country"
    
            DataGridView1.ExpandColumns()
    
            bsCustomers.Sort = "Date"
    
        End Sub
        Private Sub DataGridView1SelectAll_CurrentCellDirtyStateChanged(ByVal sender As Object, ByVal e As EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged
            If TypeOf DataGridView1.CurrentCell Is DataGridViewCheckBoxCell Then
                DataGridView1.EndEdit()
            End If
        End Sub
        ''' <summary>
        ''' Add new row, do only two fields, add more as desired
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
            Dim f As New frmEditor
    
            Try
                f.cboTitle.DataSource = mTitleList
                If f.ShowDialog = DialogResult.OK Then
                    Dim Identifier As Int32 = 0
                    ' if the row was added, insert it to the current DataTable
                    If ops.AddNewRow(f.txtDate.Text, f.txtCompanyName.Text, f.txtContactName.Text, f.cboTitle.Text, f.txtAddress.Text, f.txtCity.Text, f.txtCode.Text, f.txtCountry.Text, Identifier) Then
                        bsCustomers.DataTable.Rows.Add(New Object() {Identifier, f.txtData.Text, f.txtCompanyName.Text, f.txtContactName.Text, f.cboTitle.Text, f.txtAddress.Text, f.txtCity.Text, f.txtCode.Text, f.txtCountry.Text})
                        bsCustomers.Locate("Identifier", Identifier.ToString)
                    End If
                End If
            Finally
                f.Dispose()
            End Try
        End Sub
    
        Private Sub Search_Click(sender As Object, e As EventArgs) Handles Search.Click
            Me.TblUserBindingSource.Filter = "Date >= '" & DateTimePicker1.Value & "' and Date <= '" & DateTimePicker2.Value & "'"
        End Sub
    End Class

    Imports System.Data.OleDb
    
    Public Class DatabaseOperations
        Private Builder As New OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
            }
    
        ''' <summary>
        ''' Read USA customers from database into a DataTable
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks>
        ''' Database is assumed to be in the Bin\Debug folder.
        ''' </remarks>
        Public Function LoadCustomers() As DataTable
    
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT 
                                Identifier, 
                                Date,
                                CompanyName, 
                                ContactName, 
                                ContactTitle,
                                Address,
                                City,
                                PostalCode,
                                Country
                            FROM Customer ORDER BY CompanyName;
    
                        </SQL>.Value
    
                    Dim dt As New DataTable With {.TableName = "Customer"}
    
                    Try
                        cn.Open()
                        dt.Load(cmd.ExecuteReader)
                        ' dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
    
                    Catch ex As Exception
                        MessageBox.Show("Failed to load customer data. See error message below" & Environment.NewLine & ex.Message)
                    End Try
    
                    dt.AcceptChanges()
    
                    Return dt
    
                End Using
            End Using
        End Function
        Public Function LoadContactTitles() As List(Of String)
            Dim titleList As New List(Of String)
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT DISTINCT ContactTitle
                            FROM Customer
    
                        </SQL>.Value
    
    
                    Try
                        cn.Open()
                        Dim reader As OleDbDataReader = cmd.ExecuteReader
    
                        While reader.Read
                            titleList.Add(reader.GetString(0))
                        End While
                    Catch ex As Exception
                        MessageBox.Show("Failed to load customer data. See error message below" & Environment.NewLine & ex.Message)
                    End Try
    
    
                End Using
            End Using
    
            Return titleList
        End Function
        Public Function RemoveCurrentCustomer(ByVal pIdentfier As Integer) As Boolean
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "DELETE FROM Customer WHERE Identifier = ?"
    
                        Dim IdentifierParameter As New OleDbParameter With
                            {
                                .DbType = DbType.Int32,
                                .ParameterName = "P1",
                                .Value = pIdentfier
                            }
                        cmd.Parameters.Add(IdentifierParameter)
    
                        Try
                            cn.Open()
    
                            Dim Affected = cmd.ExecuteNonQuery
                            Return Affected = 1
    
                        Catch ex As Exception
                            Return False
                        End Try
                    End Using
                End Using
    
            Catch ex As Exception
                '
                ' Handle or not handle exceptions for failed save operation
                '
                Return False
            End Try
        End Function
        Public Function AddNewRow(ByVal pName As String, ByVal pDate As String, ByVal pContact As String, ByVal pContactTitle As String, ByVal pAddress As String, ByVal pCity As String, ByVal pPostalCode As String, ByVal pCountry As String, ByRef pIdentfier As Integer) As Boolean
            Dim Success As Boolean = True
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText =
                            <SQL>
                                INSERT INTO Customer 
                                    (
                                        Date,
                                        CompanyName,
                                        ContactName,
                                        ContactTitle,
                                        Address,
                                        City,
                                        PostalCode,
                                        Country
                                    ) 
                                Values
                                    (
                                        @Date,
                                        @CompanyName,
                                        @ContactName,
                                        @ContactTitle,
                                        @Address,
                                        @City,
                                        @PostalCode,
                                        @Country
                                    )
                            </SQL>.Value
                        cmd.Parameters.AddWithValue("@Date", pDate)
                        cmd.Parameters.AddWithValue("@CompanyName", pName)
                        cmd.Parameters.AddWithValue("@ContactName", pContact)
                        cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
                        cmd.Parameters.AddWithValue("@Address", pAddress)
                        cmd.Parameters.AddWithValue("@City", pCity)
                        cmd.Parameters.AddWithValue("@PostalCode", pPostalCode)
                        cmd.Parameters.AddWithValue("@Country", pCountry)
    
                        cn.Open()
    
                        cmd.ExecuteNonQuery()
    
                        cmd.CommandText = "Select @@Identity"
                        pIdentfier = CInt(cmd.ExecuteScalar)
    
                    End Using
                End Using
    
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function SaveChanges(ByVal sender As DataRow) As Boolean
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText =
                            <SQL>
                                UPDATE 
                                    Customer 
                                SET 
                                    Date=?,
                                    CompanyName=?, 
                                    ContactName=?,
                                    ContactTitle=?,
                                    Address=?,
                                    City=?,
                                    PostalCode=?,
                                    Country=?
                                WHERE Identifier = ?
                            </SQL>.Value
                        Dim DataParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "P1",
                                .Value = sender.Field(Of String)("Date")
                            }
    
                        cmd.Parameters.Add(DataParameter)
    
                        Dim CompanyNameParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "P2",
                                .Value = sender.Field(Of String)("CompanyName")
                            }
    
                        cmd.Parameters.Add(CompanyNameParameter)
    
                        Dim ContactNameParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "P3",
                                .Value = sender.Field(Of String)("ContactName")
                            }
    
                        cmd.Parameters.Add(ContactNameParameter)
    
                        Dim ContactTitleParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "P4",
                                .Value = sender.Field(Of String)("ContactTitle")
                            }
    
                        cmd.Parameters.Add(ContactTitleParameter)
    
                        Dim AddressParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "P5",
                                .Value = sender.Field(Of String)("Address")
                            }
    
                        cmd.Parameters.Add(AddressParameter)
    
                        Dim CityParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "P6",
                                .Value = sender.Field(Of String)("City")
                            }
    
                        cmd.Parameters.Add(CityParameter)
    
                        Dim PostalCodeParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "P7",
                                .Value = sender.Field(Of String)("PostalCode")
                            }
    
                        cmd.Parameters.Add(PostalCodeParameter)
    
                        Dim CountryParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "P8",
                                .Value = sender.Field(Of String)("Country")
                            }
    
                        cmd.Parameters.Add(CountryParameter)
    
                        Dim IdentifierParameter As New OleDbParameter With
                            {
                                .DbType = DbType.Int32,
                                .ParameterName = "P9",
                                .Value = sender.Field(Of Int32)("Identifier")
                            }
    
                        cmd.Parameters.Add(IdentifierParameter)
    
                        Try
                            cn.Open()
    
                            Dim Affected = cmd.ExecuteNonQuery
                            Return Affected = 1
    
                        Catch ex As Exception
                            Return False
                        End Try
                    End Using
                End Using
    
            Catch ex As Exception
                '
                ' Handle or not handle exceptions for failed save operation
                '
                Return False
    
            End Try
        End Function
    End Class



    • Edited by marek_maro Thursday, January 11, 2018 1:57 PM
    Thursday, January 11, 2018 1:57 PM

All replies

  • Hello,

    I'm heading out the door for work (in about one hour or so), will take a look at it and comment on the code as I'm seeing some ways to alter the code and also for asserting the data operations.


    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 11, 2018 2:18 PM
  • When you say "save" is the add or update or both? In the add code, did you check to see if AddNewRow returned true and that the new primary key has been returned?

    If the database is in the project and shown in the project, make sure "Copy to output directory" is set to copy if newer.

    Also, in the INSERT statement, rather than Date try [Date] for the field, not the parameter e.g. INSERT INTO Customer( [Date] ...

    For SaveChanges, UPDATE Customer SET [Date]

    Date I believe is a reserved word, the brackets rule out any issues with reserved words. Also check to see if Affected returns 1 in SaveChanges

    For any try/catch, place a break-point on the Return False (thinking in SaveChanges for example) and if hit examine ex in the IDE Local window (from the debug menu) or simply hover over ex to see what happened.

    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 11, 2018 3:03 PM
  • Save, a mean Add a new record. 
    In Editor Window i'm feeling te all fields and when I click save nothing hapens no error and no data added to table. 
    Thursday, January 11, 2018 5:16 PM
  • How do you know nothing happens? 

    You know Visual Studio makes a copy of your database which you can manage by clicking on that in solution Explorer?


    Success Cor


    Thursday, January 11, 2018 5:24 PM
  • How do you know nothing happens? 

    You know Visual Studio makes a copy of your database which you can manage by clicking on that in solution Explorer?


    Success Cor


    No, I didn't know about it.
    Maybe all project will be beater to see the problem - Project link
    • Edited by marek_maro Thursday, January 11, 2018 6:32 PM
    Thursday, January 11, 2018 6:32 PM
  • Save, a mean Add a new record. 
    In Editor Window i'm feeling te all fields and when I click save nothing hapens no error and no data added to table. 

    Place a break-point on in the add method and a Console.WriteLine as per the 2nd code block below.

    cmd.ExecuteNonQuery()

    Then

    pIdentfier = CInt(cmd.ExecuteScalar)
    Console.WriteLine
    Step through the code until Console.WriteLine, example pIdentifier, if it has a value that represents the new row e.g. let's say the last row before the add was 10 and afterwards we would have 11 in pIdentifier. This means the INSERT was successful. If nothing happened then the issue is either the wrong database is being updated or the database is being overwritten on each build of the project because of copy to output folder as per my code sample.


    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 11, 2018 7:20 PM
  • How do you know nothing happens? 

    You know Visual Studio makes a copy of your database which you can manage by clicking on that in solution Explorer?


    Success Cor


    No, I didn't know about it.
    Maybe all project will be beater to see the problem - Project link

    See #2 in the below link:

    https://social.msdn.microsoft.com/Forums/en-US/ad837d14-31ea-445f-b830-1a8682b69e68/faq-my-database-isnt-being-updated-but-no-errors-occurred-in-my-application?forum=adodotnetdataproviders


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, January 11, 2018 9:53 PM
  • Hello,

    I would had done this sooner but was a work.

    Here is a working solution done in VS2017.

    The table in MS-Access

    If I had used 'Date' for a field name instead of JoinDate we get

    Which is a BIG warning to pick another name.

    Main form

    Insert new record

    IMPORANT

    • I did not update the code for updating a record as that was not the issue but you get the idea.
    • Note the exception handling used as per below in DatabaseOperations which is important for knowing if there was an exception what it was.
    • Note the dates with time, that is on purpose and are from me adding new records. Of course they can be formatted as they should be but I left them unformatted on purpose.
    Public Class DatabaseOperations
        Inherits BaseExceptionProperties

    How it's used

    In the form


    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Friday, January 12, 2018 12:35 AM
  • I used example file of database. 

    In access file I added a Date column in data type of this column changed to Date/Time

    I add a date field using DateTimePicker and when I trying to save data, nothing happens...

    And second filtering between 2 dates (Private Sub Search_Click) :


    Hi marek_maro,

    Firstly you said that you want to insert new record that contains date into Access DataBase, but nothing happen, I find you use reserved access word in your insert statement , you can modify this field and try it again. If you also receive the same issue, you can add break point in inserting record and see if there is some issue.

    https://support.microsoft.com/en-in/help/248738/list-of-reserved-words-in-jet-4-0

    Secondly you want to BindSourec.filter to filter date by DateTimepicker control, do you have some issue here? If yes, please provide error message here, because I test it and there is no error. You can also use the following code to do this.

    bindsource.Filter = "Birth>=" & String.Format("#{0:yyyy/MM/dd HH:mm:ss}#", DateTimePicker2.Value) & " and Birth <=" & String.Format("#{0:yyyy/MM/dd HH:mm:ss}#", DateTimePicker3.Value)

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, January 12, 2018 7:17 AM
  • @Cherry,

    If you read my last reply I mentioned Date is a reserved word.


    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, January 12, 2018 10:11 AM
  • Yes I read all, I'm super grateful because I'm learning something new all the time. Now I'm trying to "continue fighting" the code :)
    Friday, January 12, 2018 5:48 PM
  • Yes I read all, I'm super grateful because I'm learning something new all the time. Now I'm trying to "continue fighting" the code :)
    Hey, in regards to "continue fighting the code" -- that is what makes this fun :-)

    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, January 12, 2018 5:56 PM