Nested OleDBReaders ??

[feurich]

Hi there,

 

I'm writing app that reads through a OleDBReader with

while DBIndexSet.read

End While

But within this loop I have to get information from an other table. So I created an other OleDBReader within the first one, the error I get makes me know this methode is WRONG but how should I go about it to get this running??

I'm not realy fluent in ADO.NET..as you may alrady have noticed

A confused Newcomer..:confused:

[XyBoy]

Could you give details about the error you're having ?

I believe you cannot have several Readers open at the same time on the same DB Connection. The first reader must be closed before the other to be open. So you have two options :

1. Change your script so that the first Reader's data is loaded in an in-memory structure, like an Array or a Collection. Then open your second Reader and retrieve the data. It would give something like this :

int i = 0;

while (DBIndexSet.Read) {

yourLocalStructure[i++]["aFieldName"] = DBIndexSet["aFieldName"]; // (find a correct syntax for the local structure you are using)

}

DBIndexSet.Close (); // close the first reader

for (i=0; i <= yourLocalStructure.Length ; i++) {

// open your secondReader

while (secondReader.Read) {

...

}

// don't forget to close your second Reader each time, if you have to reopen it with a different query for each record of yourLocalStructure !

}

 

2. Change your SQL query so that it retrieves all the data you want. It depends on what you are trying to do, but usually this kind of problem can be solved with a join.

 

Hope this helps !

Olivier.

[feurich]

Your wright

 

I think your wright about the connection being able to handle only 1 datareader. For details see attached file.

I have 3 tables in the database and there is already a join on 2 of them. I know you can have more releations in a database but how do you write a query to get the info out.??

datareadererror.bmp

[XyBoy]

Yes, your error message confirms what I said.

 

If you give me more information about your datamodel (the structure of the 3 tables you are querying), maybe I can help you. 3-tables joins are not especially difficult, but it might not always make sense (let's see the datamodel to decide).

 

Or try the 1st method, loading the content of the first reader into an Array or a Collection.

 

Can you describe your tables, and the 2 queries you are trying to use in your DataReaders (with the join) ?

[feurich]

Database

 

Should I post the databse ??

[XyBoy]

Well, no, just describe the tables, or at least show us the SQL queries you are trying to use in your 2 DataReaders... (give us the code you are trying to run, so we can also see the "linking" you want between the two).

Don't send your DB, since I don't have Access here. A good description should be enough ;-)

[feurich]

SourceCode

 

I'll try to discribe the database.

3 tables IndexSet_Table, Index_Table, idxVal_Table.

 

IndexSet_Table contains Unique_ID, Page_name, Page_Indexes

 

