473,231 Members | 1,846 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,231 software developers and data experts.

Error while calling stored procedure from asp page

Hi,
I am trying to run an example code from a book. However I am getting the
following error message:

Number: -2147217900
Description: Syntax error or access violation

Source: Microsoft OLE DB Provider for SQL Server

SQLState: 42000

NativeError: 0

The following is the code that is being used in the asp page:
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>

<!--Display the page data-->
<%
'Instruct VBScript to ignore the error and continue
'with the next line of code
On Error Resume Next

Set objConn = Server.CreateObject("ADODB.Connection")
sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=abcd;Initial
Catalog=sailors;Data Source = TESTSERVER"
objConn.Open sConnString
Set objcmd.ActiveConnection = objConn

'Check for errors
If objConn.Errors.Count > 0 Then
'Create an error object to access the ADO errors collection
Set objErr = Server.CreateObject("ADODB.Error")
'Declare boolean flag for critical errors
Dim blnCriticalError
'Write all errors to the page
For Each objErr In objConn.Errors
If objErr.Number <> 0 Then
Response.Write "Number: " & objErr.Number & "<P>"
Response.Write "Description: " & objErr.Description & "<P>"
Response.Write "Source: " & objErr.Source & "<P>"
Response.Write "SQLState: " & objErr.SQLState & "<P>"
Response.Write "NativeError: " & objErr.NativeError & "<P>"
blnCriticalError = True
End If
Next
'Dereference all objects
Set objErr = Nothing
If blnCriticalError Then
Response.End
End If
End If

'Declare variables and set their values
Dim adOpenForwardOnly
Dim adCmdStoredProc
Dim strBoat
adOpenForwardOnly = 0
adCmdStoredProc = 4
strBoat = "Laser"

'Create the recordset object and open the recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "sp_qparmBoats '" & CStr(strBoat) & "'"
'Response.Write strSql & "<br>"
'Response.End
objRS.Open strSQL, objConn, adOpenForwardOnly, , adCmdStoredProc

'Check for errors
If objConn.Errors.Count > 0 Then
'Create an error object to access the ADO errors collection
Set objErr = Server.CreateObject("ADODB.Error")
'Write all errors to the page
For Each objErr In objConn.Errors
If objErr.Number <> 0 Then
Response.Write "Number: " & objErr.Number & "<P>"
Response.Write "Description: " & objErr.Description & "<P>"
Response.Write "Source: " & objErr.Source & "<P>"
Response.Write "SQLState: " & objErr.SQLState & "<P>"
Response.Write "NativeError: " & objErr.NativeError & "<P>"
blnCriticalError = True
End If
Next
'Dereference all objects
Set objErr = Nothing
If blnCriticalError Then
Response.End
End If
End If

'Loop through the recordset displaying the last name field
Do While Not objRS.EOF
Response.Write objRS("BoatName") & "<P>"
objRS.MoveNext
Loop

'Close and dereference database objects
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
</BODY>
</HTML>

Any help/suggestion is appreciated here.

Dec 22 '05 #1
4 6656
Jack wrote:
Hi,
I am trying to run an example code from a book. However I am getting
the following error message:

Number: -2147217900
Description: Syntax error or access violation
Which line of code causes this error?

Source: Microsoft OLE DB Provider for SQL Server

SQLState: 42000

NativeError: 0

The following is the code that is being used in the asp page:
<HTML> <snip> <%
'Instruct VBScript to ignore the error and continue
'with the next line of code
On Error Resume Next

