Using Array or String?? urgent

[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

[mocella]

First, what's this variable "strCName" in this line?

SQLStmt2 = "Select StudentName from TableB Where StudentName ='" & strCName & "'"

 

I'm a little confused as to what you're getting by not just using the StudentName from tableA - what's different about that name in tableA than tableB? Do you really have to go to the second table here?

 

Sounds like you'd be better off doing a join between the tables in SQL. Something like:

 

SQLStmt1 = "Select TableB.StudentName from TableB Inner Join TableA On TableB.StudentName = TableA.StudentName Where TableA.StandardID = '" + ddlstandard.SelectedItem.Value + "'"

 

If you did this, all you'd have to use is a data-adapter and then bind your datagrid to the results.

 

Or am I totally missing something here? (it's still early, and it's Friday:D )

[calvin]

First of all, I'm appreciate for your helps. I do a little mistake with strCName should be strStudentName. In fact, when I select one of the student standard in dropdownlist, it will go to table A to get all the corresponding StudentName. Secondly, I want to get the others fields in Table B using StudentName. The StudentName is not the primary key in Table B.

For instance, if (john is a standard 6 student ) in Table A, and he had three record in Table B, I want to get all the record using StudentName(value=john).

 

I do a little changes from the query.

 

SQLStmt1 = "Select TableB.StudentName, StudentDOB from TableB Inner Join TableA On TableB.StudentName = TableA.StudentName Where TableA.Left(StandardID, 1)= '" + ddlstandard.SelectedItem.Value + "'"

 

** I get a Undefined function 'TableA.Left' in expression.

The reason why I want to get the StandardID with the first value on left is, actually the StandardID Value in Table A is (6A, 6B, 6C, 6D...). Do you know how to write the query? Pls reply me a.s.a.p thank you.

[mocella]

I think you just left out the column from the query - so it should look like:

TableA.columnname.Left(StandardID, 2)

 

This would get you the first two characters from your column on the lefthand side. Are you certain that the values are always going to be 2 digits in length? If not, you're going to have to code around that as well by determining where the first comma in the field sits and supply that value minus one in your left function to get the first value.

 

Sounds like a good candidate for a new table with a FK into TableB.

[calvin]

Thank you!