*very* basic question about design of ado.net

[Steve]

Sorry in advance for my ignorance. Any help would sure be
appreciated. I'm writing a fairly simple application with VB.Net and
am obviously a bit of a newbie. This application will be used by 1, 2
or at most 3 people concurrently and I'm using Access 2003 for my data
source. We are not dealing with a large amount of data (5 or 6
tables, for a total of maybe 3,000 records - one table having the
majority of that). This application is using a fairly simple form,
but upon leaving certain text boxes, I want to fill in some data. For
instance, I might have one field that is for a competition number -
when I leave it, I want to populate a description. The next field
might be a competitor number and when I leave it, I want to populate a
field with the competitor name.

Enough of the background - I created a module to open my connection
and a function within that to handle my DataReader. In that module I
have:

Imports System.Data
Imports System.data.oledb
Imports System.Data.sqlclient

Module Main
Public strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=f:\mydatabase.mdb"
Public cn As New OleDbConnection(strConn)

Function ExecuteReader(ByVal sSQLString As String) As
OleDb.OleDbDataReader

Dim dr As OleDbDataReader
Dim cmd As OleDbCommand = New OleDbCommand(sSQLString, cn)
Try
If cn.State = ConnectionState.Closed Then cn.Open()
dr = cmd.ExecuteReader()
cmd.Dispose()
Catch ex As OleDbException
MsgBox(ex.Message, MsgBoxStyle.Exclamation)
End Try
Return dr
End Function

End Module

And that's pretty much it so far. In my main code I have:

Private Sub txtCompNum_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompNum.LostFocus
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM competition where compnum =
" & "'" & txtCompNum.Text & "'")
While dr.Read
txtDance.Text = dr.GetString(2)
End While
dr.Close()
End Sub

Private Sub txtCompetitor_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompetitor.Leave
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM dancer where cardnum = " &
txtCompetitor.Text)
While dr.Read
txtName.Text = dr.GetString(3) & ", " & dr.GetString(4)
End While
dr.Close()
cn.Close()
End Sub

These seem to work well and fast. But there are a few questions:

1. Is there a "right" way to structure creating the different
components necessary to populate my form and if so, is mine along the
right lines so far?

2. Should I use "Leave" or "Lost_Focus" for my text boxes? I used to
use "Lost_Focus" with VB6, but was wondering if there was a difference
or if one was preferred.

3. If the two datareaders above only return one record, is there some
other reader I should use to improve performance?

Any other suggestions would sure be appreciated. I'm a bit
wet-behind-the-ears so I'd love some help.

Thanks!
Steve

[One Handed Man]

> These seem to work well and fast. But there are a few questions:

1. Is there a "right" way to structure creating the different
components necessary to populate my form and if so, is mine along the
right lines so far? A.) Looks ok to me, but different people have different ideas about how to
design a structure.

2. Should I use "Leave" or "Lost_Focus" for my text boxes? I used to
use "Lost_Focus" with VB6, but was wondering if there was a difference
or if one was preferred. A.) Use ( LostFocus )


3. If the two datareaders above only return one record, is there some
other reader I should use to improve performance? A.) Trust me, this is not going to make any difference with one record.
Certainly
not one you could notice.


Any other suggestions would sure be appreciated. I'm a bit
wet-behind-the-ears so I'd love some help.

Thanks!
Steve

[Ivan Weiss]

That looks pretty good, I am also a newbie and know how frustrating
ADO.NET can be. By the way since you are using access you do not need
that third import statement for SQLclient.

Also general question for anyone who might know this. I saw that Steve
called the dispose method of his command object. Is this necessary to
remove the resources or does garbage collection eventually grab it on
its own (in my case I am using a database class not a module). I am
very confused about garbage collection compared to VB6. Also with forms
do we need to call the dispose method or is just close sufficient?

-Ivan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[One Handed Man]

It depends, as a matter of course, it is a good idea to call the dispose method rather than waiting for the system to realise that a form object needs to be marked/deleted.

OHM
"Ivan Weiss" <iv*****@optonline.net> wrote in message news:uj**************@TK2MSFTNGP09.phx.gbl...
That looks pretty good, I am also a newbie and know how frustrating
ADO.NET can be. By the way since you are using access you do not need
that third import statement for SQLclient.

Also general question for anyone who might know this. I saw that Steve
called the dispose method of his command object. Is this necessary to
remove the resources or does garbage collection eventually grab it on
its own (in my case I am using a database class not a module). I am
very confused about garbage collection compared to VB6. Also with forms
do we need to call the dispose method or is just close sufficient?

-Ivan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob]

I'm no guru, but speaking from a few years of experience which began with Access
and VB5...

- Don't use SELECT *

The columns you're reading from the DB should be explicitly enumerated - even if
it's all of them. Cropping the returned columns down to just what you need will
not only save resources, but also tell you exactly what columns are used and
where so you don't have to go looking in your code and hope you got everything
when you have to change a table or a view definition.

- Don't use ordinals to access returned table data

dr.GetString(3), dr.GetString(4)... this is bad. Do you know what "magic
numbers" are? Magic Numbers are numbers that appear in code that, at a glance,
have no discernable meaning. If you want to know what column 4 is you'll have to
look it up. This practice should be avoided to keep your code readable and
maintainable. Use the column names so you don't have to worry about what order
they came in or guess what number gives you what column.

