*very* basic ADO.Net layout and design questions

[modfather]

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 & "'&quot
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

 

[chrissie1]

1. no idea (i probably dont understand the question)

2. i use validated but leave will do just the nicely.

3. i think datareader is the fastest way. but if the table has not to many records in it it is perhaps faster to put them in an array and use the arry to look up the data


Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[modfather]

Thanks for all the help I've gotten so far. This is fantastic!

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 & "'&quot
If txtCompNum.Text = "" Then
MessageBox.Show("Cannot be blank&quot
txtCompNum.Focus()
ElseIf dr Is Nothing Then
MessageBox.Show("Invalid Competition&quot
txtCompNum.Focus()
Else
While dr.Read
txtDance.Text = dr.GetString(1)
MessageBox.Show("true&quot
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

 

[chrissie1]

try it this way

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


Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong