Error while calling stored procedure from asp page

Discussion in 'ASP General' started by Jack, Dec 22, 2005.

  1. Jack

    Jack Guest

    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.
    Jack, Dec 22, 2005
    #1
    1. Advertising

  2. 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=en&lr=&c2coff=1&selm=OVlfw#


    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.
    Bob Barrows [MVP], Dec 22, 2005
    #2
    1. Advertising

  3. Jack

    Jack Guest

    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=en&lr=&c2coff=1&selm=OVlfw#
    >
    >
    > 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.
    >
    >
    >
    Jack, Dec 22, 2005
    #3
  4. 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/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
    --
    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.
    Bob Barrows [MVP], Dec 22, 2005
    #4
  5. Jack

    Jack Guest

    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/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
    > --
    > 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.
    >
    >
    >
    Jack, Dec 22, 2005
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JN
    Replies:
    0
    Views:
    3,188
  2. =?Utf-8?B?TWlrZQ==?=
    Replies:
    0
    Views:
    748
    =?Utf-8?B?TWlrZQ==?=
    Jun 22, 2005
  3. Mike P
    Replies:
    0
    Views:
    3,295
    Mike P
    Jun 19, 2006
  4. ab
    Replies:
    1
    Views:
    394
    =?Utf-8?B?QXVndXN0aW4gUHJhc2FubmE=?=
    Aug 2, 2006
  5. anoop
    Replies:
    1
    Views:
    135
    Bob Barrows [MVP]
    May 29, 2007
Loading...

Share This Page