p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: Records in Selectio Question


Message #1 by "Chip Dukes" <cdukes77@b...> on Wed, 26 Dec 2001 20:56:43 -0500
This is a multi-part message in MIME format.



------=_NextPart_000_0076_01C18E4F.D307AA20

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



If anyone can help - my thanks ...



Using the following code ...



"intRecords =3D adoRS.RecordCount"



I expect "intRecords" to contain the count of the number of records 

found from a SQL Query. When I test however I always get "-1" ... But, I 

know the SQL Query has worked properly, because I can loop through and 

display all of the records found.



I know this should work ... Aaaaargh.



Chip Dukes








Message #2 by "Rade Popovic" <poppublic@y...> on Thu, 27 Dec 2001 03:09:59 +0100
This is a multi-part message in MIME format.



------=_NextPart_000_0010_01C18E83.F80CC150

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



Try following:



adoRs.Open "your SQL",connection, 3, 3



 adoRS.MoveLast : adoRS.MoveFirst



Rade Popovic






Message #3 by "Chip Dukes" <cdukes77@b...> on Wed, 26 Dec 2001 21:43:39 -0500
This is a multi-part message in MIME format.



------=_NextPart_000_00A2_01C18E56.617F2A20

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



Thanks for your responses ... I tried what everyone suggested ... no joy 

... but I may have been taking suggestions out of context ... here's the 

test I built to try getting just the records in selection. It does work, 

the loop properly shows 4 records when run against my database. This is 

almost straight out of the WROX books.



Dim adoConn, adoCmd, adoRS, strConnectionString, intRecords



'create ADO Objects

Set adoConn =3D Server.CreateObject("ADODB.Connection")

Set adoCmd =3D Server.CreateObject("ADODB.Command")

Set adoRS =3D Server.CreateObject("ADODB.Recordset")



'open ADO Connection

strConnectionString=3D"Provider=3DSQLOLEDB.1;Persist Security 

Info=3DFalse;User ID=3Dsa;PASSWORD=3Dsa;Initial Catalog=3DMembers;Data 

Source=3Dcdukes;Locale Identifier=3D2057;Connect Timeout=3D15;Use 

Procedure for Prepare=3D1;Auto Translate=3DTrue;Packet 

Size=3D4096;Workstation ID=3Dcdukes"



'set ADO Command properties

adoCmd.CommandType =3D adCmdText

adoCmd.CommandText =3D "SELECT * FROM MemberTemp"



'Open the Connection

adoConn.Open strConnectionString



'associate the Command with the Open Connection

adoCmd.ActiveConnection =3D adoConn



'retrieve recordset

Set adoRS =3D adoCmd.Execute



'Count the records found

intRecords=3DadoRS.RecordCount



Do While not adoRS.EOF

Response.Write vbcrlf

Response.Write adoRS("Key").value

adoRS.MoveNext

Loop



Response.Write "The Number of Records found =3D " & intRecords 



'Close the connection and void the variables

adoConn.Close

Set adoConn =3D nothing

Set adoCmd =3D nothing

Set adoRS =3D nothing








Message #4 by "Kim Iwan Hansen" <kimiwan@k...> on Thu, 27 Dec 2001 09:15:28 +0100
This is a multi-part message in MIME format.



------=_NextPart_000_0002_01C18EB7.069AEB60

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: 7bit



you need to set the cursortype = adOpenStatic as the default cursortype is

adOpenForwardOnly.



-Kim



  -----Original Message-----

  From: Chip Dukes [mailto:cdukes77@b...]

  Sent: 27. december 2001 03:44

  To: ASP Databases

  Subject: [asp_databases] Re: Records in Selectio Question





  ---

  Need a present for your favorite programmer?

  E-Documents from Amazon.com at http://p2p.wrox.com/edocs.asp

  ---

  Thanks for your responses ... I tried what everyone suggested ... no joy

... but I may have been taking suggestions out of context ... here's the

test I built to try getting just the records in selection. It does work, the

loop properly shows 4 records when run against my database. This is almost

straight out of the WROX books.



  Dim adoConn, adoCmd, adoRS, strConnectionString, intRecords



  'create ADO Objects

  Set adoConn = Server.CreateObject("ADODB.Connection")

  Set adoCmd = Server.CreateObject("ADODB.Command")

  Set adoRS = Server.CreateObject("ADODB.Recordset")



  'open ADO Connection

  strConnectionString="Provider=SQLOLEDB.1;Persist Security Info=False;User

