How to filter DataViews?

[Bo Diddly]

This is what I've done so far:

I have a DataBase with:

tblBook
BookID... key
Book... All the book titles of the Bible
tblChapter
ChapterID... key
Chapter... chapter numbers (1 - 150)
tblVerse
VerseID... key
Verse... verse number (1 - 100)
tblVerseText
ID... key
BookID
ChapterID
VerseID
VerseText... Each verse from the Bible, each in its own record

Created a DataAdapter for each table, (when I run the SQL statements within
the configuration wizard, they do what I want them to do):

daBook... SELECT Book,BookID FROM tblBook ORDER BY BookID

daChapter... SELECT DISTINCT tblChapter.Chapter, tblChapter.ChapterID,
tblVerseText.ChapterID AS Expr1, tblVerseText.BookID FROM tblChapter INNER
JOIN tblVerseText ON tblChapter.ChapterID = tblVerseText.ChapterID WHERE
(tblVerseText.BookID = ?) ORDER BY tblChapter.ChapterID

daVerse... SELECT DISTINCT tblVerse.Verse, tblVerse.VerseID,
tblVerseText.ChapterID, tblVerseText.VerseID AS Expr1 FROM tblVerse INNER
JOIN tblVerseText ON tblVerse.VerseID = tblVerseText.VerseID WHERE
(tblVerseText.ChapterID = ?) ORDER BY tblVerse.VerseID

daText... SELECT VerseText, ID, VerseID FROM tblVerseText WHERE (VerseID
= ?) ORDER BY ID

Created a DataView for each DataSet

dvBook
dvChapter
dvVerse
dvText

The Controls on the form are bound to the DataViews:

cmbBook > dvBook
cmbChapter > dvChapter
cmbVerse > dvVerse
lblText > dvText

I need the item that is selected in each ComboBox to filter the choices in
the subsequent ComboBoxes.

How do I write the statement in the SelectedItemChanged event to take the
value displayed and pass it's corresponding key to filter the next ComboBox?

Thanks in advance,

Gary

 

[Bernie Yaeger]

Hi Gary,

You'll probably want to use the dataview findrows method, which will filter
the dataview on the argument to the findrows method, returning an array of
filtered rows. Then you'll have to empty the 'child' comboboxes, loop
through the filtered dataview and reload with the current appropriate cols
in the dataview rows array.

HTH,

Bernie Yaeger

 

[Cor]

Hi Bo,

Please stay with the original thread, Bernie did not know you had already
answers from OHM and me.

I think there are two methods to reach your goal. I take the one that I
think is the best. (The other is with datarelations but I think that will
consuming a lot of initial useless datatransport)

So my even simplest approach. (When you use OleDB than read OleDB where is
SQL)

You have to load your comboboxen initinal, the first one with all books, I
think and then the other ones with a dataset of the first rowvalue of each
higher combobox and in the textbox data that you get with the SQLdatareader
from the first rowvalue from the third combobox . Using SQLdatareader
methode for this gives you the less datatransport for this.

For your select see this page for the datareader (it is for stored
procedures but look for the parameters)

http://msdn.microsoft.com/library/d...tml/cpconusingstoredprocedureswithcommand.asp

IWhen the index changes from the lowest combobox, you can read your text by
again that sqlreader I think, because that will give you the less useless
datatransport.

When the index changes from the middlest combobox, you can refresh your
dataset. For that you can use the parameter command from the datadapter,
here is a link how to do that.

http://msdn.microsoft.com/library/d...html/vbconparametersindataadaptercommands.asp

For the highest combobox it is the same as for the middle one.

And do not forget when you use the event selected index change to prevent it
to do something while initializing because that throws an event with every
change in the combobox.

I hope this helps?

Copr

 

[Bo Diddly]

Bernie,

Thanks, I'll look into that as well.

Gary

 

[Bo Diddly]

Cor,

Yes, sorry about creating a new thread, I hit the wrong button!

Thanks for the links, I'll check them out.

If I wasn't so busy with other, (paying), things, I could concentrate on
this project a little more.
Trying to learn how to manipulate data and still do my regular job is eating
up a lot of my time.

Thanks for the help, I'll keep you posted,

Gary