- Just because you're a newbie doesn't mean you should start with Access instead
of SQL Server.

SQL Server is easier - not harder - to use, and far, far more stable and
scalable. If you ever want to increase the number of users for your app, you'll
be moving to it anyway so you should consider using it to begin with. If you
have the $$$, it's totally worth it.

- SQL does not belong in application code

It may not matter with a small app, but if it grows you're going to need a way
to analyze how your app accesses your DB when you need to make changes. This is
difficult if you have SQL in hard code, but simple if it's all in stored
procedures (a feature of SQL Server, these are kept server-side). Not only are
the contents of all stored procedures searchable like other data, but you can
also access table dependency information which will give you a list of
everything you need to check when you need to make changes. Put it another way -
say you need to make a change to a table that accessed from 8 different
locations in your application. Would you want to do a text search through your
code, hope you found all the references, and then recompile your app, or would
you rather just have a list of stored procedures given to you that you can edit
server-side, possibly while your app is live, without your users ever knowing?

Good luck to you and happy coding,
Bob

"Steve" <sf**@rsd-tc.com> wrote in message
news:a7**************************@posting.google.c om...

Sorry in advance for my ignorance. Any help would sure be
appreciated. I'm writing a fairly simple application with VB.Net and
am obviously a bit of a newbie. This application will be used by 1, 2
or at most 3 people concurrently and I'm using Access 2003 for my data
source. We are not dealing with a large amount of data (5 or 6
tables, for a total of maybe 3,000 records - one table having the
majority of that). This application is using a fairly simple form,
but upon leaving certain text boxes, I want to fill in some data. For
instance, I might have one field that is for a competition number -
when I leave it, I want to populate a description. The next field
might be a competitor number and when I leave it, I want to populate a
field with the competitor name.

Enough of the background - I created a module to open my connection
and a function within that to handle my DataReader. In that module I
have:

Imports System.Data
Imports System.data.oledb
Imports System.Data.sqlclient

Module Main
Public strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=f:\mydatabase.mdb"
Public cn As New OleDbConnection(strConn)

Function ExecuteReader(ByVal sSQLString As String) As
OleDb.OleDbDataReader

Dim dr As OleDbDataReader
Dim cmd As OleDbCommand = New OleDbCommand(sSQLString, cn)
Try
If cn.State = ConnectionState.Closed Then cn.Open()
dr = cmd.ExecuteReader()
cmd.Dispose()
Catch ex As OleDbException
MsgBox(ex.Message, MsgBoxStyle.Exclamation)
End Try
Return dr
End Function

End Module

And that's pretty much it so far. In my main code I have:

Private Sub txtCompNum_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompNum.LostFocus
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM competition where compnum =
" & "'" & txtCompNum.Text & "'")
While dr.Read
txtDance.Text = dr.GetString(2)
End While
dr.Close()
End Sub

Private Sub txtCompetitor_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompetitor.Leave
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM dancer where cardnum = " &
txtCompetitor.Text)
While dr.Read
txtName.Text = dr.GetString(3) & ", " & dr.GetString(4)
End While
dr.Close()
cn.Close()
End Sub

These seem to work well and fast. But there are a few questions:

1. Is there a "right" way to structure creating the different
components necessary to populate my form and if so, is mine along the
right lines so far?

2. Should I use "Leave" or "Lost_Focus" for my text boxes? I used to
use "Lost_Focus" with VB6, but was wondering if there was a difference
or if one was preferred.

3. If the two datareaders above only return one record, is there some
other reader I should use to improve performance?

Any other suggestions would sure be appreciated. I'm a bit
wet-behind-the-ears so I'd love some help.

Thanks!
Steve

[Steve Fay]

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jay B. Harlow [MVP - Outlook]]

Bob,

- Don't use ordinals to access returned table data

dr.GetString(3), dr.GetString(4)... this is bad. Do you know what "magic
numbers" are? Magic Numbers are numbers that appear in code that, at a glance, maintainable. Use the column names so you don't have to worry about what order they came in or guess what number gives you what column. This is bad advice in that it will slow down your program! Good advice for
the reason you gave!

Seeing as I normally specify the fields in the select itself (your first
item) I normally setup an Enum with the fields in the correct order, then
use the enum to retrieve the values. Using the Enum helps avoid the "Magic
number" problem. Sure some may say I just shifted the magic number problem
;-) If I change the query, I change the Enum. Alternative I will lookup the
ordininal values (based on the names as you suggest) outside the loop, then
use these ordinals inside the loop.

However in .NET when using a DataAdapter & DataTables & DataSets this item
is moot. Of course if you are not using DataSets & DataTables, then this
item definitely makes a difference.

Hope this helps
Jay
"Bob" <no*****@nospam.net> wrote in message
news:uq**************@TK2MSFTNGP12.phx.gbl... I'm no guru, but speaking from a few years of experience which began with Access and VB5...

- Don't use SELECT *

The columns you're reading from the DB should be explicitly enumerated - even if it's all of them. Cropping the returned columns down to just what you need will not only save resources, but also tell you exactly what columns are used and where so you don't have to go looking in your code and hope you got everything when you have to change a table or a view definition.

- Don't use ordinals to access returned table data

