Solved

2 ASP / MS SQL Stored Procedure Issues

Posted on 2011-09-07
26
274 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now