Asp script connected to sql server gives error

J

Jack

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.
 
B

Bob Barrows [MVP]

Jack said:
objCmd.CommandText = "{? = call up_select_count_of_boats}"
Click to expand...

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
Click to expand...

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
Click to expand...
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
Click to expand...

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.
 
J

Jack

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.
 
J

Jack

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

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Classic ASP - Stream XML from SP to Browser 3
INSERT script to SQL Server 15
Error while calling stored procedure from asp page 4
A problem with CR connected to SQL Server in ASP 1
Connection String from Include file ASP to SQL Server 2000???? 2
Behavior of ADODB.Command .Execute changes on different servers??? 1
SQL 2005/ ASP : ADODB.Connection error '800a0e7a' Provider cannot befound. It may not be properly in 1
asp and ms sql 10

Members online

No members online now.
Total: 228 (members: 1, guests: 227)
Robots: 111

Forum statistics

Threads
473,962
Messages
2,570,134
Members
46,692
Latest member
JenniferTi

Latest Threads

Top