dr.GetString(3), dr.GetString(4)... this is bad. Do you know what "magic
numbers" are? Magic Numbers are numbers that appear in code that, at a glance, have no discernable meaning. If you want to know what column 4 is you'll have to look it up. This practice should be avoided to keep your code readable and
maintainable. Use the column names so you don't have to worry about what order they came in or guess what number gives you what column.

- Just because you're a newbie doesn't mean you should start with Access instead of SQL Server.

SQL Server is easier - not harder - to use, and far, far more stable and
scalable. If you ever want to increase the number of users for your app, you'll be moving to it anyway so you should consider using it to begin with. If you have the $$$, it's totally worth it.

- SQL does not belong in application code

It may not matter with a small app, but if it grows you're going to need a way to analyze how your app accesses your DB when you need to make changes. This is difficult if you have SQL in hard code, but simple if it's all in stored
procedures (a feature of SQL Server, these are kept server-side). Not only are the contents of all stored procedures searchable like other data, but you can also access table dependency information which will give you a list of
everything you need to check when you need to make changes. Put it another way - say you need to make a change to a table that accessed from 8 different
locations in your application. Would you want to do a text search through your code, hope you found all the references, and then recompile your app, or would you rather just have a list of stored procedures given to you that you can edit server-side, possibly while your app is live, without your users ever knowing?
Good luck to you and happy coding,
Bob

"Steve" <sf**@rsd-tc.com> wrote in message
news:a7**************************@posting.google.c om...

Sorry in advance for my ignorance. Any help would sure be
appreciated. I'm writing a fairly simple application with VB.Net and
am obviously a bit of a newbie. This application will be used by 1, 2
or at most 3 people concurrently and I'm using Access 2003 for my data
source. We are not dealing with a large amount of data (5 or 6
tables, for a total of maybe 3,000 records - one table having the
majority of that). This application is using a fairly simple form,
but upon leaving certain text boxes, I want to fill in some data. For
instance, I might have one field that is for a competition number -
when I leave it, I want to populate a description. The next field
might be a competitor number and when I leave it, I want to populate a
field with the competitor name.

Enough of the background - I created a module to open my connection
and a function within that to handle my DataReader. In that module I
have:

Imports System.Data
Imports System.data.oledb
Imports System.Data.sqlclient

Module Main
Public strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=f:\mydatabase.mdb"
Public cn As New OleDbConnection(strConn)

Function ExecuteReader(ByVal sSQLString As String) As
OleDb.OleDbDataReader

Dim dr As OleDbDataReader
Dim cmd As OleDbCommand = New OleDbCommand(sSQLString, cn)
Try
If cn.State = ConnectionState.Closed Then cn.Open()
dr = cmd.ExecuteReader()
cmd.Dispose()
Catch ex As OleDbException
MsgBox(ex.Message, MsgBoxStyle.Exclamation)
End Try
Return dr
End Function

End Module

And that's pretty much it so far. In my main code I have:

Private Sub txtCompNum_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompNum.LostFocus
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM competition where compnum =
" & "'" & txtCompNum.Text & "'")
While dr.Read
txtDance.Text = dr.GetString(2)
End While
dr.Close()
End Sub

Private Sub txtCompetitor_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompetitor.Leave
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM dancer where cardnum = " &
txtCompetitor.Text)
While dr.Read
txtName.Text = dr.GetString(3) & ", " & dr.GetString(4)
End While
dr.Close()
cn.Close()
End Sub

These seem to work well and fast. But there are a few questions:

1. Is there a "right" way to structure creating the different
components necessary to populate my form and if so, is mine along the
right lines so far?

2. Should I use "Leave" or "Lost_Focus" for my text boxes? I used to
use "Lost_Focus" with VB6, but was wondering if there was a difference
or if one was preferred.

3. If the two datareaders above only return one record, is there some
other reader I should use to improve performance?

Any other suggestions would sure be appreciated. I'm a bit
wet-behind-the-ears so I'd love some help.

Thanks!
Steve

[Steve Fay]

Oops. Weird. Let me try again. Thanks for all the help I've gotten so
far. This is fantastic!

As far as how to access my fields, I read somewhere that I can use the
dr.GetOrdinal("CustomerID") to get the position for the item. This
combines a naming convention that works, plus performance isn't lost.
Ideas?

On another note, I'm having a bit of trouble with form validation:

Private Sub txtCompNum_LostFocus(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompNum.LostFocus
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM competition where compnum = "
& "'" & txtCompNum.Text & "'")
If txtCompNum.Text = "" Then
MessageBox.Show("Cannot be blank")
txtCompNum.Focus()
ElseIf dr Is Nothing Then
MessageBox.Show("Invalid Competition")
txtCompNum.Focus()
Else
While dr.Read
txtDance.Text = dr.GetString(1)
MessageBox.Show("true")
End While
End If
dr.Close()
End Sub

The problem with the above code is primarily with the "If
txtCompNum.Text = ""..." code. It seems to give me the messagebox ok,
but right after that, I don't know if the focus is being givenback to
that text box. I get an error saying the the datareader must be closed
(essentially). I can close it inside the "If" statment, but that's not
really the right way to do this, is it? Suggestions would guarantee a
christmas/hanukkah card to anyone who helps me out! :)

