Use comma separated values to create temporary recordset

I am passing form values from page1 to page2

The form values are requested by page2 with:

request.form("itemid")
request.form("amount")

And the values could be something like:

itemid= 4506, 4508
Amount = 15, 50

Can be 2 values or 10 or more.  

The first value of itemid corresponds to the first value of amount, second the same and so on.

I want to insert this values into a table.

Table: Billpaytemp
itemid  INT
amount Money

Then I will use this recordset to do other things. How can I insert this comma separated values into the table "Billpaytemp"

So that the records end up being something like:

ID1  |  itemid  |  amount
1           4506       15
2            15           50

I am using classic ASP/VBScript  and MS SQL 2008
AleksAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Big MontyConnect With a Mentor Senior Web Developer / CEO of ExchangeTree.org Commented:
you're close, you just need a comma to separate the third parameter:

sql = "insert into Billinglines ( itemID, PmtRecd, caseid ) values ( " & arrItemIDs( counter ) & ", " & arrAmounts( counter ) & "," & caseid & ")"
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
assuming you always get the same amount of values for both the itemID and amount, you could put those values into an array and loop through them, inserting them into the database. something like this should get you going. keep in mind there is no check here to make sure the data is correct:

itemIDs = request.form("itemid")
amounts = request.form("amount")

'-- do a simple check to make sure there are multiple values
if InStr( itemIDs, "," ) > 0 and InStr( amounts, "," ) then       '-- multiple values passed
    put values into an array
    arrItemIDs = Split( itemIDs, "," )
    arrAmounts = Split( amounts, "," )

    '-- now loop through the array and insert into the database
    for counter = 0 to UBound( arrItemIDs ) - 1
         if arrItemIDs( counter ) <> "" then      '-- you also may want to check to make sure it's an numerical value
               sql = "insert into Billpaytemp ( itemID, amount ) values ( " & arrItemIDs( counter ) & ", " & arrAmounts( counter ) & " )"
               conn.Execute( sql )         '-- assumes you have a connection object created and connected to the database
         end if
    next
else
    '-- code to handle only 1 or no values passed
end if

Open in new window

0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you may also want to convert this over to a command object so you don't have to worry about sql injection (like I mentioned above, this code doesn't check for correct data). I can help with that as well, but first let's get the basics working and make sure you understand what's going on
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
AleksAuthor Commented:
The above makes sense, there is one amount for each ID.  Can we do what you propose ?  I am not an expert but I do follow the logic.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
sure, you should be able to copy and paste the code I provided directly into your code, wherever you're handling the processing of the data
0
 
AleksAuthor Commented:
With the code above I got an error:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/bluedot/Intranet/Billing/SPSavePaymentLines.asp, line 16

put values into an array
-----------^
page.txt
0
 
AleksAuthor Commented:
I uploaded the page as I have it at this time, cleared out the rest of the code I had to first test this
0
 
AleksAuthor Commented:
I commented that line, and now I get this error:

Microsoft VBScript runtime error '800a01a8'

Object required: ''

/bluedot/Intranet/Billing/SPSavePaymentLines.asp, line 24


Line 24:

conn.Execute( sql )
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
had a typo there, it's a comment, so put an apostrophe at the beginning of the that line or take it out completely
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
in the text file you uploaded, where is your connection object declared and initialized? you'll need to match the name of the connection object to whatever you have currently
0
 
AleksAuthor Commented:
I tested and if I pass only ONE value no error is displayed, but if I pass 2 or more then I get the error above.
Also, if I only select one value (one id and one amount), no error is displayed but nothing is inserted into the database either
0
 
