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]

"James" <jr***********@NOSPAMsenior-direct.comNODAMNSPAM> schrieb

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.

--
Armin

http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

[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
"James" <jr***********@NOSPAMsenior-direct.comNODAMNSPAM> wrote in message
news:0b****************************@phx.gbl... 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.

[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

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

[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?

-----Original Message-----
"James" <jr***********@NOSPAMsenior-direct.comNODAMNSPAM> schrieb

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 formicrsoft.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 runtimeinstead 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 alarge number of IDs.

--
Armin

http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

.

[Armin Zingler]

"James" <jr***********@NOSPAMsenior-direct.comNODAMNSPAM> schrieb

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.
--
Armin

http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

[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.

-----Original Message-----
"James" <jr***********@NOSPAMsenior-direct.comNODAMNSPAM> schrieb

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 aDataset?

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 mostcases, but as you saw not in all cases. If you wanna discuss it, the groupmicrosoft.public.dotnet.framework.adonet is the better place.

--
Armin

http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

.

[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" <jr***********@NOSPAMsenior-direct.comNODAMNSPAM> wrote in message
news:00****************************@phx.gbl...

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?

-----Original Message-----
"James" <jr***********@NOSPAMsenior-direct.comNODAMNSPAM>

schrieb

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.

--
Armin

http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

.

[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----------------

-----Original Message-----
James,
A DataSet does not have a select statement. Only Commands have selectstatements. 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, onewith the full select & the Update, Delete & Create commands. Plus a secondone 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 theDataAdapter you use to Update a DataSet.

Hope this helps
Jay

"James" <jr***********@NOSPAMsenior-direct.comNODAMNSPAM> wrote in messagenews:00****************************@phx.gbl...

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?

>-----Original Message-----
>"James" <jrbradyNOSPAM@NOSPAMsenior- direct.comNODAMNSPAM>

schrieb

>> 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.
>
>--
>Armin
>
>http://www.plig.net/nnq/nquote.html
>http://www.netmeister.org/news/learn2quote.html
>
>.
>

.

[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

"James" <jr***********@NOSPAMsenior-direct.comNODAMNSPAM> wrote in message
news:02****************************@phx.gbl...

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----------------

-----Original Message-----
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" <jr***********@NOSPAMsenior-direct.comNODAMNSPAM>

wrote in message

news:00****************************@phx.gbl...

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?
>-----Original Message-----
>"James" <jrbradyNOSPAM@NOSPAMsenior- direct.comNODAMNSPAM> schrieb
>> 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.
>
>--
>Armin
>
>http://www.plig.net/nnq/nquote.html
>http://www.netmeister.org/news/learn2quote.html
>
>.
>

.