Thanks!
Steve
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bernie Yaeger]

Hi Steve,

Just one point: don't use datareaders. They are fast but they are read only
and forward only, so if you want to do anything with the data, you'll be
crippled. Use datasets/datatables instead. And when you refer to a column
you can refer to it as irow("fname") instead of irow(7). This is supposedly
slower (unless you have a typed dataset - I won't bore you with that) but
it's very effective and much easier to debug.

HTH,

Bernie Yaeger

"Steve" <sf**@rsd-tc.com> wrote in message
news:a7**************************@posting.google.c om...

Sorry in advance for my ignorance. Any help would sure be
appreciated. I'm writing a fairly simple application with VB.Net and
am obviously a bit of a newbie. This application will be used by 1, 2
or at most 3 people concurrently and I'm using Access 2003 for my data
source. We are not dealing with a large amount of data (5 or 6
tables, for a total of maybe 3,000 records - one table having the
majority of that). This application is using a fairly simple form,
but upon leaving certain text boxes, I want to fill in some data. For
instance, I might have one field that is for a competition number -
when I leave it, I want to populate a description. The next field
might be a competitor number and when I leave it, I want to populate a
field with the competitor name.

Enough of the background - I created a module to open my connection
and a function within that to handle my DataReader. In that module I
have:

Imports System.Data
Imports System.data.oledb
Imports System.Data.sqlclient

Module Main
Public strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=f:\mydatabase.mdb"
Public cn As New OleDbConnection(strConn)

Function ExecuteReader(ByVal sSQLString As String) As
OleDb.OleDbDataReader

Dim dr As OleDbDataReader
Dim cmd As OleDbCommand = New OleDbCommand(sSQLString, cn)
Try
If cn.State = ConnectionState.Closed Then cn.Open()
dr = cmd.ExecuteReader()
cmd.Dispose()
Catch ex As OleDbException
MsgBox(ex.Message, MsgBoxStyle.Exclamation)
End Try
Return dr
End Function

End Module

And that's pretty much it so far. In my main code I have:

Private Sub txtCompNum_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompNum.LostFocus
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM competition where compnum =
" & "'" & txtCompNum.Text & "'")
While dr.Read
txtDance.Text = dr.GetString(2)
End While
dr.Close()
End Sub

Private Sub txtCompetitor_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompetitor.Leave
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM dancer where cardnum = " &
txtCompetitor.Text)
While dr.Read
txtName.Text = dr.GetString(3) & ", " & dr.GetString(4)
End While
dr.Close()
cn.Close()
End Sub

These seem to work well and fast. But there are a few questions:

1. Is there a "right" way to structure creating the different
components necessary to populate my form and if so, is mine along the
right lines so far?

2. Should I use "Leave" or "Lost_Focus" for my text boxes? I used to
use "Lost_Focus" with VB6, but was wondering if there was a difference
or if one was preferred.

3. If the two datareaders above only return one record, is there some
other reader I should use to improve performance?

Any other suggestions would sure be appreciated. I'm a bit
wet-behind-the-ears so I'd love some help.

Thanks!
Steve

[Mark Jerde]

Steve Fay wrote:

As far as how to access my fields, I read somewhere that I can use the
dr.GetOrdinal("CustomerID") to get the position for the item. This
combines a naming convention that works, plus performance isn't lost.
Ideas?

Yeah -- let the computer do it. <g> What are those 3 users of the 3000
record DB going to do with the additonal 63 microseconds per week this
optimization MIGHT save them? ;-) Dogma from the 1970's still applies:
"Don't Optimize Abort Routines." Get the app working, use tools to see
where it's too slow if it is, and speed up the slow parts.

I have worked with people ultra concerned about micro efficiency -- ordinals
everywhere; fancy memory tricks, etc. They weren't terribly bothered with
macro items of interest to the customers -- on time delivery, reliability,
upgrades not breaking fielded systems, ease of use, etc. The overall result
is they had the fastest system that ISN'T a major force in the market today!

My advice is you worry about the ordinals after the online help is
integrated... ;-) I'm preaching to myself too. It's a classic codeslinger
occupational hazard: Being easily drawn into interesting but
off-the-critical-path items.

-- Mark

[Light Templar]

Am I correct in saying that the only use for a datareader would be to have
a fast lookup of data for displaying purposes? I'm new with VB .Net.

Just about the time I was getting really good with 6.0... :c)

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:uo**************@TK2MSFTNGP09.phx.gbl...

Hi Steve,

