Using Array or String??

[calvin]

Hi, i got a problem to bind the data to datagrid or datalist.

Scenario:
There is two table, Table A(StandardID),(StudentName), Table B(StudentName).

First of all, If I choose "Standard 2", which is one of the collection in the dropdown list, it will get the StandardID(value=2) in Table A and store the particular value of StudentName into a variable(strStudentName declare as String), which mean all standard 2 StudentName will store into the variable strStudentName.

When i bind the corresponding StudentName into a datagrid, it only show one StudentName(suppose it should be ten studentName listed).
So, I was using response.write(StudentName) to check does the variable(strStudentName) contain the value(ten student name), eventually, it display the result what i want.
Please help me solve this problem in urgent, thank you.


This is my code:

SQLStmt1 = "Select StandardID, StudentName from TableA Where StandardID = '" + ddlstandard.SelectedItem.Value + "'"

Dim cmd As New OleDbCommand(SQLStmt1, cn)
Dim objDR As OleDbDataReader = cmd.ExecuteReader()

While objDR.Read()
Dim strStudentName As String
strStudentName = objDR("StudentName")
'Respnse.Write(strStudentName)
SQLStmt2 = "Select StudentName from TableB Where StudentName ='" & strCName & "'"

Dim da As New OleDbDataAdapter(SQLStmt2, Connection)
ds = New DataSet("ModuleEnrollment")
da.Fill(ds, "ModuleEnrollment")
DataGrid1.DataSource = ds.Tables("ModuleEnrollment").DefaultView
DataBind()
End While

[KPayne]

Calvin,

From the code it appears that you are performing a SELECT for each row in the first result set. You will only see the last row of data unless you move the second SELECT and the code that follows out of the While loop.

Assuming that TableA has one row per StandardID and TableB is the child of TableA and contains multiple rows with the same StudentName, you will have to change your two SELECT commands to one. Something like:

"SELECT TableB.StudentName FROM TableB INNER JOIN
(SELECT StudentName FROM TableA WHERE StandardID = " & ddlStandard.SelectedValue & ") ta
ON ta.StudentName = TableB.StudentName"

Use this command to populate your DataSet. The first SELECT command and the DataReader are unnecessary.

Keith Payne
Technical Marketing Solutions