Ok, so this will be longwinded, but I'll try and explain what I'm trying to accomplish.

Our form has 3 buttons.

  1. Clear Database (Working)
  2. Load database from Text File
  3. Load listbox from database (I have this working)

Our project has 2 forms, one being the form which handles the button click events as well as the listbox selection event. We made a template (dbUpdt) that will process a text file using the functions and subs.

My question: In the selectindexchanged event directly below, what do I have to do to get the label text properties to display the field contents of the record? I know "_dbDR" is not right, but I'm killing myself and I cant figure it out.

       Private Sub lstStations_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstStations.SelectedIndexChanged
        Dim db As New dbUpdt
        ' setup Variables

        Dim strSQL As String      ' for the SQL command
        ' establish a connection to the DB and open it
        db.openConnection("weather.mdb")
        ' establish a connection to the DB
        ' set up the SQL statement and execute it
        strSQL = "SELECT * FROM stations " & _
           "WHERE StationName='" & lstStations.SelectedItem.ToString & "'"
        ' feed the database the SQL command and execute it
        db.query(strSQL)
        ' if the record is found, display it in the text boxes
        If _dbDR.Read Then
            lblStationID.Text = _dbDR.Item("StationID").ToString
            lblStationName.Text = _dbDR.Item("StationName").ToString
            lblState.Text = _dbDR.Item("State").ToString
        Else
            MessageBox.Show("Record not found " & lstStations.SelectedItem.ToString)
        End If

        ' close the database
        db.close()
    End Sub

dbUpdt class

Imports System.Data.OleDb
''' <summary>
''' the dbUpdt class that we implement the Trmplate we created in order to process the text files and to add, create orr append a database
''' </summary>
Public Class dbUpdt



    'Declare the necessary class level variables
    Dim _strSrc As String
    Dim _strConn As String
    Dim _dbConn As New OleDbConnection
    Dim _dbCmd As New OleDbCommand
    Dim _dbDR As OleDbDataReader

    ''' <summary>
    ''' This event opens the connection to the database.
    ''' </summary>
    ''' <param name="strVar">the source of the database file</param>
    ''' <remarks></remarks>
    Public Sub openConnection(ByVal strVar As String)
        'initializes strSrc to the database file
        _strSrc = strVar

        _strConn = "Provider=Microsoft.Jet.OLEDB.4.0" & _
            ";Data Source=" & _strSrc & _
            ";User ID=admin;Password="
        'opens the database
        _dbConn.ConnectionString = _strConn
        _dbConn.Open()
        _dbCmd.Connection = _dbConn
    End Sub

    ''' <summary>
    ''' closes the database
    ''' </summary>
    Public Sub close()
        'close database
        _dbConn.Close()
    End Sub


    ''' <summary>
    ''' This method will clear the database
    ''' </summary>
    ''' <param name="strTable">the string of the table</param>
    ''' <remarks></remarks>
    Public Sub deleteAll(ByVal strTable As String)
        'create a variable for the SQL statement
        Dim strSQL As String
        ' set up the SQL statement and execute it
        strSQL = "DELETE FROM stations"
        _dbCmd.CommandText = strSQL
        _dbCmd.ExecuteNonQuery()
        'close the database
        _dbConn.Close()
    End Sub

    ''' <summary>
    ''' This method will query the database base of the SQL string that is used
    ''' </summary>
    ''' <param name="strSQL">a string that will be used to execute a SQL statement</param>
    Public Sub query(ByVal strSQL As String)
        'use SQL statement to retrieve desired records
        _dbCmd.CommandText = strSQL
        'reads the database
        _dbDR = _dbCmd.ExecuteReader()
    End Sub

    ''' <summary>
    ''' This function checks to see if there are more records to add to the database.
    ''' </summary>
    ''' <returns>_dbDR.Read</returns>
    ''' <remarks></remarks>
    Public Function moreRecords() As Boolean
        Return _dbDR.Read
    End Function

    ''' <summary>
    ''' this method will return the contents of a specific field in the curtrent record
    ''' </summary>
    ''' <param name="strFieldName"></param>
    ''' <returns>StrRet: the field contents </returns>
    ''' <remarks></remarks>
    Public Function getField(ByVal strFieldName As String) As String
        'declare the tempory variable
        Dim strRet As String
        'if there is no record to read
        If IsDBNull(_dbDR.Item(strFieldName)) Then
            'strRet will be empty
            strRet = ""
        Else
            'otherwise the strRet will be initialized with the derired field from the record
            strRet = CStr(_dbDR.Item(strFieldName))
        End If
        'returns the field contents
        Return strRet
    End Function

    ''' <summary>
    ''' This method will execute a SQL command that will add a record into the database
    ''' </summary>
    ''' <param name="strTable">the name of a table</param>
    ''' <param name="strKeyName">the value that we are using as a key</param>
    ''' <param name="strKeyContents">the value that we used as a key</param>
    ''' <remarks></remarks>
    Public Sub addRecord(ByVal strTable As String, _
     ByVal strKeyName As String, _
     ByVal strKeyContents As String)
        'declare the SQL string
        Dim strSQL As String
        'insert the record
        strSQL = "INSERT INTO " & strTable & _
           " (" & strKeyName & ") VALUES ('" & strKeyContents & "')"
        'execute the SQL string
        _dbCmd.CommandText = strSQL
        'write to the database
        _dbCmd.ExecuteNonQuery()
    End Sub

    ''' <summary>
    ''' This method is used to write to a specific field in a specific record
    ''' </summary>
    ''' <param name="strTable">name of the table</param>
    ''' <param name="strKeyName">The name of the field that is used as a key</param>
    ''' <param name="strKeyContents">The value that you are looking for in the key</param>
    ''' <param name="strFieldName">The name of the field to be updated</param>
    ''' <param name="strFieldContents">The new value of the field to be updated</param>
    Public Sub setField(ByVal strTable As String, _
     ByVal strKeyName As String, _
     ByVal strKeyContents As String, _
     ByVal strFieldName As String, _
     ByVal strFieldContents As String)
        'declare the SQL string
        Dim strSQL As String
        ' get rid of any stray quote marks
        strFieldContents = strFieldContents.Replace("'", "")
        'the SQL string that will write the field
        strSQL = "UPDATE " & strTable & " SET " & _
           strFieldName & "='" & strFieldContents & "' " & _
           "WHERE " & strKeyName & "='" & strKeyContents & "'"
        'execute the SQL string
        _dbCmd.CommandText = strSQL
        'write to the database
        _dbCmd.ExecuteNonQuery()
    End Sub


End Class

this method will return the contents of a specific field in the curtrent record

This is in a comment on one of the functions in "your" class I suggest starting here...

Edited 3 Years Ago by G_Waddell

Thanks Waddell. Sometimes I get so mixed up it's hard to find my direction. Thank you again.

Here is how i solved my problem.

db.query(strSQL)

        If db.moreRecords Then

            ' if the record is found, display it in the text boxes
            lblStationID.Text = db.getField("StationID")
            lblStationName.Text = db.getField("StationName")
            lblState.Text = db.getField("State")
This question has already been answered. Start a new discussion instead.