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

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

:)
garethtnashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pateljituCommented:
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
garethtnashAuthor Commented:
Hello Pateljitu,

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

.. :(
0
pateljituCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Anthony PerkinsCommented:
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
garethtnashAuthor 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  
0
garethtnashAuthor 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

0
Anthony PerkinsCommented:
>>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
Anthony PerkinsCommented:
>>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
garethtnashAuthor Commented:
Eureke Mr ACP, Thank you...

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

:)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.