Solved

2 ASP / MS SQL Stored Procedure Issues

Posted on 2011-09-07
26
281 Views
Last Modified: 2012-05-12
Hello Experts,

I'm experiencing some difficulty with a Stored Procedure and the ASP VBScript call to it...

My stored procedure is called by a Form Post (but so that you can see the data being sent I've used GET here)

The first part inserts data into 2 tables, gets the Unique ID for the record inserted and then inserts more records.

Part of this (MS SQL Procedure looks like -)

IF @image1 IS NOT NULL
insert into dbo.[Direct-Product-Image](ProductID, [Default-Image], [Image])
values(@productid, 'Y', @image1)

IF @image2 IS NOT NULL
insert into dbo.[Direct-Product-Image](ProductID, [Default-Image], [Image])
values(@productid, 'N', @image2)

IF @image3 IS NOT NULL
insert into dbo.[Direct-Product-Image](ProductID, [Default-Image], [Image])
values(@productid, 'N', @image3)

IF @image4 IS NOT NULL
insert into dbo.[Direct-Product-Image](ProductID, [Default-Image], [Image])
values(@productid, 'N', @image4)

IF @image5 IS NOT NULL
insert into dbo.[Direct-Product-Image](ProductID, [Default-Image], [Image])
values(@productid, 'N', @image5)

IF @image6 IS NOT NULL
insert into dbo.[Direct-Product-Image](ProductID, [Default-Image], [Image])
values(@productid, 'N', @image6)

Open in new window


So IF No Image - ABORT INSERT --

My data for this section looks like --

&image1=test+upload%271.jpg
&image2=
&image3=
&image4=
&image5=
&image6=


No data in Images 2-6, Yet I'm getting a record inserted, I've tried doing a cleanup, by adding -

Delete From dbo.[Direct-Product-Image] where [image] = ''

But still the records are there........... :(

OK on to the second question...

My insert created records in multiple tables, including a related products table and a categories table, where the user can select multiple of both, and when the form is submitted, it creates a new record for each selection...

Here my data looks like this -

&related=16
&related=17
&related=18
&related=19
&category=2
&category=4


My ASP VBscript call to the SP reads like so -

myarray = split(UploadFormRequest("category"))

if isArray(myarray) then   ' here you're checking it's an array so if not, it doesn't throw an error
for i = 0 to uBound(myarray,1)

Dim CMDProductCategories__productid
CMDProductCategories__productid = "0"
if(Session("NEWPRODUCTID") <> "") then CMDProductCategories__productid = Session("NEWPRODUCTID")

Dim CMDProductCategories__categoryid
CMDProductCategories__categoryid = "0"
if(myarray(i) <> "") then CMDProductCategories__categoryid = myarray(i)


set CMDProductCategories = Server.CreateObject("ADODB.Command")
CMDProductCategories.ActiveConnection = MM_GolfConnection_STRING
CMDProductCategories.CommandText = "dbo.AdminAddProductToCategories"
CMDProductCategories.CommandType = 4
CMDProductCategories.CommandTimeout = 0
CMDProductCategories.Prepared = true
CMDProductCategories.Parameters.Append CMDProductCategories.CreateParameter("@RETURN_VALUE", 3, 4)
CMDProductCategories.Parameters.Append CMDProductCategories.CreateParameter("@productid", 3, 1,8,CMDProductCategories__productid)
CMDProductCategories.Parameters.Append CMDProductCategories.CreateParameter("@categoryid", 3, 1,8,CMDProductCategories__categoryid)
CMDProductCategories.Execute()

next
End if


relatedarray = split(UploadFormRequest("related"))

if isArray(relatedarray) then   ' here you're checking it's an array so if not, it doesn't throw an error
for i = 0 to uBound(relatedarray,1)

Dim CMDRelatedProducts__productid
CMDRelatedProducts__productid = "0"
if(Session("NEWPRODUCTID") <> "") then CMDRelatedProducts__productid = Session("NEWPRODUCTID")

Dim CMDRelatedProducts__relatedproductid
CMDRelatedProducts__relatedproductid = "0"
if(relatedarray(i) <> "") then CMDRelatedProducts__relatedproductid = relatedarray(i)



set CMDRelatedProducts = Server.CreateObject("ADODB.Command")
CMDRelatedProducts.ActiveConnection = MM_GolfConnection_STRING
CMDRelatedProducts.CommandText = "dbo.AdminAddProductToRelatedProducts"
CMDRelatedProducts.CommandType = 4
CMDRelatedProducts.CommandTimeout = 0
CMDRelatedProducts.Prepared = true
CMDRelatedProducts.Parameters.Append CMDRelatedProducts.CreateParameter("@RETURN_VALUE", 3, 4)
CMDRelatedProducts.Parameters.Append CMDRelatedProducts.CreateParameter("@productid", 3, 1,8,CMDRelatedProducts__productid)
CMDRelatedProducts.Parameters.Append CMDRelatedProducts.CreateParameter("@relatedproductid", 3, 1,8,CMDRelatedProducts__relatedproductid)
CMDRelatedProducts.Execute()

next
End if

Open in new window


The associated stored procedures are -

CREATE PROCEDURE [dbo].[AdminAddProductToCategories]
(
@productid int,
@categoryid int
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.[Direct-Product-Category]([Product-ID], [Category-ID])
Values(@productid, @categoryid)
END

GO

Open in new window


AND

CREATE PROCEDURE [dbo].[AdminAddProductToRelatedProducts]
(
@productid int,
@relatedproductid int
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.[Direct-Related-Products](ProductID, RelatedProductID)
Values(@productid, @relatedproductid)
END

GO

Open in new window


However instead of getting a new record for each category / related product I got -

Category;

ID      Category-ID      Product-ID
6      24      30

Should have been 1 record with a Category-ID and a second with an ID of 4

and Related I got -

ID      ProductID      RelatedProductID
1      30      161718

Should have been 16 & 17 & 18 (all different records)

Please help, let me know if you need any more of my code -

Thank you
0
Comment
Question by:garethtnash
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 13
26 Comments
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36495303
OK,

lets deal with this a question at a time.

First point, try using

IF LEN(@Image1) > 1 THEN ...

or maybe

IF @Image <> ""

and see if that helps.

Mark.
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36495308
Also, for the clean up try:

Delete From dbo.[Direct-Product-Image] where [image] IS NULL

and see if that works
0
 

Author Comment

by:garethtnash
ID: 36495402
Question 1 - Sorted - thank you...

Now the harder stuff :) Thanks Mark
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36495417
Right,

Can we run some debug code in you main section.  So would it be possible to either write out the contents of myarray to screen after you have created it.

you may be able to use something like:

<%
myarray = split(UploadFormRequest("category"))

if isArray(myarray) then   ' here you're checking it's an array so if not, it doesn't throw an error

For Each item In myarray
      Response.Write(item & "<br />")
Next
%>

and then either let the rest of your code run or put a break in to stop it.  This way we can be sure the array contains the values you think it should.
0
 

Author Comment

by:garethtnash
ID: 36495450
Thanks Mark, how do I put a break in to stop it?
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36495465
just put something in that will stop the code running,

I normally use

response.stop
0
 

Author Comment

by:garethtnash
ID: 36495557
I've commented my code out -

<%If UploadFormRequest("faction") = "finsert" AND UploadFormRequest("name") <> "" then

Function ProtectSQL(SQLString)
SQLString = Replace(SQLString, "'", "''")
SQLString = Replace(SQLString, vblf,"<br />")
SQLString = Replace(SQLString, "(","&#40;")
SQLString = Replace(SQLString, ")","&#41;")
SQLString = Trim(SQLString)
ProtectSQL = SQLString
End Function


Dim CMDProductInsert__sku
CMDProductInsert__sku = "0"
if(UploadFormRequest("sku") <> "") then CMDProductInsert__sku = Server.HTMLEncode(ProtectSQL(UploadFormRequest("sku")))

Dim CMDProductInsert__nominal
CMDProductInsert__nominal = "0"
if(UploadFormRequest("nominal") <> "") then CMDProductInsert__nominal = Server.HTMLEncode(ProtectSQL(UploadFormRequest("nominal")))

Dim CMDProductInsert__allocation
CMDProductInsert__allocation = "0"
if(UploadFormRequest("allocation") <> "") then CMDProductInsert__allocation = Server.HTMLEncode(ProtectSQL(UploadFormRequest("allocation")))

Dim CMDProductInsert__owner
CMDProductInsert__owner = "0"
if(UploadFormRequest("owner") <> "") then CMDProductInsert__owner = Server.HTMLEncode(ProtectSQL(UploadFormRequest("owner")))

Dim CMDProductInsert__name
CMDProductInsert__name = ""
if(UploadFormRequest("name") <> "") then CMDProductInsert__name = Server.HTMLEncode(ProtectSQL(UploadFormRequest("name")))

Dim CMDProductInsert__description
CMDProductInsert__description = ""
if(UploadFormRequest("description") <> "") then CMDProductInsert__description = Server.HTMLEncode(ProtectSQL(UploadFormRequest("description")))

Dim CMDProductInsert__stockcount
CMDProductInsert__stockcount = "0"
if(UploadFormRequest("stockcount") <> "") then CMDProductInsert__stockcount = Server.HTMLEncode(ProtectSQL(UploadFormRequest("stockcount")))

Dim CMDProductInsert__restocklevel
CMDProductInsert__restocklevel = "0"
if(UploadFormRequest("restocklevel") <> "") then CMDProductInsert__restocklevel = Server.HTMLEncode(ProtectSQL(UploadFormRequest("restocklevel")))

Dim CMDProductInsert__displaytgi
CMDProductInsert__displaytgi = "N"
if(UploadFormRequest("displaytgi") <> "") then CMDProductInsert__displaytgi = Server.HTMLEncode(ProtectSQL(UploadFormRequest("displaytgi")))

Dim CMDProductInsert__displaypro
CMDProductInsert__displaypro = "N"
if(UploadFormRequest("displaypro") <> "") then CMDProductInsert__displaypro = Server.HTMLEncode(ProtectSQL(UploadFormRequest("displaypro")))

Dim CMDProductInsert__availabledate
CMDProductInsert__availabledate = DateAdd("d", -1, Now())
if(UploadFormRequest("availabledate") <> "") then CMDProductInsert__availabledate = Server.HTMLEncode(ProtectSQL(UploadFormRequest("availabledate")))

Dim CMDProductInsert__specialtgi
CMDProductInsert__specialtgi = "N"
if(UploadFormRequest("specialtgi") <> "") then CMDProductInsert__specialtgi = Server.HTMLEncode(ProtectSQL(UploadFormRequest("specialtgi")))

Dim CMDProductInsert__specialpro
CMDProductInsert__specialpro = "N"
if(UploadFormRequest("specialpro") <> "") then CMDProductInsert__specialpro = Server.HTMLEncode(ProtectSQL(UploadFormRequest("specialpro")))

Dim CMDProductInsert__specialdescriptiontgi
CMDProductInsert__specialdescriptiontgi = ""
if(UploadFormRequest("specialdescriptiontgi") <> "") then CMDProductInsert__specialdescriptiontgi = Server.HTMLEncode(ProtectSQL(UploadFormRequest("specialdescriptiontgi")))

Dim CMDProductInsert__specialdescriptionpro
CMDProductInsert__specialdescriptionpro = ""
if(UploadFormRequest("specialdescriptionpro") <> "") then CMDProductInsert__specialdescriptionpro = Server.HTMLEncode(ProtectSQL(UploadFormRequest("specialdescriptionpro")))

Dim CMDProductInsert__displayspecialdesctgi
CMDProductInsert__displayspecialdesctgi = "N"
if(UploadFormRequest("displayspecialdesctgi") <> "") then CMDProductInsert__displayspecialdesctgi = Server.HTMLEncode(ProtectSQL(UploadFormRequest("displayspecialdesctgi")))

Dim CMDProductInsert__displayspecialdescpro
CMDProductInsert__displayspecialdescpro = "N"
if(UploadFormRequest("displayspecialdescpro") <> "") then CMDProductInsert__displayspecialdescpro = Server.HTMLEncode(ProtectSQL(UploadFormRequest("displayspecialdescpro")))

Dim CMDProductInsert__displayspecialpricetgi
CMDProductInsert__displayspecialpricetgi = "N"
if(UploadFormRequest("displayspecialpricetgi") <> "") then CMDProductInsert__displayspecialpricetgi = Server.HTMLEncode(ProtectSQL(UploadFormRequest("displayspecialpricetgi")))

Dim CMDProductInsert__displayspecialpricepro
CMDProductInsert__displayspecialpricepro = "N"
if(UploadFormRequest("displayspecialpricepro") <> "") then CMDProductInsert__displayspecialpricepro = Server.HTMLEncode(ProtectSQL(UploadFormRequest("displayspecialpricepro")))

Dim CMDProductInsert__tgicrriageinc
CMDProductInsert__tgicrriageinc = "N"
if(UploadFormRequest("tgicrriageinc") <> "") then CMDProductInsert__tgicrriageinc = Server.HTMLEncode(ProtectSQL(UploadFormRequest("tgicrriageinc")))

Dim CMDProductInsert__tgicarriagerate
CMDProductInsert__tgicarriagerate = 0.00
if(UploadFormRequest("tgicarriagerate") <> "") then CMDProductInsert__tgicarriagerate = Server.HTMLEncode(ProtectSQL(UploadFormRequest("tgicarriagerate")))

Dim CMDProductInsert__procarriageinc
CMDProductInsert__procarriageinc = "N"
if(UploadFormRequest("procrriageinc") <> "") then CMDProductInsert__procarriageinc = Server.HTMLEncode(ProtectSQL(UploadFormRequest("procrriageinc")))

Dim CMDProductInsert__procarriagerate
CMDProductInsert__procarriagerate = 0.00
if(UploadFormRequest("procarriagerate") <> "") then CMDProductInsert__procarriagerate = Server.HTMLEncode(ProtectSQL(UploadFormRequest("procarriagerate")))

Dim CMDProductInsert__ssp
CMDProductInsert__ssp = 0.00
if(UploadFormRequest("ssp") <> "") then CMDProductInsert__ssp = Server.HTMLEncode(ProtectSQL(UploadFormRequest("ssp")))

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

Dim CMDProductInsert__image2
CMDProductInsert__image2 = ""
if(UploadFormRequest("image2") <> "") then CMDProductInsert__image2 = UploadFormRequest("image2")

Dim CMDProductInsert__image3
CMDProductInsert__image3 = ""
if(UploadFormRequest("image3") <> "") then CMDProductInsert__image3 = UploadFormRequest("image3")

Dim CMDProductInsert__image4
CMDProductInsert__image4 = ""
if(UploadFormRequest("image4") <> "") then CMDProductInsert__image4 = UploadFormRequest("image4")

Dim CMDProductInsert__image5
CMDProductInsert__image5 = ""
if(UploadFormRequest("image5") <> "") then CMDProductInsert__image5 = UploadFormRequest("image5")

Dim CMDProductInsert__image6
CMDProductInsert__image6 = ""
if(UploadFormRequest("image6") <> "") then CMDProductInsert__image6 = UploadFormRequest("image6")

Dim CMDProductInsert__normaltgiprice
CMDProductInsert__normaltgiprice = 0.00
if(UploadFormRequest("normaltgiprice") <> "") then CMDProductInsert__normaltgiprice = Server.HTMLEncode(ProtectSQL(UploadFormRequest("normaltgiprice")))

Dim CMDProductInsert__saletgiprice
CMDProductInsert__saletgiprice = 0.00
if(UploadFormRequest("saletgiprice") <> "") then CMDProductInsert__saletgiprice = Server.HTMLEncode(ProtectSQL(UploadFormRequest("saletgiprice")))

Dim CMDProductInsert__normalproprice
CMDProductInsert__normalproprice = 0.00
if(UploadFormRequest("normalproprice") <> "") then CMDProductInsert__normalproprice = Server.HTMLEncode(ProtectSQL(UploadFormRequest("normalproprice")))

Dim CMDProductInsert__saleproprice
CMDProductInsert__saleproprice = 0.00
if(UploadFormRequest("saleproprice") <> "") then CMDProductInsert__saleproprice = Server.HTMLEncode(ProtectSQL(UploadFormRequest("saleproprice")))


set CMDProductInsert = Server.CreateObject("ADODB.Command")
CMDProductInsert.ActiveConnection = MM_GolfConnection_STRING
CMDProductInsert.CommandText = "dbo.AdminNewDirectProduct"
CMDProductInsert.CommandType = 4
CMDProductInsert.CommandTimeout = 0
CMDProductInsert.Prepared = true
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@RETURN_VALUE", 3, 4)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@sku", 200, 1,8,CMDProductInsert__sku)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@nominal", 3, 1,8,CMDProductInsert__nominal)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@allocation", 3, 1,8,CMDProductInsert__allocation)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@owner", 3, 1,8,CMDProductInsert__owner)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@name", 200, 1,100,CMDProductInsert__name)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@description", 200, 1,5000,CMDProductInsert__description)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@stockcount", 3, 1,8,CMDProductInsert__stockcount)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@restocklevel", 3, 1,8,CMDProductInsert__restocklevel)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@displaytgi", 129, 1,1,CMDProductInsert__displaytgi)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@displaypro", 129, 1,1,CMDProductInsert__displaypro)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@availabledate", 200, 1,50,CMDProductInsert__availabledate)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@specialtgi", 129, 1,1,CMDProductInsert__specialtgi)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@specialpro", 129, 1,1,CMDProductInsert__specialpro)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@specialdescriptiontgi", 200, 1,2000,CMDProductInsert__specialdescriptiontgi)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@specialdescriptionpro", 200, 1,2000,CMDProductInsert__specialdescriptionpro)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@displayspecialdesctgi", 129, 1,1,CMDProductInsert__displayspecialdesctgi)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@displayspecialdescpro", 129, 1,1,CMDProductInsert__displayspecialdescpro)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@displayspecialpricetgi", 129, 1,1,CMDProductInsert__displayspecialpricetgi)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@displayspecialpricepro", 129, 1,1,CMDProductInsert__displayspecialpricepro)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@tgicrriageinc", 129, 1,1,CMDProductInsert__tgicrriageinc)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@tgicarriagerate", 5, 1, -1,CMDProductInsert__tgicarriagerate)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@procarriageinc", 129, 1,1,CMDProductInsert__procarriageinc)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@procarriagerate", 5, 1, -1,CMDProductInsert__procarriagerate)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@ssp", 5, 1, -1,CMDProductInsert__ssp)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@image1", 200, 1,150,CMDProductInsert__image1)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@image2", 200, 1,150,CMDProductInsert__image2)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@image3", 200, 1,150,CMDProductInsert__image3)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@image4", 200, 1,150,CMDProductInsert__image4)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@image5", 200, 1,150,CMDProductInsert__image5)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@image6", 200, 1,150,CMDProductInsert__image6)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@normaltgiprice", 5, 1, -1,CMDProductInsert__normaltgiprice)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@saletgiprice", 5, 1, -1,CMDProductInsert__saletgiprice)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@normalproprice", 5, 1, -1,CMDProductInsert__normalproprice)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@saleproprice", 5, 1, -1,CMDProductInsert__saleproprice)
set Product = CMDProductInsert.Execute
Product_numRows = 0

