|
 |
asp_databases thread: problem with making a recordset use the active connection.
Message #1 by "James Cox" <imajes@p...> on Wed, 16 Jan 2002 01:21:10 -0000
|
|
Hi,
I have the following code, yet the recordset cannot use the open connection.
I'm trying to do this to maintain a persistant connection to the server, and
not have asp open one for each record in the first query, earlier in the
page, since 1000 concurrent connections is not a good idea.
Any assistance greatfully appreciated, and please cc me to any messages sent
to the list directly.
Thanks,
James Cox
----------
<%
set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open MM_icaforeign_STRING
While ((Repeat1__numRows <> 0) AND (NOT rs_data_results.EOF))
dim icaID, SQL
icaID = rs_data_results.Fields("icaref")
SQL = "SELECT SUM(receipt) as receipt, SUM(payment) as payment, SUM(disb)
as sumdisb "
SQL = SQL + "FROM data WHERE icaref ='" & icaID & "' GROUP BY icaref"
objBalance.ActiveConnection = objCon
objBalance.Source = SQL
objBalance.CursorType = AdOpenForwardOnly
objBalance.LockType = adLockReadOnly
objBalance.Open()
objBalance_numRows = 0
objBalance_total = objBalance.RecordCount
' calculate the balance.
dim strRecpt, strPaymnt, strBalance, strDisb
'check for values.
if objBalance_total <> 0 then
strRecpt = CCur(objBalance.fields("receipt"))
strPaymnt = CCur(objBalance.fields("payment"))
strDisb = CCur(objBalance.fields("sumdisb"))
else
strRecpt = 0
strPaymnt = 0
strDisb = 0
end if
strBalance = (strRecpt - strPaymnt)
strDisb = FormatNumber(strDisb)
objBalance.close()
set objBalance = Nothing
%>
the while loop ends furher down, after some html, however i didn't see any
use including it here.
Message #2 by "Kim Iwan Hansen" <kimiwan@k...> on Wed, 16 Jan 2002 09:52:29 +0100
|
|
You're forgetting to instantiate the objBalance object:
set objBalance = Server.CreateObject("ADODB.Recordset")
Further more, why do you set the properties so specifically as you do
(.activeconnection, .source, .cursortype etc.), unless it's to see how it
works?
Just use this:
objBalance.Open SQL, objCon, adOpenForwardOnly, adLockReadOnly, adCmdText
If you wanna stick with the many lines of code, here's a shortcut you can
use in VBScript:
with objBalance
.ActiveConnection = objCon
.Source = SQL
.CursorType = AdOpenForwardOnly
.LockType = adLockReadOnly
.Open()
objBalance_numRows = 0
objBalance_total = .RecordCount
end with
-Kim
> -----Original Message-----
> From: James Cox [mailto:imajes@p...]
> Sent: 16. januar 2002 02:21
> To: ASP Databases
> Subject: [asp_databases] problem with making a recordset use the active
> connection.
>
>
> Hi,
>
> I have the following code, yet the recordset cannot use the open
> connection.
> I'm trying to do this to maintain a persistant connection to the
> server, and
> not have asp open one for each record in the first query, earlier in the
> page, since 1000 concurrent connections is not a good idea.
>
> Any assistance greatfully appreciated, and please cc me to any
> messages sent
> to the list directly.
>
>
> Thanks,
>
> James Cox
>
> ----------
>
> <%
> set objCon = Server.CreateObject("ADODB.Connection")
> objCon.Open MM_icaforeign_STRING
> While ((Repeat1__numRows <> 0) AND (NOT rs_data_results.EOF))
>
> dim icaID, SQL
> icaID = rs_data_results.Fields("icaref")
> SQL = "SELECT SUM(receipt) as receipt, SUM(payment) as payment, SUM(disb)
> as sumdisb "
> SQL = SQL + "FROM data WHERE icaref ='" & icaID & "' GROUP BY icaref"
>
> objBalance.ActiveConnection = objCon
> objBalance.Source = SQL
> objBalance.CursorType = AdOpenForwardOnly
> objBalance.LockType = adLockReadOnly
> objBalance.Open()
> objBalance_numRows = 0
> objBalance_total = objBalance.RecordCount
>
> ' calculate the balance.
> dim strRecpt, strPaymnt, strBalance, strDisb
> 'check for values.
> if objBalance_total <> 0 then
> strRecpt = CCur(objBalance.fields("receipt"))
> strPaymnt = CCur(objBalance.fields("payment"))
> strDisb = CCur(objBalance.fields("sumdisb"))
> else
> strRecpt = 0
> strPaymnt = 0
> strDisb = 0
> end if
> strBalance = (strRecpt - strPaymnt)
> strDisb = FormatNumber(strDisb)
> objBalance.close()
> set objBalance = Nothing
> %>
>
> the while loop ends furher down, after some html, however i didn't see any
> use including it here.
>
>
> $subst('Email.Unsub').
>
Message #3 by "Drew, Ron" <RDrew@B...> on Wed, 16 Jan 2002 07:57:38 -0500
|
|
Don't forget the adovbs.inc
-----Original Message-----
From: Kim Iwan Hansen [mailto:kimiwan@k...]
Sent: Wednesday, January 16, 2002 3:52 AM
To: ASP Databases
Subject: [asp_databases] RE: problem with making a recordset use the
active connection.
You're forgetting to instantiate the objBalance object:
set objBalance =3D Server.CreateObject("ADODB.Recordset")
Further more, why do you set the properties so specifically as you do
(.activeconnection, .source, .cursortype etc.), unless it's to see how
it works?
Just use this:
objBalance.Open SQL, objCon, adOpenForwardOnly, adLockReadOnly,
adCmdText
If you wanna stick with the many lines of code, here's a shortcut you
can use in VBScript:
with objBalance
.ActiveConnection =3D objCon
.Source =3D SQL
.CursorType =3D AdOpenForwardOnly
.LockType =3D adLockReadOnly
.Open()
objBalance_numRows =3D 0
objBalance_total =3D .RecordCount
end with
-Kim
> -----Original Message-----
> From: James Cox [mailto:imajes@p...]
> Sent: 16. januar 2002 02:21
> To: ASP Databases
> Subject: [asp_databases] problem with making a recordset use the
> active connection.
>
>
> Hi,
>
> I have the following code, yet the recordset cannot use the open
> connection. I'm trying to do this to maintain a persistant connection
> to the server, and
> not have asp open one for each record in the first query, earlier in
the
> page, since 1000 concurrent connections is not a good idea.
>
> Any assistance greatfully appreciated, and please cc me to any
> messages sent to the list directly.
>
>
> Thanks,
>
> James Cox
>
> ----------
>
> <%
> set objCon =3D Server.CreateObject("ADODB.Connection")
> objCon.Open MM_icaforeign_STRING
> While ((Repeat1__numRows <> 0) AND (NOT rs_data_results.EOF))
>
> dim icaID, SQL
> icaID =3D rs_data_results.Fields("icaref")
> SQL =3D "SELECT SUM(receipt) as receipt, SUM(payment) as payment,
> SUM(disb) as sumdisb " SQL =3D SQL + "FROM data WHERE icaref =3D'" &
icaID
> & "' GROUP BY icaref"
>
> objBalance.ActiveConnection =3D objCon
> objBalance.Source =3D SQL
> objBalance.CursorType =3D AdOpenForwardOnly
> objBalance.LockType =3D adLockReadOnly
> objBalance.Open()
> objBalance_numRows =3D 0
> objBalance_total =3D objBalance.RecordCount
>
> ' calculate the balance.
> dim strRecpt, strPaymnt, strBalance, strDisb
> 'check for values.
> if objBalance_total <> 0 then
> strRecpt =3D CCur(objBalance.fields("receipt"))
> strPaymnt =3D CCur(objBalance.fields("payment"))
> strDisb =3D CCur(objBalance.fields("sumdisb"))
> else
> strRecpt =3D 0
> strPaymnt =3D 0
> strDisb =3D 0
> end if
> strBalance =3D (strRecpt - strPaymnt)
> strDisb =3D FormatNumber(strDisb)
> objBalance.close()
> set objBalance =3D Nothing
> %>
>
> the while loop ends furher down, after some html, however i didn't see
> any use including it here.
>
>
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or to
> unsubscribe send a blank email to
> $subst('Email.Unsub').
>
$subst('Email.Unsub').
Message #4 by "James Cox" <james.cox3@n...> on Wed, 16 Jan 2002 19:37:40 -0000
|
|
Hi,
Yes, the balance object was what I forgot to instantiate... but after doing
all that, I found that the recordset was not allowed to use the connection
method, and so failed.
i'm confused. :)
> -----Original Message-----
> From: Kim Iwan Hansen [mailto:kimiwan@k...]
> Sent: Wednesday, January 16, 2002 8:52 AM
> To: ASP Databases
> Subject: [asp_databases] RE: problem with making a recordset use the
> active connection.
>
>
> You're forgetting to instantiate the objBalance object:
> set objBalance = Server.CreateObject("ADODB.Recordset")
>
> Further more, why do you set the properties so specifically as you do
> (.activeconnection, .source, .cursortype etc.), unless it's to see how it
> works?
>
> Just use this:
> objBalance.Open SQL, objCon, adOpenForwardOnly, adLockReadOnly, adCmdText
>
> If you wanna stick with the many lines of code, here's a shortcut you can
> use in VBScript:
>
> with objBalance
> .ActiveConnection = objCon
> .Source = SQL
> .CursorType = AdOpenForwardOnly
> .LockType = adLockReadOnly
> .Open()
> objBalance_numRows = 0
> objBalance_total = .RecordCount
> end with
>
>
> -Kim
>
>
>
> > -----Original Message-----
> > From: James Cox [mailto:imajes@p...]
> > Sent: 16. januar 2002 02:21
> > To: ASP Databases
> > Subject: [asp_databases] problem with making a recordset use the active
> > connection.
> >
> >
> > Hi,
> >
> > I have the following code, yet the recordset cannot use the open
> > connection.
> > I'm trying to do this to maintain a persistant connection to the
> > server, and
> > not have asp open one for each record in the first query, earlier in the
> > page, since 1000 concurrent connections is not a good idea.
> >
> > Any assistance greatfully appreciated, and please cc me to any
> > messages sent
> > to the list directly.
> >
> >
> > Thanks,
> >
> > James Cox
> >
> > ----------
> >
> > <%
> > set objCon = Server.CreateObject("ADODB.Connection")
> > objCon.Open MM_icaforeign_STRING
> > While ((Repeat1__numRows <> 0) AND (NOT rs_data_results.EOF))
> >
> > dim icaID, SQL
> > icaID = rs_data_results.Fields("icaref")
> > SQL = "SELECT SUM(receipt) as receipt, SUM(payment) as
> payment, SUM(disb)
> > as sumdisb "
> > SQL = SQL + "FROM data WHERE icaref ='" & icaID & "' GROUP BY icaref"
> >
> > objBalance.ActiveConnection = objCon
> > objBalance.Source = SQL
> > objBalance.CursorType = AdOpenForwardOnly
> > objBalance.LockType = adLockReadOnly
> > objBalance.Open()
> > objBalance_numRows = 0
> > objBalance_total = objBalance.RecordCount
> >
> > ' calculate the balance.
> > dim strRecpt, strPaymnt, strBalance, strDisb
> > 'check for values.
> > if objBalance_total <> 0 then
> > strRecpt = CCur(objBalance.fields("receipt"))
> > strPaymnt = CCur(objBalance.fields("payment"))
> > strDisb = CCur(objBalance.fields("sumdisb"))
> > else
> > strRecpt = 0
> > strPaymnt = 0
> > strDisb = 0
> > end if
> > strBalance = (strRecpt - strPaymnt)
> > strDisb = FormatNumber(strDisb)
> > objBalance.close()
> > set objBalance = Nothing
> > %>
> >
> > the while loop ends furher down, after some html, however i
> didn't see any
> > use including it here.
> >
> >
> > $subst('Email.Unsub').
> >
>
>
> $subst('Email.Unsub').
|
|
|
View Cart
