473,728 Members | 1,619 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using a loop to input all the values of an array into an SQL query.

11 New Member
Hi. I am a relative newbie to ASP and I am working on an application that uses ASP and an Oracle 9i database. I have a form that allows the user to query the database by selecting a client name(s) from a listbox and also set a time period for the search by selecting a start date and end date. Once the query is executed a form letter(s) is (are) generated to the client(s) that details the products that have been purchased during the time period specified, and thanks the client for their purchase and asks for feedback on the customer service they received.

This is fine if only one client is being chosen, but when multiple client names are chosen from the database I'm having trouble trying to wrap my head around the necessary logic to make this work. There are a couple of issues:

1. The name of the client is stored in two separate fields in the table: last_name and first_name. In the form, these two fields are combined as one variable: v_name. But when the query is executed, each name selected in the form will have to be broken down into its two parts so the WHERE clause will look like this:

Expand|Select|Wrap|Line Numbers
  1. sqltxt="Select * from clients where (s_name=' "&v_sname&" ' and f_name=' "&v_fname&" ') order by s_name, f_name, p_date"
So I think I need to break-up the v_name variable in order to properly input values into the WHERE clause, but I'm not sure how to do this...or it could be that this is really easy and I am just too tired to think clearly right now. Should I try using the split function here?

2. The second part of the problem I am having is that I'm pretty sure that I have to use an array to hold the name values that are selected from the form. So I thought about using a for loop to go through each element of the array, plugging the values into the SQL statement. But I need the recordsets to be returned in the body of the letter, so I'm thinking the resulting recordsets need to be held in an array as well. Is this correct? In the past, I have been able to get away with using the IN keyword in SQL to go through the string. But I don't think I can do that because the names are in this format: last_name, first_name. When I use this piece of code:

Expand|Select|Wrap|Line Numbers
  1. <% 
  2. Response.write "(" &v_name& ")"
  3. %>
  4.  
The resulting output is: (Smith, Jane, Greggson, Natalie, Yost, Graham)
I can't use the IN keyword without splitting up the array...and then I'd have to try to manipulate the array to extract only even elements for the last name and odd elements for the first name. I'm not sure how to do this and I haven't been able to find anything to help with that yet.

I hope I am making some sense here. I just read back what I wrote and I think I'm getting more confused! This is probably easy and I'm just too burnt out to see it. Anyway, any help with the logic or an easy way to go about this would be greatly appreciated. If this doesn't make any sense, I will try my best to clarify.

Thanks!
Mar 16 '07 #1
10 2859
jhardman
3,406 Recognized Expert Specialist
Jaye,

One partial solution is to use something besides a comma to separate first and last name (the comma is used to separate the different entries from the form by default). Even if you don't, you can scroll through the split-generated array by twos:

Expand|Select|Wrap|Line Numbers
  1. clientNames = split(v_name, ",")
  2.  
  3. query = "SELECT * FROM clients WHERE s_name = '" & clientNames(0) &_
  4.    "' AND s_fname = '" & clientNames(1) & "'"
  5. i = 2
  6. do while i< ubound(clientNames)
  7.    query = query & " OR s_name = '" & clientNames(i) & "' AND s_fname = '" &_
  8.       clientNames(i+1) & "'"
  9.    i = 1 + 2
  10. loop
  11.  
This should put in the first set af first and last name, then go through the others only if they exist. Then you can loop through the recordset.

Expand|Select|Wrap|Line Numbers
  1. do until objRS.eof
  2.    'do whatever you need with each record
  3.    objRS.movenext
  4. loop
Let me know if this helps.

Jared
Mar 16 '07 #2
Jaye
11 New Member
Thanks Jared, I will try this and let you know how it goes!
Mar 16 '07 #3
Jaye
11 New Member
Well, I gave it a try and I messed it up somewhere...or probably in a few places. I used the code you suggested and got the following error:

Microsoft VBScript runtime error '800a000d'
Type mismatch

I know this is because I did not declare the variable to hold the resulting recordset(s) as an array. I'm not 100% sure how to do this, but I'll keep trying and if I still can't get it, I'll post my code here. I might have a syntax or logic error in there somewhere that I just cannot see right now. I have to incorporate the date into the SQL query as well. Users can generate a number of letters in a specified period of time - start date to end date. I'll see if I can get the first portion to work, and then I'll put the date range in.

I'll keep you posted. Thanks again for your help.

Jaye
Mar 19 '07 #4
Jaye
11 New Member
Ok I attempted this again. But I wanted to test it a piece at a time. Splitting the array works great and the SQL query is constructed properly - but, if I choose more than two names it takes forever to get the test output, which in this case is just a response.write sqltxt, where sqltxt=my select statement. I am now getting this error:

Active Server Pages error 'ASP 0113'

Script timed out

The maximum amount of time for a script to execute was exceeded. You can change this limit by specifying a new value for the property Server.ScriptTi meout or by changing the value in the IIS administration tools.

