How to update a record in a table without an index

esposito

Well-known member
Joined
Jul 11, 2003
Messages
103
Location
Perugia - Italy
I have created an Access database called Customers.mdb containing a table called Customers with three fields called Surname, FirstName and Phone. The .mdb file contains five records and is in the same folder as the .aspx file.

I have then created a form in ASP.NET Web Matrix containing the following namespaces:

Code:
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>

I have then placed three TextBox controls on the form and a button that should allow me to update the current record.

Unfortunately, theres no field in the table with an ID, so the only way I could find to update the record was using the following code.

Code:
Sub btnUpdate_Click(sender As Object, e As EventArgs)

                 Dim MySQL as string = "UPDATE Customers SET Surname = @Surname, FirstName = @FirstName, Phone = @Phone WHERE Surname = @Surname"
                 Dim myConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" &  "Data Source=" & server.mappath("Customers.mdb") & ";")

                 Dim Cmd as New OleDbCommand(MySQL, MyConn)
                 cmd.Parameters.Add(New OleDbParameter("@Surname", txtSurname.text))
                 cmd.Parameters.Add(New OleDbParameter("@FirstName", txtFirstName.text))
                 cmd.Parameters.Add(New OleDbParameter("@Phone", txtPhone.text))

                 MyConn.Open()
                 cmd.ExecuteNonQuery
                 MyConn.Close()

End Sub

The only problem with the code above is that if the database contains two or more records with identical surnames, they are all updated with the same data when I click the button.

Do you know any safer way to update the current record?

TIA
 
So youll have to store the original values somewhere and then use them in your WHERE clause. If your primary key is Surname + FirstName, then you have to do something like this:

Code:
Sub btnUpdate_Click(sender As Object, e As EventArgs)

                 Dim MySQL as string = "UPDATE Customers SET Surname = @Surname, FirstName = @FirstName, Phone = @Phone WHERE Surname = @OldSurname AND FirstName = @OldFirstName"
                 Dim myConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" &  "Data Source=" & server.mappath("Customers.mdb") & ";")

                 Dim Cmd as New OleDbCommand(MySQL, MyConn)
                 cmd.Parameters.Add(New OleDbParameter("@Surname", txtSurname.text))
                 cmd.Parameters.Add(New OleDbParameter("@FirstName", txtFirstName.text))
                 cmd.Parameters.Add(New OleDbParameter("@Phone", txtPhone.text))
                 cmd.Parameters.Add(New OleDbParameter("@OldSurname", OldSurname))
                 cmd.Parameters.Add(New OleDbParameter("@OldFirstName", OldFirstName))


                 MyConn.Open()
                 cmd.ExecuteNonQuery
                 MyConn.Close()

End Sub
 


Write your reply...
Back
Top