CODEWITHSUNDEEP
asp.netsqlvb.net
user3495134
user3495134

Reputation: 1

SQL update query VB ASP

I'm trying to do an update query in SQL Server database, through VB with ASP.net

This bit of code below updates all records with the same value. I want it to update just one record, depending on the "email" session variable.

Dim cmdstring As String = "UPDATE [Customer] SET card_type=@CARDTYPE"

    Email = Session("email")
    ', Card_Number, Expiry_Date, Security_Number, Name_On_Card) Values (@CARDTYPE, @CARDNUMBER, @EXPIRYDATE, @SECURITYNUMBER, @NAMEONCARD)"

    conn = New SqlConnection("data source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UniversityClothing.mdf;Integrated Security=True;User Instance=True")
    cmd = New SqlCommand(cmdstring, conn)

    cmd.Parameters.AddWithValue("@CARDTYPE", cardtype)
    cmd.Parameters.AddWithValue("@CARDNUMBER", cardnumber)
    cmd.Parameters.AddWithValue("@EXPIRYDATE", expirydate)
    cmd.Parameters.AddWithValue("@SECURITYNUMBER", securitynumber)
    cmd.Parameters.AddWithValue("@NAMEONCARD", nameoncard)

    conn.Open()

    cmd.ExecuteNonQuery()

    conn.Close()

Upvotes: 0

Views: 467

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 416121

Do this:

Using conn As New SqlConnection("data source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UniversityClothing.mdf;Integrated Security=True;User Instance=True"), _
      cmd As New SqlCommand("UPDATE [Customer] SET card_type=@CARDTYPE WHERE email = @Email", conn)

    cmd.Parameters.AddWithValue("@CARDTYPE", cardtype)
    cmd.Parameters.AddWithValue("@Email", Session("email"))

    conn.Open()
    cmd.ExecuteNonQuery()
End Using

Also note that I really don't like the AddWithValue() method. It can lead to serious performance issues.

Upvotes: 1

GratefulDisciple
GratefulDisciple

Reputation: 704

You need to add a WHERE clause. Example:

Dim cmdstring As String = "UPDATE [Customer] SET card_type=@CARDTYPE, Card_Number=@CARDNUMBER, Expiry_Date=@EXPIRYDATE, Security_Number=@SECURITYNUMBER, Name_On_Card=@NAMEONCARD WHERE Email=@EMAIL)"
Email = Session("email")

conn = New SqlConnection("data source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UniversityClothing.mdf;Integrated Security=True;User Instance=True")
cmd = New SqlCommand(cmdstring, conn)
cmd.Parameters.AddWithValue("@CARDTYPE", cardtype)
cmd.Parameters.AddWithValue("@CARDNUMBER", cardnumber)
cmd.Parameters.AddWithValue("@EXPIRYDATE", expirydate)
cmd.Parameters.AddWithValue("@SECURITYNUMBER", securitynumber)
cmd.Parameters.AddWithValue("@NAMEONCARD", nameoncard)
cmd.Parameters.AddWithValue("@EMAIL", Email)

conn.Open()

cmd.ExecuteNonQuery()

conn.Close()

NOTE THE FOLLOWING:

  1. Per PCI regulation, you should not store security number, and should do appropriate encryption for your card number
  2. I assume you have validated all the fields to prevent SQL injection

Upvotes: 0

Related Questions