Solved

Better way of writing Insert ans Select Stored Procedure,

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

Does anyone have a better way of writing the following ASP Vbscript Stored Procedure?

Many thanks


<%If UploadFormRequest("faction") = "finsert" AND UploadFormRequest("name") <> "" then %>
<%Function ProtectSQL(SQLString)
SQLString = Replace(SQLString, "'", "''") ' replace single Quotes with Double Quotes
SQLString = Replace(SQLString, vblf,"<br />") ' replace vblf with <br /> (This is mainly used for Memo fields.
SQLString = Replace(SQLString, "(","&#40;") ' replace ( with &#40;
SQLString = Replace(SQLString, ")","&#41;") ' replace ) with &#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("specialtgi") <> "") then CMDProductInsert__saletgiprice = Server.HTMLEncode(ProtectSQL(UploadFormRequest("specialtgi")))

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("specialpro") <> "") then CMDProductInsert__saleproprice = Server.HTMLEncode(ProtectSQL(UploadFormRequest("specialpro")))

%>
<%

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,10,CMDProductInsert__stockcount)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@restocklevel", 3, 1,10,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", 14, 1,10,CMDProductInsert__tgicarriagerate)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@procarriageinc", 129, 1,1,CMDProductInsert__procarriageinc)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@procarriagerate", 14, 1,10,CMDProductInsert__procarriagerate)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@ssp", 14, 1,10,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", 14, 1,10,CMDProductInsert__normaltgiprice)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@saletgiprice", 14, 1,10,CMDProductInsert__saletgiprice)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@normalproprice", 14, 1,10,CMDProductInsert__normalproprice)
CMDProductInsert.Parameters.Append CMDProductInsert.CreateParameter("@saleproprice", 14, 1,10,CMDProductInsert__saleproprice)
set Product = CMDProductInsert.Execute
Product_numRows = 0

%>

Open in new window

0
Comment
Question by:garethtnash
2 Comments
 
LVL 9

Accepted Solution

by:
DrewKjell earned 500 total points
ID: 36495642
No, from the way you have it and what it appears you are attempting to accomplish it seems to be pretty solid.

One could argue that your Dim statements should all be above the checks on if there is any form data present, but that's purely a preference.

Drew
0
 

Author Closing Comment

by:garethtnash
ID: 36521919
Thank you
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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
In this article I will describe the Copy Database Wizard 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.
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 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