AleksAuthor Commented:
This is my DB connection:  <!--#include file="../../Connections/eimmigration.asp" -->
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you post the code for that include file, taking out any sensitive data (UIDs, pw's, server locations, etc)?
0
 
AleksAuthor Commented:
This is what I currently have:  One value passed, no error but nothing is inserted.
Two values or more, error.

-- code --
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<!--#BeginBlock-->
<!--#include file="../../Connections/eimmigration.asp" -->
<!--#include file="../../includes/bdot/attysessiontimeoutvb2.asp"-->
<!--#include file="../../includes/bdot/scripts.asp"-->
<!--#BeginBlock-->

<%

itemIDs = request.form("itemid")
amounts = request.form("amount")

'-- do a simple check to make sure there are multiple values
if InStr( itemIDs, "," ) > 0 and InStr( amounts, "," ) then      

'-- multiple values passed
      '-- put values into an array
    arrItemIDs = Split( itemIDs, "," )
    arrAmounts = Split( amounts, "," )

    '-- now loop through the array and insert into the database
    for counter = 0 to UBound( arrItemIDs ) - 1
         if arrItemIDs( counter ) <> "" then      '-- you also may want to check to make sure it's an numerical value
               sql = "insert into BillinglinesTMP ( itemID, amount ) values ( " & arrItemIDs( counter ) & ", " & arrAmounts( counter ) & " )"
               conn.Execute( sql )         '-- assumes you have a connection object created and connected to the database
         end if
    next
else
    '-- code to handle only 1 or no values passed
end if

%>


<!--#BeginBlock-->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<!--#BeginBlock-->

<body>
<p>&nbsp;</p>
<p>ids: <%=request.form("itemid")%></p>
<p>amounts: <%=request.form("amount")%></p>
</body>
</html>


----------- when I pass two or more I get this error -----
Microsoft VBScript runtime error '800a01a8'

Object required: ''

/bluedot/Intranet/Billing/SPSavePaymentLines.asp, line 26
0
 
AleksAuthor Commented:
I can't, and its irrelevant really, that connection is correct, its the same I use for my recordsets, etc. all over the system and works just fine.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
please post the data for eimmigration.asp, removing sensitive data
0
 
AleksAuthor Commented:
Here

<%
' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_eimmigration_STRING
MM_eimmigration_STRING = "dsn=XXX;uid=XXX;pwd=XXXX;"
%>
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
ok, try this for your code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<!--#BeginBlock-->
<!--#include file="../../Connections/eimmigration.asp" -->
<!--#include file="../../includes/bdot/attysessiontimeoutvb2.asp"-->
<!--#include file="../../includes/bdot/scripts.asp"-->
<!--#BeginBlock-->

<%
'-- create connection object and establish a connection to the database
set conn = Server,CreateObject("ADODB.Connection")
conn.Open MM_eimmigration_STRING

itemIDs = request.form("itemid")
amounts = request.form("amount")

'-- do a simple check to make sure there are multiple values
if InStr( itemIDs, "," ) > 0 and InStr( amounts, "," ) then       

'-- multiple values passed
      '-- put values into an array
    arrItemIDs = Split( itemIDs, "," )
    arrAmounts = Split( amounts, "," )

    '-- now loop through the array and insert into the database
    for counter = 0 to UBound( arrItemIDs ) - 1
         if arrItemIDs( counter ) <> "" then      '-- you also may want to check to make sure it's an numerical value
               sql = "insert into BillinglinesTMP ( itemID, amount ) values ( " & arrItemIDs( counter ) & ", " & arrAmounts( counter ) & " )"
               conn.Execute( sql )         '-- assumes you have a connection object created and connected to the database
         end if
    next
else
    '-- code to handle only 1 or no values passed
    sql = "insert into BillinglinesTMP ( itemID, amount ) values ( " & itemIDs & ", " & amounts & " )"
    conn.Execute( sql )
end if

if conn.State <> 0 then conn.Close
set conn = nothing

%>


<!--#BeginBlock-->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<!--#BeginBlock-->

<body>
<p>&nbsp;</p>
<p>ids: <%=request.form("itemid")%></p>
<p>amounts: <%=request.form("amount")%></p>
</body>
</html>

Open in new window

0
 
AleksAuthor Commented:
Microsoft VBScript compilation error '800a0401'

Expected end of statement

/bluedot/Intranet/Billing/SPSavePaymentLines.asp, line 11

set conn = Server,CreateObject("ADODB.Connection")
-----------------^
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
change that line to

set conn = Server.CreateObject("ADODB.Connection")
0
 
AleksAuthor Commented:
We are getting there.

I passed on two ids and two amounts:

ids: 4506, 4508

amounts: 15, 40

Checked the DB but only one was inserted.

itemid      amount
4506      15.00
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
change the for loop to

for counter = 0 to UBound( arrItemIDs )
0
 
AleksAuthor Commented:
Looks good !   I need to take a break for lunch and be back to modify this code so that it uses the actual table. I used a temporary one to test, there are other values I need to insert but those are fixed so it will only be a matter of syntax.
Do you want to modify it so that it uses an object as you mentioned ?

Should be back in about 2 hrs. Do you want me to open a new ticket for the follow up of this or continue here ?
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Do you want to modify it so that it uses an object as you mentioned ?

entirely up to you, but if you do decide to go that route, I'd like to see some effort by you before I get involved. I don't mind helping you out with specifics, but the only way you're going to learn is to do it yourself :) Plus, judging from your other question you posted earlier, it seems you're familiar with the command object

if you're follow up questions are directly related to this question, continue here, otherwise if it's separate issues, open a new ticket
0
 
AleksAuthor Commented:
Well .. I am all ears. How can I use an object instead of an array ?  Do you mean a recordset ?
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
I was referring to the Command Object instead of just dynamically building the sql. It's the same method you're using here in your code, on line 13

Why don't we skip that part for now and make sure we get this working with your code you have now.
0
 
AleksAuthor Commented:
Seems to be working ... I am adding my code to insert in the appropriate tables. I will update you shortly.
0
 
AleksAuthor Commented:
I am not using the right table. I need to insert one more value

How can I modify this sql to include it ?  I tried a few things but I get a syntax error every time :(

sql = "insert into Billinglines ( itemID, PmtRecd, caseid ) values ( " & arrItemIDs( counter ) & ", " & arrAmounts( counter ) & "  caseid)"

already added:

caseid = request.querystring("caseid")

Error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'caseid'.

/bluedot/Intranet/Billing/SPSavePaymentLines.asp, line 79
0
 
AleksAuthor Commented:
I think I got it with:

sql = "insert into Billinglines ( itemID, PmtRecd, caseid ) values ( " & arrItemIDs( counter ) & ", " & arrAmounts( counter ) & " , " & (caseid) & ")"

:)
0
 
AleksAuthor Commented:
Thanks ... I will close this ticket for now until I finish my page, if I need further help Ill add a new one. Thanks for the help !!!
0
 
AleksAuthor Commented:
:)
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
my pleasure, glad I could help
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
All - I just kicked out an article that is the SQL Server solution called T-SQL:  Normalized data to a single comma delineated string and back, if you like it please click on the 'Good Article' button at the bottom.
0
All Courses

From novice to tech pro — start learning today.