Re: Operation is not allowed...

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 18:13:05 -0400

James Baker wrote:
> If the connection is open, can I execute two statements against it
> before I close the connection?

Absolutely. Did you have a problem doing it?

> I'm opening the one recordset I'm
> using with it and I'm also potentially executing the INSERT with it
> as well. Ultimately I rewrote my code (pre-Stored Procedure) as:

Looks good.

>
> Dim cmsRS, cmsSQL
> Dim insSQL
>
> Dim cn
> Set cn = Server.CreateObject("ADODB.Connection")
> cn.Open "Provider=SQLOLEDB;" & _
> "Data Source=XXXXX;" & _
> "Initial Catalog=XXXXX;" & _
> "User ID = XXXXX;Password=XXXXX"
>
> Set cmsRS = Server.CreateObject("ADODB.Recordset")
>
> cmsSQL = "SELECT ClientsFileNumber FROM TblOrder WHERE FileNumber =
> '" & filenumber & "' AND ClientCode = 'CMSNY'"

Can this return more than one record? If so, you can streamline things by
changing it to:
cmsSQL = "SELECT Count(ClientsFileNumber) FROM TblOrder WHERE " & _
"FileNumber = '" & filenumber & "' AND ClientCode = 'CMSNY'"

> cmsRS.Open cmsSQL, cn, , , 1
>

And change this:
> If NOT cmsRS.EOF Then

To:
If cmsRS(0) > 0 then 'if the first field of the recordset contains a value >
0

> insSQL = "INSERT INTO CMS (FileNumber, StatusDate, StatusTime,
> StatusComment) VALUES('" & cmsRS("ClientsFileNumber") & "', '" &
> date() & "', '" & adstime & "', 'Order has entered review process')"
> cn.Execute insSQL,,129
> End If
>
> cmsRS.Close
> Set cmsRS = Nothing
> cn.Close

Don't forget:
Set cn=nothing

Although some will argue against the need to do that given that you've
destroyed all the child objects first ...

Bob Barrows 

-- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Relevant Pages

  • Re: Operation is not allowed...
    ... If the connection is open, can I execute two statements against it before I ... Dim cmsRS, cmsSQL ... Set cmsRS = Server.CreateObject ...
    (microsoft.public.inetserver.asp.general)
  • executing sproc
    ... establish a new Connection Cnct and open it successfully. ... Before I run the stored proc, I execute a SQL Select ... Dim lcsConnectionString As String ...
    (microsoft.public.data.ado)
  • Unable to recognize stored procedure param
    ... execute from my application. ... Dim Connection As SqlConnection ... Dim Command As SqlCommand ...
    (microsoft.public.dotnet.languages.vb)
  • Re: OLEDB & Recordset
    ... You'd open the connection using a UDL file with something ... >If I execute the following code through a normal ODBC connection (In Admin ... >If I execute the same code, using a UDL file using the "OLE DB ... >Dim BarCode_Database As New ADODB.Connection ...
    (microsoft.public.sqlserver.odbc)
  • Re: Passing recordset between pages
    ... Before closing the connection: ... Recordset Results and display the results ... Execute does not make the local variables in page 1 available in page 2, ... This email account is my spam trap so I ...
    (microsoft.public.scripting.vbscript)