Session("NEWPRODUCTID") = (Product.Fields.Item("ProductID").Value)

myarray = split(UploadFormRequest("category"))

if isArray(myarray) then   ' here you're checking it's an array so if not, it doesn't throw an error

For Each item In myarray
      Response.Write(item & "<br />")
Next


'if isArray(myarray) then   ' here you're checking it's an array so if not, it doesn't throw an error
'for i = 0 to uBound(myarray,1)

'Dim CMDProductCategories__productid
'CMDProductCategories__productid = "0"
'if(Session("NEWPRODUCTID") <> "") then CMDProductCategories__productid = Session("NEWPRODUCTID")

'Dim CMDProductCategories__categoryid
'CMDProductCategories__categoryid = "0"
'if(myarray(i) <> "") then CMDProductCategories__categoryid = myarray(i)


'set CMDProductCategories = Server.CreateObject("ADODB.Command")
'CMDProductCategories.ActiveConnection = MM_GolfConnection_STRING
'CMDProductCategories.CommandText = "dbo.AdminAddProductToCategories"
'CMDProductCategories.CommandType = 4
'CMDProductCategories.CommandTimeout = 0
'CMDProductCategories.Prepared = true
'CMDProductCategories.Parameters.Append CMDProductCategories.CreateParameter("@RETURN_VALUE", 3, 4)
'CMDProductCategories.Parameters.Append CMDProductCategories.CreateParameter("@productid", 3, 1,8,CMDProductCategories__productid)
'CMDProductCategories.Parameters.Append CMDProductCategories.CreateParameter("@categoryid", 3, 1,8,CMDProductCategories__categoryid)
'CMDProductCategories.Execute()

'next
'End if


'relatedarray = split(UploadFormRequest("related"))

'if isArray(relatedarray) then   ' here you're checking it's an array so if not, it doesn't throw an error
'for i = 0 to uBound(relatedarray,1)

'Dim CMDRelatedProducts__productid
'CMDRelatedProducts__productid = "0"
'if(Session("NEWPRODUCTID") <> "") then CMDRelatedProducts__productid = Session("NEWPRODUCTID")

'Dim CMDRelatedProducts__relatedproductid
'CMDRelatedProducts__relatedproductid = "0"
'if(relatedarray(i) <> "") then CMDRelatedProducts__relatedproductid = relatedarray(i)



'set CMDRelatedProducts = Server.CreateObject("ADODB.Command")
'CMDRelatedProducts.ActiveConnection = MM_GolfConnection_STRING
'CMDRelatedProducts.CommandText = "dbo.AdminAddProductToRelatedProducts"
'CMDRelatedProducts.CommandType = 4
'CMDRelatedProducts.CommandTimeout = 0
'CMDRelatedProducts.Prepared = true
'CMDRelatedProducts.Parameters.Append CMDRelatedProducts.CreateParameter("@RETURN_VALUE", 3, 4)
'CMDRelatedProducts.Parameters.Append CMDRelatedProducts.CreateParameter("@productid", 3, 1,8,CMDRelatedProducts__productid)
'CMDRelatedProducts.Parameters.Append CMDRelatedProducts.CreateParameter("@relatedproductid", 3, 1,8,CMDRelatedProducts__relatedproductid)
'CMDRelatedProducts.Execute()

