CODEWITHSUNDEEP
sqlvb.net
Adariel Lzinski
Adariel Lzinski

Reputation: 1121

Update is updating ALL records even with WHERE clause

So... I don't know what is happening here. I have a variable set that holds the CURRENT customerID and compares to the textbox.text to update just that record...

Dim updateStatement As String =
    "UPDATE Customers SET " &
    "Name = """ & txtName.Text & """, " &
    "Address = """ & txtAddress.Text & """, " &
    "City = """ & txtCity.Text & """, " &
    "State = """ & txtState.Text & """, " &
    "ZipCode = """ & txtZipCode.Text & """" &
    "WHERE """ & txtCustomerID.Text & """ = """ & customerID & """"

Here's is the entire method code:

Private Sub UpdateCustomer()
    Dim connection As OleDbConnection = MMABooksDB.GetConnection()
    Dim updateStatement As String =
    "UPDATE Customers SET " &
    "Name = """ & txtName.Text & """, " &
    "Address = """ & txtAddress.Text & """, " &
    "City = """ & txtCity.Text & """, " &
    "State = """ & txtState.Text & """, " &
    "ZipCode = """ & txtZipCode.Text & """" &
    "WHERE """ & txtCustomerID.Text & """ = """ & customerID & """"


    Dim updateCommand As New OleDbCommand(updateStatement, connection)

    Try
        connection.Open()
        updateCommand.ExecuteNonQuery()
        Dim oledbCmd As New OleDbCommand("SELECT @@IDENTITY", connection)
        Dim customerID As Integer = customerID
    Catch ex As OleDbException : Throw ex
    Finally
        connection.Close()
    End Try
End Sub

So whenever I hit accept to update, it updates ALL records of the database...

Edit: And yes I am aware that this is "bad programming" when not using parameters, but this is how the instructor wants it done.

Upvotes: 1

Views: 379

Answers (1)

Steven Doggart
Steven Doggart

Reputation: 43743

The problem is here:

"WHERE """ & txtCustomerID.Text & """ = """ & customerID & """"

Assuming that customerID (whatever that variable is) equals the same thing as the ID in the text box, that equates to something like:

WHERE "1" = "1"

Which, of course, is always true, so all rows match that WHERE clause. You probably meant something like:

"WHERE CustomerId = """ & txtCustomerID.Text & """"

(where CustomerId is the name of your ID column) It would be much better, though, to use a parameter, since the way you have it could lead to SQL injection attacks. 

"WHERE CustomerId = @CustomerId"

Upvotes: 8

Related Questions