Get name from database?

GWHicks

I have an application I am writing that fills a combobox with a list of active employee numbers from our MSAccess Database, when a number is selected from that list I want to pull the name of that employee from the database and place it on the form. Here is the code I have right now (a little bloated due to trying to figure this out, and some of the code for future items I need to add). I am pretty sure my problem lies in the lblEmpName.Text = line but I am not sure if I just have to get the wrote code for that line, or if I need to do this a completely different way. Any assistance you could offer would be appreciated. Thanks in advance.
Greg Hicks
greg@ajlb2.com


Private Sub In_or_out()
Dim SQLEmpName As String = "select [Last Name], [First Name] from [Employees] where [Employees Number] =" & cboEmpNum.Text
Dim SQLInorOut As String = "select * from [Clock In] where [Employee Number] =" & cboEmpNum.Text
Dim conn As OleDbConnection = New OleDbConnection(data_path)
Dim GetEmpName As OleDbCommand = New OleDbCommand(SQLEmpName, conn)
Dim GetInorOut As OleDbCommand = New OleDbCommand(SQLInorOut, conn)

If cboEmpNum.Text <> "" Then
'get name corresponding to employee number selected
Try
conn.Open()
lblEmpName.Text = GetEmpName.co

Catch ex As Exception
MsgBox("Error getting employee's name from database")
Exit Sub
Finally
conn.Close()

End Try

End If
End Sub

MrEd

: I have an application I am writing that fills a combobox with a list of active employee numbers from our MSAccess Database, when a number is selected from that list I want to pull the name of that employee from the database and place it on the form. Here is the code I have right now (a little bloated due to trying to figure this out, and some of the code for future items I need to add). I am pretty sure my problem lies in the lblEmpName.Text = line but I am not sure if I just have to get the wrote code for that line, or if I need to do this a completely different way. Any assistance you could offer would be appreciated. Thanks in advance.
: Greg Hicks
: greg@ajlb2.com
:
:
: Private Sub In_or_out()
: Dim SQLEmpName As String = "select [Last Name], [First Name] from [Employees] where [Employees Number] =" & cboEmpNum.Text
: Dim SQLInorOut As String = "select * from [Clock In] where [Employee Number] =" & cboEmpNum.Text
: Dim conn As OleDbConnection = New OleDbConnection(data_path)
: Dim GetEmpName As OleDbCommand = New OleDbCommand(SQLEmpName, conn)
: Dim GetInorOut As OleDbCommand = New OleDbCommand(SQLInorOut, conn)
:
: If cboEmpNum.Text <> "" Then
: 'get name corresponding to employee number selected
: Try
: conn.Open()
: lblEmpName.Text = GetEmpName.co
:
: Catch ex As Exception
: MsgBox("Error getting employee's name from database")
: Exit Sub
: Finally
: conn.Close()
:
: End Try
:
: End If
: End Sub
:
:

Hi, ive done this once myself. But im guessing you need to put a GetEmpName.Read() before accessing the GetEmpName.co field. This is the code I got working, in case it helps.

Dim Connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Microsoft.VisualBasic.Left(Application.ExecutablePath, InStrRev(Application.ExecutablePath, "")) & "Config.mdb")
Dim strSQL As String = "SELECT FILE, CAPTURED, TITLE, DESCRIPTION FROM CAPTIONS ORDER BY ID"
Dim Command As New OleDbCommand(strSQL, Connection)
Dim Reader As OleDbDataReader
Connection.Open()
Reader = Command.ExecuteReader()
NumFiles = 0
While Reader.Read()
NumFiles = NumFiles + 1
ReDim Preserve Files(NumFiles)
Files(NumFiles).File = Reader.GetString(0)
If Not Reader.IsDBNull(1) Then Files(NumFiles).Captured = Reader.GetDateTime(1)
If Not Reader.IsDBNull(2) Then Files(NumFiles).Title = Reader.GetString(2)
If Not Reader.IsDBNull(3) Then Files(NumFiles).Descr = Reader.GetString(3)
End While
Reader.Close()
Command.Connection.Close()