'next
End if

Session.Contents.Remove("NEWPRODUCTID")
Response.Redirect("/default.asp")

End If
%>

Open in new window


But I have conflicts running on other scripts as a rsult, as now I get -

Microsoft VBScript runtime error '800a01fa'
Class not defined: 'PureUpload'
/direct/includes/newproduct-image-upload.asp, line 5


Something to do with the array?
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36495608
I'm not sure.

I can't see a reference to the file newproduct-image-upload.asp unless you are calling a function contained in it.

What happens if you don't comment out your code and just let it run but keep the

For Each item In myarray
      Response.Write(item & "<br />")
Next

in?
0
 

Author Comment

by:garethtnash
ID: 36495614
Ahhh, OK

Result 1,2,4

Does that help?
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36495616
sorry, edit that.

just comment our the line

Response.Redirect("/default.asp")

to prevent the page from re-directing and you should be able to see the output from the array on screen
0
 

Author Comment

by:garethtnash
ID: 36495629
yes I've got -

1,2,4<br /> Source

at the top of the page...

where did the ',' come from?

0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36495675
I think it is because the SPLIT commant needs an extra parameter.

Try replacing

myarray = split(UploadFormRequest("category"))

With

myarray = split(UploadFormRequest("category"),",")

And see what happens

0
 

