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!

[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.