?
Solved

Microsoft OLE DB Provider for SQL Server error '80040e14' - Syntax error or access violation

Posted on 2011-10-05
9
Medium Priority
?
2,009 Views
Last Modified: 2012-05-12
Help -

I'm getting the following error -

"Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation
/direct/includes/product-update.asp, line 300"

When trying to run the following -

Dim CMDUpdateMainProduct__productid
CMDUpdateMainProduct__productid = "0"
if(UploadFormRequest("ID") <> "") then CMDUpdateMainProduct__productid = UploadFormRequest("ID")

Dim CMDUpdateMainProduct__Image
CMDUpdateMainProduct__Image = ""
if(UploadFormRequest("image1") <> "") then CMDUpdateMainProduct__Image = UploadFormRequest("image1")


set CMDUpdateMainProduct = Server.CreateObject("ADODB.Command")
CMDUpdateMainProduct.ActiveConnection = MM_Connection_STRING
CMDUpdateMainProduct.CommandText = "dbo.AdminProduct-UpdateMainProductImage"
CMDUpdateMainProduct.CommandType = 4
CMDUpdateMainProduct.CommandTimeout = 0
CMDUpdateMainProduct.Prepared = true
CMDUpdateMainProduct.Parameters.Append CMDUpdateMainProduct.CreateParameter("@RETURN_VALUE", 3, 4)
CMDUpdateMainProduct.Parameters.Append CMDUpdateMainProduct.CreateParameter("@productid", 3, 1,8,CMDUpdateMainProduct__productid)
CMDUpdateMainProduct.Parameters.Append CMDUpdateMainProduct.CreateParameter("@Image", 200, 1,50,CMDUpdateMainProduct__Image)
CMDUpdateMainProduct.Execute()

Open in new window


300 is the CMD Execute line above


dbo.AdminProduct-UpdateMainProductImage

is -

CREATE PROCEDURE [dbo].[AdminProduct-UpdateMainProductImage]
(
@productid int,
@Image Nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
update dbo.[Direct-Product-Image]
Set Image = @Image
Where ProductID = @productid AND [Default-Image] = 'Y'
END

GO

Open in new window



Thanks in advance --

:)
0
Comment
Question by:garethtnash
  • 4
  • 3
  • 2
9 Comments
 
LVL 15

Expert Comment

by:pateljitu
ID: 36918337
Please comment this line in your code, as your StoreProcedure accepts only 2 parameters and there is no return value defined:

CMDUpdateMainProduct.Parameters.Append CMDUpdateMainProduct.CreateParameter("@RETURN_VALUE", 3, 4)
0
 

Author Comment

by:garethtnash
ID: 36918439
Hello Pateljitu,

Did that - still get the same error message... deleted the line error message changes to line 299

