Asp script connected to sql server gives error

12-20-2005

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.

12-20-2005

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.

12-20-2005

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

12-20-2005

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.

12-20-2005

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

12-20-2005

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.

12-20-2005

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Help. Getting a An error has occurred while establishing a connectionto the server. When connecting to SQL Server 2005, this failure may be causedby the fact that under the default settings SQL Server does not allow remote	aboutjav.com@gmail.com	ASP .Net	0	05-03-2008 12:43 PM
ASP.NET RTM gives problem with SQL Server State management	=?Utf-8?B?U2hlbGw=?=	ASP .Net	0	02-15-2006 02:08 PM
SQL Statement in ASP.NET 2.0 page gives strange error!	washoetech	ASP .Net	1	09-22-2005 08:05 AM
Cisco VPN client gives the following status: Not Connected	J	Cisco	6	07-17-2005 08:23 PM
A problem with CR connected to SQL Server in ASP	jane	ASP General	1	08-01-2003 08:42 PM