CFF KB - Carrz-Fox-Fire Promotions Knowledge Base

 CFF KB is all about 1 thing: The Sharing of Knowledge and the Power we gain from it.
  • Breadrumbs:

  • Microsoft OLE DB Provider for SQL Server error '80004005' Syntax error or access violation

    • CFF Knowledge Base - Share With Facebook CFF Knowledge Base - Share on Twitter CFF Knowledge Base - Share on Reddit CFF Knowledge Base - Share on Digg It CFF Knowledge Base - Share on Stumble Upon It CFF Knowledge Base - Share on Delicious
    Share With Friends (Updated 6-8-2010)
  • Article ID:
    216
  • Date Created
    Saturday, May 13, 2017
  • This Article Has been Viewed
    1430 times
  • Short Desc
    Syntax error or access violation when running a SQL script in asp classic. When there is no Parameter provided.
  • Details


    You may receive the following message if you are providing a WHERE statement, without having the CreateParameter for the Variable to be used in your statement.

     

    Microsoft OLE DB Provider for SQL Server error '80004005'
    Syntax error or access violation
    /ask/add-ons/Bottom/View.asp, line 129
  • Recreate Issue
    To recreate the issue.

    <%
    Set sql = Server.CreateObject("ADODB.Command")
    sql.ActiveConnection=Conn
    sql.Prepared = true
    sql.commandtext="SELECT col1, col2 from table where col3=?"
    set rs1 = sql.execute
    %>


    In the above statement, we are asking for a value to be called when the statement is run. However, we have no Parameter to get our value from.
    This will cause the "Access violation"
  • Resolve Issue
    To resolve the issue.

    <%
    MyValue = request.querystring("Value")
    Set sql = Server.CreateObject("ADODB.Command")
    sql.ActiveConnection=Conn
    sql.Prepared = true
    sql.commandtext="SELECT col1, col2 from table where col3=?"
    sql.Parameters.Append sql.CreateParameter("@col3", adInteger, adParamInput, , MyValue)
    set rs1 = sql.execute
    %>


    In the above example, we have added two extra parts to our code.
    #1: MyValue
    This request will grab the query from the URL to be used against our statement.

    #2:
    sql.CreateParameter
    Here we are able to grab the MyValue from #1 above and use it against our select statement.

Recent Articles

All Topics

  • Coming Soon - Knowledge Exchange

Trending Articles