Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2018-01-12
3
Medium Priority
?
25 Views
Last Modified: 2018-01-12
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
Comment
Question by:romsom
3 Comments
 
LVL 7

Accepted Solution

by:
Jan Louwerens earned 2000 total points
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
 

Author Closing Comment

by:romsom
Thank you very much; now I understand what your code does.
0
 
LVL 82

Expert Comment

by:hielo
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Join & Write a Comment

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

580 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