Error while calling stored procedure from asp page

J

Jack

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

Bob Barrows [MVP]

Jack said:
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>
<%
'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

'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=en&lr=&c2coff=1&selm=OVlfw#[email protected]


Bob Barrows
 
J

Jack

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

Bob Barrows [MVP]

Jack said:
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/microsoft.public.inetserver.asp.db/msg/c3f3f41af98eefa4?hl=en&

http://groups.google.com/group/micr...ocedure+barrows&rnum=4&hl=en#b997e62dab19d877

http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/041f359eb1c5e0b4?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/micr...cedure+barrows&rnum=10&hl=en#2d97c2fa50f7fa1f

Bob Barrows
 
J

Jack

Thanks a lot Bob for the references. I am going to through those and get
conceptual knowledge.
Best regards.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,902
Latest member
Elena68X5

Latest Threads

Top