ADO.net: 3 Data Set Questions (Issues)

[Mr. B]

I'm updating my current VB.et application. I currently reads about 6 tables
from an MS Data base. I've created a new MDB file which combines the 6 tables
into 1 table. I'm doing this because I know I'll speed up my application
loading time by more than 10 seconds (out of about 20).

I've run across three situations in which I'm stuck at:

Issue One:

If my largest Column has (say) 200 lines, other columns will have less data
lines. So say Column 1 has 50 lines of data, column 2 has 200 lines and
column 3 has 150 lines. Then column 1 and 3 will have lines containing
nothing (ie null or blank lines).

How to I test for a 'null' in a FOR statement? This is so that I can tell
when I've hit the end of my data for that column?
Issue Two:

For the above, How can I get rid of any blank lines when I bind the Datasource
to a ComboBox pull down? Column 3 will have 50 lines of blank. So if I sort
a ComboBox, the first 50 lines are blank! :(
Issue Three:

I've several ComboBox pulldowns which are bind to the same data set. But if I
change one, then the all change! How can I control them independantly?

For example, if I have 6 ComboBoxes. They are all bound to the same Data
Source, but each one is bound to a different Member of that Data Source.

So say I want to have 1 and 2 related (one changes, so does the other)... then
3 and 4 paired together similar as 1 and 2, and 5 and 6 completely independant
from all the others?

Mucho Appreciated!

Bruce

[Earl]

IsDbNull and use a DataView for each combo

"Mr. B" <Us**@NoWhere.com> wrote in message
news:ru********************************@4ax.com...

I'm updating my current VB.et application. I currently reads about 6 tables from an MS Data base. I've created a new MDB file which combines the 6 tables into 1 table. I'm doing this because I know I'll speed up my application
loading time by more than 10 seconds (out of about 20).

I've run across three situations in which I'm stuck at:

Issue One:

If my largest Column has (say) 200 lines, other columns will have less data lines. So say Column 1 has 50 lines of data, column 2 has 200 lines and
column 3 has 150 lines. Then column 1 and 3 will have lines containing
nothing (ie null or blank lines).

How to I test for a 'null' in a FOR statement? This is so that I can tell
when I've hit the end of my data for that column?
Issue Two:

For the above, How can I get rid of any blank lines when I bind the Datasource to a ComboBox pull down? Column 3 will have 50 lines of blank. So if I sort a ComboBox, the first 50 lines are blank! :(
Issue Three:

I've several ComboBox pulldowns which are bind to the same data set. But if I change one, then the all change! How can I control them independantly?

For example, if I have 6 ComboBoxes. They are all bound to the same Data
Source, but each one is bound to a different Member of that Data Source.

So say I want to have 1 and 2 related (one changes, so does the other)... then 3 and 4 paired together similar as 1 and 2, and 5 and 6 completely independant from all the others?

Mucho Appreciated!

Bruce

[Mr. B]

With Deft Fingers, "Earl" <br******@newsgroups.nospam> wrote:

IsDbNull and use a DataView for each combo

Excellent... I got the DataView working fine! Just have to work more on the
IsDbNull part. But thanks for pointing me in the right direction!

Regards,

Bruce

[Mr. B]

With Deft Fingers, "Earl" <br******@newsgroups.nospam> wrote:

IsDbNull and use a DataView for each combo
Well... that solved all but Issue Two:
Issue Two:

For the above, How can I get rid of any blank lines when I bind the
Datasource to a ComboBox pull down? Column 3 will have 50 lines of blank. So if I
sort a ComboBox, the first 50 lines are blank! :(

[Earl]

If you are binding to a datasource that has the blanks within, the easiest
way is to prevent the blanks from populating when you initially pull the
data. A lot of different ways of writing your query to do that, but
basically do not return the null cells. If you are using a stored procedure,
one way is to write subqueries for each field to filter out the nulls/empty
cells.

"Mr. B" <Us**@NoWhere.com> wrote in message
news:16********************************@4ax.com...

With Deft Fingers, "Earl" <br******@newsgroups.nospam> wrote:

IsDbNull and use a DataView for each combo

Well... that solved all but Issue Two:

Issue Two:

For the above, How can I get rid of any blank lines when I bind the
Datasource to a ComboBox pull down? Column 3 will have 50 lines of blank. So if Isort a ComboBox, the first 50 lines are blank! :(

[Mr. B]

With Deft Fingers, "Earl" <br******@newsgroups.nospam> wrote:

If you are binding to a datasource that has the blanks within, the easiest
way is to prevent the blanks from populating when you initially pull the
data. A lot of different ways of writing your query to do that, but
basically do not return the null cells. If you are using a stored procedure,
one way is to write subqueries for each field to filter out the nulls/empty
cells.

hmm... not 100% I'm sure what you mean... but I'll try playing with Filters
and see where I go/get. I don't recall seeing anything on this in my books...
but I'll look.

Thanks (again)!

Bruce

[Earl]

The datasource contains the "blanks" (empty string). The easiest way to deal
with that is to prevent it at the time you read in the data from the
database. Alternatively, you have to filter that out of your dataview. Take
a look at this post:

http://groups.google.com/groups?hl=e...amework.adonet

"Mr. B" <Us**@NoWhere.com> wrote in message
news:kv********************************@4ax.com...

With Deft Fingers, "Earl" <br******@newsgroups.nospam> wrote:

If you are binding to a datasource that has the blanks within, the easiestway is to prevent the blanks from populating when you initially pull the
data. A lot of different ways of writing your query to do that, but
basically do not return the null cells. If you are using a stored procedure,one way is to write subqueries for each field to filter out the nulls/emptycells.
hmm... not 100% I'm sure what you mean... but I'll try playing with

Filters and see where I go/get. I don't recall seeing anything on this in my books... but I'll look.

Thanks (again)!

Bruce

[Mr. B]

With Deft Fingers, "Earl" <br******@newsgroups.nospam> wrote:

The datasource contains the "blanks" (empty string). The easiest way to deal
with that is to prevent it at the time you read in the data from the
database. Alternatively, you have to filter that out of your dataview. Take
a look at this post:

http://groups.google.com/groups?hl=e...amework.adonet

Easier said than done (:

I looked at your example from above... but I dont' see how I can apply it :(

I've spent the last many hours searching the web on filtering... but they all
give great fitering information... except how to get Rid of Null values :(

One that looked promising was:

If you want to filter rows with null values, you must first convert the null
values to something such as a string:

dv.RowFilter = "Isnull(Col1,'Null') = 'Null'"

But that too didn't do too much... oh well.. might as well fall back and throw
the info into a Listbox, delete the blanks and then fire the info to my
Comboboxes... A crappy way to do it, but at least I know how to do that.

This just seems way over my head (which is hurting now) (:

Thanks anyways...

Bruce

[Mr. B]

With Deft Fingers, "Earl" <br******@newsgroups.nospam> wrote:

The datasource contains the "blanks" (empty string). The easiest way to deal
with that is to prevent it at the time you read in the data from the
database. Alternatively, you have to filter that out of your dataview. Take
a look at this post:

Well... I think I got it...

I found this posting:

http://www.akadia.com/services/dotnet_filter_sort.html

It said: To return only those columns with null values, use the following
expression:

"Isnull(Col1,'Null Column') = 'Null Column'"

.... So I just turned the Equal sign to Inequal (<>)... and it looks like it
solved my problem!

All the references to NULL's are no longer there in my pulldows! Just the
hard data!!!

My full code for one combo (cmbUserID) is as follows (where 'Employee' is the
column in the Table TSUpdate):

Dim usr1 As DataView = New DataView(DsTSUpdate.Tables("TSUpdate"))
usr1.RowFilter = "Isnull(Employee,'Null Column') <> 'Null Column'"
cmbUserID.DataSource = usr1
cmbUserID.DisplayMember = "Employee"
Regards,

Bruce