Prepopulate form and then update existing records (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

autumn1978 -> Prepopulate form and then update existing records (2/6/2006 12:13:01)

Hi,

I have searched every single thread and have read every tutorial I could find, and still cannot get how to do this. I am using FP2003 and an Access 2003 db hosted on a windows server (of course!).

The user logs into his account, and his email and password are passed via url querystring from page to page, thus displaying his pertinent information. Thus far, I have used the DRW to display the information, according to the variables in the url QueryString.

However, on the "update account info" page, I'm hand-coding and all I want to do is have the form with their address, etc., prepopulate, according to the email and password already in the url, and then let them change it as necessary and update their record. On this page, I decided to hand code it as the drw needs too much assistance. The other problem is that I want them to be able to change their email and password, also.

Here's my code, the database connection name is Database1 and the table name is user_info:

<%
DIM EMAIL1
EMAIL1 = Request.QueryString("EMAIL")
DIM PASSWORD1
PASSWORD1 = Request.QueryString("PASSWORD")
Set Catalog=Server.OpenObject("ADODB.user_info")
Catalog.open "SELECT * FROM Catalog FIRSTNAME = '::FIRSTNAME::', LASTNAME = '::LASTNAME::', EMAIL = '::EMAIL::', PASSWORD = '::PASSWORD::', URL='::URL::', EXTENSION = '::EXTENSION::', HOSTINGTYPE = '::HOSTINGTYPE::', ADDRESS = '::ADDRESS::', ADDRESS2='::ADDRESS2::', CITY = '::CITY::', USSTATE = '::USSTATE::', CANADIANPROVINCE = '::CANADIANPROVINCE::', UKDISTRICTS = '::UKDISTRICTS::', COUNTRY = '::COUNTRY::', ZIP = '::ZIP::' WHERE EMAIL1='::EMAIL::' AND PASSWORD1 = '::PASSWORD::'", "DSN=DATABASE1"
%>
<html>

<head>
<title>Login to get your free domain name and free unlimited Windows web hosting</title>
<style fprolloverstyle>A:hover {color: #FFFF00; font-weight: bold}
</style>
</head>

<body bgcolor="#5EA5FF" link="#FFFFFF" vlink="#FFFF00" alink="#FFFF00">

<table width="100%" height="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td height="88" background="images/topbg1.jpg">
<object classid="clsid:D27CDB6E-AE6D-11CF-96B8-444553540000" id="obj1" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,40,0" border="0" width="770" height="156">
<param name="movie" value="08biz_2.swf">
<param name="quality" value="High">
<embed src="08biz_2.swf" pluginspage="http://www.macromedia.com/go/getflashplayer" type="application/x-shockwave-flash" name="obj1" width="770" height="156" quality="High"></object>
</td>
</tr>
<tr>
<td height="47" background="images/top03bg.gif"><table width="770" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="166" align="left"><img src="images/latest.gif" width="166" height="47"></td>
<td width="442"><img src="images/top04.gif" width="442" height="47"></td>
<td><img src="images/special_1.gif" width="162" height="47"></td>
</tr>
</table></td>
</tr>
<tr>
<td align="left" valign="top" background="images/bg.gif">
<table width="829" height="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="171" align="center" valign="top"><table width="148" height="100%" border="0" cellpadding="0" cellspacing="0" class="text">
<tr>
<td height="404" valign="top" background="images/left_bg.gif">
<!--webbot bot="Include" U-Include="leftnav.html" TAG="BODY" --> </td>
</tr>
</table></td>
<td align="left" valign="top">
<table width="905" border="0" cellpadding="0" cellspacing="0" class="text">
<tr>
<td height="730" align="left" valign="top">
<form BOTID="0" METHOD="POST" action="UPDATEINFOQUERY.asp">
<input type="hidden" name="fpdbr_0_PagingMove" value=" |< ">
<table BORDER="0" width="839" height="105">
<tr>
<td width="833" valign="bottom">
<!--webbot bot="Include" U-Include="Login_Navbar.asp" TAG="BODY" --></td>
</tr>
<tr>
<td width="833" valign="bottom">
<form method="POST" name="Update_Account_Info" action="AccountUpdate.asp" webbot-action="--WEBBOT-SELF--">
<table border="0" cellpadding="0" cellspacing="0" id="table4">
<tr>
<td colspan="2">
 </td>
</tr>
<tr>
<td colspan="2">
<font face="Verdana" size="2"><b>Update
Information:</b></font></td>
</tr>
<tr>
<td><font size="2">First Name:</font></td>
<td>
<input type="text" name="FIRSTNAME" size="20" tabindex="1" value=""<%=FIRSTNAME%>""></td>
</tr>
<tr>
<td><font size="2">Last Name:</font></td>
<td>
<input type="text" name="LASTNAME" size="20" tabindex="2"></td>
</tr>
<tr>
<td>
<font face="Verdana" size="2">Email:</font></td>
<td>
<input type="text" name="EMAIL" size="20" tabindex="3"></td>
</tr>
<tr>
<td>
<font face="Verdana" size="2">Password:</font></td>
<td>
<input type="text" name="PASSWORD" size="6" tabindex="4" maxlength="6"></td>
</tr>
<tr>
<td>
<font face="Verdana" size="2">Domain Name:</font></td>
<td>
<input type="text" name="URL" size="20" tabindex="5"><select size="1" name="EXTENSION" tabindex="6">
<option>.COM</option>
<option>.NET</option>
<option>.ORG</option>
</select></td>
</tr>
<tr>
<td>
<font face="Verdana" size="2">Hosting Type:</font></td>
<td>
<select size="1" name="HOSTINGTYPE" tabindex="7">
<option>WINDOWS</option>
<option>UNIX</option>
</select></td>
</tr>
<tr>
<td>
<font face="Verdana" size="2">Address:</font></td>
<td>
<input type="text" name="ADDRESS" size="20" tabindex="10"></td>
</tr>
<tr>
<td> </td>
<td>
<input type="text" name="ADDRESS2" size="20" tabindex="12"></td>
</tr>
<tr>
<td>
<font face="Verdana" size="2">City:</font></td>
<td>
<input type="text" name="CITY" size="20" tabindex="13"></td>
</tr>
<tr>
<td>
<font face="Verdana" size="2">State: or</font></td>
<td>
<input type="text" name="USSTATE" size="20" tabindex="14"></td>
</tr>
<tr>
<td>
<font face="Verdana" size="2">Province: or</font></td>
<td>
<input type="text" name="CANADIANPROVINCE" size="20" tabindex="15"></td>
</tr>
<tr>
<td>
<font face="Verdana" size="2">UK District:</font></td>
<td>
<input type="text" name="UKDISTRICTS" size="20" tabindex="16"></td>
</tr>
<tr>
<td>
<font face="Verdana" size="2">Postal Code:</font></td>
<td>
<input type="text" name="ZIP" size="20" tabindex="17"></td>
</tr>
<tr>
<td>
<font face="Verdana" size="2">Country:</font></td>
<td>
<input type="text" name="COUNTRY" size="20" tabindex="18"></td>
</tr>
<tr>
<td> </td>
<td>
<input type="submit" value="Submit" name="B3"></td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
</table>
<p> </p>
</form>
<p> </td>
</tr>
</table>
</form>
</td>
</tr>
<tr>
<td valign="top"><p><img src="images/c_bg_1.gif" width="581" height="20"></p></td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
<tr>
<td height="36" background="images/bottom01bg.gif"><table width="770" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="center">
<!--webbot bot="Include" U-Include="bottom.htm" TAG="BODY" --></td>
</tr>
</table></td>
</tr>
</table>

</body>

</html>



rdouglass -> RE: Prepopulate form and then update existing records (2/6/2006 16:12:46)

quote:

<%=FIRSTNAME%>


The first thing I'd address is this. You haven't defined what the variable FIRSTNAME is yet. Yes, you've opened a DB connection but you haven't assigned anything to that variable. I'd try it this way:

Set Catalog=Server.OpenObject("ADODB.connection")
Catalog.open "DSN=DATABASE1"
set rs=Catalog.execute("SELECT * FROM Catalog FIRSTNAME = '::FIRSTNAME::', LASTNAME = '::LASTNAME::', EMAIL = '::EMAIL::', PASSWORD = '::PASSWORD::', URL='::URL::', EXTENSION = '::EXTENSION::', HOSTINGTYPE = '::HOSTINGTYPE::', ADDRESS = '::ADDRESS::', ADDRESS2='::ADDRESS2::', CITY = '::CITY::', USSTATE = '::USSTATE::', CANADIANPROVINCE = '::CANADIANPROVINCE::', UKDISTRICTS = '::UKDISTRICTS::', COUNTRY = '::COUNTRY::', ZIP = '::ZIP::' WHERE EMAIL1='::EMAIL::' AND PASSWORD1 = '::PASSWORD::'")

Then you should be able to write them like so:

<%=rs("fieldName")%>

as in:

<%=rs("FIRSTNAME")%>

That help any?



autumn1978 -> RE: Prepopulate form and then update existing records (2/7/2006 9:48:20)

Hi,

Thank you so much for your reply. I did that, and received a 500 internal server error. I have several tables in my database. Do I need to define which table to take the information from?



rdouglass -> RE: Prepopulate form and then update existing records (2/7/2006 10:06:43)

quote:

SELECT * FROM Catalog


Is "Catalog" *not* your table name? And yes, you absolutely must indicate the table for any SQL command to work.



rdouglass -> RE: Prepopulate form and then update existing records (2/7/2006 10:07:25)

quote:

received a 500 internal server error


Also, can you post any error code sent with the 500 error? You may need to turn "freindly error messages" off.



autumn1978 -> RE: Prepopulate form and then update existing records (2/7/2006 10:25:17)

This is what I get, after turning off friendly error messages and then restarting it:

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator to inform of the time the error occurred and of anything you might have done that may have caused the error.

More information about this error may be available in the server error log.



rdouglass -> RE: Prepopulate form and then update existing records (2/7/2006 10:54:19)

quote:

Is "Catalog" *not* your table name?


What is the table name you're trying to pull from?



autumn1978 -> RE: Prepopulate form and then update existing records (2/7/2006 11:13:11)

It's called user_info. I got rid of "Catalog" and replaced it with user_info, still the same error.



rdouglass -> RE: Prepopulate form and then update existing records (2/7/2006 11:22:52)

quote:

Set Catalog=Server.OpenObject("ADODB.connection")
Catalog.open "DSN=DATABASE1"
set rs=Catalog.execute("SELECT * FROM Catalog FIRSTNAME = '::FIRSTNAME::', LASTNAME = '::LASTNAME::', EMAIL = '::EMAIL::', PASSWORD = '::PASSWORD::', URL='::URL::', EXTENSION = '::EXTENSION::', HOSTINGTYPE = '::HOSTINGTYPE::', ADDRESS = '::ADDRESS::', ADDRESS2='::ADDRESS2::', CITY = '::CITY::', USSTATE = '::USSTATE::', CANADIANPROVINCE = '::CANADIANPROVINCE::', UKDISTRICTS = '::UKDISTRICTS::', COUNTRY = '::COUNTRY::', ZIP = '::ZIP::' WHERE EMAIL1='::EMAIL::' AND PASSWORD1 = '::PASSWORD::'")


OK. Let's backtrack a little and try simplifying the query to this for testing purposes:

Set Catalog=Server.OpenObject("ADODB.connection")
Catalog.open "DSN=DATABASE1"
set rs=Catalog.execute("SELECT * FROM user_info WHERE FIRSTNAME = '::FIRSTNAME::'")

Does it still error?

(Notice the "WHERE"? I may have left that out before. [&o] Sorry.)



autumn1978 -> RE: Prepopulate form and then update existing records (2/7/2006 11:34:26)

Hi,

Yes, same error message



rdouglass -> RE: Prepopulate form and then update existing records (2/7/2006 12:58:31)

Can you post the name of your database file?



autumn1978 -> RE: Prepopulate form and then update existing records (2/8/2006 13:00:05)

Hi,

The name of my database file is fds. How do I know if I have a dns or dns-less connection? I believe that matters regarding the connection string



rdouglass -> RE: Prepopulate form and then update existing records (2/8/2006 13:26:14)

quote:

Set Catalog=Server.OpenObject("ADODB.connection")


After looking at this again I suspect the issue may be right in there.

Set Catalog=Server.CreateObject("ADODB.connection")

But if that still is not the case, see if you can put this code on it's own ASP page, browse to it and see if we get any response with that: 

<%
Set Catalog=Server.CreateObject("ADODB.connection")
Catalog.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/fpdb/fds.mdb")
set rs=Catalog.execute("SELECT * FROM user_info")
Response.write(rs("FirstName"))
rs.close
set rs=nothing
Catalog.close
set Catalog=nothing
%>


That any help?



autumn1978 -> RE: Prepopulate form and then update existing records (2/9/2006 22:13:51)

Thank you for that code. I didn't get an error this time. However, it only returned one record in the table (i have about 1500). I even added a WHERE clause and i still got the same record. What do you think?



rdouglass -> RE: Prepopulate form and then update existing records (2/10/2006 15:35:59)

quote:

However, it only returned one record in the table (i have about 1500).


Yup, 'cause we haven't put a loop in yet. I was just confirming we can talk to the database and grab a record. What I would do is go back to the code posted a little ways up (using all the fields) and change the line here:

Set Catalog=Server.OpenObject("ADODB.connection")

to this:

Set Catalog=Server.CreateObject("ADODB.connection")

so that it looks like this: 

Set Catalog=Server.CreateObject("ADODB.connection") 
Catalog.open "DSN=DATABASE1" 
set rs=Catalog.execute("SELECT * FROM user_info FIRSTNAME = '::FIRSTNAME::', LASTNAME = '::LASTNAME::', EMAIL = '::EMAIL::', PASSWORD = '::PASSWORD::', URL='::URL::', EXTENSION = '::EXTENSION::', HOSTINGTYPE = '::HOSTINGTYPE::', ADDRESS = '::ADDRESS::', ADDRESS2='::ADDRESS2::', CITY = '::CITY::', USSTATE = '::USSTATE::', CANADIANPROVINCE = '::CANADIANPROVINCE::', UKDISTRICTS = '::UKDISTRICTS::', COUNTRY = '::COUNTRY::', ZIP = '::ZIP::' WHERE EMAIL1='::EMAIL::' AND PASSWORD1 = '::PASSWORD::'")


Now I'm thinking there will be another issue since we're using DRW syntax but not a full DRW so you may have to alter the code to look like this:

set rs=Catalog.execute("SELECT * FROM user_info FIRSTNAME = '" & Request.form("FIRSTNAME") & "', LASTNAME = '" & Request.form("LASTNAME") & "',...

That help any?



autumn1978 -> RE: Prepopulate form and then update existing records (2/11/2006 9:12:24)

As soon as I put the field names in there, with any syntax (even w/out the where clause), it gives me the 500 error.

I tried passing the variables through querystring and POST.



rdouglass -> RE: Prepopulate form and then update existing records (2/13/2006 10:42:11)

quote:

set rs=Catalog.execute("SELECT * FROM user_info FIRSTNAME = '::FIRSTNAME::', LASTNAME = '::LASTNAME::', EMAIL = '::EMAIL::', PASSWORD = '::PASSWORD::', URL='::URL::', EXTENSION = '::EXTENSION::', HOSTINGTYPE = '::HOSTINGTYPE::', ADDRESS = '::ADDRESS::', ADDRESS2='::ADDRESS2::', CITY = '::CITY::', USSTATE = '::USSTATE::', CANADIANPROVINCE = '::CANADIANPROVINCE::', UKDISTRICTS = '::UKDISTRICTS::', COUNTRY = '::COUNTRY::', ZIP = '::ZIP::' WHERE EMAIL1='::EMAIL::' AND PASSWORD1 = '::PASSWORD::'")


Can you try separating that line into 3 for now?

...

myTempVar = "SELECT * FROM user_info FIRSTNAME = '" & Request("FIRSTNAME") & "', ......AND PASSWORD1 = '" & Request("PASSWORD") & "'"

Response.write(myTempVar)
Response.end
....

What that should do is write your SQL to the screen. Then maybe you could post that. That should put a little more light on the subject.



autumn1978 -> RE: Prepopulate form and then update existing records (2/15/2006 13:31:00)

Oh my gosh! I got it to work! I had to give all the fields default values and make sure "Allow 0 input" was ON in the database.

The only prob I have now is that I don't know how to get my form to select input based on parameters in the querystring.

Now I only need help with two things:
1. Put a WHERE clause in my SELECT statement
2. Put a loop in the code to make it find the right record

Thank you for making my code work thus far. Any suggestions you might have about the above will be so helpful!

The code in red gives me the 500 error. As soon as I take it out, it's fine.

<%
Set Catalog=Server.CreateObject("ADODB.connection")
Catalog.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/fpdb/fds.mdb")
EMAIL = Request.QueryString("EMAIL")
PASSWORD = Request.QueryString("PASSWORD")
set rs=Catalog.execute("SELECT * FROM user_info WHERE EMAIL = '::EMAIL::'")
%>
<form method="GET" action="AccountUpdate.asp" name="FrontPage_Form1">
input type="text" name="FN" size="20" value="<%=rs("FIRSTNAME")%>"></td>
'more field names...
<a href="AccountUpdate.asp"><input type="submit" value="Submit" name="B1">
</form>
<%
rs.close
set rs=nothing
Catalog.close
set Catalog=nothing
%>




rdouglass -> RE: Prepopulate form and then update existing records (2/15/2006 14:45:00)

quote:

("SELECT * FROM user_info WHERE EMAIL = '::EMAIL::'")


Try it this way:

...WHERE EMAIL = '" & EMAIL & "'")



autumn1978 -> update worked! happy dance! (2/15/2006 21:16:50)

Thank you sooooooooooooooooooo much!!!!!!!!!!!!!

I can't believe it! It works!!!!!!



rdouglass -> RE: update worked! happy dance! (2/16/2006 9:14:38)

quote:

'::EMAIL::'


FYI: That syntax there (using double colons) is for the DRW only AFAIK as a shorthand for 'Request'. If you're not inside a FrontPage DRW, it won't work like that.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.109375