.. :(
0
 
LVL 15

Expert Comment

by:pateljitu
ID: 36918547
Can you try these couple of changes:
1.
set CMDUpdateMainProduct = Server.CreateObject("ADODB.Command")
CMDUpdateMainProduct.ActiveConnection = MM_Connection_STRING
CMDUpdateMainProduct.CommandText = "dbo.AdminProduct-UpdateMainProductImage"
CMDUpdateMainProduct.CommandType = 4
CMDUpdateMainProduct.CommandTimeout = 0
CMDUpdateMainProduct.Prepared = true
CMDUpdateMainProduct.Parameters.Append
CMDUpdateMainProduct.Parameters.Append CMDUpdateMainProduct.CreateParameter("@productid", 3, 1, 4,CMDUpdateMainProduct__productid)
CMDUpdateMainProduct.Parameters.Append CMDUpdateMainProduct.CreateParameter("@Image", 200, 1,50,CMDUpdateMainProduct__Image)
CMDUpdateMainProduct.Execute()

2. try to execute this storeprocedure directly from SQL, if possible
exec [AdminProduct-UpdateMainProductImage] <replace_with_productid>, <replace_with_valid_image>
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36919746
I suspect you need to make the following changes:
Dim CMDUpdateMainProduct__productid
CMDUpdateMainProduct__productid = "0"

If UploadFormRequest("ID") <> "" Then 
	CMDUpdateMainProduct__productid = UploadFormRequest("ID")

Dim CMDUpdateMainProduct__Image
CMDUpdateMainProduct__Image = ""
If UploadFormRequest("image1") <> "" Then 
	CMDUpdateMainProduct__Image = UploadFormRequest("image1")


set CMDUpdateMainProduct = Server.CreateObject("ADODB.Command")
CMDUpdateMainProduct.ActiveConnection = MM_Connection_STRING
CMDUpdateMainProduct.CommandText = "dbo.[AdminProduct-UpdateMainProductImage]"
CMDUpdateMainProduct.CommandType = 4
CMDUpdateMainProduct.CommandTimeout = 0
CMDUpdateMainProduct.Prepared = True
CMDUpdateMainProduct.Parameters.Append CMDUpdateMainProduct.CreateParameter("@RETURN_VALUE", 3, 4)
CMDUpdateMainProduct.Parameters.Append CMDUpdateMainProduct.CreateParameter("@productid", 3, 1, 4, CMDUpdateMainProduct__productid)
CMDUpdateMainProduct.Parameters.Append CMDUpdateMainProduct.CreateParameter("@Image", 202, 1, 50, CMDUpdateMainProduct__Image)
CMDUpdateMainProduct.Execute()

Open in new window

0
 

Author Comment

by:garethtnash
ID: 36923419
Morning Chaps,

Right -

Firstly I isolated this SP and have renamed it -

<!--#include file="../Connections/Connection.asp" -->
<%
Dim CMDMainImageUpdate__productid
CMDMainImageUpdate__productid = "0"
if(Request("ID") <> "") then CMDMainImageUpdate__productid = Request("ID")

Dim CMDMainImageUpdate__Image
CMDMainImageUpdate__Image = "0"
if(Request("image1") <> "") then CMDMainImageUpdate__Image = Request("image1")



set CMDMainImageUpdate = Server.CreateObject("ADODB.Command")
CMDMainImageUpdate.ActiveConnection = MM_Connection_STRING
CMDMainImageUpdate.CommandText = "dbo.AdminProduct-UpdateMainProductImage"
CMDMainImageUpdate.CommandType = 4
CMDMainImageUpdate.CommandTimeout = 0
CMDMainImageUpdate.Prepared = true
CMDMainImageUpdate.Parameters.Append
CMDMainImageUpdate.Parameters.Append CMDMainImageUpdate.CreateParameter("@productid", 3, 1,4,CMDMainImageUpdate__productid)
CMDMainImageUpdate.Parameters.Append CMDMainImageUpdate.CreateParameter("@Image", 200, 1,150,CMDMainImageUpdate__Image)
CMDMainImageUpdate.Execute()

Response.Redirect("default.asp")
%>

Open in new window


Pateljitu - tried first suggestion - which I think was change --

CMDMainImageUpdate.Parameters.Append CMDMainImageUpdate.CreateParameter("@RETURN_VALUE", 3, 4)

to

CMDMainImageUpdate.Parameters.Append

This returned -

"Microsoft VBScript runtime error '800a01c2'
Wrong number of arguments or invalid property assignment: 'CMDMainImageUpdate.Parameters.Append'
/direct/test6.asp, line 19"

Secondly, I ran the SP on the SQL server - it runs fine..


ACPerkins..

Changed the script to --

<!--#include file="../Connections/Connection.asp" -->
<%
Dim CMDMainImageUpdate__productid
CMDMainImageUpdate__productid = "0"

If Request("ID") <> "" Then 
	CMDMainImageUpdate__productid = Request("ID")

Dim CMDMainImageUpdate__Image
CMDMainImageUpdate__Image = ""
If Request("image1") <> "" Then 
	CMDMainImageUpdate__Image = Request("image1")


set CMDMainImageUpdate = Server.CreateObject("ADODB.Command")
CMDMainImageUpdate.ActiveConnection = MM_Connection_STRING
CMDMainImageUpdate.CommandText = "dbo.[AdminProduct-UpdateMainProductImage]"
CMDMainImageUpdate.CommandType = 4
CMDMainImageUpdate.CommandTimeout = 0
CMDMainImageUpdate.Prepared = True
CMDMainImageUpdate.Parameters.Append CMDMainImageUpdate.CreateParameter("@RETURN_VALUE", 3, 4)
CMDMainImageUpdate.Parameters.Append CMDMainImageUpdate.CreateParameter("@productid", 3, 1, 4, CMDMainImageUpdate__productid)
CMDMainImageUpdate.Parameters.Append CMDMainImageUpdate.CreateParameter("@Image", 202, 1, 50, CMDMainImageUpdate__Image)
CMDMainImageUpdate.Execute()

Response.Redirect("default.asp")
%>

Open in new window


Which returns -

"Microsoft VBScript compilation error '800a03f6'
Expected 'End'
/direct/test6.asp, line 26"

So I changed it too

<%
Dim CMDMainImageUpdate__productid
CMDMainImageUpdate__productid = "0"

If Request("ID") <> "" Then CMDMainImageUpdate__productid = Request("ID")

Dim CMDMainImageUpdate__Image
CMDMainImageUpdate__Image = ""
If Request("image1") <> "" Then CMDMainImageUpdate__Image = Request("image1")


set CMDMainImageUpdate = Server.CreateObject("ADODB.Command")
CMDMainImageUpdate.ActiveConnection = MM_Connection_STRING
CMDMainImageUpdate.CommandText = "dbo.[AdminProduct-UpdateMainProductImage]"
CMDMainImageUpdate.CommandType = 4
CMDMainImageUpdate.CommandTimeout = 0
CMDMainImageUpdate.Prepared = True
CMDMainImageUpdate.Parameters.Append CMDMainImageUpdate.CreateParameter("@RETURN_VALUE", 3, 4)
CMDMainImageUpdate.Parameters.Append CMDMainImageUpdate.CreateParameter("@productid", 3, 1, 4, CMDMainImageUpdate__productid)
CMDMainImageUpdate.Parameters.Append CMDMainImageUpdate.CreateParameter("@Image", 202, 1, 50, CMDMainImageUpdate__Image)
CMDMainImageUpdate.Execute()


%>

Open in new window


Which seems to have done the job... I think...

Can I ask what you changed - as I can't see the change? And now I have the same issue on the next SP  --

"Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation
/direct/includes/product-update.asp, line 368"

Where the SP Code is ---

Dim CMDUpdateAddImages__productid
CMDUpdateAddImages__productid = "0"
if(UploadFormRequest("productid") <> "") then CMDUpdateAddImages__productid = UploadFormRequest("ID")

Dim CMDUpdateAddImages__Image2
CMDUpdateAddImages__Image2 = ""
if(UploadFormRequest("Image2") <> "") then CMDUpdateAddImages__Image2 = UploadFormRequest("Image2")

Dim CMDUpdateAddImages__Image3
CMDUpdateAddImages__Image3 = ""
if(UploadFormRequest("Image3") <> "") then CMDUpdateAddImages__Image3 = UploadFormRequest("Image3")

Dim CMDUpdateAddImages__Image4
CMDUpdateAddImages__Image4 = ""
if(UploadFormRequest("Image4") <> "") then CMDUpdateAddImages__Image4 = UploadFormRequest("Image4")

Dim CMDUpdateAddImages__Image5
CMDUpdateAddImages__Image5 = ""
if(UploadFormRequest("Image5") <> "") then CMDUpdateAddImages__Image5 = UploadFormRequest("Image5")

Dim CMDUpdateAddImages__Image6
CMDUpdateAddImages__Image6 = ""
if(UploadFormRequest("Image6") <> "") then CMDUpdateAddImages__Image6 = UploadFormRequest("Image6")

Dim CMDUpdateAddImages__OLDImage2
CMDUpdateAddImages__OLDImage2 = ""
if(Session("OLDImage2") <> "") then CMDUpdateAddImages__OLDImage2 = Session("OLDImage2")

Dim CMDUpdateAddImages__OLDImage3
CMDUpdateAddImages__OLDImage3 = ""
if(Session("OLDImage3") <> "") then CMDUpdateAddImages__OLDImage3 = Session("OLDImage3")

Dim CMDUpdateAddImages__OLDImage4
CMDUpdateAddImages__OLDImage4 = ""
if(Session("OLDImage4") <> "") then CMDUpdateAddImages__OLDImage4 = Session("OLDImage4")

Dim CMDUpdateAddImages__OLDImage5
CMDUpdateAddImages__OLDImage5 = ""
if(Session("OLDImage5") <> "") then CMDUpdateAddImages__OLDImage5 = Session("OLDImage5")

Dim CMDUpdateAddImages__OLDImage6
CMDUpdateAddImages__OLDImage6 = ""
if(Session("OLDImage6") <> "") then CMDUpdateAddImages__OLDImage6 = Session("OLDImage6")


set CMDUpdateAddImages = Server.CreateObject("ADODB.Command")
CMDUpdateAddImages.ActiveConnection = MM_Connection_STRING
CMDUpdateAddImages.CommandText = "dbo.AdminProduct-UpdateAdditionalProductImages"
CMDUpdateAddImages.CommandType = 4
CMDUpdateAddImages.CommandTimeout = 0
CMDUpdateAddImages.Prepared = true
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@RETURN_VALUE", 3, 4)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@productid", 3, 1,8,CMDUpdateAddImages__productid)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@Image2", 200, 1,50,CMDUpdateAddImages__Image2)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@Image3", 200, 1,50,CMDUpdateAddImages__Image3)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@Image4", 200, 1,50,CMDUpdateAddImages__Image4)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@Image5", 200, 1,50,CMDUpdateAddImages__Image5)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@Image6", 200, 1,50,CMDUpdateAddImages__Image6)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@OLDImage2", 200, 1,50,CMDUpdateAddImages__OLDImage2)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@OLDImage3", 200, 1,50,CMDUpdateAddImages__OLDImage3)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@OLDImage4", 200, 1,50,CMDUpdateAddImages__OLDImage4)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@OLDImage5", 200, 1,50,CMDUpdateAddImages__OLDImage5)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@OLDImage6", 200, 1,50,CMDUpdateAddImages__OLDImage6)
CMDUpdateAddImages.Execute()

