Updating sql server through classic asp and vbscript

Question

Im trying to update details of a single customer and I'm having problems updating with the new user input. I can see the changes being passed but its not updating the sql. Here is the code -

    'Update' 
    updateC = request.QueryString("action")
    if updateC = "update" then

        Id = request.QueryString("Id")
        Name = request.QueryString("Name")
        Address = request.QueryString("Address") 
        Suburb = request.QueryString("Suburb") 
        Postcode = request.QueryString("Postcode")
        Age = request.QueryString("Age")
        Email = request.QueryString("Email")

    end if


    %>
    <form method="get" action="CreateCustomer.asp">
    Name:&nbsp;&nbsp;&nbsp;&nbsp; <input type="text" value="<%=Name %>" name="Name"><br/>
    Address:&nbsp; <input type="text" value="<%=Address %>" name="Address"><br/>
    Suburb:&nbsp;&nbsp;&nbsp; <input type="Suburb" value="<%=Suburb %>"  name="Suburb"><br/>
    Postcode: <input type="text" value="<%=Postcode %>"  name="Postcode"><br/>
    Age:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <input type="text" value="<%=Age %>"  name="Age"><br/>
    Email:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <input type="text" value="<%=Email %>"  name="Email"><br/><br/>
    <% if updateC = "update" then%>
        <input type="hidden" value="update" name="updateButton">
        <input type="submit" value="Update Customer">            
    <% else %>
        <input type="hidden" value="insert" name="insert">
        <input type="submit" value="New Customer">
    <% end if %>

    </form>

    <%       


    'Assign Variables'
    insertCheck = request.QueryString("insert")
    updCheck = request.QueryString("updateButton")
    if insertCheck = "insert" or updCheck = "update" then

        ID = request.QueryString("Id")
        Name = request.QueryString("Name")
        Address = request.QueryString("Address")
        Suburb = request.QueryString("Suburb")
        Postcode = request.QueryString("Postcode")
        Age = request.QueryString("Age")
        Email = request.QueryString("Email")

    end if

'update customer'
    updButton = request.QueryString("updateButton")
    if updButton = "update" and name<>"" then
        updateCustomer()            
    end if


     'Update customer sub procedure'
  sub updateCustomer()

        Dim uSQL, objCon

        Set objCon = CreateObject("ADODB.Connection")
        objCon.Open "Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=xxxx;Initial Catalog=Customer;Data Source=PC"

        uSQL = "UPDATE Customer SET Name = " & "'" & Name & "'" & " Where ID = " & "'" & Id & "'"
        objCon.Execute(uSQL)

        uSQL = "UPDATE Customer SET Address = " & "'" &  Address & "'" & " Where ID = " & "'" & Id & "'"
        objCon.Execute(uSQL)

        uSQL = "UPDATE Customer SET Suburb = " & "'" &  Suburb & "'" & " Where ID = " & "'" & Id & "'"
        objCon.Execute(uSQL)

        uSQL = "UPDATE Customer SET Postcode = " & "'" &  Postcode & "'" & " Where ID = " & "'" & Id & "'"
        objCon.Execute(uSQL)

        uSQL = "UPDATE Customer SET Age = " & "'" &  Age & "'" & " Where ID = " & "'" & Id & "'"
        objCon.Execute(uSQL)

        uSQL = "UPDATE Customer SET Email = " & "'" &  Email & "'" & " Where ID = " & "'" & Id & "'"  
        objCon.Execute(uSQL)

        objCon.Close

  end sub

The code above is from createcustomer.asp and the code below is from table.asp

        <td><Center><a href="CreateCustomer.asp?action=update&Id=<%= objRS("Id") %>&Name=<%= objRS("Name") %>&Address=<%= objRS("Address") %>&suburb=<%= objRS("Suburb") %>&postcode=<%= objRS("Postcode") %>&age=<%= objRS("Age") %>&email=<%= objRS("Email") %>">
        <input type="submit" value="Update"></a></Center></td>

Answer 1

Change

<% if updateC = "update" then%>
    <input type="hidden" value="update" name="updateButton">
    <input type="submit" value="Update Customer">            
<% else %>
    <input type="hidden" value="insert" name="insert">
    <input type="submit" value="New Customer">
<% end if %>

to

<% if updateC = "update" then%>
    <input type="hidden" value="<%=id%>" name="id">
    <input type="hidden" value="update" name="updateButton">
    <input type="submit" value="Update Customer">            
<% else %>
    <input type="hidden" value="insert" name="insert">
    <input type="submit" value="New Customer">
<% end if %>

Because in your current code you do not pass the id of the customer so the update method does not know who to update.

---

As others have stated though there is room for a lot of improvement, like

• avoid SQL Injection attack by sanitizing your input or using parameterized queries.
• Update the record in one go instead of an update for each field.
• Re-use your declared variable instead of reading the queryString whenever you need something (you already have most values in variables)

Answer 2

Change

updateC = request.QueryString("action")

to

updateC = request.QueryString("updateButton")