ADO.NET + VB.NET = Confusion

[James]

So far, is basically dispise ADO.NET & VB.NET. Here's why:

Too many confusing ways to do simple things. Here are two
examples that maybe someone can help me with:

(1) I have TextBox controls on a form bound to Date fields
in a DataSet. Works great, except that when the TextBox
fields lose focus, when their Text paramter is a Date, the
DataSet (or something) appends the time to it. IOW, I
enter "1/7/2004", move to the next input field, and the
value magically changes itself to "1/7/2004 12:00:00 AM".

(2) I want to use an OleDbParamter to use a SQL "IN"
clause. In other words, sometimes I want to limit the
records on my form to certain Ids, but I want the default
to be all records in the table. The DataAdapter's
SelectCommand is set to "SELECT Id, FirstName FROM
Table". I want to be able to change this to "SELECT Id,
FirstName FROM Table WHERE Id IN (1, 4, 5)" (or
whatever). How can I do this?

Please help.

 

[Armin Zingler]

So far, is basically dispise ADO.NET & VB.NET. Here's why:

Too many confusing ways to do simple things. Here are two
examples that maybe someone can help me with:

(1) I have TextBox controls on a form bound to Date fields
in a DataSet. Works great, except that when the TextBox
fields lose focus, when their Text paramter is a Date, the
DataSet (or something) appends the time to it. IOW, I
enter "1/7/2004", move to the next input field, and the
value magically changes itself to "1/7/2004 12:00:00 AM".

I haven't used databinding but I think you can handle the
System.Windows.Forms.Binding.Format event to get the right formatting.

(2) I want to use an OleDbParamter to use a SQL "IN"
clause. In other words, sometimes I want to limit the
records on my form to certain Ids, but I want the default
to be all records in the table. The DataAdapter's
SelectCommand is set to "SELECT Id, FirstName FROM
Table". I want to be able to change this to "SELECT Id,
FirstName FROM Table WHERE Id IN (1, 4, 5)" (or
whatever). How can I do this?

I don't know if this is the exact syntax (because it's a question for
micrsoft.public.dotnet.framework.adonet):

SELECT Id, FirstName FROM Table WHERE Id IN (@id1, @id2, @id3)

If the number of parameters varies, you have to build the select at runtime
instead of using parameters.

Or without parameters: "...IN (Select Id from temptable)". Table 'temptable'
must be filled before. This is usually only used whenever there can be a
large number of IDs.

 

[Jay B. Harlow [MVP - Outlook]]

James,

(1) I have TextBox controls on a form bound to Date fields
in a DataSet. Works great, except that when the TextBox

This is a side effect of having a single type that holds both Times & Dates.

As Armin suggested, I would look at the Format event to control this.

(2) I want to use an OleDbParamter to use a SQL "IN"
clause. In other words, sometimes I want to limit the

I would define two data adapters. One that does all and one that limits.
Alternatively I would define a single stored procedure that was able to do
either, based on its input parameters.

If you don't have it, I would recommend David Sceppa's book "Microsoft
ADO.NET - Core Reference" from MS Press, it is a very good tutorial on using
ADO.NET and also a good desk reference once you know ADO.NET.

Hope this helps
Jay

 

[Cor]

Hi James,

Dates are no simple things in Dotnet.

If you write 1/7/2004 it is for me the first day of july in the year 2004.

While for some others it is the seventh day of january also in 2004

In the EU we use mostly a 24hours clock when official writing it

My 2Eurocents

Cor

 

[James]

Thanks, I'll try the Binding.Format thing.

About the parameters, though, if I understand you
correctly, there's no way to change the SELECT statement
on an existing DataSet, is that right? If so, this is far
from an improvement over ADODB, don't you think?

 

[Armin Zingler]

Thanks, I'll try the Binding.Format thing.

About the parameters, though, if I understand you
correctly, there's no way to change the SELECT statement
on an existing DataSet, is that right?

You mean on an existing OleDBCommand? Or you wanna execute an SQL against a
Dataset?

If so, this is far
from an improvement over ADODB, don't you think?

Well, in ADODB, you had no parameters at all. Now you can use them in most
cases, but as you saw not in all cases. If you wanna discuss it, the group
microsoft.public.dotnet.framework.adonet is the better place.

 

[James]

FWIW, I found/figured out how to do this:

---------------START CODE SAMPLE----------------
strSQL = "SELECT * FROM TableName WHERE Id IN (1, 4, 7)"
Me.dsDataSetName.Clear()
Me.dptAdapterName.SelectCommand = _
New System.Data.OleDb.OleDbCommand(strSQL)
Me.dptAdapterName.SelectCommand.Connection =
conConnectionName
Me.dptAdapterName.Fill(Me.dsDataSetName, "TableName")
---------------END CODE SAMPLE----------------

Works great. Thanks for your responses.

 

[Jay B. Harlow [MVP - Outlook]]

James,
A DataSet does not have a select statement. Only Commands have select
statements. A DataAdapter can have a SelectCommand, plus an Update, Delete &
Create command.

You can change the text of the SelectCommand associated with a DataAdapter &
then repopulate the Parameters. However I have to seriously ask why?

It seems to be more work, when IMHO its easier to have two DataAdapters, one
with the full select & the Update, Delete & Create commands. Plus a second
one with just the partial select. Actually you could have 3 DataAdapters.
One with full select, one with partial select, then one for Update, Delete &
Create.

Remember the DataAdapter you use to Fill a DataSet does not need to be the
DataAdapter you use to Update a DataSet.

Hope this helps
Jay

 

[James]

I'm doing this because I have form controls bound to it.
If I had more than one DataAdapter, I'd hve to reboind all
my controls every time I wanted to change the underlying
dataset. That would be insane.

I DID figure out how to do this, BTW. I posted it
yesterday, but here it is again (it's a pretty useful
thing to know how to do:

---------------START CODE SAMPLE----------------
strSQL = "SELECT * FROM TableName WHERE Id IN (1, 4, 7)"
Me.dsDataSetName.Clear()
Me.dptAdapterName.SelectCommand = _
New System.Data.OleDb.OleDbCommand(strSQL)
Me.dptAdapterName.SelectCommand.Connection = _
conConnectionName
Me.dptAdapterName.Fill(Me.dsDataSetName, "TableName")
---------------END CODE SAMPLE----------------

 

[Jay B. Harlow [MVP - Outlook]]

James,
Are these custom controls?

As all the normal form controls bind to a DataView (over a DataTable,
contained in a DataSet). You do not bind to a DataAdapter, you only use the
DataAdapter to fill the DataSet, You can fill a single DataSet from any
number of DataAdapters without rebinding! You can fill a single DataTable
from any number of DataAdapters again without rebinding! As long as the
various DataAdapters have a select command that return compatible columns.

I did not say change the DataSet, I told you to use a different DataAdapter
to fill the DataSet!

I saw your post yesterday, I will continue to recommend multiple
DataAdapters! As IMHO it is the "cleaner" solution. However if your solution
works for you, go for it! I'm sure there is at least a third viable method
you can use to accomplish the same thing!

In case you don't have it, you may want to pick up David Sceppa's book
"Microsoft ADO.NET - Core Reference" from MS Press. It fully explains the
difference between binding to a DataSet and using different DataAdapters to
populate the DataSet! Also David's book is a good tutorial on ADO.NET, plus
a good desk reference once you know ADO.NET.

I hope you realize when I say use two DataAdapters, I mean populate the same
DataTable with two different DataAdapters, do not use two DataAdapters to
populate two DataTables! Otherwise as you say "that would be insane" ;-)

Hope this helps
Jay