Prev: Does anybody know of any good books on ASP.NET Membership and the Login controls?
Next: SNL Sql
From: Joey Martin on 18 Jul 2008 22:06
Here is my SQL query in my Classic ASP page. Once I get my recordset, I
want to loop thru it. I have that working. But once I grab the first
row, I need to start another sql query grabbing data from another table
based on the result from row 1. I just need to know how to OPEN the
second recordset with the same connection. See below.

Set objConn = Server.CreateObject("ADODB.CONNECTION")
Set objCommand = Server.CreateObject("ADODB.COMMAND")
Set rs = Server.CreateObject("ADODB.RECORDSET")
Set rsphoto = Server.CreateObject("ADODB.RECORDSET")
objConn.Open(strConnectString)
' Execute the command
strCmd = "select name from table where email = ( ? )"
Set objCommand.ActiveConnection = objConn
objCommand.CommandText = strCmd
objCommand.CommandType = adCmdText
Set param1 = objCommand.CreateParameter ("email", adVarChar,
adParamInput, 50)
param1.value = strEmail
objCommand.Parameters.Append param1


rs.Open objCommand

---Now I need to do something like:
sqlstr="select photo from anothertable where id= the rs("id") from row
1.

then I loop and do it all over.

I just need to know how to open up the subquery recordset using the same
connection.

Thanks!!



*** Sent via Developersdex http://www.developersdex.com ***
From: "Sylvain Lafontaine" sylvain aei ca on 18 Jul 2008 22:25
You reuse the same connection or command object. However, if the data
retrieval for the first recordset is not yet terminated (with taking into
account any cacheing), ADO will create for you a second and hidden
connection.

In your case, must probably that you should use a JOIN instead:

Select table1.id, table1.name, anothertable.photo from table1 inner join
anothertable on table1.id = anothertable.id where table1.email = ( ? )"

or:

Select T1.id, T1.name, T2.photo from table1 as T1 inner join anothertable as
T2 on T1.Id = T2.Id
where T1.email = ( ? )"

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Joey Martin" <joey(a)kytechs.com> wrote in message
news:O3VobQU6IHA.4468(a)TK2MSFTNGP02.phx.gbl...
> Here is my SQL query in my Classic ASP page. Once I get my recordset, I
> want to loop thru it. I have that working. But once I grab the first
> row, I need to start another sql query grabbing data from another table
> based on the result from row 1. I just need to know how to OPEN the
> second recordset with the same connection. See below.
>
> Set objConn = Server.CreateObject("ADODB.CONNECTION")
> Set objCommand = Server.CreateObject("ADODB.COMMAND")
> Set rs = Server.CreateObject("ADODB.RECORDSET")
> Set rsphoto = Server.CreateObject("ADODB.RECORDSET")
> objConn.Open(strConnectString)
> ' Execute the command
> strCmd = "select name from table where email = ( ? )"
> Set objCommand.ActiveConnection = objConn
> objCommand.CommandText = strCmd
> objCommand.CommandType = adCmdText
> Set param1 = objCommand.CreateParameter ("email", adVarChar,
> adParamInput, 50)
> param1.value = strEmail
> objCommand.Parameters.Append param1
>
>
> rs.Open objCommand
>
> ---Now I need to do something like:
> sqlstr="select photo from anothertable where id= the rs("id") from row
> 1.
>
> then I loop and do it all over.
>
> I just need to know how to open up the subquery recordset using the same
> connection.
>
> Thanks!!
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***


