ASP.NET Discussion - Find left x part of DB field via SQL

Find left x part of DB field via SQL

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: ASP.NET Discussion
Forum Description: Discussion and chat on ASP.NET related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=13278
Printed Date: 29 March 2026 at 11:44pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Find left x part of DB field via SQL

Posted By: pedalcars
Subject: Find left x part of DB field via SQL
Date Posted: 09 January 2005 at 2:32pm

I've just started in asp.net and I thought I'd do a page I've been meaning to add for a while in aspx rather than asp.

I want to select records in a database by IP address of the poster, so I've got this SQL statement, which works fine for complete IP addresses:

SelectStatement = _

"SELECT * From tblMainTable " & _

"WHERE ip = '" & IPSearch & "' " & _

"ORDER BY time DESC"

However, I'd also like to search for partial addresses, to enable me to find all instances in a range, e.g. search for "127.0" and find any instance starting with that.

I've tried this:

ReqLength = IPSearch.Length



SelectStatement = _

"SELECT * From tblMainTable " & _

"WHERE ip.Substring(0, ReqLength) = '" & IPSearch & "' " & _

"ORDER BY time DESC"

The thought being, find the length of IPSearch and look for records, the left part of which (to the length of IPSearch) match.

But I get this error:

Exception Details: System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

Source Error:

Line 44: Adapter.Fill(ipDS,"tblMainTable")

Help!?

Thanks in advance!

-------------
http://www.pedalcars.info/ - www.pedalcars.info

The most fun on four wheels

Replies:

Posted By: dj air
Date Posted: 09 January 2005 at 2:57pm

try the

" WHERE (((ip.Substring) LIKE '" & strIPSearch & "%' ")) "

Posted By: dpyers
Date Posted: 09 January 2005 at 3:26pm

I convert ip addresses to their doubleword equivilent and store them as such. Makes comparisons a lot easier.

To convert an ip like "A.B.C.D", the formula is

(A*16777216)+(B*65536)+(C*256)+D

ip="255.255.255.255"
 
dwdIP=split(ip,".")
 IPnumber=(dwdIP(0)*16777216)+(dwdIP(1)*65536)+(dwdIP(2)*256) +(dwdIP(3))

To see if an IP falls within a range, you'd convert the ip, and compare it to the concerted 127.0.0.0 and converted 128.0.0.0 numbers.

As an aside... you can use the doubleword number in an http request as well, so http://www.somesite.com - http://www.somesite.com , http://216.239.33.100 - http://216.239.33.100 , and http://3639550308 - http://3639550308 would all take you to the same site.

The code to conver from a doubleword back to dot notation is a little more complicated, but if you need it, I'll post is.

-------------

Lead me not into temptation... I know the short cut, follow me.

Posted By: pedalcars
Date Posted: 10 January 2005 at 5:17pm

dj air wrote:

try the

" WHERE (((ip.Substring) LIKE '" & strIPSearch & "%' ")) "

Thanks, but still not working. Still get the same error. OK, initially got an "expected end of statement" error instead, cut the above to:

" WHERE (((ip.Substring) LIKE '" & strIPSearch & "%' )) "

I'm still stuck I'm afraid!

dpyers, thanks for the suggestion - I can think of at least one place to use this already. The code to reverse the calculation would be handy too, ta!

-------------
http://www.pedalcars.info/ - www.pedalcars.info

The most fun on four wheels

Posted By: dpyers
Date Posted: 10 January 2005 at 9:55pm

<%    
Function fncMakeDblWdIP(ByVal IP_dotted_quad)
' Convert a conventional quad dot format ip to a double-word number
 Dim dblIPnumber
 Dim arrIP
 
 arrIP=split(IP_dotted_quad,".")
 
 dblIPnumber = (arrIP(0)*16777216)+(arrIP(1)*65536)+(arrIP(2)*256) +(arrIP(3))
 'Alternative way of doing the math
 'dblIPnumber = (arrIP(0)*(256 ^ 3))+(arrIP(1)*(256 ^ 2))+(arrIP(2)*256) +(arrIP(3))
 
 fncMakeDblWdIP = dblIPnumber
 
End Function 

Function fncConvertDblWdIP(ByVal dblIPnumber)
' Convert a double-word number ip to a conventional quad dot format
 Dim IP_dotted_quad
 Dim sOctet
 Dim i

 For i = 3 To 0 Step -1
 
  ' create each octet
  sOctet = Int(dblIPnumber / (256 ^ i))
  
  ' Build the dot decimal quad ip
  IP_dotted_quad = IP_dotted_quad & sOctet & "."
  
  ' Remove the number that we just parsed
  dblIPnumber = dblIPnumber - (sOctet * (256 ^ i))
  
 Next
 
 ' Delete last dot
 IP_dotted_quad = Left(IP_dotted_quad , Len(IP_dotted_quad ) - 1)
 
 fncConvertDblWdIP = IP_dotted_quad 
 
End Function 
 '----------------------------------------------------------- ----------
'Test code
'response.write "127.0.0.1  = " & fncMakeDblWdIP("127.0.0.1") & "<br/>"
'response.write "2130706433 = " & fncConvertDblWdIP("2130706433") & "<br/>"
%>

-------------

Lead me not into temptation... I know the short cut, follow me.

Posted By: pedalcars
Date Posted: 04 February 2005 at 11:31am

Thanks all. The above wildcard does indeed work, for the benefit of anyone else watching.

Not sure why it didn't originally, maybe a caching problem.

I've just revisited the file I was wanting to change, and now the same solution works fine!

Weird...

-------------
http://www.pedalcars.info/ - www.pedalcars.info

The most fun on four wheels