INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ASP.NET Forum General Use

Sorting Class C IP Addresses In Datagrid by adamroof
Posted: 23 Oct 04

As you all may know, sorting IP Addresses is difficult as 192.168.1.100 comes before 192.168.1.99 using text sorting.

A solution some have come up with is to add a column to a full list of ip addresses in a table, adding AA, AB, AC, AD and sorting off of that... see this link
http://www.winnetmag.com/SQLServer/Article/Article...

But i think i have the solution for dynamicicity!(if thats a word?!)

CODE

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HTMLControls
Imports Microsoft.VisualBasic

Public Class ip_cb
    Inherits System.Web.UI.Page

    Protected dlIP As DataGrid
    Protected message As Label

    Dim ConnectionString As String = "server=(local);database=helpdesk;trusted_connection=true"

    Sub Page_Load(Sender As Object, E As EventArgs)
        If Not Page.IsPostBack Then
            BindDL("IPAddress ASC")
        End If
    End Sub
    
    Sub BindDL(sortExpr As String)
    Dim intSubnet As Integer = Request.QueryString("subnet")
    If intSubnet < 10 Then
        intSubnet = 11
    Else if intSubnet < 100 Then
        intSubnet = 12
    Else If intSubnet > 99 Then
         intSubnet = 13
    End If

        Dim objConn As New SqlConnection(ConnectionString)
        Dim strIP As String = "192.168." & Request.QueryString("subnet") & "."
        Dim sSql As String = "SELECT t1.IPType, t1.IPAddress, t2.HdwrNetBIOS, CAST(SUBSTRING(t1.IPAddress," & intSubnet & ",3) AS Integer) As Value FROM tblHdwrIP t1 LEFT JOIN tblHdwr t2 ON t1.HdwrID = t2.HdwrID WHERE t1.IPAddress LIKE '"  & strIP & "%'" & " ORDER BY " & sortExpr
        Dim objComm As New SqlCommand(sSql, objConn)
        Dim da As New SqlDataAdapter(objComm)
        Dim ds As New DataSet()
                          
          da.Fill(ds)
            dlIP.DataSource = ds
            dlIP.DataBind()
        objConn.Close()
    End Sub
    
    Sub SortColumn(sender as Object, e as DataGridSortCommandEventArgs)
           BindDL(e.SortExpression)
    End Sub
        
End Class

Heres the meat and potatoes...

CAST(SUBSTRING(t1.IPAddress," & intSubnet & ",3) AS Integer) As Value

1. You need a numeric field from a string...CAST(),
2. Only grab the last 3 numbers after the last "dot"...
     SUBSTRING()
3. Declare that SQL As a column name
4. <asp:BoundColumn DataField="Value" Visible=False />
5. <asp:BoundColumn DataField="IPAddress" sortExpression="Value" />

The If intSubnet < 10 = 11 part is to denote the first number after the dot within the SUBSTRING(variable, start location, count) - lookup on MSDN

I am passing in the Subnet of a 192.168.x.x network from user selection. Im sure this will kickstart you into formatting your own subnet ranges

Heres my aspx...

[code]
<%@ Page Language="VB" debug="true" inherits="ip_cb" src="ip.aspx.vb" %>
<html>
<head>
    <title>IP Addresses</title>
    <link rel=stylesheet type="text/css" href="css\page.css">
</head>
<body>
    <form runat="server">
    <div class=head>IP List</div><div align=right><a href=javascript:window.close() class=hyper>Close</a></div>
       <hr>
       <asp:Label id=message runat=server EnableViewState=False class=messages />
    <table border=1 id=ipInfo runat=server width=350 class=hyper bgColor=#D3D3D3 cellpadding=3 cellspacing=0>
        <TR>
            <TD align=center>
                <asp:DataGrid id=dlIP runat=server AutoGenerateColumns=False cellspacing=1 AllowSorting="True" OnSortCommand="SortColumn">
                    <headerStyle cssClass=hyper />
                    <columns>
                     <asp:BoundColumn DataField="IPType" HeaderText="IP Type" SortExpression="IPType" />
                     <asp:BoundColumn DataField="IPAddress" HeaderText="IP Address" SortExpression="Value" />
                    <asp:BoundColumn DataField="HdwrNetBIOS" HeaderText="NetBIOS" SortExpression="HdwrNetBIOS" />    
                    <asp:BoundColumn DataField="Value" HeaderText="Value" SortExpression="Value" Visible=False />
                    </columns>
                </asp:DataGrid>
            </TD>
        </TR>
    </TABLE>
    </FORM>
</BODY>
[code}

Back to Microsoft: ASP.NET FAQ Index
Back to Microsoft: ASP.NET Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close