locked
update row in linked tables RRS feed

  • Question

  • The following code has been used to update the row data in the data sheet where there are two tables linked together
    But do not do anything

       con.Open()
            Dim Sav As New OleDb.OleDbCommand
            Sav.Connection = con
            Sav.CommandType = CommandType.Text
            Sav.CommandText = "UPDATE tb1,tb2 SET tb1.clien_mail = '" & _
            TextBox2.Text & "' , tb1.model_mail = '" & _
             TextBox3.Text & "' , tb2.probl_acce = '" & _
            TextBox4.Text & "' , tb2.main_acce= '" & _
            TextBox5.Text & "' ,tb2.typ_acce = '" & _
           TextBox6.Text & "' where tb1.ID_mail = tb2.ID_acce '" & _
             Label2.Text & "'"
            Sav.ExecuteNonQuery()
            Dim DataAdapter As New OleDbDataAdapter("SELECT tb1.ID_mail ,tb1.clien_mail, tb1.model_mail, tb2.probl_acce,tb2.main_acce,tb2.typ_acce  FROM tb1, tb2 where tb1.ID_mail = tb2.ID_acce", con)
            DataSet1.Clear()
            DataAdapter.Fill(DataSet1, "tb1,tb2")
            DataGridView1.Refresh()
            count()
            If con.State = ConnectionState.Open Then
                con.Close()
            End If

    Monday, January 1, 2018 7:57 PM

