search using range by two cbs, varied by rbs

[lunaclover]

Okay I'm having a hard time even describing my problem (as indicated in the subject line above), so here it goes.

For some background:
I have a datagrid displaying many columns dependent on the selections made in comboboxes right above it. Then I write
SqlDataAdapter3.SelectCommand.Parameters("@param1").Value = cb1.Text
for each combobox. There are a total of 12 constraints (the combobox selections).

I have two comboboxes, one is called 'Min' and one is called 'Max'. They are pulling from the db using the same sqlserveradapters pointed at the same column, but with two different datasets (one for min and one for max). When running, you can select from 0 to 16 from both boxes. I want it to pull a range (e.g. Min is set to 8 and max is set to 12, it will pull all records where columnA is between 8 and 12). This is tricky enough if you're me and just beginning. But it gets even trickier - I want the user to be able to select between two radio buttons, rb1 or rb2. If the user selects rb1, I want the data to pull from columnA, populate the two combboxes with the appropriate data, have the user select one from each cb, and when the user pushes the button, the datagrid displays only records that fall between the user-specified 'Min' and 'Max'. If the user selects rb2, I want the comboboxes to change columns (i.e. use a different sql data adapter configured to column2), display these numbers from this column that the user can choose from, and then when the almighty button is hit, display the results that fall between this min and max within this column.

Please tell me that makes sense. I would think this would be an easy thing to find but maybe I am using the wrong search words. Regardless, I haven't had any luck finding it so I thought I would post it. I just want to know how to start.. anything anybody could offer would be great!
For example, can you even connect one combobox to two different sql server adapters and change it depending on a radiobutton selection? Where should the code be to grab records which fall between the indicated range - this is confusing to me. I guess I will need something also that ensures min to always be smaller than max.. now that I think about it. This is seeming too much to ask - does anybody know where I can go to learn something like this?

Thanks again,
lunaclover

 

[SBendBuckeye]

Let's try to break this down a little bit to make it more manageable.

1. Is your code working when you set the constraints based on the 12 comboboxes - are you getting any data, some data, part of the right data, etc

2. Are your min and max comboboxes separate from the initial 12 you refer to or are they part of the 12

3. Do you have access to Sql Server tools like Query Analyzer

Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[lunaclover]

Hi Buckeye - thanks for responding!

To answer your questions (1) The other 12 combo boxes are working, pulling the right data and everything's showing up in the datagrid that should up to this point. (2) These two comboboxes (Min and Max) are new additions, but are pulling from the same db. I guess I only mentioned the other 12 as a 'helpful background' to my program in its entirity.. (3) Yes, I do have access to these tools, but am quite new to them.

The query in the sqldataadapter for the 'Min' and 'Max' dataset is ----SELECT DISTINCT CEILING (columnA) as expr1---- so I can get whole numbers to search with, since the ones in the db are 11.2, 14.2, etc. It works, and I can run the program and select from the combobox 11, 12, 13, 14, etc. All whole numbers. Hopefully this doesn't confuse the issue. Again, just trying to give background. Now I just need to figure out how to search this column using the min and max restraints.

Please let me know if there are any questions I can answer that will help anybody answer my huge one! : )

Thanks again,
luna clover

 

[SBendBuckeye]

Still a little bit confused. It sounds like you create a DataTable in a DataSet using the 12 ComboBoxes as selection criteria.

Do you then create 2 more DataTables based on Min/Max values which are a subset of the original DataTable above or do the Min/Max values get incorporated into the selection criteria before you build the first DataTable?

If they are separate, then will the Min/Max values always be a subset of DataTable1.

For clarity in future discussions, how about calling the first DataTable created with the 12 comboboxes DataTable12 or DT12. If the Min/Max DataTables are separate, how about calling them DataTableMin and DataTableMax or DTMin and DTMax. It might help keep things a little bit clearer.

Have a great day!

j2consulting@yahoo.com

 

[stsuing]

If you use a dataset to pull columnA and ColumnB values right from the start and keep them it 2 tables in the same dataset.

Then it would be a matter of binding the Min and Max comboboxes to the appropriate table when the radiobutton change

 

[lunaclover]

Whoa. You lost me Buckeye. Let's just forget about the 12 anythings, I think they're getting us off-track.

Here we go - I have two columns in the same table, in the same db. One is columnA, one is columnB. I want to be able to use them as a search criteria, to search the database for any records in which this field, ColumnA, fell into that user-specified range.

Say, for example, I am talking about book prices. I want to buy a book from anywhere between 10 to 15 dollars. This columnA is my price column, and I want to build two cbs where when the program is running, I can select 10 in the min box and 15 in the max box, and the dataset will fill with all the books that cost anywhere from $10 to $15. That's all. The two rbs would be equivelent to, say, a tape or a book. So, if the user selected the 'Tape' rb (this would be ColumnB, instead of the 'Book' rb (ColumnA)), and filled in the cbs min and max with $10 and $15 respectively, the program would return all tapes that cost between 10 and 15 dollars.

Hopefully this makes things clearer.

StSuing, yes, this is it. How would I go about switching it from one column to the next - can't a control only be attached to one table.column? How would you get it to switch? And then once this was accomplished, well I'm just not sure how to make the code for searching for a range like this within the same column.. where would I put it, in the background in VB or configure it in the sqldataadapater? I'm confused on how I should approach this problem.

Thanks to you both and I hope this clears things up! I was overwhelmed yesterday when I wrote it, so I'm sorry for the confusing information overload.

LunaClover

 

[stsuing]

Your problem is still a bit unclear

So the min and max dollar don't change programmatically?

I think what your looking for is the Dataview class. You can use the filter property to limit the records to those within the range you are looking for.

Something like
dim dv as DataView = _dataSetWithTableOfTapesAndBooks.TableOfTapesAndBooks

If rb1.checked then
'Filter rows on column A
dv.Filter = "colA > " + cb1.value + " colA < " + cb1.value
else
'Filter Rows on column b
dv.Filter = "colB > " + cb1.value + " colB < " + cb1.value

end if

This is just pseudocode. Is this what your looking for?

 

[lunaclover]

Hi Stsuing! I think that is just what I need. I am new to programming and don't know any code regarding interacting with dbs, but I was really just looking for the logic I would have to use, and this really helps!

I can't say yet if I can make it work, as it's Saturday night and I won't be able to try it out until Monday morning at work. I will let you know, and thank you again so much.

Lunaclover