Solved

Better way of writing Insert ans Select Stored Procedure,

Posted on 2011-09-07
2
233 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore 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.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 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

12 Experts available now in Live!

Get 1:1 Help Now