E:\web\learnaspcom\htdocs\freebook\learn\ubtoc.xml LearnAsp.com - ASP ASP.net Free Lessons
Search Search

#1 worldwide
FREE Coding Lessons
since 1996
   THE BEST WAY to learn ASP & Asp.net!
Advertise Here!
click for details 		Credits Host:
DiscountASP.net 		Server Admin:
The "Team" 		Contact Info.
Charles M. Carroll 		<Asp.net blog>
<personal site>
xxx

DataTable: Uniquing a Column
by Charles Carroll

This example illustrates how to created a sorted DataView and then use that to create an unique datatable for a specific column.

   filename=/experiments/datatableunique/sample1.aspx

<Test Script Below>

 

<%@ trace="true"%>
<%@ Import Namespace="System.Data.sqlclient" %>
<%@ Import Namespace="System.Configuration.ConfigurationSettings" %>
<%@ Import Namespace="System.Data" %>
<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
    trace.tracemode=TraceMode.SortByTime
    Dim Conn as SQLConnection
    Dim Rdr as SQLDatareader
    TRY
        Dim strConn as string =AppSettings("LearnaspSamples")
        Dim strSQL as string ="select * from publishers where city<>''" ' where state='CA'

        ' DataTable for Publishers from New York
        dim dt1 as new datatable("temp")
        conn=new SQLconnection(strConn)
        Dim adapter As New SQLDataAdapter(strSQL,Conn)
        adapter.Fill(dt1)
        conn.Close()
        
        dim ds1 as new DataSet
        ds1.Tables.Add (dt1)
        
               ' make the datatable
        dim dtUniqueCity as new datatable()
        with dtUniqueCity
             .Columns.Add(new DataColumn("City",GetType(String)))
        end with

        ' loop through data add Uniques to newly created datatable
        'Dim datarow1 As DataRow
        
        Dim strCityCurrent as string=""
        Dim strCityPrev as string=""

        Dim dv1 As new DataView(ds1.Tables(0))
        dv1.Sort = "City"
        Dim datarow1 As DataRowView
        ' dv1.RowStateFilter = DataViewRowState.ModifiedCurrent        
        FOR EACH datarow1 IN dv1 ' ds1.tables(0).Rows
             strCityCurrent=datarow1("City")
             trace.write("strCityCurrent",strCityCurrent)
             IF strCityCurrent=strCityPrev
                 ' nothing to do
             ELSE

                 DIM dtblrowTemp as datarow
                 dtblrowTemp = dtUniqueCity.NewRow()
                  dtblrowTemp(0) = strCityCurrent
                        dtUniqueCity.Rows.Add(dtblrowTemp)
             END IF
             strCityPrev=strCityCurrent
             trace.write("strCityPrev",strCityPrev)
         NEXT
         ds1.Tables.Add(dtUniqueCity)

        myDataGrid.DataSource= ds1.Tables(1)
        myDataGrid.DataBind()
    CATCH exc1 as exception
        litExc.text=exc1.tostring()
    FINALLY
        If not(conn is Nothing)
            IF Conn.State=System.Data.Connectionstate.Open THEN Conn.Close()
        End If
    END TRY
End Sub
</script>
<html><head>
<title>DbTableUnique</title>
</head>
<body bgcolor="#FFFFFF">
<font face="Verdana"><h3>DataTable Unique</h3></font>
<asp:literal id="litexc" EnableViewState="false" runat="server" />
<ASP:DataGrid id="MyDataGrid" EnableViewState="false" runat="server" />
</body></html>
There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.