Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with recordset.

Status
Not open for further replies.
goffc

goffc

Technical User
May 23, 2001
5
US
I'm new to SQL. I have a page that is requesing an memberID from another page. It takes it inserts it into a table and displays the members info. it used to be a number, but i am switching it to text and it's not working. The database record has been set to text. I would really appreciate any help. thanks,

the error is with this line...


RS.Open "SELECT * FROM Members WHERE memberID=" & "member_ID"


--------------------------------here's the code-----


<%
' Get the Member ID
member_ID = Request( &quot;mid&quot; )

' Open the Database Connection
Set Con = Server.CreateObject( &quot;ADODB.Connection&quot; )
Con.Open &quot;jobs&quot;

' Open the Recordset
Set RS = Server.CreateObject( &quot;ADODB.Recordset&quot; )
RS.ActiveConnection = Con
RS.CursorType = 3
RS.Open &quot;SELECT * FROM Members WHERE memberID=&quot; & &quot;member_ID&quot;
IF NOT RS.EOF THEN
first_Name = RS( &quot;firstName&quot; )
last_Name = RS( &quot;lastName&quot; )
email_mem = RS( &quot;email&quot; )
phone_mem = RS( &quot;phone&quot; )
address_mem = RS( &quot;address&quot; )
city_mem = RS( &quot;city&quot; )
state_mem = RS( &quot;state&quot; )
zipMem = RS( &quot;zip&quot; )
END IF
' Close the Recordset
RS.Close
 
Sort by date Sort by votes
Yo need to query using the value of member_ID not the string &quot;member_ID&quot;. You must also delimit the criteria with ' to indicate data type test.

RS.Open &quot;SELECT * FROM Members WHERE memberID='&quot; &
member_ID & &quot;'&quot; Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:
 
Upvote 0 Downvote
Hey, I appreciate the reply.

I tried to use what you told me and I get a script error.
 
