DataTable Looping search

Results 1 to 2 of 2

Thread: DataTable Looping search

  1. #1
    Join Date
    Dec 1969

    Default DataTable Looping search

    Hi I have a datatable that I am looping through and trying to remove the duplicate phone numbers on. From what I have figured out in the following code is that I can accomplish this if the phone numbers are right next to each other.. so my original sql statement can order by homephone and my script will work, but how then do I resort my new datatable order by CloseDate, before I bind it to me datagrid<BR><BR>[code language="VB.NET"]<BR>Dim strsql2 As String = "select Fullname, Phone, CloseDate, RO, Attempt1Date,Attempt2Date,Attempt3Date, Letterdate from vServiceSurvey where LocationID = &#039;" & LocationID & "&#039; and Fullname is not null order by CloseDate DESC"<BR> Dim adjAdapter As New SqlDataAdapter(strsql2, objConn)<BR><BR> Dim dtPhone As New DataTable("dtPhone") &#039;creates datatable that the unfiltered results will go into<BR> ds.Tables.Add(dtPhone)<BR> adjAdapter.Fill(ds, "dtPhone") &#039;Fills Table with sql results<BR><BR> Dim dtUniquePhone As New DataTable() &#039;Creates the datatable to hold unique values<BR> Dim dsTemp As New DataSet() &#039;This is the dataset that will hold the filtered results<BR><BR> With dtUniquePhone<BR><BR> .Columns.Add(New DataColumn("FullName", GetType(String)))<BR> .Columns.Add(New DataColumn("Phone", GetType(String)))<BR> .Columns.Add(New DataColumn("RO", GetType(Int32)))<BR> .Columns.Add(New DataColumn("CloseDate", GetType(Int32)))<BR> .Columns.Add(New DataColumn("Attempt1Date", GetType(Date)))<BR> .Columns.Add(New DataColumn("Attempt2Date", GetType(String)))<BR> .Columns.Add(New DataColumn("Attempt3Date", GetType(String)))<BR> .Columns.Add(New DataColumn("LetterDate", GetType(String)))<BR><BR> End With<BR><BR> Dim datarow1 As DataRow<BR><BR> Dim strPhoneCurrent As String = "" &#039;this hold current phone<BR><BR> Dim strPhonePrev = "" &#039;Hold last phone used<BR><BR> For Each datarow1 In dtPhone.Rows &#039;for each row in dtPhone do the following<BR><BR> strPhoneCurrent = datarow1.Item("Phone") &#039;This will grab the next phone number in the list<BR><BR> If strPhoneCurrent = strPhonePrev Then &#039;if it is the same as the previous then do nothing <BR><BR> Else<BR><BR> Dim dtblerowTemp As DataRow &#039;If no dup is found create a datarow<BR> dtblerowTemp = dtUniquePhone.NewRow() &#039;add row to dtUniquePhone <BR> dtblerowTemp.Item("Phone") = strPhoneCurrent &#039;Make new row = to phone<BR> dtblerowTemp.Item("RO") = datarow1.Item("RO")<BR> dtblerowTemp.Item("FullName") = datarow1.Item("FullName")<BR> dtblerowTemp.Item("CloseDate") = datarow1.Item("CloseDate")<BR> dtblerowTemp.Item("Attempt1Date") = datarow1.Item("Attempt1Date")<BR> dtblerowTemp.Item("Attempt2Date") = datarow1.Item("Attempt2Date")<BR> dtblerowTemp.Item("Attempt3Date") = datarow1.Item("Attempt3Date")<BR> dtblerowTemp.Item("LetterDate") = datarow1.Item("LetterDate")<BR> dtUniquePhone.Rows.Add(dtblerowTemp) &#039;Attach the row<BR><BR> End If<BR><BR> strPhonePrev = strPhoneCurrent<BR><BR> Next<BR><BR>[code]

  2. #2

    Default RE: DataTable Looping search

    You dont sort the table directly, you sort a view 8&#124<BR>(It made sense after I figured it out)<BR><BR>myDataSet.Tables["myTableName"].DefaultView.Sort = "fieldName ASC"<BR><BR>Then you bind to that view instead of the table.<BR>myDataGrid.DataSource = myDataSet.Tables["myTableName"].DefaultView<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts