Populate a DataGrid from Multiple Databases - Order Problem

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!
Crjunkyou can create your own custom class and implement icomparable interface
, then use arraylist to bind data to datagrid

crjunk wrote:
> 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
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
elton_wang@.hotmail.com

>--Original Message--
>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
>.
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()