Solved

2 ASP / MS SQL Stored Procedure Issues

Posted on 2011-09-07
26
279 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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