none
Adding second dateTime in DataBase RRS feed

  • Question

  • Hello, 
    Im using ready example and I want to adapt to my needs. I try to add second date in to my database and I get this error : 

    This is my file - File

    I added Data1 to DatabaseOperations (code lower) DataTimePicekr2 to MainForm

    Using cn As New OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT 
                                Identifier, 
                                JoinDate,
                                CompanyName, 
                                ContactName, 
                                ContactTitle,
                                Data1,
                                Address,
                                City,
                                PostalCode                            
                            FROM Customer ORDER BY CompanyName;
    
                        </SQL>.Value
    
     Public Function AddNewRow(
            ByVal pName As String,
            ByVal pContact As String,
            ByVal pContactTitle As String,
            ByVal pCity As String,
            ByVal pAddress As String,
            ByVal pPostalCode As String,
            ByVal pJoinDate As DateTime,
            ByVal pData1 As DateTime,
            ByRef pIdentfier As Integer) As Boolean
    
      INSERT INTO Customer 
                                    (
                                        CompanyName,
                                        ContactName,
                                        ContactTitle,
                                        City,
                                        Address,
                                        PostalCode,
                                        JoinDate,
                                        Data1
                                    ) 
                                Values
                                    (
                                        @CompanyName,
                                        @ContactName,
                                        @ContactTitle,
                                        @City,
                                        @Address,
                                        @PostalCode,
                                        @JoinDate,
                                        @Data1
                                    )
                            </SQL>.Value
    
                        cmd.Parameters.AddWithValue("@CompanyName", pName)
                        cmd.Parameters.AddWithValue("@ContactName", pContact)
                        cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
                        cmd.Parameters.AddWithValue("@City", pCity)
                        cmd.Parameters.AddWithValue("@Address", pAddress)
                        cmd.Parameters.AddWithValue("@PostalCode", pPostalCode)
                        cmd.Parameters.AddWithValue("@JoinDate", pJoinDate)
                        cmd.Parameters.AddWithValue("@Data1", pData1)
                        cn.Open()

    Tuesday, January 16, 2018 2:42 PM

Answers

  • Here is the update method

    Public Function UpdateRow(ByVal pDataRow As DataRow) As Boolean
    
        Try
            Using cn As New OleDbConnection(Builder.ConnectionString)
    
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            UPDATE 
                                Customer 
                            SET 
                                CompanyName = @CompanyName, 
                                ContactName = @ContactName,
                                ContactTitle = @ContactTitle,
                                JoinDate = @JoinDate, 
                                Data1 = @Data1
                            WHERE Identifier = @Identifier
                        </SQL>.Value
    
    
                    cmd.Parameters.AddWithValue("@CompanyName", pDataRow.Field(Of String)("CompanyName"))
                    cmd.Parameters.AddWithValue("@ContactName", pDataRow.Field(Of String)("ContactName"))
                    cmd.Parameters.AddWithValue("@ContactTitle", pDataRow.Field(Of String)("ContactTitle"))
                    cmd.Parameters.AddWithValue("@JoinDate", pDataRow.Field(Of DateTime)("JoinDate").Date)
                    cmd.Parameters.AddWithValue("@Data1", pDataRow.Field(Of DateTime)("Data1").Date)
                    cmd.Parameters.AddWithValue("@Identifier", pDataRow.Field(Of Integer)("Identifier"))
    
                    Try
                        cn.Open()
    
                        Dim Affected = cmd.ExecuteNonQuery
                        Return Affected = 1
                    Catch ex As Exception
    
                        mHasException = True
                        mLastException = ex
    
                        Return IsSuccessFul
    
                    End Try
                End Using
            End Using
    
        Catch ex As Exception
            mHasException = True
            mLastException = ex
    
            Return IsSuccessFul
    
        End Try
    End Function

    Form code for editing, not the test for null, this is because I added the field after the fact and didn't populate the field.

    Private Sub EditCurrentCustomer()
    
        Dim f As New frmEditor
    
    
        Try
            ' Generally we don't bind data like this but instead pass data via
            ' a custom new constructor, place the data into a class e.g. a Customer
            ' class then populate controls from this. The class would be public
            ' so when the form is closed we would get the values. I wanted to show
            ' data binding as another option.
            '
            ' Two fields are bound while the third (the combo box) is manually set.
            '
            f.txtCompanyName.DataBindings.Add("Text", bsCustomers, "CompanyName")
            f.txtContactName.DataBindings.Add("Text", bsCustomers, "ContactName")
            f.cboTitle.DataSource = mTitleList
            f.DateTimePicker1.Value = CType(bsCustomers.Current, DataRowView).Row.Field(Of DateTime)("JoinDate")
            Dim testForNull = CType(bsCustomers.Current, DataRowView).Row("Data1")
            If Convert.IsDBNull(testForNull) Then
                f.DateTimePicker2.Value = Now
            Else
                f.DateTimePicker2.Value = CType(bsCustomers.Current, DataRowView).Row.Field(Of DateTime)("Data1")
            End If
    
            ' Display the current Contact title by locating
            ' the title in a list of titles. We could optimize
            ' this by having a ContactTitle table yet I'm simply using
            ' a pre-existing table from Microsoft.
            f.cboTitle.SelectedIndex = mTitleList.IndexOf(bsCustomers.CurrentRow.Field(Of String)("ContactTitle"))
    
            f.ActiveControl = f.cmdCancel
    
            If f.ShowDialog = DialogResult.OK Then
    
                bsCustomers.CurrentRow.SetField(Of String)("ContactTitle", f.cboTitle.Text)
                bsCustomers.CurrentRow.SetField(Of DateTime)("JoinDate", f.DateTimePicker1.Value)
                bsCustomers.CurrentRow.SetField(Of DateTime)("Data1", f.DateTimePicker2.Value)
                If ops.UpdateRow(bsCustomers.CurrentRow) Then
                    bsCustomers.DataTable.AcceptChanges()
                Else
                    If Not ops.IsSuccessFul Then
                        MessageBox.Show($"Edit failed: {ops.LastExceptionMessage}")
                    End If
                End If
            Else
                bsCustomers.CancelEdit()
            End If
        Finally
            f.Dispose()
        End Try
    
    End Sub

    First two rows were done with the code above.


    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

    • Marked as answer by marek_maro Friday, January 19, 2018 12:46 PM
    Thursday, January 18, 2018 10:47 AM
    Moderator

