p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

proasp_howto thread: Reuse of RecordSet


Message #1 by rangelini@w... on Wed, 14 Mar 2001 18:05:57
Hi Jerry,
Thanks for your input. I declare the parameters in the store procedure. So 
I assume that when they are passed it doesn't take as much time since the  
the datatypes are declared in there?! I used this method anyways because I 
could not find any examples of opening a record and passing the cmd 
parameters to it. I had posted the question but noone replied to it. 

By the way I don't need to inclose them in single quotes when I pass the 
values since they are numbers. I will probably need that for varchar or 
char.

Thanks again for your interest. I will test your method as well.
Renata

> You should be using the ADODB.Command object for this.  When you pass
> parameters the way your doing it the database hase to do all the
> calculations to figure out the datatypes, lenghth, and various other 
things.
> This puts unnecessary stress on your database server.  By specifying this
> information with the Command object you free the database from having to
> process extra information.  For a data intensive, high traffic site I 
would
> strongly recommend following the following example. MTC - Jerry
> 
> _______________________________________________________
> 
> Set oCmd = Server.CreateObject("ADODB.Command")
> 
> With oCmd
> 	.ActiveConnection = DSNSTRING
> 	.CommandText="sp_CreateModelOrder"
> 	.CommandType=adCmdStoredProc
> 	
> .Parameters.Append(.CreateParameter("UserId",adChar,adParamInput,Len
(userid
> ),userid))
> 	
> .Parameters.Append(.CreateParameter("CartId",adChar,adParamInput,Len
(cartid)
> ,cartid))
> 	
> .Parameters.Append(.CreateParameter
("ShipMethodId",adChar,adParamInput,Len(s
> hipmethodid ),shipmethodid))		
> End With
> Set oRs = Server.CreateObject("ADODB.Recordset")
> oRs.Open oCmd,,adOpenDynamic
> _______________________________________________________
> 
> 
> 
> PS.  For your example wouldn't you need to change it to the following in
> order for it to work.
> 
> rs.open "sp_CreateModelOrder '" & userid & "',' " & cartid & "',' " &
> shipmethodid & "'", conn, adOpenDynamic
> 
> 
> 
> -----Original Message-----
> From: rangelini@w... [mailto:rangelini@w...]
> Sent: Friday, March 16, 2001 12:48 PM
> To: How To
> Subject: Re: Reuse of RecordSet
> 
> 
> I got it! Thanks all for your help the rs.open worked and in case anyone 
> needs to know this is how u pass inputs to the stored procedure once u 
> rs.open:
> rs.open "sp_CreateModelOrder " & userid & ", " & cartid & ", " & 
> shipmethodid , conn, adOpenDynamic
> 
> No need for the cmd line :0)
> Thanks again,
> Renata
> 
> 
> 
> > Hi Parag,
> > I tried doing what you suggested but I also have to send parameters to 
> the 
> > store procedure. How do I do that? Below is the code. I keep getting 
and 
> > error asking for the parameters if I don't add adCmdStoredProc but if 
I 
> do
> > 
> > ADODB.Recordset error '800a0bb9' 
> > Arguments are of the wrong type, are out of acceptable range, or are 
in 
> > conflict with one another. 
> > /store/CompleteOrder.asp, line 47 
> > 
> > 
> > 'open Database
> > Set conn = Server.CreateObject("ADODB.Connection")
> > conn.Open data_source
> > 'open connection for store procedure
> > Set cmd = Server.CreateObject("ADODB.Command")
> > Set rs = Server.CreateObject("ADODB.RecordSet")
> > Set cmd.ActiveConnection = conn
> > 
> > cmd.CommandType = &HOOO4
> > cmd.Parameters.Append cmd.CreateParameter("UserId", 3, &H0001, 4, 
userid)
> > cmd.Parameters.Append cmd.CreateParameter("CartId", 3, &H0001, 
4,cartid)
> > cmd.Parameters.Append cmd.CreateParameter("ShipMethodId", 3, &H0001, 
> > 4,shipmethodid)
> > 
> > rs.open "sp_CreateModelOrder", conn, adOpenDynamic, adCmdStoredProc
> > 
> > > Hi Renata,
> > > 
> > > Try using Open method of the recordset object with source as the 
> command
> > > object.
> > > Also set the cursorlocation to adUseClient i.e. use a client side 
> > cursor. I
> > > guess this should solve ur problem.
> > > 
> > > Regards,
> > > 
> > > Parag Phanasgaonkar
> > > Software Engineer
> > > Patni Computer Systems Ltd.
> > > 
> > > E-Mail: parag.phanasgaonkar@p...
> > > 
> > > 
> > > I select the rs from a stored procedure by using the following code:
> > > <-------------------------------------------
> > > Set cmd = Server.CreateObject("ADODB.Command")
> > > Set rs = Server.CreateObject("ADODB.Recordset")
> > > Set cmd.ActiveConnection = conn
> > > cmd.CommandText = "sp_CreateModelOrder"
> > > cmd.CommandType = &H0004
> > > 
> > > cmd.Parameters.Append cmd.CreateParameter("UserId", 3, &H0001, 4, 
> userid)
> > > cmd.Parameters.Append cmd.CreateParameter("CartId", 3, &H0001, 
> 4,cartid)
> > > cmd.Parameters.Append cmd.CreateParameter("ShipMethodId", 3, &H0001,
> > > 4,shipmethodid)
> > > Set rs = cmd.Execute(adOpenDynamic)
> > > -------------------------------------------->
> > > 
> > > I then loop through until rs.eof to add elements to an xml doc.
> > > Once other functions run I want to reaccess this same rs to display 
the
> > > items for the order completed. But when I try to rs.Movefirst or 
> reacess
> > > it I get an error message. What am I doing wrong? I thought once I 
use
> > > adOPenDynamic the rs could be moved in any directinons.
> > > Thanks for your help.
> > > Renata
> > > 
> > > ---------------------------------------------------------------------
-
> > > 

  Return to Index