none
Question about databases : "UPDATE ... SET... " RRS feed

  • Question

  • Greetings.

    I still  have the same problem as mentioned in this link.

    How can I get the statement out of the linear statement " --- " and put some controls' value inside the statement ?

    Suppose I have : "Update Clients Set Found = '200550' Where ID = '100020'";

    I need it to be :  "Update"   txtname.text  "SET"  txtcontent.text  ...

    Thanks in advance.


    • Edited by Kevin993 Tuesday, February 20, 2018 1:59 PM
    Tuesday, February 20, 2018 1:57 PM

Answers

  • Hi Kevin,

    Sorry for a late response, busy work day.

    Try the following example I created that uses a DataTable as the data source of a DataGridView. Make a change to the Found column in the DataGridView, leave the cell and the field is updated just like if done in ms-access.

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


    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 Kevin993 Wednesday, February 21, 2018 8:32 AM
    Wednesday, February 21, 2018 1:20 AM
    Moderator

All replies

  • Hello,

    It's always best to use parameters. Note the parameters are named yet that does not matter for MS-Access which does parameters in ordinal position unlike SQL-Server which uses named parameters and can be done out of position of the variables used in the SQL UPDATE statement.

    As coded this will work with MS-Access and by changing the data provider will work with SQL-Server. Note how I did the command text, this is for VS2013 and higher  

    Public Function UpdateRowForForumQuestion(ByVal pIdentifer As Integer, ByVal pValue As String) As Boolean
        Using cn As New OleDbConnection("Your connection string goes here")
    
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        UPDATE 
                            Clients 
                        SET 
                            Found = @Found, 
                        WHERE Id = @Identifier
                    </SQL>.Value
    
                cmd.Parameters.AddWithValue("@Found", pValue)
                cmd.Parameters.AddWithValue("@ContactName", pIdentifer)
    
                Try
    
                    cn.Open()
                    Return cmd.ExecuteNonQuery = 1
    
                Catch ex As Exception
                    '
                    ' for a real app we handle this better
                    '
                    Return False
                End Try
            End Using
        End Using
    End Function

    Alternate method to do the command text

    Public Function UpdateRowForForumQuestion(ByVal pIdentifer As Integer, ByVal pValue As String) As Boolean
        Using cn As New OleDbConnection("Your connection string goes here")
    
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText = "UPDATE Clients SET Found = @Found, WHERE Id = @Identifier"
                cmd.Parameters.AddWithValue("@Found", pValue)
                cmd.Parameters.AddWithValue("@ContactName", pIdentifer)
    
                Try
    
                    cn.Open()
                    Return cmd.ExecuteNonQuery = 1
    
                Catch ex As Exception
                    '
                    ' for a real app we handle this better
                    '
                    Return False
                End Try
            End Using
        End Using
    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

    • Proposed as answer by Cor Ligthert Tuesday, February 20, 2018 2:15 PM
    Tuesday, February 20, 2018 2:15 PM
    Moderator
  • Hello,

    It's always best to use parameters. Note the parameters are named yet that does not matter for MS-Access which does parameters in ordinal position unlike SQL-Server which uses named parameters and can be done out of position of the variables used in the SQL UPDATE statement.

    As coded this will work with MS-Access and by changing the data provider will work with SQL-Server. Note how I did the command text, this is for VS2013 and higher  

    Public Function UpdateRowForForumQuestion(ByVal pIdentifer As Integer, ByVal pValue As String) As Boolean
        Using cn As New OleDbConnection("Your connection string goes here")
    
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        UPDATE 
                            Clients 
                        SET 
                            Found = @Found, 
                        WHERE Id = @Identifier
                    </SQL>.Value
    
                cmd.Parameters.AddWithValue("@Found", pValue)
                cmd.Parameters.AddWithValue("@ContactName", pIdentifer)
    
                Try
    
                    cn.Open()
                    Return cmd.ExecuteNonQuery = 1
    
                Catch ex As Exception
                    '
                    ' for a real app we handle this better
                    '
                    Return False
                End Try
            End Using
        End Using
    End Function

    Alternate method to do the command text

    Public Function UpdateRowForForumQuestion(ByVal pIdentifer As Integer, ByVal pValue As String) As Boolean
        Using cn As New OleDbConnection("Your connection string goes here")
    
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText = "UPDATE Clients SET Found = @Found, WHERE Id = @Identifier"
                cmd.Parameters.AddWithValue("@Found", pValue)
                cmd.Parameters.AddWithValue("@ContactName", pIdentifer)
    
                Try
    
                    cn.Open()
                    Return cmd.ExecuteNonQuery = 1
    
                Catch ex As Exception
                    '
                    ' for a real app we handle this better
                    '
                    Return False
                End Try
            End Using
        End Using
    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

    is Pidentifier the index of database cell that should be updated ? and pValue is the new updated value of the cell ?

    Tuesday, February 20, 2018 5:48 PM
  • pIdentifier is the primary key to the record in the database table and pValue is the value to update the field too

    So pIdentifier in this case is 100020 and pValue is 200550

    "Update Clients Set Found = '200550' Where ID = '100020'";


    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 20, 2018 6:18 PM
    Moderator
  • Hi Kevin,

    Sorry for a late response, busy work day.

    Try the following example I created that uses a DataTable as the data source of a DataGridView. Make a change to the Found column in the DataGridView, leave the cell and the field is updated just like if done in ms-access.

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


    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 Kevin993 Wednesday, February 21, 2018 8:32 AM
    Wednesday, February 21, 2018 1:20 AM
    Moderator
  • [...]

    Suppose I have : "Update Clients Set Found = '200550' Where ID = '100020'";

    I need it to be :  "Update"   txtname.text  "SET"  txtcontent.text  ...




    If you need a statement where ‘Clients’ and ‘Found’ are the changeable parts, then I think you have to consider string concatenation, for example:

       Dim s = "UPDATE [" & txtname.Text & "] SET [" & txtcontent.Text & "] = ? WHERE [Id]=?"

    To specify the values, use parameters.


    • Edited by Viorel_MVP Wednesday, February 21, 2018 6:40 AM
    Wednesday, February 21, 2018 6:40 AM
  • Hi Kevin,

    Sorry for a late response, busy work day.

    Try the following example I created that uses a DataTable as the data source of a DataGridView. Make a change to the Found column in the DataGridView, leave the cell and the field is updated just like if done in ms-access.

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


    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


    Thanks a lot.
    Wednesday, February 21, 2018 8:33 AM
  • Thank you buddy. That's also what I was looking for
    Wednesday, February 21, 2018 8:33 AM