Just one point: don't use datareaders. They are fast but they are read only and forward only, so if you want to do anything with the data, you'll be
crippled. Use datasets/datatables instead. And when you refer to a column you can refer to it as irow("fname") instead of irow(7). This is supposedly slower (unless you have a typed dataset - I won't bore you with that) but
it's very effective and much easier to debug.

HTH,

Bernie Yaeger

"Steve" <sf**@rsd-tc.com> wrote in message
news:a7**************************@posting.google.c om...

Sorry in advance for my ignorance. Any help would sure be
appreciated. I'm writing a fairly simple application with VB.Net and
am obviously a bit of a newbie. This application will be used by 1, 2
or at most 3 people concurrently and I'm using Access 2003 for my data
source. We are not dealing with a large amount of data (5 or 6
tables, for a total of maybe 3,000 records - one table having the
majority of that). This application is using a fairly simple form,
but upon leaving certain text boxes, I want to fill in some data. For
instance, I might have one field that is for a competition number -
when I leave it, I want to populate a description. The next field
might be a competitor number and when I leave it, I want to populate a
field with the competitor name.

Enough of the background - I created a module to open my connection
and a function within that to handle my DataReader. In that module I
have:

Imports System.Data
Imports System.data.oledb
Imports System.Data.sqlclient

Module Main
Public strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=f:\mydatabase.mdb"
Public cn As New OleDbConnection(strConn)

Function ExecuteReader(ByVal sSQLString As String) As
OleDb.OleDbDataReader

Dim dr As OleDbDataReader
Dim cmd As OleDbCommand = New OleDbCommand(sSQLString, cn)
Try
If cn.State = ConnectionState.Closed Then cn.Open()
dr = cmd.ExecuteReader()
cmd.Dispose()
Catch ex As OleDbException
MsgBox(ex.Message, MsgBoxStyle.Exclamation)
End Try
Return dr
End Function

End Module

And that's pretty much it so far. In my main code I have:

Private Sub txtCompNum_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompNum.LostFocus
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM competition where compnum =
" & "'" & txtCompNum.Text & "'")
While dr.Read
txtDance.Text = dr.GetString(2)
End While
dr.Close()
End Sub

Private Sub txtCompetitor_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompetitor.Leave
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM dancer where cardnum = " &
txtCompetitor.Text)
While dr.Read
txtName.Text = dr.GetString(3) & ", " & dr.GetString(4)
End While
dr.Close()
cn.Close()
End Sub

These seem to work well and fast. But there are a few questions:

1. Is there a "right" way to structure creating the different
components necessary to populate my form and if so, is mine along the
right lines so far?

2. Should I use "Leave" or "Lost_Focus" for my text boxes? I used to
use "Lost_Focus" with VB6, but was wondering if there was a difference
or if one was preferred.

3. If the two datareaders above only return one record, is there some
other reader I should use to improve performance?

Any other suggestions would sure be appreciated. I'm a bit
wet-behind-the-ears so I'd love some help.

Thanks!
Steve

[Giorgio Gentili]

Hello and for VB6 users like me and you, the changes to data access also
makes me not understand a bit of how this strange new thing work :-D , if
you wants the short answer it's yes, but you must think how now the things
are maded in dotnet (not needed to do something special, read if you want to
understand better how things are done). Every data access in dot net tries
to be as disconnected as possible, so looking this way, in a certain way,
every way to work with data is disconnected at some level. for example,
dataset object (the other approach to handle data in a more ado recordset
way) keeps "all the data" (i'm not certainly sure maybe just make an
automated look ahead) in the memory and then, when you modifies this info,
it keeps the changes in a datarow and the original in other, then you
finally reject or accept this changes sending the data changed (and that's
the fun part, just the changed data) to the real table minimizing the
overuse (or overusage, i don't know :-D) of opens connections and network
use. So, for example in web apps or other apps that many users will use,
it's often better work directly with datareaders and commands than datasets
because it uses a lot more of memory.

Hope this helps, and not get bored.

PS: Sorry for the English

GiorgioG
"Light Templar" <[e-mail]> escribió en el mensaje
news:#5*************@TK2MSFTNGP09.phx.gbl...

Am I correct in saying that the only use for a datareader would be to have a fast lookup of data for displaying purposes? I'm new with VB .Net.

Just about the time I was getting really good with 6.0... :c)

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:uo**************@TK2MSFTNGP09.phx.gbl...

Hi Steve,

Just one point: don't use datareaders. They are fast but they are read

only

and forward only, so if you want to do anything with the data, you'll be
crippled. Use datasets/datatables instead. And when you refer to a

column

you can refer to it as irow("fname") instead of irow(7). This is

supposedly

slower (unless you have a typed dataset - I won't bore you with that) but it's very effective and much easier to debug.

HTH,

Bernie Yaeger

"Steve" <sf**@rsd-tc.com> wrote in message
news:a7**************************@posting.google.c om...

Sorry in advance for my ignorance. Any help would sure be
appreciated. I'm writing a fairly simple application with VB.Net and
am obviously a bit of a newbie. This application will be used by 1, 2
or at most 3 people concurrently and I'm using Access 2003 for my data
source. We are not dealing with a large amount of data (5 or 6
tables, for a total of maybe 3,000 records - one table having the
majority of that). This application is using a fairly simple form,
but upon leaving certain text boxes, I want to fill in some data. For
instance, I might have one field that is for a competition number -
when I leave it, I want to populate a description. The next field
might be a competitor number and when I leave it, I want to populate a
field with the competitor name.

Enough of the background - I created a module to open my connection
and a function within that to handle my DataReader. In that module I
have:

Imports System.Data
Imports System.data.oledb
Imports System.Data.sqlclient

Module Main
Public strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=f:\mydatabase.mdb"
Public cn As New OleDbConnection(strConn)

Function ExecuteReader(ByVal sSQLString As String) As
OleDb.OleDbDataReader

Dim dr As OleDbDataReader
Dim cmd As OleDbCommand = New OleDbCommand(sSQLString, cn)
Try
If cn.State = ConnectionState.Closed Then cn.Open()
dr = cmd.ExecuteReader()
cmd.Dispose()
Catch ex As OleDbException
MsgBox(ex.Message, MsgBoxStyle.Exclamation)
End Try
Return dr
End Function

End Module

And that's pretty much it so far. In my main code I have:

Private Sub txtCompNum_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompNum.LostFocus
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM competition where compnum =
" & "'" & txtCompNum.Text & "'")
While dr.Read
txtDance.Text = dr.GetString(2)
End While
dr.Close()
End Sub

Private Sub txtCompetitor_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompetitor.Leave
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM dancer where cardnum = " &
txtCompetitor.Text)
While dr.Read
txtName.Text = dr.GetString(3) & ", " & dr.GetString(4)
End While
dr.Close()
cn.Close()
End Sub

