PDA

View Full Version : use the values obtained from a table after closing the connection?

Mhtml
10-18-2002, 10:03 AM
How can I use the values obtained from a table after closing the connection?

Like calling them like this <%=Variable%>
raf
10-18-2002, 10:34 AM
simple :
store the values of the recordset in a variable (this sounds to evident so maybe i didn't understand your problem)
like this


dim variable1
variable1 = rsNameRecordset.Fields("nameofvariable in recordset")


this variable can be called throughout the page (even if connection (or recordset to be more precise) is closed.

if you want to use it on another page without reconnecting to the db, do it like this

variable1 = rsNameRecordset.Fields("nameofvariable in recordset")

session("variable1") = rsNameRecordset.Fields("nameofvariable in recordset")


does this help ?
Mhtml
10-18-2002, 11:52 AM
I always seem to get an error saying that it cannot work because the recodrset is closed or invalid. But when I call it before closing the conn or rs it works.
raf
10-18-2002, 01:23 PM
which is very logically. After closing the recordset (+ setting it to nothing to free memory on your webserver) it becomes inaccesssible.

buth with this sort of coding, there should be now problem (i never got any problems with it)


dim rsLogin
set rsLogin = server.CreateObject("adodb.recordset")

dim sql
sql="select BTAuser,BTApassword,voornaam,naam,profiel from gebruikers where BTAuser='loginuser'"
sql=replace(sql,"loginuser",gebruiker)
rsLogin.Open sql, conBTAmain

dim user
user = rsLogin.Fields("BTAuser")
session("user") = rsLogin.Fields("BTAuser")

rsLogin.Close
set rsLogin=nothing

response.write(user)



It should write the value for BTAuser to te browser. And on other pages (within that session) you can retrieve te same info from the sessionvariable.
Do you mean this doesn't work with you ?
:confused:
Roelf
10-18-2002, 02:07 PM
i always use:
variable = rsNameOfRecordSet.Fields("FieldName").Value
karolmcauley
10-18-2002, 04:15 PM
MHTML,

I recently something similar to this problem you seem to be having, and i have managed to get around it i.e. referencing a Db record after you close the DB connection. For instance:

Your DB connection here
Do ur SQL query
Set objUsers = Your DB Connection.Execute(Your SQL query)

If NOT objUsers.EOF Then
your variable name1 = objUsers("DBrecord Name1")
your variable name2 = objUsers("DBrecord Name2")
your variable name3 = objUsers("DBrecord Name3")
etc etc for however many variables you wish to create
Do something here
Else
do something here
End If

Then when u close your connection simply reference the variable name appropriate to the DB Record name anywhere in the form.

Eg. <body>
I want to display the first record <%your variable name1%>
I want to display the second record <%your variable name2%>
I want to display the third record <%your variable name3%>
</body>

This definitely works as i am currently using something very similar.

Regards
whammy
10-19-2002, 02:58 AM
I usually would just open a connection, run everything, and then close the connection, using subroutines:


call openconnection() 'This will call a Sub that opens the connection
call displayform() 'This displays the form
call closeconnection() 'This closes the connection


It's kind of modular that way... of course I come from a BASIC programming background originally, so I'm used to using subroutines. Also, if you keep as much code in that process as possible as PURE asp, it supposedly speeds up the processing on the server-side.

But please feel free to chime in here on this issue - anyone! :)
raf
10-21-2002, 09:19 AM
i'll go with whammy. stick to straight asp.
about the problem :
Mhtml, is there still a problem ?

in general:
you can open (and close) multiple recordsets on one connection (just give them different names). i do it frequently. so with a bit of thinking before you start coding, you can write a code were you don't need to use info from a closed recordset. (especcialy if you've got a good databasedesign so you can use an inner join on almost any tables to fetch the data you need). If you can't avoid it, store the info in a variable (or an array).
Mhtml
10-21-2002, 12:15 PM
I have it working now. I now use subroutines becuase I get a lot of answers from whammy and it sought of rubbed off, but it works fine now.