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/ArticleID/7846/7846.html
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}