Author Comment

by:garethtnash
ID: 36495695
1<br />2<br />4<br />

???
0
 
LVL 10

Accepted Solution

by:
OnALearningCurve earned 500 total points
ID: 36495759
That's better because your array now contain the entries

1 2 & 4

where as before it contained 1, 2, & 4,

but you said your source data was:

&related=16
&related=17
&related=18
&related=19
&category=2
&category=4

so the array should only really contain 2 & 4

is this definitely right? or should the 1 be in there?

if it should, remove the debug code and see what happens in your database.

Also change the line

relatedarray = split(UploadFormRequest("related"))

to be

relatedarray = split(UploadFormRequest("related"),",")

to fix the relatedarray also.

HTH

Mark.
0
 

Author Comment

by:garethtnash
ID: 36496250
Mark, you are a star thank you :)

Can I be really cheeky? Is there a cleaner way to write my ASP VBScript?

this section -

Dim CMDProductInsert__sku
CMDProductInsert__sku = "0"
if(UploadFormRequest("sku") <> "") then CMDProductInsert__sku = Server.HTMLEncode(ProtectSQL(UploadFormRequest("sku")))

Dim CMDProductInsert__nominal
CMDProductInsert__nominal = "0"
if(UploadFormRequest("nominal") <> "") then CMDProductInsert__nominal = Server.HTMLEncode(ProtectSQL(UploadFormRequest("nominal")))