Answers

  • Hello,

    I would suggest doing something like the following were there is no need to reload data if the update is successful. You can update field in the current DataTable via someDataTable.Row(x).SetField(Of String)("SomeField","SomeValue").

    Note I test the return value of ExecuteNonQuery, if one row should be updated then ExecuteNonQuery would return 1, otherwise failure. Why failure? Perhaps the answer is the update statement is incorrect or you are targeting the wrong database or you have "Copy to output folder" for the database set to Copy Always rather than Copy if newer.

    In the class below, you create a new instance e.g. Dim Ops = New DataOperations.

    To update, pass in the values from the text box controls.

    BTW the WHERE condition you currently have looks odd e.g. adding Label2.Text which seems to be invalid and not sure why there is no run-time exception thrown back from the database.

    Public Class DataOperations
        Private mConnectionString As String
        Public Sub New()
            mConnectionString = "TODO"
        End Sub
        ''' <summary>
        ''' Update record in database table, if returning true
        ''' simple update the original DataTable rather than reloading
        ''' all data. e.g. someDataRow.SetField(Of String)("SomeField", "SomeValue")
        ''' </summary>
        ''' <param name="pClien_mail"></param>
        ''' <param name="pModel_mail"></param>
        ''' <param name="pProbl_acce"></param>
        ''' <param name="pMain_acce"></param>
        ''' <param name="pTyp_acce"></param>
        ''' <param name="pID_acce"></param>
        ''' <returns></returns>
        Public Function UpdateRecord(
            ByVal pClien_mail As String,
            ByVal pModel_mail As String,
            ByVal pProbl_acce As String,
            ByVal pMain_acce As String,
            ByVal pTyp_acce As String,
            ByVal pID_acce As Integer) As Boolean
    
            Using cn As New OleDbConnection With {.ConnectionString = mConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "UPDATE tb1,tb2 SET tb1.clien_mail =?,tb1.model_mail = ?, " &
                        "tb2.probl_acce = ?, tb2.main_acce= ?, tb2.typ_acce = ? " &
                        "WHERE tb1.ID_mail = tb2.ID_acce"
    
                    cmd.Parameters.AddWithValue("@P1", pClien_mail)
                    cmd.Parameters.AddWithValue("@P2", pModel_mail)
                    cmd.Parameters.AddWithValue("@P3", pProbl_acce)
                    cmd.Parameters.AddWithValue("@P4", pMain_acce)
                    cmd.Parameters.AddWithValue("@P5", pTyp_acce)
                    cmd.Parameters.AddWithValue("@P6", pID_acce)
    
                    cn.Open()
                    '
                    ' I suspect only one row should be affect, if so ExecuteNonQuery will return 1,
                    ' otherwise any other value is a failure. If a failure try creating the same
                    ' update query in ms-access and run it, see if it works there.
                    '
                    Return cmd.ExecuteNonQuery = 1
    
                End Using
            End Using
    
            Return True
    
        End Function
    
    End Class
    Lastly, are you sure the WHERE condition is met?


    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 2, 2018 1:10 AM

All replies

  • Is your DataGridView1 bound with data source?

    Ashidacchi

    Monday, January 1, 2018 10:09 PM
  • connection code

       Public con As New OleDb.OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" & Application.StartupPath & "\web_database.accdb;Jet OLEDB:Database Password=kluytiaSDqwer3210549")
        Dim DataSet1 As New DataSet
        Dim BindingSource1 As BindingSource
        Dim BindingSource2 As BindingSource
        Dim DataAdapter1 As New OleDbDataAdapter
        Sub conn()
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT tb1.ID_mail ,tb1.clien_mail, tb1.model_mail, tb2.probl_acce,tb2.main_acce,tb2.typ_acce  FROM tb1, tb2 where tb1.ID_mail = tb2.ID_acce", con)
            con.Open()
            DataAdapter1 = New OleDbDataAdapter(cmd)
            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter1)
            DataSet1 = New DataSet()
            DataAdapter1.Fill(DataSet1, "tb1,tb2")
            DataGridView1.DataSource = DataSet1
            DataGridView1.DataMember = "tb1,tb2"
            con.Close()
    
        End Sub

    Monday, January 1, 2018 10:49 PM
  • Where do you bind a data source to DataGridView1?

    Ashidacchi

    Monday, January 1, 2018 10:52 PM
  • Where do you bind a data source to DataGridView1?

    Ashidacchi

    error

    Syntax error (missing operator) in query expression 'tb1.ID_mail = tb2.ID_acce '1''

      TextBox6.Text & "' where tb1.ID_mail = tb2.ID_acce '" & _
             Label2.Text & "'"
            Sav.ExecuteNonQuery()

    Monday, January 1, 2018 11:28 PM
  • Hello,

    I would suggest doing something like the following were there is no need to reload data if the update is successful. You can update field in the current DataTable via someDataTable.Row(x).SetField(Of String)("SomeField","SomeValue").

    Note I test the return value of ExecuteNonQuery, if one row should be updated then ExecuteNonQuery would return 1, otherwise failure. Why failure? Perhaps the answer is the update statement is incorrect or you are targeting the wrong database or you have "Copy to output folder" for the database set to Copy Always rather than Copy if newer.

    In the class below, you create a new instance e.g. Dim Ops = New DataOperations.

    To update, pass in the values from the text box controls.

    BTW the WHERE condition you currently have looks odd e.g. adding Label2.Text which seems to be invalid and not sure why there is no run-time exception thrown back from the database.

    Public Class DataOperations
        Private mConnectionString As String
        Public Sub New()
            mConnectionString = "TODO"
        End Sub
        ''' <summary>
        ''' Update record in database table, if returning true
        ''' simple update the original DataTable rather than reloading
        ''' all data. e.g. someDataRow.SetField(Of String)("SomeField", "SomeValue")
        ''' </summary>
        ''' <param name="pClien_mail"></param>
        ''' <param name="pModel_mail"></param>
        ''' <param name="pProbl_acce"></param>
        ''' <param name="pMain_acce"></param>
        ''' <param name="pTyp_acce"></param>
        ''' <param name="pID_acce"></param>
        ''' <returns></returns>
        Public Function UpdateRecord(
            ByVal pClien_mail As String,
            ByVal pModel_mail As String,
            ByVal pProbl_acce As String,
            ByVal pMain_acce As String,
            ByVal pTyp_acce As String,
            ByVal pID_acce As Integer) As Boolean
    
            Using cn As New OleDbConnection With {.ConnectionString = mConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "UPDATE tb1,tb2 SET tb1.clien_mail =?,tb1.model_mail = ?, " &
                        "tb2.probl_acce = ?, tb2.main_acce= ?, tb2.typ_acce = ? " &
                        "WHERE tb1.ID_mail = tb2.ID_acce"
    
                    cmd.Parameters.AddWithValue("@P1", pClien_mail)
                    cmd.Parameters.AddWithValue("@P2", pModel_mail)
                    cmd.Parameters.AddWithValue("@P3", pProbl_acce)
                    cmd.Parameters.AddWithValue("@P4", pMain_acce)
                    cmd.Parameters.AddWithValue("@P5", pTyp_acce)
                    cmd.Parameters.AddWithValue("@P6", pID_acce)
    
                    cn.Open()
                    '
                    ' I suspect only one row should be affect, if so ExecuteNonQuery will return 1,
                    ' otherwise any other value is a failure. If a failure try creating the same
                    ' update query in ms-access and run it, see if it works there.
                    '
                    Return cmd.ExecuteNonQuery = 1
    
                End Using
            End Using
    
            Return True
    
        End Function
    
    End Class
    Lastly, are you sure the WHERE condition is met?


    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 2, 2018 1:10 AM