none
problem when update date field in database RRS feed

  • Question

  • hi

    When I update the database without the date field it is done without problems
    But when you update the database and update the date field, the process does not complete
    Although the date field with the date table is short and also in
    DateTimePicker1

    datatim

    my code

     Sub UPDATETB()
    
            Dim com As New OleDbCommand("UPDATE CUSTOMERDETAILS_TB SET CUSTOMERDETAILS_CODE=@CUSTOMERDETAILS_CODE,CUSTOMERDETALIS_NAME=@CUSTOMERDETALIS_NAME,CUSTOMERDETALIS_COMPANY=@CUSTOMERDETALIS_COMPANY,CUSTOMERDETALIS_ADDRESS=@CUSTOMERDETALIS_ADDRESS,CUSTOMERDETALIS_TELEPHON=@CUSTOMERDETALIS_TELEPHON,CUSTOMERDETALIS_NOTE=@CUSTOMERDETALIS_NOTE,CUSTOMERDETALIS_DATE=@CUSTOMERDETALIS_DATE,CUSTOMERDETALIS_SHOW=@,CUSTOMERDETALIS_SHOW WHERE CUSTOMERDETALIS_ID = @CUSTOMERDETALIS_ID", con)
            Dim checkUPDAT As Boolean = True
            Dim dateup As Date = Format(FPAPERCCLIENONUPDATE.DATATIM_DATERECOR.Value, "dd/MM/yyyy")
            Try
                con.Open()
    
                com.Parameters.AddWithValue("@CUSTOMERDETAILS_CODE", FPAPERCCLIENONUPDATE.LAB_CODEVAL.Text)
                com.Parameters.AddWithValue("@CUSTOMERDETALIS_NAME", FPAPERCCLIENONUPDATE.TEXT_CLIENTNAM.Text)
                com.Parameters.AddWithValue("@CUSTOMERDETALIS_COMPANY", FPAPERCCLIENONUPDATE.TEXT_CLIENTCOMP.Text)
                com.Parameters.AddWithValue("@CUSTOMERDETALIS_ADDRESS", FPAPERCCLIENONUPDATE.TEXT_CLIENTADDRES.Text)
                com.Parameters.AddWithValue("@CUSTOMERDETALIS_TELEPHON", FPAPERCCLIENONUPDATE.TEXT_CLIENTTELEPHON.Text)
                com.Parameters.AddWithValue("@CUSTOMERDETALIS_NOTE", FPAPERCCLIENONUPDATE.TEXT_CLIENTNOT.Text)
                com.Parameters.AddWithValue("@CUSTOMERDETALIS_DATE", dateup)
                com.Parameters.AddWithValue("@CUSTOMERDETALIS_SHOW", checkUPDAT)
                com.Parameters.AddWithValue("@CUSTOMERDETALIS_ID", FPAPERCCLIENONUPDATE.LAB_IDVAL.Text)
    
    
                If com.ExecuteNonQuery() = 1 Then
                    conn()
                    Me.DATAG_CLIENONLIN.FirstDisplayedScrollingRowIndex = Me.DATAG_CLIENONLIN.RowCount - 1
                    Me.DATAG_CLIENONLIN.Rows(Me.DATAG_CLIENONLIN.RowCount - 1).Selected = True
                    MsgBox("update success")
                Else
                    MsgBox("update field")
                End If
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                con.Close()
            End Try
        End Sub


    • Edited by ahmeddc Wednesday, October 10, 2018 11:55 PM
    Wednesday, October 10, 2018 11:53 PM

Answers

  • One thing I notice was 

    Dim dateup As Date = Format(FPAPERCCLIENONUPDATE.DATATIM_DATERECOR.Value, "dd/MM/yyyy")

    DateTime are stored in a database table without any formatting, you can of course show the date/time field formatted but it's not actually stored with formatting.

    In the following code sample which is from a MSDN code sample I wrote takes a DataRow where JoinDate is a DateTime column (no formatting as indicated above). Note each field type is defined from the DataRow via Field(Of X).

    Note the method shown is part of a data class where the lines commented with ignore are from a base class which don't affect the outcome of the SQL.

    Public Function UpdateRowDemo(pDataRow As DataRow) As Boolean
    
        Try
            Using cn As New OleDbConnection("....")
    
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                        UPDATE 
                            Customer 
                        SET 
                            CompanyName = @CompanyName, 
                            ContactName = @ContactName,
                            ContactTitle = @ContactTitle,
                            JoinDate = @JoinDate
                        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"))
                    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 ' ignore
                        mLastException = ex ' ignore
    
                        Return IsSuccessFul ' ignore
    
                    End Try
                End Using
            End Using
    
        Catch ex As Exception
            mHasException = True ' ignore
            mLastException = ex ' ignore
    
            Return IsSuccessFul ' ignore
    
        End Try
    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

    • Marked as answer by ahmeddc Thursday, October 11, 2018 1:10 PM
    Thursday, October 11, 2018 10:46 AM
    Moderator

All replies

  • Hi,

    I test your code and it works fine.

    Please set a breakpoint to see which step the problem is and give more code.

    Best Regards,

    Alex


    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.

    Thursday, October 11, 2018 1:38 AM
  • You should be able to use the datetimepicker.value as the second arg in AddWithValue.

    Also, are you getting the messagebox? aka, an exception?


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thursday, October 11, 2018 1:45 AM
  • One thing I notice was 

    Dim dateup As Date = Format(FPAPERCCLIENONUPDATE.DATATIM_DATERECOR.Value, "dd/MM/yyyy")

    DateTime are stored in a database table without any formatting, you can of course show the date/time field formatted but it's not actually stored with formatting.

    In the following code sample which is from a MSDN code sample I wrote takes a DataRow where JoinDate is a DateTime column (no formatting as indicated above). Note each field type is defined from the DataRow via Field(Of X).

    Note the method shown is part of a data class where the lines commented with ignore are from a base class which don't affect the outcome of the SQL.

    Public Function UpdateRowDemo(pDataRow As DataRow) As Boolean
    
        Try
            Using cn As New OleDbConnection("....")
    
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                        UPDATE 
                            Customer 
                        SET 
                            CompanyName = @CompanyName, 
                            ContactName = @ContactName,
                            ContactTitle = @ContactTitle,
                            JoinDate = @JoinDate
                        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"))
                    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 ' ignore
                        mLastException = ex ' ignore
    
                        Return IsSuccessFul ' ignore
    
                    End Try
                End Using
            End Using
    
        Catch ex As Exception
            mHasException = True ' ignore
            mLastException = ex ' ignore
    
            Return IsSuccessFul ' ignore
    
        End Try
    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

    • Marked as answer by ahmeddc Thursday, October 11, 2018 1:10 PM
    Thursday, October 11, 2018 10:46 AM
    Moderator