[thelist] callingm sql stored proc using asp

Vlad Alexander (XStandard) vlad.alexander at xstandard.com
Tue Oct 12 16:01:34 CDT 2004


Hi Brian,

Try using this approach to fetching data from the database:

Dim objConn, rsData, strConnectionString, strCmd, strValue

Const strID = "YourUsername" 'change me
Const strPWD = "YourPWD" 'change me
Const strServer = "YourServer" 'change me
Const strDatabase = "YourDB" 'change me
Const strDriver = "{SQL SERVER}"
Const strProvider = "SQLOLEDB"

Set objConn = Server.CreateObject("ADODB.Connection")

strConnectionString = "UID=" & strUID & _
        ";PWD=" & strPWD & _
        ";SERVER=" & strServer & _
        ";LANGUAGE=us_english" & _
        ";DATABASE=" & strDatabase & _
        ";DRIVER=" & strDriver & _
        ";PROVIDER=" & strProvider & ";"


objConn.Open strConnectionString
strCmd = "storedproc" 'change me

Set rsData = objConn.Execute(strCmd)
Do Until rsData.EOF
 strValue = rsData("xxxx").Value 'change me
 rsData.MoveNext
Loop

If objConn.State <> 0 Then
 objConn.Close
End If
Set objConn = Nothing



Regards,
-Vlad
http://xstandard.com

----- Original Message ----- 
From: "Brian Delaney" <brian.delaney at mccmh.net>
To: <thelist at lists.evolt.org>; <vlad.alexander at xstandard.com>
Sent: Tuesday, October 12, 2004 4:45 PM
Subject: Re: [thelist] callingm sql stored proc using asp


> Thanks for the help..when I run this stored proc in query analyzer it
> does return 6 rows.
>
> I suspect that I am violating some rule that when you call a SP from an
> asp web page it cannot
> create temp tables on the fly nor call a use defined function????
>
> thanks
>
> Vlad Alexander (XStandard) wrote:
>
> >Hi Brian,
> >
> >You are returning an emtpy recordset. Your test does not catch this. In
> >order to test for empty recordsets, do this:
> >
> >If Not (rsJailDiv.BOF And rsJailDiv.EOF) Then
> >
> >End If
> >
> >By the way, it's best not to call your stored procedures with "sp_"
prefix".
> >This prefix is reserved. "sp" does not stand for stored procedure - it
> >stands for "special". My preference is to do the following:
> >
> >getXXXX
> >updXXXX
> >delXXXX
> >
> >Regards,
> >-Vlad
> >http://xstandard.com
> >
> >
> >----- Original Message ----- 
> >From: "Brian Delaney" <brian.delaney at mccmh.net>
> >To: <thelist at lists.evolt.org>
> >Sent: Tuesday, October 12, 2004 3:52 PM
> >Subject: [thelist] callingm sql stored proc using asp
> >
> >
> >
> >
> >>I am calling a stored proc on my sql server from asp.
> >>
> >>For some reason if I call this sp sp_returnwhosnew I get the error
> >>"that this operation not allowed when the object is closed"
> >>
> >>here is the asp:
> >>
> >><%
> >>
> >>Call OpenJailDivConn()
> >>
> >>sDate = FormatDateTime(now, 2)
> >>
> >>Set rsJailDiv = Server.CreateObject("ADODB.Recordset")
> >>strSQL = "sp_returnwhosnewtest "
> >>rsJailDiv.Open strSQL, JailDivConn
> >>iCtr = 0
> >>DIM varlname
> >>DIM var
> >>If NOT rsJailDiv.EOF Then
> >>    while not rsJailDiv.EOF
> >>etc. etc.
> >>
> >>If I changed the strSQL to any other sp it works fine.
> >>
> >>The SP uses a ##TEMP table and also calls a user defined function that
> >>does date manipulation. Is this the problem?
> >>
> >>Any help will be appreciated.
> >>
> >>
> >>
> >>*
> >>*
> >>*
> >>This message, including any attachments, is intended solely for the use
of
> >>
> >>
> >the named recipient(s) and may contain confidential and/or priveleged
> >information.  Any unauthorized review, use, disclosure or distribution of
> >this communication(s) is expressly prohibited.  If you are not the
intended
> >recipient, please contact the sender by reply e-mail and destroy any and
all
> >copies of the original message.
> >
> >
> >>-- 
> >>
> >>* * Please support the community that supports you.  * *
> >>http://evolt.org/help_support_evolt/
> >>
> >>For unsubscribe and other options, including the Tip Harvester
> >>and archives of thelist go to: http://lists.evolt.org
> >>Workers of the Web, evolt !
> >>
> >>
> >>
> >
> >
> >
> >
>
> *
> *
> *
> This message, including any attachments, is intended solely for the use of
the named recipient(s) and may contain confidential and/or priveleged
information.  Any unauthorized review, use, disclosure or distribution of
this communication(s) is expressly prohibited.  If you are not the intended
recipient, please contact the sender by reply e-mail and destroy any and all
copies of the original message.
>




More information about the thelist mailing list