Hello all, I am currently trying

[bassguy]

Hello all,

I am currently trying to write some standards for coding for my department (...I am the department) and
I have an interesting question for discussion.

When I am connecting to a Database i have found there are several methods that can be used. here are a few of the scripts:


cnn="driver={SQL Server};server=rgreennt;uid=sa;pwd=;database=pubs"
Set rs=server.createobject("adodb.recordset&quot
ssql="Select * from Author"
rs.open ssql,cnn

This gives me a recordset to display.

Also there is:


Set cnn = New ADODB.Connection
cnn.ConnectionString = "driver={SQL server};server=rgreennt;uid=sa;pwd=;database=pubs"
cnn.Open
Set rs =cnn.Execute ("Select * from Author&quot


This also gives me a recordset to display
Yet a third example:



Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim fldloop As ADODB.Field
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter
Dim provStr As String
Dim royalty As Variant


cn.Provider = "sqloledb"
provStr "Server=MyServer;Database=pubs;Trusted_Connection=yes"
cn.Open provStr
Set cmd.ActiveConnection = cn
cmd.CommandText = "myProc"
cmd.CommandType = adCmdStoredProc

Set param1 = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
cmd.Parameters.Append param1

Set param2 = cmd.CreateParameter("Output", adInteger, adParamOutput)
cmd.Parameters.Append param2

Set param3 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append param3
royalty = Trim(InputBox("Enter royalty:&quot)
param3.Value = royalty
Set rs = cmd.Execute



Now with the exception of the paramaters in the 3rd instance, it appears I can get a recordset by any of the following if the prepwork is correct.

rs.open
cnn.execute
cmd.Execute


What are the pros and cons?

thanks for the time

Bassguy

 

[TonyU]

[tt]
Here you go

faq333-178

[tt]"A Successful man is one who can build
a firm foundation with the bricks
that others throw at him"
[/tt]

​

 

[bassguy]

I understand the difference between ODBC and OLEDB....at least I think so...the question is really "what are the pros and cons of using recordset.open, rs=connection.execute and rs=command.execute?"

again thanks
bassguy

 

[EmmetA]

Typically a rs.Open command will leave the record set open, while the rs.Execute command will run the SQL statment or populate the record set one time.

 

[onpnt]

http://www.takempis.com/adoobjects.asp

this should help you out! You cannot mandate productivity, you must provide the tools to let people become their best.
-Steve Jobs
admin@onpntwebdesigns.com

 

[Einstein47]

Although some may appreciate the power that option 3 allows, when it gets down to the main guts of a web page, it is in the power of the SQL.

There are times when option 2 will be all that is needed - a single RS with the unique value requested. However, option 1 allows for:

While Not RS.EOF
  ...
  RS.MoveNext
Wend

looping. Which as a standard may allow for easier debugging of all your web pages. You won't have to remember, "was this the time I only needed one value?" If all your Objects for reading/writing the data stream are identical, you only need to think of the SQL that does the work on the data.

This is especially important if you change databases somewhere down the road. Then you only need to change the connection string and the rest can remain as is (or with only slight modifications).

Good luck, and remember that when you are your own boss, you may realize that you are working for an idiot. Einstein47
("For every expert, there is an equal and opposite expert." - Arthur C. Clarke)