-------------------

So I changed the time limit to "server.ScriptT imeout=320", but I'm still getting the error. I haven't even opened a connection to the database at this point. Here is my code so far, it works perfectly if only two names are chosen from the listbox, but the minute three or more are chosen, I get the time out error.

Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim clientNames
  3. clientNames = split(v_fullname, ",")
  4.  
  5. sqltxt1 = "SELECT * FROM clients WHERE (s_name = '" & clientNames(0) &_
  6.    "' AND s_fname = '" & clientNames(1) & "'" & ")"
  7. i = 2
  8. do while i< ubound(clientNames)
  9.    sqltxt1 = sqltxt1 & " OR (s_name = '" & clientNames(i) & "' AND s_fname = '" &_
  10.       clientNames(i+1) & "'" &")"
  11.    i = 1 + 2
  12. Loop
  13.  
  14. response.write sqltxt1 
  15.  
  16. %>
  17.  
Am I doing something wrong that might be causing this to happen? I haven't had the script timeout error before. Any advice would be greatly appreciated!
Mar 19 '07 #5
Jaye
11 New Member

Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim clientNames
  3. clientNames = split(v_fullname, ",")
  4.  
  5. sqltxt1 = "SELECT * FROM clients WHERE (s_name = '" & clientNames(0) &_
  6.    "' AND s_fname = '" & clientNames(1) & "'" & ")"
  7. i = 2
  8. do while i< ubound(clientNames)
  9.    sqltxt1 = sqltxt1 & " OR (s_name = '" & clientNames(i) & "' AND s_fname = '" &_
  10.       clientNames(i+1) & "'" &")"
  11.    i = 1 + 2
  12. Loop
  13.  
  14. response.write sqltxt1 
  15.  
  16. %>
  17.  
OK...just found the error in my code, instead of typing i=i+2, I typed i=1+2. So no more time out error. I will test the recordset portion now. I am thinking of using the GetRows() method. I will post results. Any advice would be appreciated, thanks.
Mar 19 '07 #6
jhardman
3,406 Recognized Expert Specialist
OK...just found the error in my code, instead of typing i=i+2, I typed i=1+2. So no more time out error. I will test the recordset portion now. I am thinking of using the GetRows() method. I will post results. Any advice would be appreciated, thanks.
Sorry, I think I put the 1+2 problem in the code. my mistake.

No, recordset doesn't need to be declared as an array, it needs to be set by the server:
Expand|Select|Wrap|Line Numbers
  1. set objRS = server.createobject("ADODB.recordset")
Are you connecting to the db through ADO?

Jared
Mar 20 '07 #7
Jaye
11 New Member
Hey Jared!

So glad to see you! Thanks for all your help thus far. I am still working on this code. To answer your question, yes, I am using ADO.

I am attempting to use the getString method now, so far it works...but I have to get it to work the way I need it to for these form letters that need to be generated. I tried getRows yesterday, but didn't have any luck with it. There was a syntax error somewhere or something was going wrong, so I decided to try getString instead.

The issue that I am trying to deal with now is using the BETWEEN operator for the dates. I have added the date factor into the SQL statement and it looks something like this now:

Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM clients INNER JOIN products ON clients.product_code=products.product_code WHERE s_name = '" & clientNames(0) &_ "' AND s_fname = '" & clientNames(1) & "'" 
  2. AND (to_date(client.p_date,'MM/DD/YYYY')) BETWEEN (to_date('v_sdate','MM/DD/YYYY') and (to_date('v_fdate','MM/DD/YYYY'))"
Just as an example I've put in the variables - v_sdate and v_fdate. In the actual SQL statement that is constructed, the variables are replaced by the values chosen by user. I'm getting a couple of errors, one is "missing right parentheses", which I think I fixed in the latest version of the SQL statement and the one that I'm getting now is "invalid month". So I'm looking through some articles on to_date and the between operator in ORACLE now to see if I can figure out what I'm doing now.

I haven't tried to manipulate the recordset info yet...sigh...it 's going to be a really late night! But when I have something coded and if it doesn't work, I'll post here and hopefully you will be able to help. Thanks again!
Mar 20 '07 #8
Jaye
11 New Member
Ok...I think I may have straightened out the date issue...but now I am really having trouble trying to figure out how to generate letters for each customer properly. When I query the database, I get multiple entries for a given person, which is ok...but not sure how to manipulate the recordset to generate only one letter per person listing all the products they have bought in a given time period. Here is a sample of the data output from getstring to hopefully explain the problem I'm having a little clearer (I've added headings to make the output more meaningful):

surname, firstname, product#, year, price, warranty
Doe, Jane, HH-3444, 2005, 49.52, N
Pitt, Brad, R-342, 1998, 44.78, N
Pitt, Brad, R-3477, 2001, 300.43, Y
Smith, John, R-333, 1998, 56.40, Y
Smith, John, HH-398, 2000, 107.24, Y
Smith, John, J-9883, 2000, 22.79, N