Upvote 0 Downvote
Hey, Here's the code:
(It's saying script error and pointing to this line -->

RS.Open &quot;SELECT * FROM Members WHERE memberID='&quot; &
member_ID & &quot;'&quot;

The page before this one is sending the variable MBR-000005, to this page as mid. <a href=&quot;updatemember.asp?mid=MBR-000005&quot;>Update your profile</a> I'm doing this just to test this page. It's eventually going to be a session variable.

Thanks

---- code ---------------------------------------

<%
' Get the Member ID
member_ID = Request( &quot;mid&quot; )

' Open the Database Connection
Set Con = Server.CreateObject( &quot;ADODB.Connection&quot; )
Con.Open &quot;jobs&quot;

' Open the Recordset
Set RS = Server.CreateObject( &quot;ADODB.Recordset&quot; )
RS.ActiveConnection = Con
RS.CursorType = 3
RS.Open &quot;SELECT * FROM Members WHERE memberID='&quot; &
member_ID & &quot;'&quot;
IF NOT RS.EOF THEN
first_Name = RS( &quot;firstName&quot; )
last_Name = RS( &quot;lastName&quot; )
email_mem = RS( &quot;email&quot; )
phone_mem = RS( &quot;phone&quot; )
address_mem = RS( &quot;address&quot; )
city_mem = RS( &quot;city&quot; )
state_mem = RS( &quot;state&quot; )
zipMem = RS( &quot;zip&quot; )
END IF
' Close the Recordset
RS.Close


FUNCTION SELECTED( firstVal, secondVal )
IF cSTR( firstVal ) = cSTR( secondVAL ) THEN
SELECTED = &quot; SELECTED &quot;
END IF
END FUNCTION

%>
<html>
<head><title>Update Member Profile </title></head>
<body bgcolor=&quot;gray&quot;>

<form method=&quot;post&quot; action=&quot;managemembers.asp&quot;>

<center>
<table width=&quot;600&quot; border=1 bgcolor=&quot;lightyellow&quot;
cellpadding=&quot;4&quot; cellspacing=&quot;0&quot;>
<tr>
<td colspan=&quot;2&quot; bgcolor=&quot;yellow&quot;>
<font face=&quot;Arial&quot; size=&quot;3&quot;><b>
Update Product
</b></font>
</td>
</tr>
<tr>
<td>
<b> First Name:</b>
</td>
<td>
<input name=&quot;first_Name&quot;
size=&quot;50&quot; maxlength=&quot;50&quot;
value=&quot;<%=Server.HTMLEncode( first_Name )%>&quot;>
</td>
</tr>
<tr>
<td>
<b> Last Name:</b>
</td>
<td>
<input name=&quot;last_Name&quot;
size=&quot;50&quot; maxlength=&quot;50&quot;
value=&quot;<%=Server.HTMLEncode( last_Name )%>&quot;>
</td>
</tr>

<tr>
<td>
<b>Email:</b>
</td>
<td>
<input name=&quot;email_mem&quot;
size=&quot;50&quot; maxlength=&quot;50&quot;
value=&quot;<%=Server.HTMLEncode( email_mem )%>&quot;>
</td>
</tr>
<tr>
<td>
<b>Phone:</b>
</td>
<td>
<input name=&quot;phone_mem&quot;
size=&quot;50&quot; maxlength=&quot;50&quot;
value=&quot;<%=Server.HTMLEncode( phone_mem )%>&quot;>
</td>
</tr>
<tr>
<td>
<b>Address:</b>
</td>
<td>
<input name=&quot;address_mem&quot;
size=&quot;50&quot; maxlength=&quot;50&quot;
value=&quot;<%=Server.HTMLEncode( address_mem )%>&quot;>
</td>
</tr>

<tr>
<td>
<b>City:</b>
</td>
<td>
<input name=&quot;city_mem&quot;
size=&quot;50&quot; maxlength=&quot;50&quot;
value=&quot;<%=Server.HTMLEncode( city_mem )%>&quot;>
</td>
</tr>

<tr>
<td>
<b>State:</b>
</td>
<td>
<input name=&quot;state_mem&quot;
size=&quot;50&quot; maxlength=&quot;2&quot;
value=&quot;<%=Server.HTMLEncode( state_mem )%>&quot;>
</td>
</tr>

<tr>
<td>
<b>Zip Code:</b><br>
*numbers only
</td>
<td>
<input name=&quot;zipMem&quot; size=&quot;10&quot;
value=&quot;<%=zipMem%>&quot;>
</td>
</tr>
<tr>
<td colspan=2 align=&quot;right&quot;>
<input type=&quot;submit&quot; value=&quot;Update Member&quot;>
</td>
</tr>
</table>
</center>

<input name=&quot;member_ID&quot; type=&quot;hidden&quot; value=&quot;<%=member_ID%>&quot;>
<input name=&quot;updateMember&quot; type=&quot;hidden&quot; value=&quot;1&quot;>
</form>

</body>
</html>
 
Upvote 0 Downvote
Actually, It says &quot;Syntax error&quot; and points to that line.
 
Upvote 0 Downvote
I'm not very familiar with opening recordsets from a web page. Is it possible that the rs.open statemnt syntax is incomplete or in error rather than the SQL statement causing the error? The SQL statement appears to be valid. Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:
 
Upvote 0 Downvote
I'm gonna check on that. The weird thing is that it works when I send the variable through as say &quot;12345&quot;, a number, and have the column in the database set to number. But when I change the column to text and try to sent the variable through as MBR-12345, it chokes.
 
Upvote 0 Downvote
Try leaving your recordset open, not closing it, what happens then?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

serino
  • Locked
  • Question
Help with a Query. 1
Replies
13
Views
537
strongm
strongm
BugZap13
  • Locked
  • Question
Need help creating a date from two fields to be used in where clause
Replies
5
Views
333
mikrom
mikrom
datajockey123
  • Locked
  • Question
Note / Information box with date and time stamp for each entery
Replies
2
Views
645
dhookom
dhookom
serino
  • Locked
  • Question
Delete with WHERE Clause. 2
Replies
4
Views
664
serino
serino
serino
  • Locked
  • Question
Help with SQL inner join statement 1
Replies
2
Views
320
serino
serino

Part and Inventory Search

Sponsor

Back
Top