my UPDATE command

[student21]

Hi i have my INSERT and DELETE command working fine my data gets saved to my database to deleted,
my problem is my UPDATE command. i have a form which displays 'employee' information the form includes text boxes i.e forname surnam etc and this is where the data is displayed when i select a text box and edit the data for instance if i chance the surname and then select my button on my form to update the data back to the database it changes all of the data so that the fornames are no longer names they all display -1 as a forname i dont know why this happens i was wondering if someone could explain where i am going wrong
my code is

OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand

        OleDbConnection1 = New System.Data.OleDb.OleDbConnection


        OleDbUpdateCommand1.CommandText = "UPDATE Employees SET Forename = @p1 OR  Surname = @p2 OR  Role = @p3 OR  Email = @p4 OR  Telephone = @p5 OR  Extension = @p6 OR  Mobile =@p7"
        OleDbUpdateCommand1.Connection = Me.OleDbConnection1
        OleDbUpdateCommand1.Parameters.AddWithValue("@p1", ForenameTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p2", SurnameTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p3", RoleTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p4", EmailTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p5", TelephoneTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p6", ExtensionTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p7", MobileTextBox.Text)
        OleDbUpdateCommand1.Connection = Me.OleDbConnection1

        OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\mydatabase.mdb"

        Try

            OleDbConnection1.Open()

            OleDbUpdateCommand1.ExecuteNonQuery()

        Catch ex As OleDb.OleDbException

            MessageBox.Show(ex.ToString)

        End Try

        OleDbConnection1.Close()
        MsgBox("Employee Details Have Now Been Deleted From Database", MsgBoxStyle.OkOnly + MsgBoxStyle.OkOnly)
    End Sub

[kvprajapati]

Comma (,) separator instead of "OR".

UPDATE TableName set col1=@p1,col2=@p2 where col3=@p3

[student21]

hi, thank you i took the OR out and replaced it with a commer so my code is not

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand

        OleDbConnection1 = New System.Data.OleDb.OleDbConnection


        OleDbUpdateCommand1.CommandText = "UPDATE Employees SET Forename = @p1,Surname = @p2,Role = @p3,Email = @p4,Telephone = @p5,Extension = @p6,Mobile =@p7"
        OleDbUpdateCommand1.Connection = Me.OleDbConnection1
        OleDbUpdateCommand1.Parameters.AddWithValue("@p1", ForenameTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p2", SurnameTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p3", RoleTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p4", EmailTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p5", TelephoneTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p6", ExtensionTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p7", MobileTextBox.Text)
        OleDbUpdateCommand1.Connection = Me.OleDbConnection1

        OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\mydatabase.mdb"

        Try

            OleDbConnection1.Open()

            OleDbUpdateCommand1.ExecuteNonQuery()

        Catch ex As OleDb.OleDbException

            MessageBox.Show(ex.ToString)

        End Try

        OleDbConnection1.Close()
        MsgBox("Employee Details Have Now Been Deleted From Database", MsgBoxStyle.OkOnly + MsgBoxStyle.OkOnly)
   

    End Sub
End Class

after changing the code i went in and changed the surname using the surname text box and now the code changes all y data to the one ive changed i dont understand why? ive gone from all of the forename chanine to -1 to now all of the records change to the one i have updated?

[Oxiegen]

In your SELECT query you need to also extract the record ID and use that in a WHERE-clause for your UPDATE query. UPDATE TableName set col1=@p1,col2=@p2 where col3=@p3 WHERE RecordID = <some_number>

[student21]

Hi thank you for your response
do i chnge my select query from

SELECT        EmployeeID, DepartmentID, Forename, Surname, Role, Email, Telephone, Extension, Mobile, Photo
FROM            Employees

....
to this

SELECT        EmployeeID, DepartmentID, Forename, Surname, Role, Email, Telephone, Extension, Mobile, Photo
FROM            Employees
WHERE EmployeeID =<some_number>

[Oxiegen]

Not unless that part of your program didn't work before.
Because you already SELECT the EmployeeID, all you need to do is use that in your UPDATE query.

[student21]

Hi sorry i dont really understand should i just change it from

 "UPDATE Employees SET Forename = @p1,Surname = @p2,Role = @p3,Email = @p4,Telephone = @p5,Extension = @p6,Mobile =@p7"

to this

"UPDATE Employees SET EmployeeID=@p1 Forename = @p2,Surname = @p3,Role = @p4,Email = @p5,Telephone = @p6,Extension = @p7,Mobile =@p8"

[Oxiegen]

Use this code for your update (just add the parts in red).

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand

        OleDbConnection1 = New System.Data.OleDb.OleDbConnection


        OleDbUpdateCommand1.CommandText = "UPDATE Employees SET Forename = @p1,Surname = @p2,Role = @p3,Email = @p4,Telephone = @p5,Extension = @p6,Mobile =@p7 WHERE EmployeeID = $p8"
        OleDbUpdateCommand1.Connection = Me.OleDbConnection1
        OleDbUpdateCommand1.Parameters.AddWithValue("@p1", ForenameTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p2", SurnameTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p3", RoleTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p4", EmailTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p5", TelephoneTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p6", ExtensionTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p7", MobileTextBox.Text)
        OleDbUpdateCommand1.Parameters.AddWithValue("@p8", EmployeeID.Text)
        OleDbUpdateCommand1.Connection = Me.OleDbConnection1

        OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\mydatabase.mdb"

        Try

            OleDbConnection1.Open()

            OleDbUpdateCommand1.ExecuteNonQuery()

        Catch ex As OleDb.OleDbException

            MessageBox.Show(ex.ToString)

        End Try

        OleDbConnection1.Close()
        MsgBox("Employee Details Have Now Been Deleted From Database", MsgBoxStyle.OkOnly + MsgBoxStyle.OkOnly)
   

    End Sub
End Class