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

[Jaye]

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:

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:

<% 
Response.write "(" &v_name& ")"
%>
 

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!

[jhardman]

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:

clientNames = split(v_name, ",")
 
query = "SELECT * FROM clients WHERE s_name = '" & clientNames(0) &_
   "' AND s_fname = '" & clientNames(1) & "'"
i = 2
do while i< ubound(clientNames)
   query = query & " OR s_name = '" & clientNames(i) & "' AND s_fname = '" &_
      clientNames(i+1) & "'"
   i = 1 + 2
loop
 

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.

do until objRS.eof
   'do whatever you need with each record
   objRS.movenext
loop

Let me know if this helps.

Jared

[Jaye]

Thanks Jared, I will try this and let you know how it goes!

[Jaye]

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

[Jaye]

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.ScriptTimeout or by changing the value in the IIS administration tools.

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

So I changed the time limit to "server.ScriptTimeout=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.

<%
Dim clientNames
clientNames = split(v_fullname, ",")
 
sqltxt1 = "SELECT * FROM clients WHERE (s_name = '" & clientNames(0) &_
   "' AND s_fname = '" & clientNames(1) & "'" & ")"
i = 2
do while i< ubound(clientNames)
   sqltxt1 = sqltxt1 & " OR (s_name = '" & clientNames(i) & "' AND s_fname = '" &_
      clientNames(i+1) & "'" &")"
   i = 1 + 2
Loop
 
response.write sqltxt1 
 
%>
 

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!

[Jaye]

<%
Dim clientNames
clientNames = split(v_fullname, ",")
 
sqltxt1 = "SELECT * FROM clients WHERE (s_name = '" & clientNames(0) &_
   "' AND s_fname = '" & clientNames(1) & "'" & ")"
i = 2
do while i< ubound(clientNames)
   sqltxt1 = sqltxt1 & " OR (s_name = '" & clientNames(i) & "' AND s_fname = '" &_
      clientNames(i+1) & "'" &")"
   i = 1 + 2
Loop
 
response.write sqltxt1 
 
%>
 

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.

[jhardman]

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:

set objRS = server.createobject("ADODB.recordset")

Are you connecting to the db through ADO?

Jared

[Jaye]

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:

"SELECT * FROM clients INNER JOIN products ON clients.product_code=products.product_code WHERE s_name = '" & clientNames(0) &_ "' AND s_fname = '" & clientNames(1) & "'" 
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!

[Jaye]

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!

[jhardman]

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:

dim userName
userName = objRS("surname") & ", " & objRS("firstname")
 
'start everytihng for the first record, but don't send it
'because we haven't checked the next record yet, then proceed 
 
objRS.movenext
do until objRS.eof
   if userName = objRS("surname") & ", "& objRS("firstname") then
      'this is the same user, so this record should be appended to 
      'the first message.  If you want, you can leave off user name here
      'since it is a repeat of the above
      objRS.moveNext
   else
      'this is a different user.
      'send the previous message, then continue
      userName = objRS("surname") & ", " & objRS("firstname")
      'start forming the next message, but don't send it
      objRS.moveNext
   end if
loop
 
'you have one unsent message left, so send it now.

how does that look?

Jared

[Jaye]

That looks good. I have been working on it for the past couple of days but with numerous errors. I'm trying to break it down and take it piece by piece. I'm not sure how I'm going to append the data to the previous record if the clientname is the same. I'll work on it some more and post my code. Thanks again for your help.