Dim CMDProductInsert__allocation
CMDProductInsert__allocation = "0"
if(UploadFormRequest("allocation") <> "") then CMDProductInsert__allocation = Server.HTMLEncode(ProtectSQL(UploadFormRequest("allocation")))

Dim CMDProductInsert__owner
CMDProductInsert__owner = "0"
if(UploadFormRequest("owner") <> "") then CMDProductInsert__owner = Server.HTMLEncode(ProtectSQL(UploadFormRequest("owner")))

Dim CMDProductInsert__name
CMDProductInsert__name = ""
if(UploadFormRequest("name") <> "") then CMDProductInsert__name = Server.HTMLEncode(ProtectSQL(UploadFormRequest("name")))

Dim CMDProductInsert__description
CMDProductInsert__description = ""
if(UploadFormRequest("description") <> "") then CMDProductInsert__description = UploadFormRequest("description")

Dim CMDProductInsert__stockcount
CMDProductInsert__stockcount = "0"
if(UploadFormRequest("stockcount") <> "") then CMDProductInsert__stockcount = Server.HTMLEncode(ProtectSQL(UploadFormRequest("stockcount")))

Dim CMDProductInsert__restocklevel
CMDProductInsert__restocklevel = "0"
if(UploadFormRequest("restocklevel") <> "") then CMDProductInsert__restocklevel = Server.HTMLEncode(ProtectSQL(UploadFormRequest("restocklevel")))

Dim CMDProductInsert__displaytgi
CMDProductInsert__displaytgi = "N"
if(UploadFormRequest("displaytgi") <> "") then CMDProductInsert__displaytgi = Server.HTMLEncode(ProtectSQL(UploadFormRequest("displaytgi")))

Dim CMDProductInsert__displaypro
CMDProductInsert__displaypro = "N"
if(UploadFormRequest("displaypro") <> "") then CMDProductInsert__displaypro = Server.HTMLEncode(ProtectSQL(UploadFormRequest("displaypro")))

Dim CMDProductInsert__availabledate
CMDProductInsert__availabledate = DateAdd("d", -1, Now())
if(UploadFormRequest("availabledate") <> "") then CMDProductInsert__availabledate = Server.HTMLEncode(ProtectSQL(UploadFormRequest("availabledate")))

Dim CMDProductInsert__specialtgi
CMDProductInsert__specialtgi = "N"
if(UploadFormRequest("specialtgi") <> "") then CMDProductInsert__specialtgi = Server.HTMLEncode(ProtectSQL(UploadFormRequest("specialtgi")))

Dim CMDProductInsert__specialpro
CMDProductInsert__specialpro = "N"
if(UploadFormRequest("specialpro") <> "") then CMDProductInsert__specialpro = Server.HTMLEncode(ProtectSQL(UploadFormRequest("specialpro")))

Dim CMDProductInsert__specialdescriptiontgi
CMDProductInsert__specialdescriptiontgi = ""
if(UploadFormRequest("specialdescriptiontgi") <> "") then CMDProductInsert__specialdescriptiontgi = UploadFormRequest("specialdescriptiontgi")

Dim CMDProductInsert__specialdescriptionpro
CMDProductInsert__specialdescriptionpro = ""
if(UploadFormRequest("specialdescriptionpro") <> "") then CMDProductInsert__specialdescriptionpro = UploadFormRequest("specialdescriptionpro")

Dim CMDProductInsert__displayspecialdesctgi
CMDProductInsert__displayspecialdesctgi = "N"
if(UploadFormRequest("displayspecialdesctgi") <> "") then CMDProductInsert__displayspecialdesctgi = Server.HTMLEncode(ProtectSQL(UploadFormRequest("displayspecialdesctgi")))

Dim CMDProductInsert__displayspecialdescpro
CMDProductInsert__displayspecialdescpro = "N"
if(UploadFormRequest("displayspecialdescpro") <> "") then CMDProductInsert__displayspecialdescpro = Server.HTMLEncode(ProtectSQL(UploadFormRequest("displayspecialdescpro")))

