Ok, so this will be longwinded, but I'll try and explain what I'm trying to accomplish.
Our form has 3 buttons.
- Clear Database (Working)
- Load database from Text File
- 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