Open in new window


Thanks  
0
 

Author Comment

by:garethtnash
ID: 36923482
I've updated the SP so that it reads (as below)

But I'm still getting --

"Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation
/direct/includes/product-update.asp, line 368"

I must have missed the most important change?

Thanks


Dim CMDUpdateAddImages__productid
CMDUpdateAddImages__productid = "0"
if UploadFormRequest("productid") <> "" then CMDUpdateAddImages__productid = UploadFormRequest("ID")

Dim CMDUpdateAddImages__Image2
CMDUpdateAddImages__Image2 = ""
if UploadFormRequest("Image2") <> "" then CMDUpdateAddImages__Image2 = UploadFormRequest("Image2")

Dim CMDUpdateAddImages__Image3
CMDUpdateAddImages__Image3 = ""
if UploadFormRequest("Image3") <> "" then CMDUpdateAddImages__Image3 = UploadFormRequest("Image3")

Dim CMDUpdateAddImages__Image4
CMDUpdateAddImages__Image4 = ""
if UploadFormRequest("Image4") <> "" then CMDUpdateAddImages__Image4 = UploadFormRequest("Image4")

Dim CMDUpdateAddImages__Image5
CMDUpdateAddImages__Image5 = ""
if UploadFormRequest("Image5") <> "" then CMDUpdateAddImages__Image5 = UploadFormRequest("Image5")