All replies

  • What sql type did you use when adding the Data1 field to the database?

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Tuesday, January 16, 2018 2:54 PM
    Moderator
  • What is the value of pData1? If there is no valid Date value then you will need to assign System.DBNull.Value to the @Data1 parameter.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, January 16, 2018 3:22 PM
  • Beside the question from the others: Did you use the datatimepicker Value?

    Success Cor

    Tuesday, January 16, 2018 3:29 PM
  • Yes, I add Data1 to access file and mark data as Date/Time and I use DateTimePiceker as Value :)

    Private Sub AddNewRow_Click(ByVal sender As System.Object, ByVal e As EventArgs) Handles ToolStripButton2.Click
            Dim f As New frmEditor
    
            Try
                f.cboTitle.DataSource = mTitleList
                If f.ShowDialog = DialogResult.OK Then
                    Dim Identifier As Integer = 0
                    ' if the row was added, insert it to the current DataTable
                    If ops.AddNewRow(f.txtCompanyName.Text, f.txtContactName.Text, f.cboTitle.Text, f.txtCity.Text, f.txtAddress.Text, f.txtCode.Text, f.DateTimePicker1.Value, f.DateTimePicker1.Value, Identifier) Then
                        bsCustomers.DataTable.Rows.Add(New Object() {Identifier, f.DateTimePicker1.Value, f.txtCompanyName.Text, f.txtContactName.Text, f.cboTitle.Text, f.txtCity.Text, f.txtAddress.Text, f.txtCode.Text, f.DateTimePicker1.Value})
                        bsCustomers.Locate("Identifier", Identifier.ToString)
                    Else
                        If Not ops.IsSuccessFul Then
                            MessageBox.Show($"Failed to add new record: {ops.LastExceptionMessage}")
                        End If
                    End If
                End If
            Finally
                f.Dispose()
            End Try
        End Sub
    End Class

    • Edited by marek_maro Tuesday, January 16, 2018 4:02 PM
    Tuesday, January 16, 2018 3:59 PM
  • Hello,

    Here is a working version.

    https://1drv.ms/u/s!AtGAgKKpqdWjiSSCNnFSc2HZqUSB


    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

    Tuesday, January 16, 2018 4:17 PM
    Moderator
  • Marek,

    Probably you use an autoidentifier integer. If that is the case, you should keep our hands away from that. It is only to make rows in the database unique (You are now assigning 0 to it)


    Success Cor

    Tuesday, January 16, 2018 4:54 PM
  • Hi marek_maro,

    If you want to insert new record into Access Database, please pay attention to Date type. For example you want to add DataTimePicekr2's value as date time into Access Database, then you have an error message "data type mismatch in criteria expression" , you know the DataTimePicekr2's value like #1/17/2018 10:27:07 AM#,  , it will not the same format in Access Database, so you will get this error message. You can take a look the following code to change type of  DataTimePicekr2's value.

     cmd.Parameters.AddWithValue("@Date1", DateTimePicker1.Value.ToString("yyyy/MM/dd"))
                    cmd.Parameters.AddWithValue("@Date2", DateTimePicker2.Value.ToString("yyyy/MM/dd"))

    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.

    • Proposed as answer by Stanly Fan Wednesday, January 17, 2018 7:05 AM
    Wednesday, January 17, 2018 2:30 AM
    Moderator
  • As always, thank you for your all help and valuable remarks.

    There is one problem when editing, after the saved date is not updated :(

    Hello,

    Here is a working version.

    https://1drv.ms/u/s!AtGAgKKpqdWjiSSCNnFSc2HZqUSB

    Thursday, January 18, 2018 8:44 AM
  • Here is the update method

    Public Function UpdateRow(ByVal pDataRow As DataRow) As Boolean
    
        Try
            Using cn As New OleDbConnection(Builder.ConnectionString)
    
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            UPDATE 
                                Customer 
                            SET 
                                CompanyName = @CompanyName, 
                                ContactName = @ContactName,
                                ContactTitle = @ContactTitle,
                                JoinDate = @JoinDate, 
                                Data1 = @Data1
                            WHERE Identifier = @Identifier
                        </SQL>.Value
    
    
                    cmd.Parameters.AddWithValue("@CompanyName", pDataRow.Field(Of String)("CompanyName"))
                    cmd.Parameters.AddWithValue("@ContactName", pDataRow.Field(Of String)("ContactName"))
                    cmd.Parameters.AddWithValue("@ContactTitle", pDataRow.Field(Of String)("ContactTitle"))
                    cmd.Parameters.AddWithValue("@JoinDate", pDataRow.Field(Of DateTime)("JoinDate").Date)
                    cmd.Parameters.AddWithValue("@Data1", pDataRow.Field(Of DateTime)("Data1").Date)
                    cmd.Parameters.AddWithValue("@Identifier", pDataRow.Field(Of Integer)("Identifier"))
    
                    Try
                        cn.Open()
    
                        Dim Affected = cmd.ExecuteNonQuery
                        Return Affected = 1
                    Catch ex As Exception
    
                        mHasException = True
                        mLastException = ex
    
                        Return IsSuccessFul
    
                    End Try
                End Using
            End Using
    
        Catch ex As Exception
            mHasException = True
            mLastException = ex
    
            Return IsSuccessFul
    
        End Try
    End Function

    Form code for editing, not the test for null, this is because I added the field after the fact and didn't populate the field.

    Private Sub EditCurrentCustomer()
    
        Dim f As New frmEditor
    
    
        Try
            ' Generally we don't bind data like this but instead pass data via
            ' a custom new constructor, place the data into a class e.g. a Customer
            ' class then populate controls from this. The class would be public
            ' so when the form is closed we would get the values. I wanted to show
            ' data binding as another option.
            '
            ' Two fields are bound while the third (the combo box) is manually set.
            '
            f.txtCompanyName.DataBindings.Add("Text", bsCustomers, "CompanyName")
            f.txtContactName.DataBindings.Add("Text", bsCustomers, "ContactName")
            f.cboTitle.DataSource = mTitleList
            f.DateTimePicker1.Value = CType(bsCustomers.Current, DataRowView).Row.Field(Of DateTime)("JoinDate")
            Dim testForNull = CType(bsCustomers.Current, DataRowView).Row("Data1")
            If Convert.IsDBNull(testForNull) Then
                f.DateTimePicker2.Value = Now
            Else
                f.DateTimePicker2.Value = CType(bsCustomers.Current, DataRowView).Row.Field(Of DateTime)("Data1")
            End If
    
            ' Display the current Contact title by locating
            ' the title in a list of titles. We could optimize
            ' this by having a ContactTitle table yet I'm simply using
            ' a pre-existing table from Microsoft.
            f.cboTitle.SelectedIndex = mTitleList.IndexOf(bsCustomers.CurrentRow.Field(Of String)("ContactTitle"))
    
            f.ActiveControl = f.cmdCancel
    
            If f.ShowDialog = DialogResult.OK Then
    
                bsCustomers.CurrentRow.SetField(Of String)("ContactTitle", f.cboTitle.Text)
                bsCustomers.CurrentRow.SetField(Of DateTime)("JoinDate", f.DateTimePicker1.Value)
                bsCustomers.CurrentRow.SetField(Of DateTime)("Data1", f.DateTimePicker2.Value)
                If ops.UpdateRow(bsCustomers.CurrentRow) Then
                    bsCustomers.DataTable.AcceptChanges()
                Else
                    If Not ops.IsSuccessFul Then
                        MessageBox.Show($"Edit failed: {ops.LastExceptionMessage}")
                    End If
                End If
            Else
                bsCustomers.CancelEdit()
            End If
        Finally
            f.Dispose()
        End Try
    
    End Sub

    First two rows were done with the code above.


    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

    • Marked as answer by marek_maro Friday, January 19, 2018 12:46 PM
    Thursday, January 18, 2018 10:47 AM
    Moderator
  • Hi marek_maro,

    If you want to insert new record into Access Database, please pay attention to Date type. For example you want to add DataTimePicekr2's value as date time into Access Database, then you have an error message "data type mismatch in criteria expression" , you know the DataTimePicekr2's value like #1/17/2018 10:27:07 AM#,  , it will not the same format in Access Database, so you will get this error message. You can take a look the following code to change type of  DataTimePicekr2's value.

     cmd.Parameters.AddWithValue("@Date1", DateTimePicker1.Value.ToString("yyyy/MM/dd"))
                    cmd.Parameters.AddWithValue("@Date2", DateTimePicker2.Value.ToString("yyyy/MM/dd"))

    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.

    How is it possible while somebody shows  he is using the DateTimePicker value (which is the correct way) that 2 Microsoft MSFT are telling to use a string format in Chinese Date format. 

    Success Cor


    • Edited by Cor Ligthert Thursday, January 18, 2018 11:22 AM
    Thursday, January 18, 2018 11:21 AM
  • At the beginning, Welcome  to everyone.

    I have a question to this project. If I wanted to use dates with an hour? I should change the Function AddNewRow, UpdateRow and change DateTimePicker to custom format and that shout by all ??



    Saturday, January 20, 2018 7:19 PM
  • At the beginning, Welcome  to everyone.

    I have a question to this project. If I wanted to use dates with an hour? I should change the Function AddNewRow, UpdateRow and change DateTimePicker to custom format and that shout by all ??



    Hello,

    The only thing that would change for the AddNewRow is to add another parameter for time if the intent is to separate time from date, otherwise you can use a DateTime rather than a Date parameter. For the UpdateRow, it wants a DataRow, you would adjust similarly as indicated for the AddNewRow.

    Server side you can store the DateTime say as a datetime(7) or datetimeOffset (of users are in multiple time zones) or time(7) but when using time(7) there is no time type in .NET so you need to consider (using a TimeSpan type) how you will work with time in your application.

    In regards to using a DateTimePicker, you can have one for date, one for time or perhaps a custom control for time as shown here which I created as an alternate to the DateTimePicker which provides acccess to the time via TimeSpan variables and propertiers.

    In closing, the original code sample (without the date stuff) is here.


    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

    Saturday, January 20, 2018 8:47 PM
    Moderator
  • This example from the main thread is fine for my needs.
    I got a little problem....
    I changed what was needed, but wen I add new record I get date like that :
    2018-01-21 00:00 wen I edit record I can set any Time.
    In Edit form
    I changed DateTimePicker to custom "yyyy-MM-dd HH:mm"
    Sunday, January 21, 2018 10:07 AM
  • Karen uses the DateTimePicker Value Date. That is an Int64 representation in 100th nanotick starting at 1/1/1 0:0:0

    It is not important what your timepicker shows. 

    However, try to avoid hacking other threads, it makes a mess of it. 

    Better to create your own new questions.


    Success Cor

    Sunday, January 21, 2018 10:48 AM