SQL Syntax - Insert multiple records with one submission - Classic ASP

Hi Experts,
I'm submitting multiple records with one submission. The problem is that some of my records contain commas, therefore the code below does not insert them correctly in the database table. Can you please let me know how to modify it? I would really appreciate your help.

strDestQueueId = Request.Form("DestQueueId")

for index = 1 to Request.Form("csvValues").Count
   strCsvValues = Request.Form("csvValues")(index)

   arrayValues = Split(strCsvValues, ",")
   strDeviceName = arrayValues(0)
   strOU = arrayValues(1)
   strSubOU = arrayValues(2)
   strFlt = arrayValues(3)

   'create SQL string for inserting data into database
   strSQL = "INSERT INTO TestTable (DeviceName, OU, SubOU, Flt, DestQueueId) VALUES ('"& strDeviceName &"','"& strOU &"','"& strSubOU &"','"& strFlt &"','"& strDestQueueId &"')"

   'now add data with Execute() method
   Conn.Execute(strSql)
next

Open in new window

romsomIT DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan LouwerensSoftware EngineerCommented:
Just use a character that you know is not anywhere in your data (such as a colon or a pipe symbol).

<input type="checkbox" name="csvValues" value="<%=rs("DeviceName") & "|" & rs("OU") & "|" & rs("SubOU") %>">

Open in new window


arrayValues = Split(strCsvValues, "|")

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
romsomIT DeveloperAuthor Commented:
Thank you very much; now I understand what your code does.
0
hieloCommented:
You should really be using Parameterized Queries (see below).  Building dynamic queries based on user input (which is what you are doing) just leaves your application exposed to SQL injection attacks.

' somewhere here you are setting a connection to the DB, initializing the "Conn"

Set dbCommand = Server.CreateObject("ADODB.Command")
Set dbCommand.ActiveConnection = Conn
dbCommand.CommandType = adCmdText
dbCommand.CommandText = "INSERT INTO TestTable (DeviceName, OU, SubOU, Flt, DestQueueId) VALUES (?,?,?,?,?)"

' Create and append a Parameter object foreach of the fields
' Assuming that all your fields are VarChar and that all of them accept at most 50 chars, then you can initialize your parameters as shown below.
' If any of your fields is not "varChar", then change adVarChar to whatever is appropriate for your field.  Look up the corresponding/appropriate dataType enum at https://www.w3schools.com/asp/ado_datatypes.asp:
' You also need to change that "50" to whatever your maximum field length happens to be for each specific field
' You can leave the value as an empty string.  You then update it in the "For" loop to the actual value you get from the form submission
dbCommand.Parameters.Append (dbCommand.CreateParameter("DeviceName",  adVarChar, adParamInput, 50, ""))
dbCommand.Parameters.Append (dbCommand.CreateParameter("OU",          adVarChar, adParamInput, 50, ""))
dbCommand.Parameters.Append (dbCommand.CreateParameter("SubOU",       adVarChar, adParamInput, 50, ""))
dbCommand.Parameters.Append (dbCommand.CreateParameter("Flt",         adVarChar, adParamInput, 50, ""))
dbCommand.Parameters.Append (dbCommand.CreateParameter("DestQueueId", adVarChar, adParamInput, 50, ""))

strDestQueueId = Request.Form("DestQueueId")

for index = 1 to Request.Form("csvValues").Count
   strCsvValues = Request.Form("csvValues")(index)

   arrayValues = Split(strCsvValues, ",")
   strDeviceName = arrayValues(0)
   strOU = arrayValues(1)
   strSubOU = arrayValues(2)
   strFlt = arrayValues(3)

	' Update with the expected values
	dbCommand.Parameters.Item(0).value = strDeviceName
	dbCommand.Parameters.Item(1).value = strOU
	dbCommand.Parameters.Item(2).value = strSubOU
	dbCommand.Parameters.Item(3).value = strFlt
	dbCommand.Parameters.Item(4).value = strDestQueueId

   ' execute the INSERT command
   dbCommand.Execute
next

Open in new window

1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.