Dim CMDUpdateAddImages__Image6
CMDUpdateAddImages__Image6 = ""
if UploadFormRequest("Image6") <> "" then CMDUpdateAddImages__Image6 = UploadFormRequest("Image6")

Dim CMDUpdateAddImages__OLDImage2
CMDUpdateAddImages__OLDImage2 = ""
if Session("OLDImage2") <> "" then CMDUpdateAddImages__OLDImage2 = Session("OLDImage2")

Dim CMDUpdateAddImages__OLDImage3
CMDUpdateAddImages__OLDImage3 = ""
if Session("OLDImage3") <> "" then CMDUpdateAddImages__OLDImage3 = Session("OLDImage3")

Dim CMDUpdateAddImages__OLDImage4
CMDUpdateAddImages__OLDImage4 = ""
if Session("OLDImage4") <> "" then CMDUpdateAddImages__OLDImage4 = Session("OLDImage4")

Dim CMDUpdateAddImages__OLDImage5
CMDUpdateAddImages__OLDImage5 = ""
if Session("OLDImage5") <> "" then CMDUpdateAddImages__OLDImage5 = Session("OLDImage5")

Dim CMDUpdateAddImages__OLDImage6
CMDUpdateAddImages__OLDImage6 = ""
if Session("OLDImage6") <> "" then CMDUpdateAddImages__OLDImage6 = Session("OLDImage6")