Dim CMDProductInsert__displayspecialpricetgi
CMDProductInsert__displayspecialpricetgi = "N"
if(UploadFormRequest("displayspecialpricetgi") <> "") then CMDProductInsert__displayspecialpricetgi = Server.HTMLEncode(ProtectSQL(UploadFormRequest("displayspecialpricetgi")))

Dim CMDProductInsert__displayspecialpricepro
CMDProductInsert__displayspecialpricepro = "N"
if(UploadFormRequest("displayspecialpricepro") <> "") then CMDProductInsert__displayspecialpricepro = Server.HTMLEncode(ProtectSQL(UploadFormRequest("displayspecialpricepro")))

Dim CMDProductInsert__tgicrriageinc
CMDProductInsert__tgicrriageinc = "N"
if(UploadFormRequest("tgicrriageinc") <> "") then CMDProductInsert__tgicrriageinc = Server.HTMLEncode(ProtectSQL(UploadFormRequest("tgicrriageinc")))

Dim CMDProductInsert__tgicarriagerate
CMDProductInsert__tgicarriagerate = 0.00
if(UploadFormRequest("tgicarriagerate") <> "") then CMDProductInsert__tgicarriagerate = Server.HTMLEncode(ProtectSQL(UploadFormRequest("tgicarriagerate")))

Dim CMDProductInsert__procarriageinc
CMDProductInsert__procarriageinc = "N"
if(UploadFormRequest("procrriageinc") <> "") then CMDProductInsert__procarriageinc = Server.HTMLEncode(ProtectSQL(UploadFormRequest("procrriageinc")))

Dim CMDProductInsert__procarriagerate
CMDProductInsert__procarriagerate = 0.00
if(UploadFormRequest("procarriagerate") <> "") then CMDProductInsert__procarriagerate = Server.HTMLEncode(ProtectSQL(UploadFormRequest("procarriagerate")))

Dim CMDProductInsert__ssp
CMDProductInsert__ssp = 0.00
if(UploadFormRequest("ssp") <> "") then CMDProductInsert__ssp = Server.HTMLEncode(ProtectSQL(UploadFormRequest("ssp")))

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

Dim CMDProductInsert__image2
CMDProductInsert__image2 = ""
if(UploadFormRequest("image2") <> "") then CMDProductInsert__image2 = UploadFormRequest("image2")

Dim CMDProductInsert__image3
CMDProductInsert__image3 = ""
if(UploadFormRequest("image3") <> "") then CMDProductInsert__image3 = UploadFormRequest("image3")

Dim CMDProductInsert__image4
CMDProductInsert__image4 = ""
if(UploadFormRequest("image4") <> "") then CMDProductInsert__image4 = UploadFormRequest("image4")

Dim CMDProductInsert__image5
CMDProductInsert__image5 = ""
if(UploadFormRequest("image5") <> "") then CMDProductInsert__image5 = UploadFormRequest("image5")

Dim CMDProductInsert__image6
CMDProductInsert__image6 = ""
if(UploadFormRequest("image6") <> "") then CMDProductInsert__image6 = UploadFormRequest("image6")

Dim CMDProductInsert__normaltgiprice
CMDProductInsert__normaltgiprice = 0.00
if(UploadFormRequest("normaltgiprice") <> "") then CMDProductInsert__normaltgiprice = Server.HTMLEncode(ProtectSQL(UploadFormRequest("normaltgiprice")))

Dim CMDProductInsert__saletgiprice
CMDProductInsert__saletgiprice = 0.00
if(UploadFormRequest("saletgiprice") <> "") then CMDProductInsert__saletgiprice = Server.HTMLEncode(ProtectSQL(UploadFormRequest("saletgiprice")))

Dim CMDProductInsert__normalproprice
CMDProductInsert__normalproprice = 0.00
if(UploadFormRequest("normalproprice") <> "") then CMDProductInsert__normalproprice = Server.HTMLEncode(ProtectSQL(UploadFormRequest("normalproprice")))

Dim CMDProductInsert__saleproprice
CMDProductInsert__saleproprice = 0.00
if(UploadFormRequest("saleproprice") <> "") then CMDProductInsert__saleproprice = Server.HTMLEncode(ProtectSQL(UploadFormRequest("saleproprice")))


