recordset problem in session

farrukhijaz

06-10-2007, 09:38 AM

I have database in sql server 2000. I am creating session starting from login page. session value is successfully passed to 2nd page. I want to extract profile information from the same table (employees) that contains login and password.
in 2nd page, recordset in vbscript is giving error.
my record set code is given below.


<%
Dim profile_show
Dim profile_show_numRows
a = session("sessSignum")
Set profile_show = Server.CreateObject("ADODB.Recordset")
profile_show.ActiveConnection = MM_test_travel_STRING
profile_show.Source = "SELECT * FROM dbo.test_emp WHERE Signum = ' " & session("sessSignum") & " '"
profile_show.CursorType = 0
profile_show.CursorLocation = 2
profile_show.LockType = 1
profile_show.Open()

profile_show_numRows = 0
%>

<body>
<form id="form1" name="form1" method="post" action="">

<% b =(profile_show.Fields.Item("Signum").Value)%></form>

</body>
</html>
<%
profile_show.Close()
Set profile_show = Nothing
%>

the code runs fine if we give exact value in sql WHERE clause. but putting in some variable give error.
urgent help needed.

Spudhead

06-11-2007, 03:01 PM

1. If your code gives you an error message, post the error message.
2. If your error happens when you fire SQL statements at a database, response.write() your SQL statement, and post it.

glenngv

06-12-2007, 10:19 PM

You have a leading and trailing space in the Signum value.
profile_show.Source = "SELECT * FROM dbo.test_emp WHERE Signum = ' " & session("sessSignum") & " '"
Change it to:
profile_show.Source = "SELECT * FROM dbo.test_emp WHERE Signum = '" & session("sessSignum") & "'"
If you assign the session to variable a then you can replace the session in the query to a.

And also you need to always check if the recordset contains records or not before displaying them. This is to avoid unexpected runtime errors if no record is found.
<%
if not profile_show.EOF and not profile_show.BOF then
'recordset contains record, do what you want
response.write "Signum:" & profile_show.Fields.Item("Signum").Value
else
'recordset is empty, display error
response.write "Record not found."
profile_show.close
set profile_show = nothing
response.end
end if
%>