set CMDUpdateAddImages = Server.CreateObject("ADODB.Command")
CMDUpdateAddImages.ActiveConnection = MM_Connection_STRING
CMDUpdateAddImages.CommandText = "dbo.AdminProduct-UpdateAdditionalProductImages"
CMDUpdateAddImages.CommandType = 4
CMDUpdateAddImages.CommandTimeout = 0
CMDUpdateAddImages.Prepared = true
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@RETURN_VALUE", 3, 4)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@productid", 3, 1,4,CMDUpdateAddImages__productid)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@Image2", 202, 1,150,CMDUpdateAddImages__Image2)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@Image3", 202, 1,150,CMDUpdateAddImages__Image3)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@Image4", 202, 1,150,CMDUpdateAddImages__Image4)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@Image5", 202, 1,150,CMDUpdateAddImages__Image5)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@Image6", 202, 1,150,CMDUpdateAddImages__Image6)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@OLDImage2", 202, 1,150,CMDUpdateAddImages__OLDImage2)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@OLDImage3", 202, 1,150,CMDUpdateAddImages__OLDImage3)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@OLDImage4", 202, 1,150,CMDUpdateAddImages__OLDImage4)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@OLDImage5", 202, 1,150,CMDUpdateAddImages__OLDImage5)
CMDUpdateAddImages.Parameters.Append CMDUpdateAddImages.CreateParameter("@OLDImage6", 202, 1,150,CMDUpdateAddImages__OLDImage6)
CMDUpdateAddImages.Execute()

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36923907
>>Expected 'End' <<
You are right I had omitted the End If

Please post the contents of the new Stored Procedure, showing all the parameters.

Incidentally, there is no harm in including the RETURN parameter.  Regardless of whether you have one or not, it is always implied.

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 36923935
>>Can I ask what you changed - as I can't see the change?<<
1. Since you are using a dash in your Stored Procedure name you have to enclose it in square brackets.
2. Integers are 4 bytes and not 8 (that is a bigint).  But truth be told you could have used 0 as it is ignored.  I just wanted you to understand the difference.
3. You are using nvarchar (not sure why, but that is another story) and not varchar, so it is 202 and not 200.

set CMDUpdateMainProduct = Server.CreateObject("ADODB.Command")
CMDUpdateMainProduct.ActiveConnection = MM_Connection_STRING
CMDUpdateMainProduct.CommandText = "dbo.[AdminProduct-UpdateMainProductImage]"
CMDUpdateMainProduct.CommandType = 4
CMDUpdateMainProduct.CommandTimeout = 0
CMDUpdateMainProduct.Prepared = True
CMDUpdateMainProduct.Parameters.Append CMDUpdateMainProduct.CreateParameter("@RETURN_VALUE", 3, 4)
CMDUpdateMainProduct.Parameters.Append CMDUpdateMainProduct.CreateParameter("@productid", 3, 1, 4, CMDUpdateMainProduct__productid)
CMDUpdateMainProduct.Parameters.Append CMDUpdateMainProduct.CreateParameter("@Image", 202, 1, 50, CMDUpdateMainProduct__Image)
CMDUpdateMainProduct.Execute()
0
 

Author Closing Comment

by:garethtnash
ID: 36924481
Eureke Mr ACP, Thank you...

Square Brakets was the problem, but thank you for all the help and advise...

:)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question