I am writing a program in VB2005 using MS Access as a database where the user can select an instructor from a combo box, and then a test they provide from a second combo. My first combo box works fine where it correctly displays the 3 instructors I have. My second combo box correctly displays the correct number of tests, but only displays the tests from the first instructor (doesn't change during the SelectedIndexChanged Event). I know it is passing the correct UserID (instructor's ID #) but beyond that, I'm not sure why it won't display correctly.
From SelectedIndexChanged Event:
CODE
Private Sub cboInstructor_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboInstructor.SelectedIndexChanged
Dim x As Integer
If bLoaded Then
sUserID = cboInstructor.Text
For x = 0 To dsInstructor.Tables(0).Rows.Count - 1
If dsInstructor.Tables(0).Rows(x).Item(3) = sUserID Then
iUserID = dsInstructor.Tables(0).Rows(x).Item("UserID")
End If
Next
dsQuizName = oQuizName.GetData(iUserID)
cmgrQuizName = CType(Me.BindingContext(dsQuizName.Tables(0)), CurrencyManager)
cboTest.DataSource = dsQuizName.Tables(0)
cboTest.DisplayMember = "Description"
cboTest.ValueMember = "UserID"
End If
End Sub
From the clsQuizName class:
CODE
Private Function CreateDASelectCommand() As OleDb.OleDbCommand
Dim sSQL As String 'string with SQL
Dim cmd As New OleDb.OleDbCommand(sSQL, m_oCn) 'command object
'---------------------------------------------------------
'--- Set up the SELECT Command
'---------------------------------------------------------
sSQL = "Select Description, UserID, QuizID from qryQuizName"
cmd.CommandText = sSQL
cmd.CommandType = CommandType.Text 'this is SQL rather than a stored procedure
Return cmd 'return the full command
End Function
Public Function GetData(ByVal inUserID As Integer) As DataSet
Dim sSQLOrig As String = "" 'The original SQL used for all records
Try
''--- Create a new DataSet
sSQLOrig = m_oDA.SelectCommand.CommandText
m_oDA.SelectCommand.CommandText &= " where UserID = " & iUserID.ToString
m_oCn.Open()
m_oDS = New DataSet
''--- Fill the DataSet with the Customers
m_oDA.Fill(m_oDS)
m_oCn.Close()
''//--- Return the DataSet
Return m_oDS
Catch ex As Exception
Throw ex
Finally
'reset SQL back to generic 'Grab Everything'
m_oDA.SelectCommand.CommandText = sSQLOrig
End Try
End Function
My SQL code from my MS Access query:
SELECT tblQuiz.Description, tblInstQuiz.UserID, tblQuiz.QuizID
FROM tblQuiz INNER JOIN tblInstQuiz ON tblQuiz.QuizID = tblInstQuiz.QuizID;
Any questions or you need more information, please ask.
Thanks for your help!
| 