473,320 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 3481
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Hameed Khan | last post by:
hi all, i am getting some problems with my first socket script. can any one of you point me why this is happening. the server script suppose to accept one connection at a time and send countdown...
5
by: Boris Nikolaevich | last post by:
This is backwards of what I usually want--normally if you have a long-running ASP script, it's a good idea to check to see whether the client is still connected so you can cancel execution. ...
0
by: Freebase | last post by:
Something changed recently on our W2K SP4 machine when we installed INTERSOLV ODBC software... the following script just opens a connection to an MS-Access DB, gets a record then tears down the...
12
by: tshad | last post by:
I am not sure why I am getting this error: I have the following code I want to run from another include file that holds all my functions. functions.inc...
8
by: Sergei | last post by:
Hi, I am displaying modal dialog and passing values from the main form to the modal dialog and back. It works fine but if I used the following syntax on Page_Load(just for testing) in VB to...
21
by: hemant.singh | last post by:
Hello all, I am try'g to send window.location.href to the server script who will generate dynamic javascript according to the referral name comg in as param Now bcz <script language="javascript"...
7
by: muttu2244 | last post by:
Hi Everyone I want to run a python script in all the machines that are connected through local network and collect the information about that machine such as HDD size, RAM capacity(with number...
0
rajiv07
by: rajiv07 | last post by:
Hi to all this is the code i am using to do some task but it gives the following error.i am breaking my head why the error is coming please help me on this. The Error in log file. (2)No such...
5
by: This | last post by:
I have a pretty basic emailing script that sends a relatively small number (150) of html emails. The emails are compiled, personalised from a mysql db subscribers list, and sent using mail() -...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.