ASP/SQL Question

[artsapimp]

ASP/SQL question -
(Different form from the one mentioned above) The form being used is for a call center which has close to 50 checkboxes of things that could be sold. The idea behind this is for the user to check the box indicating a sale. The data input team will then need a form which will display each sale in a list. I can use IF statements all day and make this work but it doesn't seem like the best way to approach this project. Any other solution or suggestion would be very helpful.

My Assumption (SQL pulls all data where status = 'Open'... then it will only display the field names where rst.field.value = 'True' (Yes/No Field in Access). I am assuming this can be done but I can't find documentation showing me the process.)

Thanks for any help.

Art

[monte96]

Only the checkboxes that are checked will be sent when the form is submitted.

If I understand your question, you want to know how to determine which ones were checked?

I had a similar page where I submitted checkboxes. Put the value of your record ID (primary key) in the value attribute of the checkbox. When it is submitted, only the values that have a checked checkbox will be in the Request.Forms collection.

[artsapimp]

Thanks for the response, but I'm not sure I understand 100% yet.

Are you saying I should have the recordset open just long enough to get the RecordID and then name each checkbox using that RecordID and a unique identifier? Like this...
[/code]
open db...
varRecordID = rst("RecordID")
rst.Close
...
<input type="checkbox" name="<%= varRecordID%>ProductName">Product Name
[/code]

How would I write the code for Request.Form to show everything sent over? I am used to specifying the name of the form object I'm requesting it from.

[monte96]

OK.. I'm assuming that the items that are available for sale are coming out of a database on the page with the form on it.

If your not already reading the primary key for the table, add it in that initial database read.

When you populate the controls on the form, just add the primary key to the value of the checkboxes (They will all have the same name so you can probably do this in a loop. In fact, I recomend it):

<INPUT type=checkbox name=chkSale id=chkSale value="<%=rs.Fields("PrimaryKey")%>>

[artsapimp]

Ok, I understand more now.

The page is actually static so there is not interaction with the database until it's submitted. I can change that so it's not a problem.

Here's where I am so far and please tell me where I'm wrong. If I enter a value of... we'll say 123 is the RecordID... when it's passed to the next page it's then recorded as chkProductName with a value of 123 right? I'm not understanding how that is going to save it to the database any different than it being referenced as a 123. I could be (and I'm sure I am) doing this wrong because I am kind of back where I started where it will still need a large number of If..Then or Select Case statements.

Here is a new suggestion that might be where you were going with this process.

There is a Sale table and an ItemsSold table. When the user submits the form the basic information is sent to the Sale table including name, date, time, phoneNumber, etc. That will create the RecordID which I will save to varPK or varRecordID. It will then run a For..Loop which will grab only the products which were checked and add them 1 record at a time into the ItemsSold table with a relationship back to the varRecordID or varPK.

Am I making this too complicated?

[monte96]

Before I offer any more suggestions, can you be a bit more specific on what your trying to do. I think I may have it confused..

[artsapimp]

The form is used by a call center who's primary goal is to fix customer issues (tech support). They are also required to sell things which sometimes takes too much time to fill out orders and do work other than support. In these situations the reps will simply fill out the customer's information through the browser and click the products they have sold. If it was only 1 product that would be easy, but there are about 50-60 products to choose from which makes it kind of complicated.

I could make the Sale table have each product as a category and fill each box with a True or False but that is not the best way to do it. We have to remember the people retrieving the data don't want to see a list of 49 things the customer didn't buy, just the products they did.

I hope this helps you understand where I'm at with this.

Thanks again for taking time out of your day to help me with this.

[monte96]

Ok.. first of all, the first page should be reading the products from a database. By hard coding all of the items available for sale, you are making the addition of new items unnecessarily more complicated.

Second. If you have the items already in a table, each with their own unique ID, all you have to pass to the next page is the ID of the item sold. To pass only the ID, the checkbox is perfect because ONLY the checkboxes that have been checked will be passed in the form data. So, only the items sold will be passed.

On the page receiving the form data, you can get the IDs that were sold out of the Request.Form collection and do whatever you want with them.

For instance, you could create a customer order table that contained the id for the customer, and the id of any items they purchased.

When opening up that table, simply join the other two tables containing customers and products using the IDs to give your order people the full detail.

[artsapimp]

The reason their not pulling from a DB is because there is different formatting and different validation for each product. If we were selling shoes and basketballs I would definately be using a database to hold the information and simply run through it to produce the page, but it's not that simple. Some of the products are actually services, some are physical items, some are added features to existing products, etc.

I guess I'm not good at explaining it but that's pretty much the same track I was following toward in my last suggestion. I will do it that way.

Thank you very much for your help.