These seem to work well and fast. But there are a few questions:

1. Is there a "right" way to structure creating the different
components necessary to populate my form and if so, is mine along the
right lines so far?

2. Should I use "Leave" or "Lost_Focus" for my text boxes? I used to
use "Lost_Focus" with VB6, but was wondering if there was a difference
or if one was preferred.

3. If the two datareaders above only return one record, is there some
other reader I should use to improve performance?

Any other suggestions would sure be appreciated. I'm a bit
wet-behind-the-ears so I'd love some help.

Thanks!
Steve

[Jay B. Harlow [MVP - Outlook]]

Light & Giorgio,
David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS Press,
explains DataReaders, DataAdapters & DataSets along with when to use which
when.

DataReaders are used to retrieve information from you database.

DataAdapters is used to adapt information from your database into a
DataTable

DataSets are collections of DataTables, used to store & manipulate data in a
disconnected fashion. DataSets can contain data from XML files instead of
from a database.

Hope this helps
Jay
"Light Templar" <[e-mail]> wrote in message
news:%2***************@TK2MSFTNGP09.phx.gbl...

Am I correct in saying that the only use for a datareader would be to have a fast lookup of data for displaying purposes? I'm new with VB .Net.

Just about the time I was getting really good with 6.0... :c)

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:uo**************@TK2MSFTNGP09.phx.gbl...

Hi Steve,

Just one point: don't use datareaders. They are fast but they are read

only

and forward only, so if you want to do anything with the data, you'll be
crippled. Use datasets/datatables instead. And when you refer to a

column

you can refer to it as irow("fname") instead of irow(7). This is

supposedly

slower (unless you have a typed dataset - I won't bore you with that) but it's very effective and much easier to debug.

HTH,

Bernie Yaeger

"Steve" <sf**@rsd-tc.com> wrote in message
news:a7**************************@posting.google.c om...

Sorry in advance for my ignorance. Any help would sure be
appreciated. I'm writing a fairly simple application with VB.Net and
am obviously a bit of a newbie. This application will be used by 1, 2
or at most 3 people concurrently and I'm using Access 2003 for my data
source. We are not dealing with a large amount of data (5 or 6
tables, for a total of maybe 3,000 records - one table having the
majority of that). This application is using a fairly simple form,
but upon leaving certain text boxes, I want to fill in some data. For
instance, I might have one field that is for a competition number -
when I leave it, I want to populate a description. The next field
might be a competitor number and when I leave it, I want to populate a
field with the competitor name.

Enough of the background - I created a module to open my connection
and a function within that to handle my DataReader. In that module I
have:

Imports System.Data
Imports System.data.oledb
Imports System.Data.sqlclient

Module Main
Public strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=f:\mydatabase.mdb"
Public cn As New OleDbConnection(strConn)

Function ExecuteReader(ByVal sSQLString As String) As
OleDb.OleDbDataReader

Dim dr As OleDbDataReader
Dim cmd As OleDbCommand = New OleDbCommand(sSQLString, cn)
Try
If cn.State = ConnectionState.Closed Then cn.Open()
dr = cmd.ExecuteReader()
cmd.Dispose()
Catch ex As OleDbException
MsgBox(ex.Message, MsgBoxStyle.Exclamation)
End Try
Return dr
End Function

End Module

And that's pretty much it so far. In my main code I have:

Private Sub txtCompNum_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompNum.LostFocus
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM competition where compnum =
" & "'" & txtCompNum.Text & "'")
While dr.Read
txtDance.Text = dr.GetString(2)
End While
dr.Close()
End Sub

Private Sub txtCompetitor_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompetitor.Leave
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM dancer where cardnum = " &
txtCompetitor.Text)
While dr.Read
txtName.Text = dr.GetString(3) & ", " & dr.GetString(4)
End While
dr.Close()
cn.Close()
End Sub

These seem to work well and fast. But there are a few questions:

1. Is there a "right" way to structure creating the different
components necessary to populate my form and if so, is mine along the
right lines so far?

2. Should I use "Leave" or "Lost_Focus" for my text boxes? I used to
use "Lost_Focus" with VB6, but was wondering if there was a difference
or if one was preferred.

3. If the two datareaders above only return one record, is there some
other reader I should use to improve performance?

Any other suggestions would sure be appreciated. I'm a bit
wet-behind-the-ears so I'd love some help.

Thanks!
Steve

[Light Templar]

