By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,478 Members | 1,833 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,478 IT Pros & Developers. It's quick & easy.

Recordset object versus command object

P: n/a
Tom
I want to open a recordset object on an .asp page. When I open the
recordset I would like to use a stored procedure that expects a parameter to
be passed for the stored procedure. I will then use the recordset to loop
thru the recordset, update values from the recordset and then update the
database by passing parmeters to another stored procedure. I would like to
use the recordset object but can it be used to pass a parameter to a stored
procedure? Do I need to use a command object to open the records instead in
order to pass the parameter to a stored procedure?

Thanks a bunch!

Jul 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Tom wrote:
I want to open a recordset object on an .asp page. When I open the
recordset I would like to use a stored procedure that expects a
parameter to be passed for the stored procedure. I will then use the
recordset to loop thru the recordset, update values from the
recordset and then update the database by passing parmeters to
another stored procedure. I would like to use the recordset object
but can it be used to pass a parameter to a stored procedure? Do I
need to use a command object to open the records instead in order to
pass the parameter to a stored procedure?

Thanks a bunch!


If you provide some details, perhaps we can suggest another method to
accomplish your task that would be much more efficient than looping through
a recordset.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #2

P: n/a
you can do both... The recordset way is less code, and the command object
way is elegant. Or, just execute your queries using Connection object's
Execute method.

Actually, for the update portion, you can just execute the SQL in a
connection object, bringing back no recordset.

Conn.Execute "mySP '12', '23', 'This is Update Date'", , adExecuteNoRecords

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"Tom" <ho****************@yahoo.nospam.com> wrote in message
news:Oe**************@TK2MSFTNGP11.phx.gbl...
I want to open a recordset object on an .asp page. When I open the
recordset I would like to use a stored procedure that expects a parameter to be passed for the stored procedure. I will then use the recordset to loop
thru the recordset, update values from the recordset and then update the
database by passing parmeters to another stored procedure. I would like to use the recordset object but can it be used to pass a parameter to a stored procedure? Do I need to use a command object to open the records instead in order to pass the parameter to a stored procedure?

Thanks a bunch!

Jul 19 '05 #3

P: n/a
Tom
Hello Bob

The recordset represents a "test". The parameter passed to the stored
procedure is the requested test number.
The recordset returns with list of the questions for that test.
This is the start of the loop thru the recordset.
There are variable(up to)eight different types of questions. These are
yes/no, checkbox, multiple checkbox, text etc.
The questions have a number that represents what type of element (yes/no,
checkbox, etc).
The type of question number is evaluated in a Case statement. The HTML
layout is different in each case select based on type but the same recordset
is used for all questions. The data from the recordset is used to build the
appropriate question and question element on the page.
The user makes the required choice.
This data is captured and then written to the data base.
The application moves to the next record in the recordset.
This is the end of the looping thu all of the questions.

I will have a stored procedure that will accept the answers to the questions
and update the database from a second recordset.
I dont think this will be an issue.

When I am trying to go thru the recordset by using the command object I seem
loose the recordset functionality (EOF,BOF etc).
When I go thru the recordset using the command object I can safely pass the
test number to the SP.

When I am using the recordset I am unsure of how to call the SP and pass the
parameter while using a recordset object.
The recordset allows me to go forward and backward thru the recordset. I
dont have to requery the database for the next question and I get to update
the completed test question as they are answered.

This is what the asp page is trying to accomplish. I am trying to make this
a dynamic application so that you will only need to pick the test number to
start the process and get a test. The test number can be assigned before
hand in the user table.

One requirement is showing one question at a time.
This is an intranet type project so I am not worried about the update
traffic for the database at the end of each question.

I hope this gives and idea of what I am trying to do with the page.

Thanks for any insights, comments, ideas!
Later
Thanks a ton

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:#9**************@TK2MSFTNGP09.phx.gbl...
Tom wrote:
I want to open a recordset object on an .asp page. When I open the
recordset I would like to use a stored procedure that expects a
parameter to be passed for the stored procedure. I will then use the
recordset to loop thru the recordset, update values from the
recordset and then update the database by passing parmeters to
another stored procedure. I would like to use the recordset object
but can it be used to pass a parameter to a stored procedure? Do I
need to use a command object to open the records instead in order to
pass the parameter to a stored procedure?

Thanks a bunch!


If you provide some details, perhaps we can suggest another method to
accomplish your task that would be much more efficient than looping through
a recordset.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 19 '05 #4

P: n/a
Tom wrote:
Hello Bob

The recordset represents a "test". The parameter passed to the stored
procedure is the requested test number.
The recordset returns with list of the questions for that test.
This is the start of the loop thru the recordset.
There are variable(up to)eight different types of questions. These
are yes/no, checkbox, multiple checkbox, text etc.
The questions have a number that represents what type of element
(yes/no, checkbox, etc).
The type of question number is evaluated in a Case statement. The
HTML layout is different in each case select based on type but the
same recordset is used for all questions. The data from the
recordset is used to build the appropriate question and question
element on the page.
Everything up to here deals with displaying the data in the recordset to the
user. so far it's not relevant to your question about updating the database
....
The user makes the required choice.
OK, so you've looped through the recordset, sent the html to the client,
where the user makes the choice.
This data is captured and then written to the data base.
By a form submission?
The application moves to the next record in the recordset.
Huh? How do you have the same recordset open at this point? I'm totally
missing something. All server-side objects go out of scope during the time
the user is able to do something.
This is the end of the looping thu all of the questions.
You have this working as described? Please - more details.

I will have a stored procedure that will accept the answers to the
questions and update the database from a second recordset.
I dont think this will be an issue..
You've lost me. Why do you need a recordset here? Just pass the data from
the form submission to the stored procedure ...

When I am trying to go thru the recordset
again with the recordset! What recordset? Did you open another recordset
when the data from the form submission was received by the server-side page?
by using the command object
I seem loose the recordset functionality (EOF,BOF etc).
You do not need to use a Command object, but what you are saying makes no
sense. EOF and BOF should always work. What may be lost are some of the
capabilities that depend on cursor type: MoveFirst, bookmarks, etc. What
many people fail to realize is that the cursor type is limited when using
the Execute method to open a recordset. I control over the cursor location
and type is needed, then the recordset's Open method must be used. You can
still use a Command object:
'create the command object and set its properties, then
set rs=createobject("adodb.recordset")
rs.Open cmd
When I go thru the recordset using the command object I can safely
pass the test number to the SP.
More detail please. I don't understand this statement at all. You don't "go
through" a recordset with a command object. You might _open_ a recordset
using a command object, but the involvement of the command object ends
there.

When I am using the recordset I am unsure of how to call the SP and
pass the parameter while using a recordset object.


The simplest method is to use the Connection object:

set rs=createobject("adodb.recordset")
'set the needed recordset properties if necessary, then
conn.procedurename parm1, ..., parmN, rs

Again. You've given me a birds-eye view of the process. Hopefully i've
answered your question, but if you want any further suggestions, you will
need to go into more detail.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.