access to database two times, same page, same object?

[kybbe]

Hi!

I have a situation where I access an SQL Server 2000
database two times one the same page, via the
same "command object". First through a stored procedure
then via "ordinary asp sql query".

Could someone please tell me if I use good/efficient
coding here, it works well and I have had no problem with
it. But after reading some articles I´m not sure if it´s
better coding to close the command object after executing
the stored procedure and then declare a new one for
the "ordinary asp sql query"?
(including the second query into the stored procedure is not an option)

Here is a "compressed" version of my code:


Set MyConec=Server.CreateObject("ADODB.Connection&quot
constr="Driver=SQL Server; Data Source=myDatasource; User ID=myUserID; Password=myPassword"
DataAnslutn.Open constr


Set ComObj = Server.CreateObject("ADODB.Command&quot

With ComObj
.ActiveConnection = MyConec
.CommandText = "SP_name"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter ("NeededValue", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter ("@Name", adVarChar, adParamInput, 255, Name)
.Parameters.Append .CreateParameter ("@InputNumber", adInteger, adParamInput, , InputNumber)
.Parameters.Append .CreateParameter("@new_id", adInteger, adParamOutput)

.Execute, , adExecuteNoRecords

NeededValue=ComObj(0)

If NeededValue = 0 Then
new_id = .Parameters("@new_id&quot

SQL = SQL & " INSERT INTO myTable (Value1,myID,InputNumber) VALUES "
SQL = SQL & "(" & Value1 & "," & new_id & "," & InputNumber & &quot"

.CommandText = SQL
.CommandType = adCmdText

.Execute, , adExecuteNoRecords

End If

End With

MyConec.Close
Set MyConec = Nothing
Set ComObj = Nothing


Grateful for comments on this

Thanks in advance
/Anders

 

[GaryC123]

Nope its better to use the same object and that is how most people will do it, if you keep closing and reopening objects then that is more work for the server.

http://www.intouchmedialtd.com