Populate a DataGrid from Multiple Databases - Order Problem

[crjunk]

I have 4 different databases that I'm having to pull data from in order
to populate a datagrid. I am able to do this, but my problem is that
because I'm pulling the data from 4 different databases, the data is
ordered alphabetically but is grouped by database.

Here is an example of what is happening to the data in the datgrid with
the code that I have now.
DB1 Apple
DB1 Bird
DB1 Cake
DB2 Airplane
DB2 Boat
DB2 Circle
DB3 Amazing
DB3 Blue
etc.....

I want ALL the data in the datagrid ordered alphabetically, reguardless
of which database it is from.

This is the code that I'm using to bind data to my datagrid. Can I add
something to my code to order the data correctly or do I need to go
about this another way?

Private Sub BindData()

GetConnectionString()

Dim strSQL As String = "SELECT DISTINCT DB_PDESCR, DB_PPROD
FROM PUB.PCFPOLCY "
Dim strWhere As String = ""
Dim strOrderBy As String = ""

Dim tmpID As String = Request.QueryString("ID")

'Ensure that the ID is 4 charactes long
While Len(Trim(tmpID)) < 4
tmpID = "0" & Trim(tmpID)
End While

strWhere = " WHERE DB_PPROD = '" & Trim(tmpID) & "'"
strOrderBy = " ORDER BY DB_PDESCR"
strSQL = strSQL & strWhere & strOrderBy

Dim myDA As New OdbcDataAdapter
Dim myDS As New DataSet
Dim myCommand_amfnat As New OdbcCommand(strSQL,
amfnat_OdbcConnection)
Dim myCommand_msba As New OdbcCommand(strSQL,
msba_OdbcConnection)
Dim myCommand_bcam As New OdbcCommand(strSQL,
bcam_OdbcConnection)
Dim myCommand_afca As New OdbcCommand(strSQL,
afca_OdbcConnection)

'Create the DataAdapter for AMFNAT and Populate the DataSet
myDA.SelectCommand = myCommand_amfnat
myDA.Fill(myDS)

'Create the DataAdapter for MSBA and Populate the DataSet
myDA.SelectCommand = myCommand_msba
myDA.Fill(myDS)

'Create the DataAdapter for BCAM and Populate the DataSet
myDA.SelectCommand = myCommand_bcam
myDA.Fill(myDS)

'Create the DataAdapter for AFCA and Populate the DataSet
myDA.SelectCommand = myCommand_afca
myDA.Fill(myDS)

'Set the datagrid's datasource to the dataset and databind
dgAllCompanies.DataSource = myDS
dgAllCompanies.DataBind()

'Display error message if there are no records.
If myDS.Tables(0).Rows.Count = 0 Then
lblNoResults.Visible = True
dgAllCompanies.Visible = False
Else
lblNoResults.Visible = False
dgAllCompanies.Visible = True
End If

''*** Clean Up
myDS.Dispose()
myDS = Nothing

myDA.Dispose()
myDA = Nothing

myCommand_amfnat.Dispose()
myCommand_amfnat = Nothing

myCommand_msba.Dispose()
myCommand_msba = Nothing

myCommand_bcam.Dispose()
myCommand_bcam = Nothing

myCommand_afca.Dispose()
myCommand_afca = Nothing

End Sub


Thanks for taking the time to look at my problem!
Crjunk

 

[ashish]

you can create your own custom class and implement icomparable interface
, then use arraylist to bind data to datagrid

 

[Elton Wang]

If you can put whole data from four databases into one
datatable, you can use dataview's (=
datatable.DefaultView) Sort property to sort all data.
Then you bind datagrid with the sorted dataview. It shows
alphabetically ordered data.

HTH

Elton Wang
(e-mail address removed)

 

[crjunk]

Thanks Elton and ashish. I figured out that I could use a DataView
before I read your message after doing a bunch of searching. Thanks
for your help. Here is what I added/changed in my code.

'Create a DataView so that I can order the data before the
DataGrid is populated.
'Otherwise the data will be in alphabetical order in DataGrid,
but grouped by DataBase.
Dim dvArrange As DataView = myDS.Tables(0).DefaultView
dvArrange.Sort = "DB_PDESCR"

'Set the datagrid's datasource to the DataView and bind data.
dgAllCompanies.DataSource = dvArrange
dgAllCompanies.DataBind()