search record in database to update - asp

Report · Mar 13, 2010

Hi,

I need help with searching for a value in the database (i.e. email address) so the user can update or delete their records.

I have no trouble updating. I usually list the data and then click on the one that needs updating.

I'm having trouble retrieving the information after the user has typed in their email address.

Here is my code so far - at the moment I have listed the data in a table with an "update" link next to each row that allows me to update.

Where would I put the text field where the user can type in their email? I've tried different ways but was unsuccessful.

Hope you can help

Thanks

<%

.

strSQL = "SELECT * FROM emails"

rsEmail.Open strSQL, conn

%>

<html>

</head>

<body>

<div class="insert"><a href="insertform.html">Insert</a></div>

</div>

<tr>

<td width="300">firstname</td>

<td width="378">lastname</td>

<td width="121">email</td>

<td width="92">Update</td>

<td width="92">Delete</td>

</tr>

</table>

<%

Do While not rsEmail.EOF

%>

<td width="91"><% Response.Write ("<a href=""update_form.asp?ID=" & rsEmail("ID") & """>")%>Update<%Response.Write ("</a>") %></td>

</tr>

<%

rsEmail.MoveNext

Loop

'Reset server objects

rsEmail.Close

Set rsEmail = Nothing

Set conn = Nothing

%>

</table>

</div>

</body>

</html>

(i rephrased my question earlier but the views were the same so I thought I should re-post my new quesion as I am really struggling! ) hope someone can help!

Thank you

Report · Mar 13, 2010

>I'm having trouble retrieving the information

>after the user has typed in their email address.

Please explain the desired workflow. It sounds like you want a user to be able to search and then update a record. Is that correct? What records are they allowed to update? Do they need to already be logged in. We need a more complete picture to offer possible solutions.

Report · Mar 13, 2010

Hi

I have a database of clients and to keep it up to date, we will be sending out an email asking them to confirm if they're details are correct and up to date. Details like Name telephone and address etc (just personal details)

In that email will be a link to a webpage where they can confirm their details are up to date. They will have to type in their email address (so this is kind of like the login) which will then retrieve their information to update or confirm they are correct.

But yes, the user will need to type in their email address to update the record.

Retrieving the users information from the email typed in is what I'm having trouble with.

Hope that makes sense - hope you can help.

Thanks

Report · Mar 13, 2010

Have you looked at any of the tutorials for using DW to create search/result pages?

You can try these:

http://www.adobe.com/support/dreamweaver/programs/mx_search/mx_search02.html

http://www.sebastiansulinski.co.uk/tutorials/show_item/40/display_search_result

However, what you are attempting could be considered dangerous as you are allowing unauthenticated users to modify data on your site and possibly gain access to sensitive customer data.

Report · Mar 15, 2010

Hi,

I see what you're saying - I told my client about it but they said that it won't be a problem.

Thanks for the links you sent me. I cannot connect directly to my server using the database panel in dreamweaver (I'm using 1and1 and they have some security setting) I have to pretty much hand code it.

Can you help?

Thanks again

Report · Mar 15, 2010

Hi,

I'm getting closer - but can't spot where I'm going wrong.The email typed in appears in the url when I click submit but details do not appear in the textfields after I click the find button (in update_form2.asp)

I did a test database with an ID field as the primary key - When I typed in the ID number the details appeared in the textfields so I can search to update that record. Can you spot why its not working when I type in an email?

Many Thanks

<%
'Dimension variables
Dim conn
Dim rsEmail
Dim strSQL

<---------database connection--------------->
Set rsEmail= Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT * FROM emails"

rsEmail.Open strSQL, conn

%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>One</title>

</head>

<body>

</FORM>

</body>

</html>

Update form2.asp :-

<%

Dim conn
Dim rsEmail
Dim strSQL
Dim search

search= Request("email")

<----database connection---->

Set rsEmail= Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT * FROM emails WHERE email=" & search

rsEmail.Open strSQL, conn
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Update Form</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><style type="text/css">
</head>
<body>

<h1>Update Form</h1>

<form name="form" method="post" action="update_entry.asp">
<table width="100%" cellpadding="5">
<tr>
    <td>email</td>
    <td><input name="email" type="text" id="email" value="<% = rsEmail("email") %>    " size="30" /></td>
</tr>
<tr>
    <td>firstname</td>
    <td><input name="firstname" type="text" id="firstname" value="<% = rsEmail("firstname") %>    " size="30" />
    </td>
</tr>
<tr>
    <td>lastname</td>
    <td><input name="lastname" type="text" value="<% = rsEmail("lastname") %>    " size="30" />
      <input type="hidden" name="email" value="<% = rsEmail("email") %>"></td>
</tr>
<tr>
    <td> </td>
    <td><input type="submit" name="Submit" value="Submit"></td>
</tr>

</table>

</form>

</div>
</body>
</html>

<%

rsEmail.Close
Set rsEmail= Nothing
Set conn = Nothing
%>

Report · Mar 15, 2010

If you are using the GET method, then you should use the querystring collection

search= Request.Querystring("email")

Report · Mar 16, 2010

Thank you for your reply. I tried search = Request.Querystring("email") and I got the following error message: -

Microsoft OLE DB Provider for SQL Server error '80040e14'

The multi-part identifier "test@email.com" could not be bound.

/1one/test/update_form2.asp, line 29

This is what is passed in the url: -

/update_form2.asp?email=test%email.com

(I know test@email.com is in the database)

Line 29 is

rsMenu.Open strSQL, conn

Any ideas?

Report · Mar 16, 2010

strSQL = "SELECT * FROM emails WHERE email=" & search

The parameter in the where clause of your select statment should be wrapped within quotes. Try this:

strSQL = "SELECT * FROM emails WHERE email='" & search & "'"

Report · Mar 16, 2010

Thank you sooo much! that worked perfectly!

Thanks

Report · Mar 16, 2010

You're welcome

search record in database to update - asp

1 Correct answer