Solved

Use comma separated values to create temporary recordset

Posted on 2014-10-21
33
161 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
  • 18
  • 14
33 Comments
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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 32

Expert Comment

by:Big Monty
Comment Utility
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
Comment Utility
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
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 32

Expert Comment

by:Big Monty
Comment Utility
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 32

Expert Comment

by:Big Monty
Comment Utility
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
Comment Utility
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
Comment Utility
This is my DB connection:  <!--#include file="../../Connections/eimmigration.asp" -->
0
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
Comment Utility
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
Comment Utility
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 32

Expert Comment

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

Author Comment

by:amucinobluedot
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
Comment Utility
Microsoft VBScript compilation error '800a0401'

Expected end of statement

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

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

Expert Comment

by:Big Monty
Comment Utility
change that line to

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

Author Comment

by:amucinobluedot
Comment Utility
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 32

Expert Comment

by:Big Monty
Comment Utility
change the for loop to

for counter = 0 to UBound( arrItemIDs )
0
 

Author Comment

by:amucinobluedot
Comment Utility
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 32

Expert Comment

by:Big Monty
Comment Utility
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
Comment Utility
Well .. I am all ears. How can I use an object instead of an array ?  Do you mean a recordset ?
0
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I think I got it with:

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

:)
0
 
LVL 32

Accepted Solution

by:
Big Monty earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
:)
0
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
my pleasure, glad I could help
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now