Compare multiple fields in RecordSet and update only existing records?

[OreoBean1]

Hey guys,

I'm setting up a new page to connect to a recordset and only update a record that exists. The page is supposed to compare three fields in the record (to know which record to update). I know the page is working, it returns the error "No update permissions!". I still don't know if the code is right. I think I've removed any mis-spellings. Here is the entire page:

<%
[COLOR=green]'connect to DB[/color]
Dim conn
Dim connectstr
Dim db_name, db_usrname, db_usrpwd
Dim db_table
Dim db_server
Dim fnUpdStr, lnUpdStr, ad1UpdStr

db_server= "[COLOR=green][i]SERVER_NAME[/i][/color]"
db_name= "[COLOR=green][i]DATABASE_NAME[/i][/color]"
db_table= "[COLOR=green][i]TABLE_NAME[/i][/color]"
db_usrname= "[COLOR=green][i]USER_NAME[/i][/color]"
db_usrpwd= "[COLOR=green][i]PASSWORD[/i][/color]"
%>

<h3>Update Record</h3>
<%
[COLOR=green]'connect to DB.Recordset[/color]
set conn=Server.CreateObject("ADODB.Connection")
connectstr= "Driver={MySQL ODBC 3.51 Driver};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_usrname & ";PWD=" & db_usrpwd
conn.Open connectstr
fnUpdStr= Request.Form("fname")
lnUpdStr= Request.Form("lname")
ad1UpdStr= Request.Form("address1")
if Request.form("fname")="" then
  set rs=Server.CreateObject("ADODB.Recordset")
  rs.open "SELECT * FROM customers WHERE fname='" & fnUpdStr & "' AND lname='" & lnUpdStr & "' AND address1='" & ad1UpdStr & "'",conn
  %>
[COLOR=green]'display the form[/color]
<form method="post" action="[COLOR=green][i]SAME_ASP_PAGE[/i][/color]">
  <table>
  <%for each x in rs.Fields%>
  <tr>
  <td><%=x.name%></td>
  <td><input name="<%=x.name%>" value="<%=x.value%>" size="20"></td>
  <%next%>
  </tr>
  </table>
  <br /><br />
  <input type="submit" value="Update record">
  </form>
[COLOR=green]'establish update argument[/color]
<%
else
  sql="UPDATE customers SET "
  sql=sql & "fname='" & Request.Form("fname") & "',"
  sql=sql & "lname='" & Request.Form("lname") & "',"
  sql=sql & "address1='" & Request.Form("address1") & "',"
  sql=sql & "address2='" & Request.Form("address2") & "',"
  sql=sql & "city='" & Request.Form("city") & "',"
  sql=sql & "state='" & Request.Form("state") & "',"
  sql=sql & "zip='" & Request.Form("zip") & "',"
  sql=sql & "country='" & Request.Form("country") & "'"
  sql=sql & "homephone='" & Request.Form("homephone") & "',"
  sql=sql & "cellphone='" & Request.Form("cellphone") & "',"
  sql=sql & "email='" & Request.Form("email") & "',"
  sql=sql & " WHERE fname='" & fnUpdStr & "' AND lname='" & lnUpdStr & "' AND address1='" & ad1UpdStr & "'"
  on error resume next
  conn.Execute sql
  if err<>0 then
    response.write("No update permissions!")
  else 
    response.write("Record " & cid & " was updated!")
  end if 
end if
conn.close
%>

Any suggestions?

 

[steven290]

use this for your error handle

if err<>0 then
    err = err & Err.Number & " / " & Err.Description & "<br>"
    response.write err
  else
    response.write("Record " & cid & " was updated!")
end if

 

[steven290]

but at glance I can see that your doing a "if first name field is blank select from customers where fname is equal to first name (blank, will not work), i think you use was suppose to use

...
if Request.form("fname")<>"" then
...

 

[OreoBean1]

...
if Request.form("fname")<>"" then
...

Thanks for the quick reply! When I use this:

if Request.form("fname")="" then

I think it displays the blank form without any preset values (which is what it should do). I'm not at that PC now, I'll what you suggested when I am though and see what it does.

 

[steven290]

but your trying to select from the db even though its blank

if Request.form("fname")="" then
  set rs=Server.CreateObject("ADODB.Recordset")
  rs.open "SELECT * FROM customers WHERE fname='" & fnUpdStr & "' AND lname='" & lnUpdStr & "' AND address1='" & ad1UpdStr & "'",conn

 

[OreoBean1]

Well, I tried what you said (both the error reporting and the SQL select). Now it thinks it's working. I checked the database and it didn't update the record. It also returned the wrong CID number when it said it updated.

Am I missing something? Should I use more arguments to define the record it should edit? I want it to search all the records and only update the one. I thought if it compares three fields it would match less records. I can't tell if it's even matching the record.

 

[OreoBean1]

Steven290,

It returns "Update Record!... CID 13" when I use the error reporting you recommended. Which should be a good thing, right? Also, if I do:

if Request.form("fname")<>"" then

wouldn't it search the records after the field is populated?Because, right now it won't even display the form. I can get the form with:

if Request.form("fname")="" then

but as you know, it doesn't change any records. I even tested it with an existing record I used the values from the three fields it should compare, and only if those three fields match (which they do) it should update that record. I figure it should eliminate changing any records that it shouldn't be changing.

So maybe I should put my SELECT argument after the form display?

 

[steven290]

if you use the select on the form, you can't use the request("fname") as a search criteria

 

[OreoBean1]

so I should pull all the records then use the Request("fname") from the form. ok, I'll try that!

 

[OreoBean1]

I didn't realize it at first, but this script has a second part to it. The script before this pulls all records and displays them, changing one field into a link to a form, something like this:

<%
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM customers",conn
%>

<h2>List Database</h2>
<table border="1" width="100%">
<tr>
<%
for each x in rs.Fields
  response.write("<th>" & ucase(x.name) & "</th>")
next
do until rs.EOF 
%>
<tr>
<form method="post" action="[COLOR=green][i]SAMPLE.ASP[/i][/color]">
<%
for each x in rs.Fields
  if lcase(x.name)="customerid" then
%>
    <td>
    <input type="submit" name="customerID" value="
<%=x.value%>">

The link is the same Request.Form("") statement it is checking before it pulls the matching record (it should not be blank).

What I want to do though is display the form; submit the customer id or the first and last name; search the DB; then display the record for editing... I cannot seem to pull the right record though.