Set objConn = Server.CreateObject("ADODB.Connection")
sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=abcd;Initial
Catalog=sailors;Data Source = TESTSERVER"
I hope you are not planning to use the sa account in your application (and
that the password really isn't "abcd")

objConn.Open sConnString
Set objcmd.ActiveConnection = objConn

'Check for errors
If objConn.Errors.Count > 0 Then 'Create an error object to access the ADO errors collection
Set objErr = Server.CreateObject("ADODB.Error") not needed - vbscript is loosely typed. A simple "dim objErr statement would
have sufficed

<snip>
'Declare variables and set their values
Dim adOpenForwardOnly
Dim adCmdStoredProc
adOpenForwardOnly = 0
adCmdStoredProc = 4
You could have used constants for these:
const adOpenForwardOnly = 0

In fact, you can use a metadata tag in global.as to avoid the need to do
this at all. See
http://www.aspfaq.com/show.asp?id=2112
Dim strBoat
strBoat = "Laser"

'Create the recordset object and open the recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "sp_qparmBoats '" & CStr(strBoat) & "'"
It is a bad idea to use "sp_" to prefix your stored procedures. This prefix
should be reserved for system stored procedures, because SQL Server
processes them as if they were. When it receives a command ot execute a
procedure with tat prefix, it assumes it's a system procedure and wastes
time looking for it in hte Master database, only going back to your database
to look for it when it does not find it in Master. Granted, there's not a
lot of tie wasted: the real problems begin when you make the mistake of
giving your procedure the same name as a system procedure ... guess which
one will always be executed ...?
'Response.Write strSql & "<br>"
'Response.End
objRS.Open strSQL, objConn, adOpenForwardOnly, , adCmdStoredProc


By saying "strSQL = "sp_qparmBoats '" & CStr(strBoat) & "'""
you have created a dynamic sql statement. Therefore adCmdStoredProc is NOT
the correct command type to be using here. For a string of text containing a
dynamic sql statement, you must use adCmdText, not adCmdStoredProc

I prefer using the procedure-as-connection-method technique for this. In
your case I would do this:

Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.sp_qparmBoats strBoat, objRS
'You should now have an open recordset

More on this technique can be found here:
http://groups.google.com/groups?hl=e...TNGP12.phx.gbl
Bob Barrows
--
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 22 '05 #2
Thanks a lot for all the advise with regard to the code. I went through all
of those. By the way, putting adcmdText instead of adCmdStoredProc did the
job and I do not get any errors as before. I have a quick question here. Do
you have any code reference to calling stored procedure with input or output
parameters from asp? What I mean by it is that e.g. from a form, I would like
to call stored procedure with paramters and the parameter values would be
supplied by users in the form submission instead of being embedded in code.
In any event I appreciate all your help here. Thanks again. Best regards.
"Bob Barrows [MVP]" wrote:
Jack wrote:
Hi,
I am trying to run an example code from a book. However I am getting
the following error message:

Number: -2147217900
Description: Syntax error or access violation


Which line of code causes this error?

Source: Microsoft OLE DB Provider for SQL Server

SQLState: 42000

NativeError: 0

The following is the code that is being used in the asp page:
<HTML>

<snip>
<%
'Instruct VBScript to ignore the error and continue
'with the next line of code
On Error Resume Next

Set objConn = Server.CreateObject("ADODB.Connection")
sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=abcd;Initial
Catalog=sailors;Data Source = TESTSERVER"


I hope you are not planning to use the sa account in your application (and
that the password really isn't "abcd")


objConn.Open sConnString
Set objcmd.ActiveConnection = objConn

'Check for errors
If objConn.Errors.Count > 0 Then

'Create an error object to access the ADO errors collection
Set objErr = Server.CreateObject("ADODB.Error")

not needed - vbscript is loosely typed. A simple "dim objErr statement would
have sufficed

<snip>
'Declare variables and set their values
Dim adOpenForwardOnly
Dim adCmdStoredProc
adOpenForwardOnly = 0
adCmdStoredProc = 4


You could have used constants for these:
const adOpenForwardOnly = 0

In fact, you can use a metadata tag in global.as to avoid the need to do
this at all. See
http://www.aspfaq.com/show.asp?id=2112
Dim strBoat
strBoat = "Laser"

'Create the recordset object and open the recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "sp_qparmBoats '" & CStr(strBoat) & "'"


It is a bad idea to use "sp_" to prefix your stored procedures. This prefix
should be reserved for system stored procedures, because SQL Server
processes them as if they were. When it receives a command ot execute a
procedure with tat prefix, it assumes it's a system procedure and wastes
time looking for it in hte Master database, only going back to your database
to look for it when it does not find it in Master. Granted, there's not a
lot of tie wasted: the real problems begin when you make the mistake of
giving your procedure the same name as a system procedure ... guess which
one will always be executed ...?
'Response.Write strSql & "<br>"
'Response.End
objRS.Open strSQL, objConn, adOpenForwardOnly, , adCmdStoredProc


By saying "strSQL = "sp_qparmBoats '" & CStr(strBoat) & "'""
you have created a dynamic sql statement. Therefore adCmdStoredProc is NOT
the correct command type to be using here. For a string of text containing a
dynamic sql statement, you must use adCmdText, not adCmdStoredProc

I prefer using the procedure-as-connection-method technique for this. In
your case I would do this:

Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.sp_qparmBoats strBoat, objRS
'You should now have an open recordset

More on this technique can be found here:
http://groups.google.com/groups?hl=e...TNGP12.phx.gbl
Bob Barrows
--
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 22 '05 #3
Jack wrote:
Thanks a lot for all the advise with regard to the code. I went
through all of those. By the way, putting adcmdText instead of
adCmdStoredProc did the job and I do not get any errors as before. I
have a quick question here. Do you have any code reference to calling
stored procedure with input or output parameters from asp?


Google should find you many examples, such as these:
http://groups.google.com/group/micro...98eefa4?hl=en&

http://groups.google.com/group/micro...97e62dab19d877

http://groups.google.com/group/micro...1c5e0b4?hl=en&

For output and return parameters, you need to use an explicit Command
object. See the link I included in my first message. It contains a link to a
code generator I wrote for stored procedures with output and return
parameters.

Here's another:
http://groups.google.com/group/micro...97c2fa50f7fa1f

Bob Barrows
--
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 22 '05 #4
Thanks a lot Bob for the references. I am going to through those and get
conceptual knowledge.
Best regards.

"Bob Barrows [MVP]" wrote:
Jack wrote:
Thanks a lot for all the advise with regard to the code. I went
through all of those. By the way, putting adcmdText instead of
adCmdStoredProc did the job and I do not get any errors as before. I
have a quick question here. Do you have any code reference to calling
stored procedure with input or output parameters from asp?


Google should find you many examples, such as these:
http://groups.google.com/group/micro...98eefa4?hl=en&

http://groups.google.com/group/micro...97e62dab19d877

http://groups.google.com/group/micro...1c5e0b4?hl=en&

For output and return parameters, you need to use an explicit Command
object. See the link I included in my first message. It contains a link to a
code generator I wrote for stored procedures with output and return
parameters.

Here's another:
http://groups.google.com/group/micro...97c2fa50f7fa1f

Bob Barrows
--
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 22 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Bob Murdoch | last post by:
I have an ASP application that calls a COM function to create a custom report as an Excel file. This works in a synchronous fashion, as long as the report does not take too long to create. If...
2
by: CJM | last post by:
I have page that starts a transaction and runs several StoredProcs before committing or rollingback. An initial SP create a header records, and then the code goes into a loop and runs 4 other SP's...
6
by: amitavabardhan | last post by:
How can I return an error code from a sql server stored procedure and use it in my asp page to generate an alert indicating that some error has ocurred?
1
by: Jen S | last post by:
I feel like I'm missing something obvious here, but I'm stumped... I have a stored procedure with code that looks like: INSERT INTO MyTableA ( ...fields... ) VALUES (...values...) IF...
0
by: Rhino | last post by:
I've written several Java stored procedures now (DB2 V7.2) and I'd like to write down a few "best practices" for reference so that I will have them handy for future development. Would the...
2
by: singlal | last post by:
Hi, my question was not getting any attention because it moved to 2nd page; so posting it again. Sorry for any inconvenience but I need to get it resolved fast. Need your help! ...
0
by: aravindalapat | last post by:
Hi All, I am facing an error when I try to invoke a remote nested stored proc. Please find the details below. 1) SP TEST1 is defined in DB2 instance DB2A. It is calling a stored procedure ...
9
by: Ratfish | last post by:
I'm getting a "2014:: Commands out of sync; you can't run this command now" error on a php page when I try to call a second stored procedure against a MySQL db. Does anyone know why I might be...
20
by: =?Utf-8?B?SnVzdGluIERvaA==?= | last post by:
"Active Server Pages error 'ASP 0113' Script timed out" I am still getting this error, and I am not sure how I could fix this. When I execute the query at SQL 2005, it only takes 8 seconds....
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.