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