Thanks for the explaination. I'm going to take a few classes in the summer
to bring me up to speed with .net, but I really didn't expect such a
drastic, or what seems like a drastic, departure from 6.0. Kinda has my
head spinning a bit. :c) My company just decided to upgrade everything
and move to .net about a month ago.

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:%2***************@TK2MSFTNGP11.phx.gbl...

Bob,

- Don't use ordinals to access returned table data

dr.GetString(3), dr.GetString(4)... this is bad. Do you know what "magic
numbers" are? Magic Numbers are numbers that appear in code that, at a glance,

maintainable. Use the column names so you don't have to worry about what

order

they came in or guess what number gives you what column.

This is bad advice in that it will slow down your program! Good advice for
the reason you gave!

Seeing as I normally specify the fields in the select itself (your first
item) I normally setup an Enum with the fields in the correct order, then
use the enum to retrieve the values. Using the Enum helps avoid the "Magic
number" problem. Sure some may say I just shifted the magic number problem
;-) If I change the query, I change the Enum. Alternative I will lookup

the ordininal values (based on the names as you suggest) outside the loop, then use these ordinals inside the loop.

However in .NET when using a DataAdapter & DataTables & DataSets this item
is moot. Of course if you are not using DataSets & DataTables, then this
item definitely makes a difference.

Hope this helps
Jay
"Bob" <no*****@nospam.net> wrote in message
news:uq**************@TK2MSFTNGP12.phx.gbl...

I'm no guru, but speaking from a few years of experience which began with

Access

and VB5...

- Don't use SELECT *

The columns you're reading from the DB should be explicitly enumerated - even if

it's all of them. Cropping the returned columns down to just what you

need will

not only save resources, but also tell you exactly what columns are used and

where so you don't have to go looking in your code and hope you got

everything

when you have to change a table or a view definition.

- Don't use ordinals to access returned table data

dr.GetString(3), dr.GetString(4)... this is bad. Do you know what "magic
numbers" are? Magic Numbers are numbers that appear in code that, at a

glance,

have no discernable meaning. If you want to know what column 4 is you'll

have to

look it up. This practice should be avoided to keep your code readable

and maintainable. Use the column names so you don't have to worry about what

order

they came in or guess what number gives you what column.

- Just because you're a newbie doesn't mean you should start with Access

instead

of SQL Server.

SQL Server is easier - not harder - to use, and far, far more stable and
scalable. If you ever want to increase the number of users for your app,

you'll

be moving to it anyway so you should consider using it to begin with. If

you

have the $$$, it's totally worth it.

- SQL does not belong in application code

It may not matter with a small app, but if it grows you're going to need

a way

to analyze how your app accesses your DB when you need to make changes. This is

difficult if you have SQL in hard code, but simple if it's all in stored
procedures (a feature of SQL Server, these are kept server-side). Not

only are

the contents of all stored procedures searchable like other data, but

you can

also access table dependency information which will give you a list of
everything you need to check when you need to make changes. Put it

another way -

say you need to make a change to a table that accessed from 8 different
locations in your application. Would you want to do a text search

through your

code, hope you found all the references, and then recompile your app, or would

you rather just have a list of stored procedures given to you that you

can edit

server-side, possibly while your app is live, without your users ever

knowing?

Good luck to you and happy coding,
Bob

"Steve" <sf**@rsd-tc.com> wrote in message
news:a7**************************@posting.google.c om...

Sorry in advance for my ignorance. Any help would sure be
appreciated. I'm writing a fairly simple application with VB.Net and
am obviously a bit of a newbie. This application will be used by 1, 2
or at most 3 people concurrently and I'm using Access 2003 for my data
source. We are not dealing with a large amount of data (5 or 6
tables, for a total of maybe 3,000 records - one table having the
majority of that). This application is using a fairly simple form,
but upon leaving certain text boxes, I want to fill in some data. For
instance, I might have one field that is for a competition number -
when I leave it, I want to populate a description. The next field
might be a competitor number and when I leave it, I want to populate a
field with the competitor name.

Enough of the background - I created a module to open my connection
and a function within that to handle my DataReader. In that module I
have:

Imports System.Data
Imports System.data.oledb
Imports System.Data.sqlclient

Module Main
Public strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=f:\mydatabase.mdb"
Public cn As New OleDbConnection(strConn)

Function ExecuteReader(ByVal sSQLString As String) As
OleDb.OleDbDataReader

Dim dr As OleDbDataReader
Dim cmd As OleDbCommand = New OleDbCommand(sSQLString, cn)
Try
If cn.State = ConnectionState.Closed Then cn.Open()
dr = cmd.ExecuteReader()
cmd.Dispose()
Catch ex As OleDbException
MsgBox(ex.Message, MsgBoxStyle.Exclamation)
End Try
Return dr
End Function

End Module

And that's pretty much it so far. In my main code I have:

Private Sub txtCompNum_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompNum.LostFocus
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM competition where compnum =
" & "'" & txtCompNum.Text & "'")
While dr.Read
txtDance.Text = dr.GetString(2)
End While
dr.Close()
End Sub

Private Sub txtCompetitor_Leave(ByVal sender As Object, ByVal e As
System.EventArgs) Handles txtCompetitor.Leave
Dim dr As OleDbDataReader
dr = ExecuteReader("SELECT * FROM dancer where cardnum = " &
txtCompetitor.Text)
While dr.Read
txtName.Text = dr.GetString(3) & ", " & dr.GetString(4)
End While
dr.Close()
cn.Close()
End Sub

