Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Asp script connected to sql server gives error

Reply
Thread Tools

Asp script connected to sql server gives error

 
 
Jack
Guest
Posts: n/a
 
      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.

 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      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.


 
Reply With Quote
 
 
 
 
Jack
Guest
Posts: n/a
 
      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.
>
>
>

 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      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.


 
Reply With Quote
 
Jack
Guest
Posts: n/a
 
      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.
>
>
>

 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      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.


 
Reply With Quote
 
Jack
Guest
Posts: n/a
 
      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.
>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


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



Advertisments