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.
>>Development (Web and Desktop) >> Web Development >> ASP (Active Server Pages)

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

Article ID: 216 - Date Created Saturday, May 13, 2017 - This Article Has been Viewed 223 times.
- Written By:

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

CFF Knowledge Base - Share on MySpace 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)
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.