These seem to work well and fast. But there are a few questions:

1. Is there a "right" way to structure creating the different
components necessary to populate my form and if so, is mine along the
right lines so far?

2. Should I use "Leave" or "Lost_Focus" for my text boxes? I used to
use "Lost_Focus" with VB6, but was wondering if there was a difference
or if one was preferred.

3. If the two datareaders above only return one record, is there some
other reader I should use to improve performance?

Any other suggestions would sure be appreciated. I'm a bit
wet-behind-the-ears so I'd love some help.

Thanks!
Steve

[Light Templar]

Yeah, I know the words... :c) Putting to use is proving to be more
difficult. We oldsters don't learn new languages like you youngsters. :c)

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:OT**************@TK2MSFTNGP11.phx.gbl...

Light & Giorgio,
David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS Press,
explains DataReaders, DataAdapters & DataSets along with when to use which
when.

DataReaders are used to retrieve information from you database.

DataAdapters is used to adapt information from your database into a
DataTable

DataSets are collections of DataTables, used to store & manipulate data in a disconnected fashion. DataSets can contain data from XML files instead of
from a database.

Hope this helps
Jay
"Light Templar" <[e-mail]> wrote in message
news:%2***************@TK2MSFTNGP09.phx.gbl...

Am I correct in saying that the only use for a datareader would be to

have

a fast lookup of data for displaying purposes? I'm new with VB .Net.

Just about the time I was getting really good with 6.0... :c)

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:uo**************@TK2MSFTNGP09.phx.gbl...

Hi Steve,

Just one point: don't use datareaders. They are fast but they are read

only

and forward only, so if you want to do anything with the data, you'll

be crippled. Use datasets/datatables instead. And when you refer to a

column

you can refer to it as irow("fname") instead of irow(7). This is

supposedly

slower (unless you have a typed dataset - I won't bore you with that)

but it's very effective and much easier to debug.

HTH,

Bernie Yaeger

"Steve" <sf**@rsd-tc.com> wrote in message
news:a7**************************@posting.google.c om...
> Sorry in advance for my ignorance. Any help would sure be
> appreciated. I'm writing a fairly simple application with VB.Net and > am obviously a bit of a newbie. This application will be used by 1, 2 > or at most 3 people concurrently and I'm using Access 2003 for my data > source. We are not dealing with a large amount of data (5 or 6
> tables, for a total of maybe 3,000 records - one table having the
> majority of that). This application is using a fairly simple form,
> but upon leaving certain text boxes, I want to fill in some data. For > instance, I might have one field that is for a competition number -
> when I leave it, I want to populate a description. The next field
> might be a competitor number and when I leave it, I want to populate a > field with the competitor name.
>
> Enough of the background - I created a module to open my connection
> and a function within that to handle my DataReader. In that module I > have:
>
> Imports System.Data
> Imports System.data.oledb
> Imports System.Data.sqlclient
>
> Module Main
> Public strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data > Source=f:\mydatabase.mdb"
> Public cn As New OleDbConnection(strConn)
>
> Function ExecuteReader(ByVal sSQLString As String) As
> OleDb.OleDbDataReader
>
> Dim dr As OleDbDataReader
> Dim cmd As OleDbCommand = New OleDbCommand(sSQLString, cn)
> Try
> If cn.State = ConnectionState.Closed Then cn.Open()
> dr = cmd.ExecuteReader()
> cmd.Dispose()
> Catch ex As OleDbException
> MsgBox(ex.Message, MsgBoxStyle.Exclamation)
> End Try
> Return dr
> End Function
>
> End Module
>
> And that's pretty much it so far. In my main code I have:
>
> Private Sub txtCompNum_Leave(ByVal sender As Object, ByVal e As
> System.EventArgs) Handles txtCompNum.LostFocus
> Dim dr As OleDbDataReader
> dr = ExecuteReader("SELECT * FROM competition where compnum = > " & "'" & txtCompNum.Text & "'")
> While dr.Read
> txtDance.Text = dr.GetString(2)
> End While
> dr.Close()
> End Sub
>
> Private Sub txtCompetitor_Leave(ByVal sender As Object, ByVal e As > System.EventArgs) Handles txtCompetitor.Leave
> Dim dr As OleDbDataReader
> dr = ExecuteReader("SELECT * FROM dancer where cardnum = " &
> txtCompetitor.Text)
> While dr.Read
> txtName.Text = dr.GetString(3) & ", " & dr.GetString(4)
> End While
> dr.Close()
> cn.Close()
> End Sub
>
> These seem to work well and fast. But there are a few questions:
>
> 1. Is there a "right" way to structure creating the different
> components necessary to populate my form and if so, is mine along the > right lines so far?
>
> 2. Should I use "Leave" or "Lost_Focus" for my text boxes? I used to > use "Lost_Focus" with VB6, but was wondering if there was a difference > or if one was preferred.
>
> 3. If the two datareaders above only return one record, is there some > other reader I should use to improve performance?
>
> Any other suggestions would sure be appreciated. I'm a bit
> wet-behind-the-ears so I'd love some help.
>
> Thanks!
> Steve