ID=sa;PASSWORD=sa;Initial Catalog=Members;Data Source=cdukes;Locale

Identifier=2057;Connect Timeout=15;Use Procedure for Prepare=1;Auto

Translate=True;Packet Size=4096;Workstation ID=cdukes"



  'set ADO Command properties

  adoCmd.CursorType = adOpenStatic

  adoCmd.CommandType = adCmdText

  adoCmd.CommandText = "SELECT * FROM MemberTemp"



  'Open the Connection

  adoConn.Open strConnectionString



  'associate the Command with the Open Connection

  adoCmd.ActiveConnection = adoConn



  'retrieve recordset

  Set adoRS = adoCmd.Execute



  'Count the records found

  intRecords=adoRS.RecordCount



  Do While not adoRS.EOF

  Response.Write vbcrlf

  Response.Write adoRS("Key").value

  adoRS.MoveNext

  Loop



  Response.Write "The Number of Records found = " & intRecords



  'Close the connection and void the variables

  adoConn.Close

  Set adoConn = nothing

  Set adoCmd = nothing

  Set adoRS = nothing




$subst('Email.Unsub').






Message #5 by "Breidenbach, Beth" <Beth.Breidenbach@g...> on Thu, 27 Dec 2001 10:35:09 -0500
This message is in MIME format. Since your mail reader does not understand

this format, some or all of this message may not be legible.



------_=_NextPart_001_01C18EEC.1158CA94

Content-Type: text/plain;

	charset="iso-8859-1"



If you change the line 

"Set adoRS = adoCmd.Execute"

to 

"adoRS.Open adoCmd,,3"

 

it will open the recordset with the results of adoCmd, and set the

cursortype to adOpenStatic (which allows you to see the recordcount).  As

Kim's response noted, the default value is adOpenForwardOnly, which doesn't

allow you to see record counts.

 

Beth Breidenbach

 



-----Original Message-----

From: Chip Dukes [mailto:cdukes77@b...]

Sent: Wednesday, December 26, 2001 6:44 PM

To: ASP Databases

Subject: [asp_databases] Re: Records in Selectio Question





---

Need a present for your favorite programmer?

E-Documents from Amazon.com at http://p2p.wrox.com/edocs.asp

--- 

Thanks for your responses ... I tried what everyone suggested ... no joy ...

but I may have been taking suggestions out of context ... here's the test I

built to try getting just the records in selection. It does work, the loop

properly shows 4 records when run against my database. This is almost

straight out of the WROX books.

 

Dim adoConn, adoCmd, adoRS, strConnectionString, intRecords

 

'create ADO Objects 

Set adoConn = Server.CreateObject("ADODB.Connection") 

Set adoCmd = Server.CreateObject("ADODB.Command") 

Set adoRS = Server.CreateObject("ADODB.Recordset") 

 

'open ADO Connection 

strConnectionString="Provider=SQLOLEDB.1;Persist Security Info=False;User

ID=sa;PASSWORD=sa;Initial Catalog=Members;Data Source=cdukes;Locale

Identifier=2057;Connect Timeout=15;Use Procedure for Prepare=1;Auto

Translate=True;Packet Size=4096;Workstation ID=cdukes" 

 

'set ADO Command properties 

adoCmd.CommandType = adCmdText 

adoCmd.CommandText = "SELECT * FROM MemberTemp" 

 

'Open the Connection 

adoConn.Open strConnectionString 

 

'associate the Command with the Open Connection 

adoCmd.ActiveConnection = adoConn 

 

'retrieve recordset 

Set adoRS = adoCmd.Execute 

 

'Count the records found 

intRecords=adoRS.RecordCount 

 

Do While not adoRS.EOF 

Response.Write vbcrlf 

Response.Write adoRS("Key").value 

adoRS.MoveNext 

Loop 

 

Response.Write "The Number of Records found = " & intRecords  

 

'Close the connection and void the variables 

adoConn.Close 

Set adoConn = nothing 

Set adoCmd = nothing 

Set adoRS = nothing




$subst('Email.Unsub'). 







  Return to Index