Index_table contains Index_Name, Index_Label, Index_Type, Index_Value(I don't need this row), Required, Index_ID

 

IdxVal_Table contains Unique_id(I don't know why), Index_ID, idx_Val.

The relations are between Indexset_Table.Unique_ID and Index_Table.Unique_ID and between..Index_Table.Index_ID and idxVal_Table.index_ID

 

Hope this is enough to see what's going on?

 

GreetZ,

 

See the attached file for the code i'm using.

frmmain.vb

[XyBoy]

Ok, so first, here is what I understand from reading your code (a small description for those who don't want to open it) :

- the "IndexSet" table describes some kind of "page"

- the "Index" table describes the items ("indices") the page contains.

 

When loading your form, you add a control per "item" for the selected "page". Each control can be one of the following, depending on the "Index_Type" field of the "Index" table :

- TextBox (IndexType="TB") : a simple TextBox is added with just a Label in front of it

- DateTimePicker (IndexType="DB") : a dropdown calendar is added with the same label in front of it.

- ComboBox (IndexType="LB") : a comboBox is added AND filled with values for the user to select.

 

These values come from the third table, IdxVal.

 

So :

 

1. It would not be wise to use a join, because IdxVal contain data only for some of the records of Index (the ones with IndexType="LB").

 

2. You can load the data in two times, like this :

- load the content of the first Reader (description of each item) into an Array. This array must have one row per item to display, and on column per "useful" field ("Index_Type", "Index_Label", "Index_Id").

- then close your Reader and run your code browsing your Array. You open a Reader to read the IdxVal table only when the Index_Type is "LB".

 

3. OR (and this is the solution I would use if the "IdxVal" table is not too big), load the Values into a DataTable first, and then use the Reader to get each Item, like this :

- Use a DataAdapter to Fill() a DataTable with the content of the IdxVal table.

- Open the Reader with your join query describing each Item.

- For each Item, do exactly as you are doing now, but instead of retrieving the IdxVal data with the reader, just do

foreach (DataRow row in IdxValDataTable.SelectRows("Index_ID=" + g_DBIndex.Item("Index_ID") ) ) {

cboIndexSet.Items.Add(row.items("idx_val"));

}

 

Simple, isn't it ?

The big advantage of this solution is that you retrieve the IdxVal table only once, so it is better if several ComboBoxes use the same data (which also means "same Index_ID for 2 items", I don't know if it is possible with your current datamodel. Think about it, though, it might be interesting to change it...)

 

Hope this helps. Tell me if you need more details....

 

Olivier.

[feurich]

Your not a newcomer :-)

 

So this means that if the database gets records added to it the datatable needs to be updated.

I say this because there is also a Administration module present in the project that lets the user create pages end indices and indexvalues. So after the database is updated the datatable should also be updated...

 

Do you have some code on creating a datatable ??

 

Thanks for the info.

Actually the way I realy want to do it butI don't have the knowledge Yet is by creating a class that handles al the data in de database. Do you have some guidelines on that to ???

 

 

:cool:

Edited October 29, 2003 by feurich

[feurich]

Datatable ??

 

HI,

 

I'm trying to implement your datatble idee. BUt i need some help on the creating of a data table. So far I have..

Dim m_idxDataTable As New DataTable

OleDbDataAdapter1.Fill(m_idxDataTable)

THe OleDB adapter has a query in it tat retrieves all the information out the idxVal_Table.

 

But how do I get to the information in the datatable??

I don't get the selected rows atribute. What am I doing wrong??

 

Cire:confused:

[XyBoy]

I) For the table creation :

your method is right :

yourCommand = new OldDbCommand ("select * from IdxVal;", yourConnection);

yourAdapter = new OleDBDataAdapter();

yourAdapter.SelectCommand = yourCommand;

yourAdapter.Fill (m_idxDataTable);

// here your DataTable contains all the schema informations (columns) + the data itself.

 

II) Accessing the data with your DataTable

1. you can browse the rows like this :

foreach (DataRow row in m_idxDataTable.Rows) {

index_value = row["Idx_Val"]; // for instance.

}

2. If you want to browse a subset of the rows, you can Select them like this (sorry for the mistake : I meant Select(), not SelectRows(), look at the have the exact syntax you can use) :

foreach (DataRow row in m_idxDataTable.Select (" (some filter) ") ) {

Index_value = row["Idx_Val"];

}

3. You can also use a DataView to see a subset of the DataTable. This is especially useful when populating a ComboBox, because you can use it as DataSource :

yourCombo.DataSource = new DataView (m_idxDataTable, " (some filter) ", " (optional sorting criteria) ", DataViewRowState.CurrentRows); // see the doc about the DataView constructors for details.

yourCombo.DisplayMember = "Idx_Val"; // the field of your view to be displayed.

yourCombo.ValueMemeber = "Idx_Val"; // here you would put the field that should be written to the "Index" table. See doc about DataBinding for more details (maybe a some details below ;-) ).

 

III) Updating the DataTable when the data in the DB has changed.

1. First thought, if your config app and your client app are independent, it is not completely dumm to say that the changes made by an administrator would only be visible in the client app when it is restarted. If the two modules are part of the same app (two different windows in the same app instance), then just use the same DataTable for both. The config module would add/delete/modify data in the DataTable, and the client module would only read it. If the two apps have to run on different machines, but you really need to have them synchronized, then user Remoting. That is, you have a single instance of a kind of "server" app that just administers the data (accessing the DB, and providing the data to the "client" applications, and you have "client" applications (the config module and the "client" module) getting the data from the server process (and not from the DB).

 

2. To update the DataTable : just Fill() again your DataTable with your adapter. All the rows of the DB table will be loaded in the DataTable. The existing rows of your DataTable will not be erased, so the standard behavior would be that the same rows will exist several times in your DataTable, UNLESS (that is what you have to do) you have configured the Adapter to read the keys (yourDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey) -> it will recognize that a DB row already exists in the DataTable, and it will update it instead of adding it.

 

III) To Update the Database with data modified in the DataTable.

