Problems in update and delete commands

[kinjal.jain.7393]

I am creating a project in winforms in vb.net. My requirement is to update and delete records in SQL database with a button click event. My update coding updates the whole table instead of a particular database row.I have four columns in my table. Is there any coding way where I can randomly update any two cell of a row with a where clause or condition?

[Begginnerdev]

Quite a simple solution to your problem would be to write a query that does so!

For example:

Private Sub Button1_Click(ByVal sender as Object, ByVal e As EventArgs) Handles Button1.Click
   Dim con As New OleDBConnection("YourConnectionStringHere")
   Dim cmd As New OleDBCommand("UPDATE table1 SET col2=? WHERE col1=?",con)
   Try
      'When using OleDB you must use ? for the parameter.
      'When using other libraries use @parameter to name them.   
      cmd.Parameters.AddWithValue("@val",ValueToInsert)
      cmd.Parameters.AddWithValue("@myUniqueVal",UniqueValue)

      'Once added, the query will look like:
      ' UPDATE table1 SET col2=ValueToInsert WHERE col1=UniqueValue
      con.Open
      cmd.ExecuteNonQuery()
   Catch ex As Exception
      MsgBox(ex.Message)
   End Try
End Sub

[Santanu.Das]

Did you check & confirm that the value of the matching feild of that particular row differ from the condition value in where clause?

What do you mean by ?

where I can randomly update any two cell of a row

You can update any number of valid fields values of a single/multiple row/rows, if you create proper SQL Statement.
The UPDATE SQL Statement is

Update <YourTableName> Set <FieldName1>='Value1', <FieldName2>='Value2'......... Where <ConditionFieldName1>='ConditionValue1' And <ConditionFieldName2>='ConditionValue2'

[Piolo_1]

what tools do you want to use? simplify

Datagridview or textbox.

''''''Datagridview

For Each row As DataGridViewRow In dgv1.Rows
If row.Cells(0).FormattedValue <> "" Or row.Cells(1).FormattedValue <> "" Or row.Cells(2).FormattedValue <> "" Or row.Cells(3).FormattedValue <> "" Or row.Cells(5).FormattedValue <> "" Or row.Cells(4).FormattedValue <> Then
Using cmd As New MySqlCommand("Update tablename SET culomn1='" & (row.Cells(0).FormattedValue) & "',culomn2='" & (row.Cells(2).FormattedValue) & "', culomn3='" & (row.Cells(7).FormattedValue) & "'
where Column1='" & (row.Cells(0).FormattedValue) & "'", conString)
cmd.CommandType = CommandType.Text
result = cmdtransfer.ExecuteNonQuery
End Using
End If
Next

[Piolo_1]

you have two options to use either textboxes or datagridviews

For Each row As DataGridViewRow In dgv1.Rows                   
                   If row.Cells(0).FormattedValue <> "" Or row.Cells(1).FormattedValue <> "" Or row.Cells(2).FormattedValue <> "" Or row.Cells(3).FormattedValue <> ""  Then
                  Using cmd As New MySqlCommand("Update tablename SET `culomn1`='" & (row.Cells(0).FormattedValue) & "',`culomn2`='" & (row.Cells(2).FormattedValue) & "', `culomn3`='" & (row.Cells(7).FormattedValue) & "'
                 where `Column1`='" & (row.Cells(0).FormattedValue) & "'", conString)
                            cmd.CommandType = CommandType.Text
                            result = cmdtransfer.ExecuteNonQuery
                        End Using
                    End If
                Next

---------------------------------------------------------------------------

Using conupdate As New MySqlConnection("server=localhost;user id=root;pwd=password;database=inventorysystem")
                conupdate.Open()
                Try
                    Using cmd As New MySqlCommand("UPDATE table1 SET `Column1`='" & txt1.Text & "', `Column2`='" & txt2.text & "'' where `Column1`='" & txt1.text & "'", conupdate)
                        cmd.ExecuteNonQuery()
                        cmd.CommandType = CommandType.Text
                    End Using
'''