saving data from database to listboxes

• • Reply

  19 years ago

  by Veronica

  Veronica

  , United States

  Joined 19 years ago

  I am fetching data from two different tables (one for studentID and StudentName; the other for StudentID and grades) to fill two listboxes on a form; the boxes have to be populated so that when you select a name from the combo box, you only see the grades for that student.  I am using DB.Execute with manual coding to access the database.  How do I save the data to a temporary variable so that I can make the listboxes work outside the connection?  Also, how do I manipulate the listproperties/listindex so the grades box will show only the grades for the studentname selected in the other box?

  Report abuse
• • Reply

  19 years ago

  by matsail

  matsail

  , United States

  Joined 19 years ago

  Why don't you just use one listbox, and one text box.  The listbox to list all the names and the text box to show the grade of the name you choose.  If you do it this way then you can use the CHANGE event for the listbox to query the database for the Grade of the selected Name.  Something like this:
  

  
  Private Sub dlstName_Change()
  

  
  Dim cmd As ADODB.Command, cnn As ADODB.Connection
  Dim a As ADODB.Recordset, st As String, found As Boolean
  Set cnn = New ADODB.Connection
  Set cmd = New ADODB.Command
  

  
  

  
  If Not IsNull(dlstName) And dlstName <> Empty Then
     cnn.Open "Provider = SQLOLEDB ;PERSIST SECURITY INFO = FALSE;" & _
     "USER ID = sa;Initial catalog = timersql;" & _
     "INITIAL FILE NAME = C:\MSSQL7\Data\someDatabase.mdf"
     cmd.ActiveConnection = cnn
     cmd.CommandText = "spGetGradesforName"
     cmd.CommandType = adCmdStoredProc
     Set a = cmd.Execute(, CStr(dlstName))
     If Not a.EOF Then
         txtGrade = CStr(a!Grade)
     End If
  End If
  

  
  

  
  

  
  End Sub

  Report abuse