(that is for your config module)

Use your DataAdapter (again!).

You have to use the same DataAdapter that the one you used to load the data. Normally you would have to write the UpdateCommand of the adapter (like you did for the SelectCommand), but you can use a CommandBuilder to do it for you (check a CommandBuilder exists for OleDb, I know there is one for Odbc). So :

yourDataAdapter = new OleDbDataAdapter();

yourCommand = new OleDbCommand ("select *...", conn);

new CommandBuilder (yourDataAdapter); // a new command builder is being attached to your adapter

yourDataAdpater.SelectCommand = yourCommand; // here you set the SelectCommand for the DataAdapter. The CommandBuilder is informed of this and build the Update, Insert and Delete commands accordingly.

yourDataAdapter.Fill (yourDataTable);

.... your app runs, modifying data in the DataTable ....

yourDataAdapter.Update (yourDataTable); // send the updated data to the DB. Make sure it is the same instance of the DataAdapter as above, and that you don't have used it for filling another table -> the UpdateCommand that the CommandBuilder has set must be accurate !

 

IV) Writing a class that manages all the data for you

Yes, that is a very good idea. Think of it before writing your forms ! But think of your form structure so that you know what you need.

Have a look at databinding to see if you can use it. It is very helpful, but I agree that your "dynamic" layout might not be the easiest to implement with databinding.

The basic idea of binding is that you have a list of records, and that a form displays one of this records at a time. For any control on the form, you tell wich property of the record you want to be displayed. Like this :

Name_TextBox.DataBindings.Add ("Text", yourRecordsList, "Name");

yourRecordsList can be a DataTable or a DataView (to be preferred). The form displays one record (row) of yourRecordList, and automatically set the Text property of your TextBox to have the value of the "Name" field of the displayed record. (If you have programmed with MS Access, the behavior is almost the same).

The problem : you have to identify in your application what would be the RecordList, and what would be a Record.

I can't help you much on this...

But once you have decided what is your Record, and what is your RecordList, you will have to build your DataSet so that it contains a DataTable that corresponds to the so-called RecordList.

 

Good Luck !

Olivier.

[feurich]

Thanks for the very complete information.

 

Hi there,

 

Much thanks for the elaborate explanation it makes a lot very clear. The only thing is that when I code the for next loop, the loops loops twice trhough the idxdatarows. I think this is because there are 2 datarows in the table.

 

For Each idxDataRow In m_idxDataTable.Rows

If CType(g_DBIndex.Item("Index_ID"), String) =_

CType(idxDataRow.Item("index_id"), String) Then_

cbobox.Items.Add(idxDataRow.Item("idx_val"))

End If

Next

 

How can I only loop the datarow("Index_ID") ??

Edited November 3, 2003 by feurich

[XyBoy]

I don't get it, your algorithm should loop through every row of the idxDataTable, and do something (add an item to the combo) for those where "index_id" equals the current g_DBIndex's index_id.

So I can't explain why each row would be produced twice. Make sure you didn't left some code doing the same thing elsewhere.

 

Anyway, if I were you, I would use the following syntax to make it faster and simpler :

for each idxDataRow in m_idxDataTables.Select ("index_id=" + g_DBIndex.Item ("Index_ID"))

cbobox.Items.Add (idxDataRow.Item ("idx_val"))

next

 

Note : I don't know in VB, but in C# you don't need to convert a value to string if you concatenate with a string

Note 2 : I am not sure if you can use it in VB, but to convert anything to string, we use in C# the ToString() method : it exists for all objects, and is to be used like this :

index_id_str = g_DBIndex.Item("Index_ID").ToString ()

 

Hope this helps.

Olivier.