set CMDProductInsert = Server.CreateObject("ADODB.Command")
CMDProductInsert.ActiveConnection = MM_GolfConnection_STRING
CMDProductInsert.CommandText = "dbo.AdminNewDirectProduct"
CMDProductInsert.CommandType = 4
CMDProductInsert.CommandTimeout = 0
CMDProductInsert.Prepared = true
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@RETURN_VALUE", 3, 4)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@sku", 200, 1,8,CMDProductInsert__sku)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@nominal", 3, 1,8,CMDProductInsert__nominal)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@allocation", 3, 1,8,CMDProductInsert__allocation)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@owner", 3, 1,8,CMDProductInsert__owner)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@name", 200, 1,100,CMDProductInsert__name)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@description", 200, 1,5000,CMDProductInsert__description)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@stockcount", 3, 1,8,CMDProductInsert__stockcount)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@restocklevel", 3, 1,8,CMDProductInsert__restocklevel)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@displaytgi", 129, 1,1,CMDProductInsert__displaytgi)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@displaypro", 129, 1,1,CMDProductInsert__displaypro)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@availabledate", 200, 1,50,CMDProductInsert__availabledate)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@specialtgi", 129, 1,1,CMDProductInsert__specialtgi)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@specialpro", 129, 1,1,CMDProductInsert__specialpro)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@specialdescriptiontgi", 200, 1,2000,CMDProductInsert__specialdescriptiontgi)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@specialdescriptionpro", 200, 1,2000,CMDProductInsert__specialdescriptionpro)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@displayspecialdesctgi", 129, 1,1,CMDProductInsert__displayspecialdesctgi)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@displayspecialdescpro", 129, 1,1,CMDProductInsert__displayspecialdescpro)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@displayspecialpricetgi", 129, 1,1,CMDProductInsert__displayspecialpricetgi)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@displayspecialpricepro", 129, 1,1,CMDProductInsert__displayspecialpricepro)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@tgicrriageinc", 129, 1,1,CMDProductInsert__tgicrriageinc)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@tgicarriagerate", 5, 1, -1,CMDProductInsert__tgicarriagerate)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@procarriageinc", 129, 1,1,CMDProductInsert__procarriageinc)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@procarriagerate", 5, 1, -1,CMDProductInsert__procarriagerate)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@ssp", 5, 1, -1,CMDProductInsert__ssp)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@image1", 200, 1,150,CMDProductInsert__image1)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@image2", 200, 1,150,CMDProductInsert__image2)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@image3", 200, 1,150,CMDProductInsert__image3)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@image4", 200, 1,150,CMDProductInsert__image4)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@image5", 200, 1,150,CMDProductInsert__image5)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@image6", 200, 1,150,CMDProductInsert__image6)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@normaltgiprice", 5, 1, -1,CMDProductInsert__normaltgiprice)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@saletgiprice", 5, 1, -1,CMDProductInsert__saletgiprice)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@normalproprice", 5, 1, -1,CMDProductInsert__normalproprice)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@saleproprice", 5, 1, -1,CMDProductInsert__saleproprice)
set Product = CMDProductInsert.Execute
Product_numRows = 0

Open in new window

0
 

Author Closing Comment

by:garethtnash
ID: 36496256
Great help - thank you :)
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36496284
I'll take a look but I can't promise anything.

It might be tomorrow morning now before I can get to it but I will see what I can do.

and thanks for the points :)
0
 

Author Comment

by:garethtnash
ID: 36496335
Thanks for all your help, points well deserved :)

Thank you
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36496502
I've had a quick look and I'm no expert so don't take this as gospel but I don;t think thee is much you can do with it.

the only possible change I may try ( and this would simply be to reduce the number of lines) would possibly be to declare you variables at the start in one line rather than three.

for example, I think you could replace:

Dim CMDProductInsert__sku
CMDProductInsert__sku = "0"
if(UploadFormRequest("sku") <> "") then CMDProductInsert__sku = Server.HTMLEncode(ProtectSQL(UploadFormRequest("sku")))

With:

if(UploadFormRequest("sku") <> "") then CMDProductInsert__sku = Server.HTMLEncode(ProtectSQL(UploadFormRequest("sku"))) else CMDProductInsert__sku = "0"

but I'm not sure if you can get away without the Dim, you would have to test it out and see.

Other than that, I've got nothing

except that your initial file at the start of the question would seriously benefit from some indentation ;)

Hope this helps,

Mark.
0
 

Author Comment

by:garethtnash
ID: 36496524
Thanks Mark
0
 

Author Comment

by:garethtnash
ID: 36503592
Mark - are you around at all, I've got another testing query I think you might be able to help with,

In my insert form the user could select multiple options from a list box

Now I have the edit form, but can only get it to highlight the first option selected....

Grateful if you wouldn't mind taking a quick peak for me please if you have a chance ---

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_27297659.html#a36503370

Thank you
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36504366
I'm away from a pc at the moment but if it's nit already been answered I'll take a look in the morning

Cheers,

Mark
0
 

Author Comment

by:garethtnash
ID: 36504389
Thanks Mark :)
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36509022
Did the last suggestion from pateljitu work?
0
 

Author Comment

by:garethtnash
ID: 36509108
Morning Mark,

You in GMT zone as well then?

Yes - thank you, I've just managed to get it working.

Thanks for your continued support :)
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36509169
No worries and yes,

stuck in sunny UK  (obviously the sunny bit was a lie)

Glad you got it sorted.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 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