troubleshooting Question

2 ASP / MS SQL Stored Procedure Issues

Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland asked on
ASPVB ScriptMicrosoft SQL Server 2008
26 Comments1 Solution433 ViewsLast Modified:
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)

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

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

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

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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 26 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 26 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros