Hi...
I'm having a big problem with a datagrid that obtains data from 2 different
locations...
Active Directory and SQL Database
The data is inserted into a datatable and the sorted by a dataview...
however due to alphabetic paging and the postback ("I think") the contacts
from the SQL Database appear on ever page when you page through the datagrids
results...
How do I get around this???
Should I store the results in something else other than a datatable... what
do you recommend???
Thanks for any help!
<code>
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
'Loads user controls
Dim c1 As Control = LoadControl("../userControls/footer.ascx")
phFooter.Controls.Add(c1)
Dim c2 As Control = LoadControl("../userControls/headerMenu.ascx")
phHeaderMenu.Controls.Add(c2)
Dim c3 As Control = LoadControl("../userControls/Header.ascx")
phHeader.Controls.Add(c3)
Dim c4 As Control = LoadControl("../userControls/QuickLinks.ascx")
phQuickLinks.Controls.Add(c4)
'Create table to contain contacts
createTable()
If Not Page.IsPostBack Then
'Bind Contacts information from Active Directory for
alphabetic filtering
BindGrid()
End If
End Sub
Private Sub createTable()
Dim tbcontacts As DataTable = New DataTable("contacts")
tbcontacts.Columns.Add(" ", System.Type.GetType("System.String"))
tbcontacts.Columns.Add("Name", System.Type.GetType("System.String"))
tbcontacts.Columns.Add("Dept.", System.Type.GetType("System.String"))
tbcontacts.Columns.Add("Ext", System.Type.GetType("System.String"))
tbcontacts.Columns.Add("Email", System.Type.GetType("System.String"))
ds.Tables.Add(tbcontacts)
End Sub
'Function to Page through contacts
Sub DataGrid1_Paged(ByVal sender As Object, ByVal e As
DataGridPageChangedEventArgs)
DataGrid1.CurrentPageIndex = e.NewPageIndex
BindGrid()
End Sub
'Add's alphabetic filtering for contacts list
Private Sub DataGrid1_ItemCreated(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemCreated
If e.Item.ItemType = ListItemType.Footer Then
e.Item.Cells.Clear()
Dim tc As New TableCell
tc.ColumnSpan = 5
e.Item.Cells.Add(tc)
Dim spacer As New LiteralControl
spacer.Text = " "
tc.Controls.Add(spacer)
'Adds Alphabetical list to the footer of the DataGrid
Dim i As Integer
For i = 65 To 65 + 25
Dim l As New LinkButton
Dim lc As New LiteralControl
lc.Text = " "
l.Text = Chr(i)
l.CommandName = "alpha"
l.CommandArgument = Chr(i)
tc.Controls.Add(l)
tc.Controls.Add(lc)
Next
'Adds View All text to the footer of the DataGrid
Dim showAll As New LinkButton
showAll.Text = "...View All"
showAll.CommandName = "alpha"
showAll.CommandArgument = ""
tc.Controls.Add(showAll)
End If
End Sub
' Adds Active Directory User information into contacts table
Sub BindGrid(Optional ByVal alpha As String = "")
Dim strADPath As String
strADPath = "domain.dk"
Dim de As DirectoryEntry = New DirectoryEntry("LDAP://" & strADPath,
"username", "password")
Dim src As DirectorySearcher
If alpha = "" Then
DataGrid1.AllowPaging = True
src = New
DirectorySearcher("(&(objectCategory=Person)(objec tClass=user))")
Else
DataGrid1.AllowPaging = False
src = New
DirectorySearcher("(&(objectCategory=Person)(objec tClass=user)(sn=" & alpha &
"*))")
End If
src.SearchRoot = de
src.SearchScope = SearchScope.Subtree
For Each res As SearchResult In src.FindAll
Dim dr As DataRow = ds.Tables("contacts").NewRow
dr(" ") = "<img src='../images/user.gif'>"
If res.Properties.Contains("sn") And
res.Properties.Contains("givenName") And res.Properties.Contains("Initials")
Then
dr("Name") = CStr(res.Properties("sn")(0)) & ", " &
CStr(res.Properties("givenName")(0)) & " " &
CStr(res.Properties("Initials")(0))
Else
dr("Name") = ""
End If
If res.Properties.Contains("physicalDeliveryOfficeNam e") Then
dr("Dept.") =
CStr(res.Properties("physicalDeliveryOfficeName")( 0))
Else
dr("Dept.") = ""
End If
If res.Properties.Contains("telephoneNumber") Then
Dim TeleNumber As String =
CStr(res.Properties("telephoneNumber")(0))
dr("Ext") = "#" & Right(TeleNumber, Len(TeleNumber) -
InStr(TeleNumber, "1"))
Else
dr("Ext") = ""
End If
If res.Properties.Contains("mail") Then
dr("Email") = CStr(res.Properties("mail")(0))
Else
dr("Email") = ""
End If
ds.Tables("contacts").Rows.Add(dr)
Next
'Adds Non Active Directory Users into the DataGrid
Dim Myconn As New
SqlConnection(ConfigurationSettings.AppSettings("s trConn"))
'Dim tbcontactsNonAD As DataTable = New DataTable("contactsNonAD")
Dim cmd As New SqlCommand("NonADUsers", Myconn)
cmd.CommandType = CommandType.StoredProcedure
Myconn.Open()
Dim dread As SqlDataReader = cmd.ExecuteReader
While dread.Read()
Dim row As DataRow = ds.Tables("contacts").NewRow()
row(" ") = "<img src='../images/user.gif'>"
row("Name") = dread.GetString("0") & ", " &
dread.GetString("1") & " " & dread.GetString("2")
row("Dept.") = dread.GetString("3")
row("Ext") = dread.GetString("4")
row("Email") = dread.GetString("5")
ds.Tables("contacts").Rows.Add(row)
End While
Myconn.Close()
'For i As Integer = 0 To ds.Tables("contactsNonAD").Rows.Count - 1
'
ds.Tables("contacts").Rows.Add(ds.Tables("contacts NonAD").Rows(i).ItemArray)
'Next
'End of Non Active Directory DataGrid Addition
Dim myDataView As DataView
myDataView = New DataView(ds.Tables("contacts"))
myDataView.Sort = "Name ASC"
' Binds Contact data from Active Directory to DataGrid
DataGrid1.DataSource = myDataView
DataGrid1.DataBind()
End Sub
' Filters Contacts alphabectically when user chooses a letter
Private Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs ) Handles
DataGrid1.ItemCommand
If e.CommandName = "alpha" Then
BindGrid(e.CommandArgument)
End If
End Sub
