• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

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

0
romsom
Asked:
romsom
1 Solution
 
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
 
romsomAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now