From: "Sylvain Lafontaine" sylvain aei ca on 18 Jul 2008 22:40
I forgot to mention that with SQL-Server 2005 and the latest driver (the
Native driver), you can use MARS (Multiple Active Recordsets, see
http://msdn.microsoft.com/en-us/library/bb264566.aspx ) but I still believe
that using a JOIN is probably the best solution in your case.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uop5vaU6IHA.4988(a)TK2MSFTNGP04.phx.gbl...
> You reuse the same connection or command object. However, if the data
> retrieval for the first recordset is not yet terminated (with taking into
> account any cacheing), ADO will create for you a second and hidden
> connection.
>
> In your case, must probably that you should use a JOIN instead:
>
> Select table1.id, table1.name, anothertable.photo from table1 inner join
> anothertable on table1.id = anothertable.id where table1.email = ( ? )"
>
> or:
>
> Select T1.id, T1.name, T2.photo from table1 as T1 inner join anothertable
> as T2 on T1.Id = T2.Id
> where T1.email = ( ? )"
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Joey Martin" <joey(a)kytechs.com> wrote in message
> news:O3VobQU6IHA.4468(a)TK2MSFTNGP02.phx.gbl...
>> Here is my SQL query in my Classic ASP page. Once I get my recordset, I
>> want to loop thru it. I have that working. But once I grab the first
>> row, I need to start another sql query grabbing data from another table
>> based on the result from row 1. I just need to know how to OPEN the
>> second recordset with the same connection. See below.
>>
>> Set objConn = Server.CreateObject("ADODB.CONNECTION")
>> Set objCommand = Server.CreateObject("ADODB.COMMAND")
>> Set rs = Server.CreateObject("ADODB.RECORDSET")
>> Set rsphoto = Server.CreateObject("ADODB.RECORDSET")
>> objConn.Open(strConnectString)
>> ' Execute the command
>> strCmd = "select name from table where email = ( ? )"
>> Set objCommand.ActiveConnection = objConn
>> objCommand.CommandText = strCmd
>> objCommand.CommandType = adCmdText
>> Set param1 = objCommand.CreateParameter ("email", adVarChar,
>> adParamInput, 50)
>> param1.value = strEmail
>> objCommand.Parameters.Append param1
>>
>>
>> rs.Open objCommand
>>
>> ---Now I need to do something like:
>> sqlstr="select photo from anothertable where id= the rs("id") from row
>> 1.
>>
>> then I loop and do it all over.
>>
>> I just need to know how to open up the subquery recordset using the same
>> connection.
>>
>> Thanks!!
>>
>>
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>
>


From: Joey Martin on 18 Jul 2008 23:47
Let's assume I cannot do a join. How could I write this to work the
right way?

rs.Open objCommand

While Not rs.EOF
thetype=rs("prop_type")

sqlphoto="select id from table2 where id ='" & rs("mls_acct") & "'"

rsphoto.open sqlphoto, objConn
thephoto=rsphoto("thumbfile")



I have it working to grab the first row and run the subquery correctly.
But, it does not go to the second loop.

I get the following error: "Operation is not allowed when the object is
open." and it's on this line:
rsphoto.open sqlphoto, objConn

Any ideas??

THANKS!





*** Sent via Developersdex http://www.developersdex.com ***
From: Erland Sommarskog on 19 Jul 2008 04:29
Joey Martin (joey(a)kytechs.com) writes:
> Let's assume I cannot do a join. How could I write this to work the
> right way?

Why wouldn't you be able to do a join?

> rs.Open objCommand
>
> While Not rs.EOF
> thetype=rs("prop_type")
>
> sqlphoto="select id from table2 where id ='" & rs("mls_acct") & "'"
>
> rsphoto.open sqlphoto, objConn
> thephoto=rsphoto("thumbfile")
>
> I have it working to grab the first row and run the subquery correctly.
> But, it does not go to the second loop.
>
> I get the following error: "Operation is not allowed when the object is
> open." and it's on this line:
> rsphoto.open sqlphoto, objConn

I'm a little surprised, since, as Sylvain said, ADO usually opens a new
connection behind your back.

But this is a server-side cursor, right? If you change your cursors to be
client-side instead, this is less of an issue, I believe. If you want to
use server-side cursors you need two connections. (Or investigate MARS,
but I think an extra connection is better.)
--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 |  Next  |  Last
Pages: 1 2
Prev: Does anybody know of any good books on ASP.NET Membership and the Login controls?
Next: SNL Sql