| Author |
 Topic Search  Topic Options
|
pedalcars 
Senior Member
Joined: 12 August 2002
Location: United Kingdom
Status: Offline
Points: 268
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Find left x part of DB field via SQL
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!
|
|
|
 |
dj air
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 January 2005 at 2:57pm |
|
try the
" WHERE (((ip.Substring) LIKE '" & strIPSearch & "%' ")) "
|
|
dpyers
Senior Member
Joined: 12 May 2003
Status: Offline
Points: 3937
|
Post Options
Thanks(0)
Quote Reply
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.
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.
|
|
pedalcars
Senior Member
Joined: 12 August 2002
Location: United Kingdom
Status: Offline
Points: 268
|
Post Options
Thanks(0)
Quote Reply
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!
|
|
|
|
dpyers
Senior Member
Joined: 12 May 2003
Status: Offline
Points: 3937
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
pedalcars
Senior Member
Joined: 12 August 2002
Location: United Kingdom
Status: Offline
Points: 268
|
Post Options
Thanks(0)
Quote Reply
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...
|
|
|
|
