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

garethtnash
garethtnash used Ask the Experts™
on
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 --

:)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
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)

Author

Commented:
Hello Pateljitu,

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

.. :(
Top Expert 2011

Commented:
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>
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Top Expert 2012

Commented:
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

Author

Commented:
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  

Author

Commented:
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

Top Expert 2012

Commented:
>>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.

Top Expert 2012
Commented:
>>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()

Author

Commented:
Eureke Mr ACP, Thank you...

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

:)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial