Asp script connected to sql server gives error

Discussion in 'ASP General' started by Jack, Dec 20, 2005.

  1. Jack

    Jack Guest

    I have the following asp script which I am trying to run against sql server.

    <HTML>
    <HEAD>
    <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
    <TITLE>Chapter 12 - Command Object</TITLE>
    </HEAD>
    <BODY>
    Receiving a Return Value FROM SQL Server<br><br>
    <%


    'Create and open the database object
    Set objConn = Server.CreateObject("ADODB.Connection")
    set objcmd = Server.CreateObject("ADODB.Command")

    sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=abcd;Initial
    Catalog=sailors;Data Source = TESTSERVER"


    objConn.Open sConnString
    Set objcmd.ActiveConnection = objConn

    'Declare the variables

    Dim adCmdStoredProc
    Dim adInteger
    Dim adParamReturnValue
    adCmdStoredProc = 4
    adInteger = 3
    adParamReturnValue = 4

    'Create a parameter object

    Set objParm = Server.CreateObject("ADODB.Parameter")

    'Set the command object properties

    objCmd.CommandText = "{? = call up_select_count_of_boats}"
    objCmd.CommandType = adCmdStoredProc
    'Set the parameter and append it to the paramaters collection

    Set objParm = objCmd.CreateParameter("Return", adInteger,
    adParamReturnValue,,0)
    objCmd.Parameters.Append objParm
    objCmd.Execute


    Response.Write "There are " & objCmd.Parameters.Item("Return").Value & "
    Registered Boats Listed"

    'Dereference object
    Set objParm = Nothing
    Set objCmd = Nothing
    objConn.Close
    Set objConn = Nothing
    %>

    </BODY>
    </HTML>

    However, I am getting error message as follows:Microsoft OLE DB Provider for
    SQL Server (0x80040E10)
    No value given for one or more required parameters.
    /beginaspdatabase1/pg497b.asp, line 44
    Line 44 is the objCmd.Execute

    The stored associated stored procedure is:
    CREATE PROCEDURE up_select_count_of_boats AS
    DECLARE @count INT
    SELECT @count = count(boatsid) FROM boats
    Return @count
    GO

    I am not sure why I am getting error. Any suggestion/help is appreciated.
    Thanks.
     
    Jack, Dec 20, 2005
    #1
    1. Advertising

  2. Jack wrote:
    > objCmd.CommandText = "{? = call up_select_count_of_boats}"


    Should be simply:
    objCmd.CommandText = "up_select_count_of_boats"

    > objCmd.CommandType = adCmdStoredProc
    > 'Set the parameter and append it to the paramaters collection
    >
    > Set objParm = objCmd.CreateParameter("Return", adInteger,
    > adParamReturnValue,,0)
    > objCmd.Parameters.Append objParm
    > objCmd.Execute


    Should be
    objCmd.Execute ,,128 '128=adExecuteNoRecords

    >
    > However, I am getting error message as follows:Microsoft OLE DB
    > Provider for SQL Server (0x80040E10)
    > No value given for one or more required parameters.
    > /beginaspdatabase1/pg497b.asp, line 44

    You never set the Command's Activeconnection property to an open Connection
    object.

    Set objCmd.ActiveConnection=objConn
    ....
    objCmd.Execute ,,128

    > Line 44 is the objCmd.Execute
    >
    > The stored associated stored procedure is:
    > CREATE PROCEDURE up_select_count_of_boats AS
    > DECLARE @count INT
    > SELECT @count = count(boatsid) FROM boats
    > Return @count
    > GO


    It's not effecting anything here, but it's a good practice to inclued "SET
    NOCOUNT ON" in all stored precedures to be executed via ADO:
    CREATE PROCEDURE up_select_count_of_boats AS
    SET NOCOUNT ON
    ....
    go

    This will prevent the sending of extra closed resultsets containing the
    informational "x rows affected" messages.

    --
    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.
     
    Bob Barrows [MVP], Dec 20, 2005
    #2
    1. Advertising

  3. Jack

    Jack Guest

    Thanks for the help Bob. I appreciate it. Now the code looks as follows:

    <HTML>
    <HEAD>
    <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
    <TITLE>Chapter 12 - Command Object</TITLE>
    </HEAD>
    <BODY>
    Receiving a Return Value FROM SQL Server<br><br>
    <%
    'Instruct VBScript to ignore the error and continue with the next line of code
    'On Error Resume Next

    'Create and open the database object
    Set objConn = Server.CreateObject("ADODB.Connection")
    set objcmd = Server.CreateObject("ADODB.Command")

    sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=abcd;Initial
    Catalog=sailors;Data Source = TESTSERVER"


    objConn.Open sConnString
    Set objcmd.ActiveConnection = objConn

    'Declare the variables

    Dim adCmdStoredProc
    Dim adInteger
    Dim adParamReturnValue
    adCmdStoredProc = 4
    adInteger = 3
    adParamReturnValue = 4

    'Create a parameter object

    Set objParm = Server.CreateObject("ADODB.Parameter")

    'Set the command object properties

    objCmd.CommandText = "call up_select_count_of_boats"
    objCmd.CommandType = adCmdStoredProc
    'Set the parameter and append it to the paramaters collection

    Set objParm = objCmd.CreateParameter("Return", adInteger,
    adParamReturnValue,,0)
    objCmd.Parameters.Append objParm
    objCmd.Execute,,128


    Response.Write "There are " & objCmd.Parameters.Item("Return").Value & "
    Registered Boats Listed"

    'Dereference object
    Set objParm = Nothing
    Set objCmd = Nothing
    objConn.Close
    Set objConn = Nothing



    %>

    </BODY>
    </HTML>

    However, now I am getting a error stating the following:

    Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E14) Syntax
    error or access violation which is at line 44 or the execute statement line.
    Why is this happening now? Thanks.


    "Bob Barrows [MVP]" wrote:

    > Jack wrote:
    > > objCmd.CommandText = "{? = call up_select_count_of_boats}"

    >
    > Should be simply:
    > objCmd.CommandText = "up_select_count_of_boats"
    >
    > > objCmd.CommandType = adCmdStoredProc
    > > 'Set the parameter and append it to the paramaters collection
    > >
    > > Set objParm = objCmd.CreateParameter("Return", adInteger,
    > > adParamReturnValue,,0)
    > > objCmd.Parameters.Append objParm
    > > objCmd.Execute

    >
    > Should be
    > objCmd.Execute ,,128 '128=adExecuteNoRecords
    >
    > >
    > > However, I am getting error message as follows:Microsoft OLE DB
    > > Provider for SQL Server (0x80040E10)
    > > No value given for one or more required parameters.
    > > /beginaspdatabase1/pg497b.asp, line 44

    > You never set the Command's Activeconnection property to an open Connection
    > object.
    >
    > Set objCmd.ActiveConnection=objConn
    > ....
    > objCmd.Execute ,,128
    >
    > > Line 44 is the objCmd.Execute
    > >
    > > The stored associated stored procedure is:
    > > CREATE PROCEDURE up_select_count_of_boats AS
    > > DECLARE @count INT
    > > SELECT @count = count(boatsid) FROM boats
    > > Return @count
    > > GO

    >
    > It's not effecting anything here, but it's a good practice to inclued "SET
    > NOCOUNT ON" in all stored precedures to be executed via ADO:
    > CREATE PROCEDURE up_select_count_of_boats AS
    > SET NOCOUNT ON
    > ....
    > go
    >
    > This will prevent the sending of extra closed resultsets containing the
    > informational "x rows affected" messages.
    >
    > --
    > 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.
    >
    >
    >
     
    Jack, Dec 20, 2005
    #3
  4. Jack wrote:
    > Thanks for the help Bob. I appreciate it. Now the code looks as
    > follows:

    <snip>
    > objCmd.Execute,,128

    <snip>
    >> objCmd.Execute ,,128


    See the difference? :)

    --
    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.
     
    Bob Barrows [MVP], Dec 20, 2005
    #4
  5. Jack

    Jack Guest

    Bob,
    Sorry for the screw up. I corrected the syntax and still I am getting the
    same error as before. Any further hints?

    "Bob Barrows [MVP]" wrote:

    > Jack wrote:
    > > Thanks for the help Bob. I appreciate it. Now the code looks as
    > > follows:

    > <snip>
    > > objCmd.Execute,,128

    > <snip>
    > >> objCmd.Execute ,,128

    >
    > See the difference? :)
    >
    > --
    > 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.
    >
    >
    >
     
    Jack, Dec 20, 2005
    #5
  6. It should work ...

    You should try my free code generator available at
    http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp (here's the
    download link:
    http://www.thrasherwebdesign.com/downloads1/sp_parameters.zip)

    Oh wait, you still have the call keyword in the commandtext. Get rid of it.
    It should simply be the name of the stored procedure ... nothing else:
    objCmd.CommandText = "up_select_count_of_boats"


    Jack wrote:
    > Bob,
    > Sorry for the screw up. I corrected the syntax and still I am getting
    > the same error as before. Any further hints?
    >
    > "Bob Barrows [MVP]" wrote:
    >
    >> Jack wrote:
    >>> Thanks for the help Bob. I appreciate it. Now the code looks as
    >>> follows:

    >> <snip>
    >>> objCmd.Execute,,128

    >> <snip>
    >>>> objCmd.Execute ,,128

    >>
    >> See the difference? :)
    >>
    >> --
    >> 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.


    --
    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.
     
    Bob Barrows [MVP], Dec 20, 2005
    #6
  7. Jack

    Jack Guest

    Thanks Bob. I am going to try this product by your recommendation. Best
    regards.

    "Bob Barrows [MVP]" wrote:

    > It should work ...
    >
    > You should try my free code generator available at
    > http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp (here's the
    > download link:
    > http://www.thrasherwebdesign.com/downloads1/sp_parameters.zip)
    >
    > Oh wait, you still have the call keyword in the commandtext. Get rid of it.
    > It should simply be the name of the stored procedure ... nothing else:
    > objCmd.CommandText = "up_select_count_of_boats"
    >
    >
    > Jack wrote:
    > > Bob,
    > > Sorry for the screw up. I corrected the syntax and still I am getting
    > > the same error as before. Any further hints?
    > >
    > > "Bob Barrows [MVP]" wrote:
    > >
    > >> Jack wrote:
    > >>> Thanks for the help Bob. I appreciate it. Now the code looks as
    > >>> follows:
    > >> <snip>
    > >>> objCmd.Execute,,128
    > >> <snip>
    > >>>> objCmd.Execute ,,128
    > >>
    > >> See the difference? :)
    > >>
    > >> --
    > >> 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.

    >
    > --
    > 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.
    >
    >
    >
     
    Jack, Dec 20, 2005
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. washoetech
    Replies:
    1
    Views:
    1,657
    Hans Kesting
    Sep 22, 2005
  2. =?Utf-8?B?U2hlbGw=?=
    Replies:
    0
    Views:
    359
    =?Utf-8?B?U2hlbGw=?=
    Feb 15, 2006
  3. Keithb
    Replies:
    4
    Views:
    459
    =?Utf-8?B?TWlsb3N6IFNrYWxlY2tp?=
    Oct 10, 2006
  4. jane
    Replies:
    1
    Views:
    177
  5. Varun Goel

    LDAP Server not connected error

    Varun Goel, Jan 17, 2008, in forum: Ruby
    Replies:
    11
    Views:
    221
    Varun Goel
    Jan 21, 2008
Loading...

Share This Page