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()

GetConnectionSt ring()

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.QuerySt ring("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_amfna t As New OdbcCommand(str SQL,
amfnat_OdbcConn ection)
Dim myCommand_msba As New OdbcCommand(str SQL,
msba_OdbcConnec tion)
Dim myCommand_bcam As New OdbcCommand(str SQL,
bcam_OdbcConnec tion)
Dim myCommand_afca As New OdbcCommand(str SQL,
afca_OdbcConnec tion)

'Create the DataAdapter for AMFNAT and Populate the DataSet
myDA.SelectComm and = myCommand_amfna t
myDA.Fill(myDS)

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

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

'Create the DataAdapter for AFCA and Populate the DataSet
myDA.SelectComm and = 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.Vi sible = True
dgAllCompanies. Visible = False
Else
lblNoResults.Vi sible = False
dgAllCompanies. Visible = True
End If

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

myDA.Dispose()
myDA = Nothing

myCommand_amfna t.Dispose()
myCommand_amfna t = 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
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()

GetConnectionSt ring()

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.QuerySt ring("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_amfna t As New OdbcCommand(str SQL,
amfnat_OdbcConn ection)
Dim myCommand_msba As New OdbcCommand(str SQL,
msba_OdbcConnec tion)
Dim myCommand_bcam As New OdbcCommand(str SQL,
bcam_OdbcConnec tion)
Dim myCommand_afca As New OdbcCommand(str SQL,
afca_OdbcConnec tion)

'Create the DataAdapter for AMFNAT and Populate the DataSet
myDA.SelectComm and = myCommand_amfna t
myDA.Fill(myDS)

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

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

'Create the DataAdapter for AFCA and Populate the DataSet
myDA.SelectComm and = 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.Vi sible = True
dgAllCompanies. Visible = False
Else
lblNoResults.Vi sible = False
dgAllCompanies. Visible = True
End If

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

myDA.Dispose()
myDA = Nothing

myCommand_amfna t.Dispose()
myCommand_amfna t = 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

[Elton Wang]

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

HTH

Elton Wang
el********@hotm ail.com

-----Original Message-----
I have 4 different databases that I'm having to pull data from in orderto populate a datagrid. I am able to do this, but my problem is thatbecause I'm pulling the data from 4 different databases, the data isordered alphabetically but is grouped by database.

Here is an example of what is happening to the data in the datgrid withthe 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, reguardlessof which database it is from.

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

Private Sub BindData()

GetConnectionSt ring()

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

Dim tmpID As String = Request.QuerySt ring("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_amfna t As New OdbcCommand(str SQL,
amfnat_OdbcCon nection)
Dim myCommand_msba As New OdbcCommand(str SQL,
msba_OdbcConne ction)
Dim myCommand_bcam As New OdbcCommand(str SQL,
bcam_OdbcConne ction)
Dim myCommand_afca As New OdbcCommand(str SQL,
afca_OdbcConne ction)

'Create the DataAdapter for AMFNAT and Populate the DataSet myDA.SelectComm and = myCommand_amfna t
myDA.Fill(myDS)

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

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

'Create the DataAdapter for AFCA and Populate the DataSet myDA.SelectComm and = 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.Vi sible = True
dgAllCompanies. Visible = False
Else
lblNoResults.Vi sible = False
dgAllCompanies. Visible = True
End If

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

myDA.Dispose()
myDA = Nothing

myCommand_amfna t.Dispose()
myCommand_amfna t = 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

.

[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()