469,625 Members | 1,917 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

Asp script connected to sql server gives error

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.

Dec 20 '05 #1
6 3361
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.
Dec 20 '05 #2
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.

Dec 20 '05 #3
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.
Dec 20 '05 #4
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.

Dec 20 '05 #5
It should work ...

You should try my free code generator available at
http://www.thrasherwebdesign.com/ind...s&hp=links.asp (here's the
download link:
http://www.thrasherwebdesign.com/dow...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.
Dec 20 '05 #6
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/ind...s&hp=links.asp (here's the
download link:
http://www.thrasherwebdesign.com/dow...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.

Dec 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Hameed Khan | last post: by
12 posts views Thread by tshad | last post: by
8 posts views Thread by Sergei | last post: by
21 posts views Thread by hemant.singh | last post: by
rajiv07
reply views Thread by rajiv07 | last post: by
5 posts views Thread by This | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.