locked
Adding only one date RRS feed

  • Question

  • I rebuilt the program for my needs (program for borrowing construction tools) and I got a little problem... I would like to add only one date when adding a new data (JoinDate), and the second date was added when editing/returning field (Date1). 
    The first date is adding automaticly, but I can't not add a second "Date1"

        ''' <summary>
        ''' Add a new customer to the database table
        ''' </summary>
        ''' <param name="pName"></param>
        ''' <param name="pContact"></param>
        ''' <param name="pContactTitle"></param>
        ''' <param name="pAddress"></param>
        ''' <param name="pCity"></param>
        ''' <param name="pPostalCode"></param>
        ''' <param name="pIdentfier"></param>
        ''' <returns></returns>
        Public Function AddNewRow(
            ByVal pName As String,
            ByVal pContact As String,
            ByVal pContactTitle As String,
            ByVal pAddress As String,
            ByVal pCity As String,
            ByVal pPostalCode As String,
            ByVal pJoinDate As DateTime,
            ByVal pDate1 As DateTime,
            ByRef pIdentfier As Integer) As Boolean
    
            Dim Success As Boolean = True
            Try
                Using cn As New OleDbConnection(Builder.ConnectionString)
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText =
                            <SQL>
                                INSERT INTO Customer 
                                    (
                                        CompanyName,
                                        ContactName,
                                        ContactTitle,
                                        Address,
                                        City,
                                        PostalCode,
                                        JoinDate,
                                        Date1
                                    ) 
                                Values
                                    (
                                        @CompanyName,
                                        @ContactName,
                                        @ContactTitle,
                                        @Address,
                                        @City,
                                        @PostalCode,
                                        @JoinDate,
                                        @Date1
                                    )
                            </SQL>.Value
                        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("@JoinDate", pJoinDate.ToString("yyyy-MM-dd HH:mm"))
                        cmd.Parameters.AddWithValue("@Date1", pDate1.ToString("yyyy-MM-dd HH:mm"))
                        cn.Open()
                        cmd.ExecuteNonQuery()
                        cmd.CommandText = "Select @@Identity"
                        pIdentfier = CInt(cmd.ExecuteScalar)
                    End Using
                End Using
    
            Catch ex As Exception
                mHasException = True
                mLastException = ex
                Success = False
            End Try
    
            Return Success
    
        End Function
        ''' <summary>
        ''' Add new row, do only two fields, add more as desired
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub AddNewRow_Click(ByVal sender As System.Object, ByVal e As EventArgs) Handles ToolStripButton2.Click
            Dim f As New frmEditor
    
            Try
                f.txtKlucz.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.txtWydajacy.Text, f.txtPobieajacy.Text, f.txtKlucz.Text, f.txtPrzyjmujacy.Text, f.txtZdajacy.Text, f.txtStatus.Text, f.DateTimePicker1.Value, f.DateTimePicker2.Value, Identifier) Then
                        bsCustomers.DataTable.Rows.Add(New Object() {Identifier, f.DateTimePicker1.Value, f.txtWydajacy.Text, f.txtPobieajacy.Text, f.txtKlucz.Text, f.DateTimePicker2.Value, f.txtPrzyjmujacy.Text, f.txtZdajacy.Text, f.txtStatus.Text})
                        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






    • Edited by marek_maro Tuesday, February 6, 2018 5:59 PM
    Tuesday, February 6, 2018 5:50 PM

Answers

  • If I understand the question, you only need one date e.g. JoinDate. If so you remove the second date pDate1 from the function then remove it from the INSERT and the parameter as shown below. If this is not correct let me know.

    I made as you write but I get a bug message like Data1 is somewhere and he has bad data :(

    Here is a complete updated version. For the Add and Update I have two versions, for adding one version does both dates while the other does not do Data1, same goes for the update method. Tested both versions and they work fine. Note each will spit out the INSERT or UPDATE statement to the console so you can see them.


    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 Wednesday, February 7, 2018 1:57 PM
    Wednesday, February 7, 2018 11:17 AM
  • This should work

    bsCustomers.Sort = "Identifier DESC"


    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 Wednesday, February 7, 2018 6:58 PM
    Wednesday, February 7, 2018 3:49 PM

All replies

  • If I understand the question, you only need one date e.g. JoinDate. If so you remove the second date pDate1 from the function then remove it from the INSERT and the parameter as shown below. If this is not correct let me know.

    Public Function AddNewRow(
        ByVal pName As String,
        ByVal pContact As String,
        ByVal pContactTitle As String,
        ByVal pAddress As String,
        ByVal pCity As String,
        ByVal pPostalCode As String,
        ByVal pJoinDate As DateTime,
        ByRef pIdentfier As Integer) As Boolean
    
        Dim Success As Boolean = True
        Try
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            INSERT INTO Customer 
                                (
                                    CompanyName,
                                    ContactName,
                                    ContactTitle,
                                    Address,
                                    City,
                                    PostalCode,
                                    JoinDate
                                ) 
                            Values
                                (
                                    @CompanyName,
                                    @ContactName,
                                    @ContactTitle,
                                    @Address,
                                    @City,
                                    @PostalCode,
                                    @JoinDate
                                )
                        </SQL>.Value
    
                    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("@JoinDate", pJoinDate.ToString("yyyy-MM-dd HH:mm"))
    
                    cn.Open()
    
                    cmd.ExecuteNonQuery()
    
                    cmd.CommandText = "Select @@Identity"
                    pIdentfier = CInt(cmd.ExecuteScalar)
    
                End Using
            End Using
    
        Catch ex As Exception
            mHasException = True
            mLastException = ex
            Success = False
        End Try
    
        Return Success
    
    End Function

     

    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, February 6, 2018 7:28 PM
  • I was busy answering and clicked the wrong button :-)

    You seems to misunderstand the datatable (it is just an in memory kind of array of datarows). 

    What you are doing is CRUD programming (has nothing to do with waste). 

    https://en.wikipedia.org/wiki/Create,_read,_update_and_delete

    You need in my perception the second time an Update SQL command. 


    Success Cor

    Tuesday, February 6, 2018 7:33 PM
  • I rebuilt the program for my needs (program for borrowing construction tools) and I got a little problem... I would like to add only one date when adding a new data (JoinDate), and the second date was added when editing/returning field (Date1). 

    Hi marek_maro,

    You said that you want to add only one date when adding a new data, what it means? And the second date was added when editing/returning field, I am not sure what do you mean, please  elaborate on what you want to do。

    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.

    Wednesday, February 7, 2018 7:52 AM
  • Sorry its difficult to explain. 
    Maybe this can help.... i got 2 buttons Borrow - green and Return - red
    First I only fill 4 fields and when someone returns tool I fill second 4 fields.


    • Edited by marek_maro Wednesday, February 7, 2018 10:34 AM
    Wednesday, February 7, 2018 10:32 AM
  • If I understand the question, you only need one date e.g. JoinDate. If so you remove the second date pDate1 from the function then remove it from the INSERT and the parameter as shown below. If this is not correct let me know.

    I made as you write but I get a bug message like Data1 is somewhere and he has bad data :(
    Wednesday, February 7, 2018 10:36 AM
  • If I understand the question, you only need one date e.g. JoinDate. If so you remove the second date pDate1 from the function then remove it from the INSERT and the parameter as shown below. If this is not correct let me know.

    I made as you write but I get a bug message like Data1 is somewhere and he has bad data :(

    Here is a complete updated version. For the Add and Update I have two versions, for adding one version does both dates while the other does not do Data1, same goes for the update method. Tested both versions and they work fine. Note each will spit out the INSERT or UPDATE statement to the console so you can see them.


    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 Wednesday, February 7, 2018 1:57 PM
    Wednesday, February 7, 2018 11:17 AM
  • Yes this is what I thought, 

    Did you look at my message?

    What you have to do is to select again the date the tool was borrowed, fill again the data on screent and then you can do an update where you use the ID in the where clause. 

    I only don't understand what a tool has to do with a key.

    I don't spent much time by setting it in code, because I get the idea you don't read what I write.  


    Success Cor

    Wednesday, February 7, 2018 12:14 PM
  • Yes, i have see. 

    I didn't change everything it's a work version, now I testing and making all options. 
    Thanks for advice and help :)
    Wednesday, February 7, 2018 1:57 PM
  • Last quick question, I want to sort columns by Identifier. 

    bsCustomers.Sort = "Identifier"
    But I want to sort opposite from high to low 

    Wednesday, February 7, 2018 3:31 PM
  • This should work

    bsCustomers.Sort = "Identifier DESC"


    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 Wednesday, February 7, 2018 6:58 PM
    Wednesday, February 7, 2018 3:49 PM
  • Last thing... I don't know if I made something wrong but I change view column to see also time and (...)

                DataGridView1.Columns("JoinDate").DefaultCellStyle.Format = "MM-dd-yyyy HH:mm"
                DataGridView1.Columns("Data1").DefaultCellStyle.Format = "MM-dd-yyyy HH:mm"

    and change datetimepicker 1&2 to custom date time "MM-dd-yyyy HH:mm"

    Adding works : 

    then I edit : 

    And turning off program and compile again time reset and second date disapear



    • Edited by marek_maro Wednesday, February 7, 2018 7:14 PM
    Wednesday, February 7, 2018 7:12 PM
  • I don't know if this what you want, otherwise look at my first and second message

    Success Cor

    Wednesday, February 7, 2018 7:41 PM
  • If you are getting a blank time e.g. 12:00 then it because of this in the edit method

    cmd.Parameters.AddWithValue("@JoinDate", pDataRow.Field(Of DateTime)("JoinDate").Date)

    Lose .Date

    cmd.Parameters.AddWithValue("@JoinDate", pDataRow.Field(Of DateTime)("JoinDate"))
    PS I could not reply earlier as I don't have the ms-access data provider installed at work, only SQL-Server and Oracle.


    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, February 8, 2018 12:00 AM
  • Ok, no problem for me... a got loot of time so don't wory about me :)

    Now time is ok, but second Data1 disappears after reset 

    Thursday, February 8, 2018 9:34 AM
  • Ok, no problem for me... a got loot of time so don't wory about me :)

    Now time is ok, but second Data1 disappears after reset 

    Change this

    If ops.UpdateRow(bsCustomers.CurrentRow, False) Then

    To this

    If ops.UpdateRow(bsCustomers.CurrentRow) Then


    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, February 8, 2018 10:53 AM