Solved

Use comma separated values to create temporary recordset

Posted on 2014-10-21
33
177 Views
Last Modified: 2016-03-28
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
0
Comment
Question by:amucinobluedot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 18
  • 14
33 Comments
 
LVL 33

Expert Comment

by:Big Monty
ID: 40394778
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
 
LVL 33

Expert Comment

by:Big Monty
ID: 40394787
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
 

Author Comment

by:amucinobluedot
ID: 40394889
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
Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

 
LVL 33

Expert Comment

by:Big Monty
ID: 40394900
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
 

Author Comment

by:amucinobluedot
ID: 40394911
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
 

Author Comment

by:amucinobluedot
ID: 40394913
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
 

Author Comment

by:amucinobluedot
ID: 40394921
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
 
LVL 33

Expert Comment

by:Big Monty
ID: 40394923
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
 
LVL 33

Expert Comment

by:Big Monty
ID: 40394928
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
 

Author Comment

by:amucinobluedot
ID: 40394930
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
 

Author Comment

by:amucinobluedot
ID: 40394934
This is my DB connection:  <!--#include file="../../Connections/eimmigration.asp" -->
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40394943
can you post the code for that include file, taking out any sensitive data (UIDs, pw's, server locations, etc)?
0
 

Author Comment

by:amucinobluedot
ID: 40394946
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
 

Author Comment

by:amucinobluedot
ID: 40394947
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
 
LVL 33

Expert Comment

by:Big Monty
ID: 40394950
please post the data for eimmigration.asp, removing sensitive data
0
 

Author Comment

by:amucinobluedot
ID: 40394951
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
 
LVL 33

Expert Comment

by:Big Monty
ID: 40394956
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
 

Author Comment

by:amucinobluedot
ID: 40394965
Microsoft VBScript compilation error '800a0401'

Expected end of statement

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

set conn = Server,CreateObject("ADODB.Connection")
-----------------^
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40394972
change that line to

set conn = Server.CreateObject("ADODB.Connection")
0
 

Author Comment

by:amucinobluedot
ID: 40395000
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
 
LVL 33

Expert Comment

by:Big Monty
ID: 40395013
change the for loop to

for counter = 0 to UBound( arrItemIDs )
0
 

Author Comment

by:amucinobluedot
ID: 40395026
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
 
LVL 33

Expert Comment

by:Big Monty
ID: 40395039
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
 

Author Comment

by:amucinobluedot
ID: 40395320
Well .. I am all ears. How can I use an object instead of an array ?  Do you mean a recordset ?
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40395413
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
 

Author Comment

by:amucinobluedot
ID: 40395439
Seems to be working ... I am adding my code to insert in the appropriate tables. I will update you shortly.
0
 

Author Comment

by:amucinobluedot
ID: 40395517
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
 

Author Comment

by:amucinobluedot
ID: 40395528
I think I got it with:

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

:)
0
 
LVL 33

Accepted Solution

by:
Big Monty earned 500 total points
ID: 40395529
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
 

Author Comment

by:amucinobluedot
ID: 40395536
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
 

Author Closing Comment

by:amucinobluedot
ID: 40395538
:)
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40395563
my pleasure, glad I could help
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41525191
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

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

628 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