By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,187 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,187 IT Pros & Developers. It's quick & easy.

Error while calling stored procedure from asp page

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.