The letters generated need to list all the products and product details for each person. So, only one letter for John Smith, listing all three products. I'm not sure how to do this when I'm not sure how many entries are in the database for a given person. Should I try an If..then statement while looping through the recordset? I know this is probably really simple...I just can't think right now.

Should I generate the letter based on names picked for the query and then run a second query to get all product info for that name and time range? Am I making any sense? This is probably really easy...I'm just getting caught up with the fact that I need to get both the surname and first name where they are separate fields. Ugh...I'm totally confused...and yet I feel like the answer is right in front of me and I'm making it more complicated than it needs to be...

Any help would be greatly appreciated, thanks!
Apr 3 '07 #9
jhardman
3,406 Recognized Expert Specialist
Should I try an If..then statement while looping through the recordset? I know this is probably really simple...I just can't think right now.

Any help would be greatly appreciated, thanks!
Yes, that is how I would do it:
Expand|Select|Wrap|Line Numbers
  1. dim userName
  2. userName = objRS("surname") & ", " & objRS("firstname")
  3.  
  4. 'start everytihng for the first record, but don't send it
  5. 'because we haven't checked the next record yet, then proceed 
  6.  
  7. objRS.movenext
  8. do until objRS.eof
  9.    if userName = objRS("surname") & ", "& objRS("firstname") then
  10.       'this is the same user, so this record should be appended to 
  11.       'the first message.  If you want, you can leave off user name here
  12.       'since it is a repeat of the above
  13.       objRS.moveNext
  14.    else
  15.       'this is a different user.
  16.       'send the previous message, then continue
  17.       userName = objRS("surname") & ", " & objRS("firstname")
  18.       'start forming the next message, but don't send it
  19.       objRS.moveNext
  20.    end if
  21. loop
  22.  
  23. 'you have one unsent message left, so send it now.
how does that look?

Jared
Apr 3 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

7
5656
by: Gary | last post by:
I haver a table of students - Say 100 students that I need to be able to update/delete and amend. I know I can do this one student at a time which is simple but lets say I want to see all the students on the screen at the same time, modify some, mark some for deletion and even have blank fields at the end to add a new record. In HTML which is generated I label each row and input field with a name/number combination i.e <input type=text...
2
4080
by: Nick | last post by:
Loop to create an array from a dynamic form. I'm having trouble with an application, and I'll try to explain it as clearly as possible: 1. I have a form with two fields, say Apples and Oranges. The user selects from a drop down menu, between 1-10 of each and clicks submit. The resulting page will display a NEW form, with rows and a list of fields for the amount of each items selected.
2
13317
by: Tom Fitzgibbon | last post by:
Stupid question: How do I loop through many text boxes on a page and get values for each box and put into an array? For example document.CreateEvent.test1.value will not take an array value for the object name. Tried to figure out if associative arrays might help but failed miserably. Obviously I can name the text boxes anything, but can't get the values out.
6
4963
by: hoover_richard | last post by:
I am a newbie to C++ and I need help with a simple program I am trying to write. My program is designed to print all of the odd integers contained in an array and output the sum of the odd integers. My code is listed below, but the problem is that my output of sum is wrong. For example, I am using 1347830 for my integers and the program outputs 373 and after adding 373 you should get 13 for the total, but that doesn't happen. Any help...
16
3495
by: Ian Davies | last post by:
Hello Needing help with a suitable solution. I have extracted records into a table under three columns 'category', 'comment' and share (the category column also holds the index no of the record in a hidden field) I wish the user to be able to edit the data in the table, so I have extracted the records into hiddenfield, textareas, dropdown list and checkbox so that they can make changes. I named these elements as arrays and wish to run an...
21
34428
KevinADC
by: KevinADC | last post by:
Note: You may skip to the end of the article if all you want is the perl code. Introduction Uploading files from a local computer to a remote web server has many useful purposes, the most obvious of which is the sharing of files. For example, you upload images to a server to share them with other people over the Internet. Perl comes ready equipped for uploading files via the CGI.pm module, which has long been a core module and allows users...
4
6498
by: TechnoAtif | last post by:
Hi ALL I have entered some array values using checkboxes into mysql database through a form. Next iam creating a searchpage where all those cateogories inserted through checkboxes has to be retrieved using list/menu box. When i check only a single checkbox to insert the checked category ,selecting that category through list box gives out the entire data of the user corresponding to that category. However when i check multiple checkboxes and...
1
4609
by: javediq143 | last post by:
Hi All, This is my first post in this forum. I'm developing a CMS for my latest website. This CMS is also in PhP & MySQL. I'm done with the ADD section where the Admin can INSERT new records in Database but I'm stuck in the EDIT. I'm getting 2 problems over here. Below is the description: 1)The FIRST page will list all the records from the table which Admin can EDIT with CHECKBOX for each record to select. He can select one or more than one...
0
8899
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8757
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9418
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9272
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9196
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9129
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4524
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3235
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2160
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.