Re: SQL stored procedure not working.

From: Lin Ma (a_at_a.com)
Date: 03/23/04 

Date: Tue, 23 Mar 2004 13:55:38 -0600

Thanks Bob, It is working.

One more question: I use AddOpenKeySet to get the recountcount. Like:

rs.open strSQL, strConn, AdOpenKeySet

Then I use (Response.Write rs.recordcount) to display the total record from
the query.

If I do not use that, how can I get the total record count? Do I need
something in the stored procedure?

Thanks,

Lin

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23Uz4zhQEEHA.2088@TK2MSFTNGP10.phx.gbl...
> Lin Ma wrote:
> > I create a stored procedure and a asp page. But I got an error.
> > Please help.
> >
> > Thanks,
> >
> > Lin
> >
> > Error Type:
> > Microsoft OLE DB Provider for SQL Server (0x80040E10)
> > Procedure 'getCustomerList' expects parameter '@Location', which was
> > not supplied.
> >
> > --------------- asp page --------------
> > Dim cmd, ln
> >
> > Set cmd = Server.CreateObject("ADODB.Command")
> > With cmd
> > .ActiveConnection = strCustomerCon
> > .commandText = "getCustomerList"
> > .CommandType = adCmdStoredProc
>
> The first parameter you should be creating is the Return parameter:
> .Parameters.Append .CreateParameter ("RETURN_VALUE", _
> adInteger, adParamReturnValue)
>
> > .Parameters.Append .CreateParameter ("@Location", adVarChar,
> > adParamInput, 25)
> > .Parameters("@Location") = "TX1"
> > .Execute ln, , adOpenKeySet
>
> There are several problems here:
> 1. You aren't supplying a recordset variable to receive the resultset
> returned from this stored procedure. This is all that you need:
>
> Set rs= .Execute
>
> 2. You cannot specify the cursor type via the third argument in the
Execute
> method. That argument is for CommandType (which you've already set) and
> ExecuteOption arguments.
> 3. Even if you could specify the cursor type there, you probably should
not
> be attempting to open an expensive keyset cursor. It is very rare in asp
> that you will have a cursor open long enough to care about changes made by
> other users (which is the only reason to use a keyset cursor).
>
> > End with
> >
> > ------------------- Stored procedure --------------
> > CREATE PROCEDURE [dbo].[getCustomerList]
> > (
> > @Location nvarchar(25)
> > )
> > AS
> > Select * from Customer where Location = @Location order by
> > CustomerName
> > GO
>
> Since your procedure has no output parameters, and you don't seem to be
> interested in the RETURN value, you do not need to use a Command object.
You
> can simply say this:
>
> set cn=createobject("adodb.connection")
> cn.open strCustomerCon
> Set rs=createobject("adodb.recordset")
> cn.getCustomerList "TX1", rs
>
> If you are determined to use the Command object, you might want to try my
> stored procedure code generator which is available here:
> http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp
>
> HTH,
> 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.
>
>

Relevant Pages

  • Best Practices
    ... An important part of the conversion is moving a large number of Oracle stored procedure to VB.NET; the reason is that they containt too much business logic. ... define cursor 1 ... Should I execute the select statements at the beginning of the function, or should I execute them in the "middle" of the function? ... Do I use the same datareader for all the select statements (eg call ExecuteReader on the same datareader with different command object - or maybe the same command object with a different command string)? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... these words at the beginning before sending them to the cursor. ... Looking for a FAQ on Indexing Services/SQL FTS ... >>Create table Noise ... >>> 1) check noise words inside stored procedure ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Case statement issue
    ... except with a cursor other than the default ... > I have taken the sample of paging using a stored procedure from the ... > case statement, then the error message. ... > The for loop also seems to be a problem. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Case statement issue
    ... except with a cursor other than the default ... > I have taken the sample of paging using a stored procedure from the ... > case statement, then the error message. ... > The for loop also seems to be a problem. ...
    (microsoft.public.inetserver.asp.general)
  • RE: how to call a stored procedure that returns cursor??
    ... | Content-Class: urn:content-classes:message ... | i have a stored procedure that returns a cursor. ... JDBC directly. ...
    (microsoft.public.sqlserver.jdbcdriver)