查询IP数据库的问题

[linwenfeng]

数据库Access2000:IP.mdb 表:gdip 里面有如下数据
ip1(字段) ip2(字段) address(字段)
59.32.254.0 59.33.1.255 广东省汕头市 ADSL
59.33.2.0 59.33.36.64 广东省中山市 ADSL
59.33.36.65 59.33.36.65 广东省中山市 联通绿色网苑
59.33.36.66 59.33.36.200 广东省中山市 ADSL
59.33.36.201 59.33.36.201 广东省中山市 沙溪南路5号2楼(雷柏高服装有限公司)
59.33.36.202 59.33.37.255 广东省中山市 ADSL

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim strCN As String
strCN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ip.mdb;"
cn.Open strCN
rs.Open "select * from gdip", cn, adOpenStatic, adLockOptimistic

这时如果用户在text1里输入59.32.254.34,按下command1(查询)的话,程序怎么才能在text2(多行文本框)中返回对应的数据(广东省汕头市 ADSL)呢

[haipingma]

方法一:
rs.filter="ip1='" & trim(text1.text) & "'"
方法二:
set rs = new adodb.recordset
rs.Open "select * from gdip where ip1='" & trim(text1.text) & "'", cn, adOpenStatic, adLockOptimistic

if rs.recordcount>0 then
rs.movefirst
text2.text = rs.fields("address").value
else
text2.text = ""
end if

[haipingma]

或者用
rs.find

[linwenfeng]

你这样只能查询IP地址刚刚=的
如果是输入这两个IP地址中间的地址,就无法返回任何数据了,比如查询"59.32.254.1 59.32.254.2 59.32.254.13..........."等等,都不行
59.32.254.0 59.33.1.255 广东省汕头市 ADSL

[linwenfeng]

我现在是用这样的
rs.Open "select * from ip where ip1<='" & Trim(Text1) & "' and ip2>='" & Trim(Text1) & "'", cn, adOpenStatic, adLockOptimistic
不过这方法查询61.154.79.170竟然小于61.154.79.34,因为170和34,的第一位,3比1大,所以判断为61.154.79.34比较大,哎,不好弄啊

[linwenfeng]

如果不碰到上面的问题,用我那个方法还是行的,有谁有办法解决吗

[szjhxu]

我的思路是将IP地址转换成12位的字符串,即"59.32.254.2"转换成"059032254002"
然后进行比较.

程序参见:
ip=text1.text
sqlcmd="select * from gdip where IP2Str(ip1)<='"&IP2Str(ip)&"' and IP2Str(ip2)>='"&IP2Str(ip)&"'"
rs.open sqlcmd,cn,1,3
if not rs.eof then
print rs("address")
end if

'自定义函数,实现将IP转换成纯数字的字符串
Public Function IP2Str(ByVal IP As String) As String
Dim P As Integer, S As String, tmp As String
P = InStr(IP, ".")
Do While P <> 0
tmp = Trim(Left(IP, P - 1))
tmp = IIf(Len(tmp) < 3, String(3 - Len(tmp), "0") & tmp, tmp)
IP2Str = IP2Str & tmp
IP = Mid(IP, P + 1)
P = InStr(IP, ".")
Loop
tmp = Trim(IP)
tmp = IIf(Len(tmp) < 3, String(3 - Len(tmp), "0") & tmp, tmp)
IP2Str = IP2Str & tmp
End Function

[linwenfeng]

楼上的思路不错,和我一样(我有思路,但不会写,哈哈)

[linwenfeng]

试试看先

[linwenfeng]

提示IP2Str未定义???
我把声明复制进去了啊

[szjhxu]

我改了一下,你看看:

Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim strCN As String
strCN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ip.mdb;"
cn.Open strCN
IP = Trim(Text1.Text)
strSQL = "select * from gdip"
rs.Open strSQL, cn, 1, 3
Do While Not rs.EOF
If IP2Str(rs("ip1")) < IP2Str(IP) And IP2Str(rs("ip2")) >= IP2Str(IP) Then
Exit Do
Else
rs.MoveNext
End If
Loop
If Not rs.EOF Then Text2 = rs("address") Else Text2 = "没找到"
cn.Close

End Sub


Public Function IP2Str(ByVal IP As String) As String
Dim P As Integer, tmp As String
IP = Trim(IP)
P = InStr(IP, ".")
Do While P <> 0
tmp = Trim(Left(IP, P - 1))
tmp = IIf(Len(tmp) < 3, String(3 - Len(tmp), "0") & tmp, tmp)
IP2Str = IP2Str & tmp
IP = Mid(IP, P + 1)
P = InStr(IP, ".")
Loop
tmp = Trim(IP)
tmp = IIf(Len(tmp) < 3, String(3 - Len(tmp), "0") & tmp, tmp)
IP2Str = IP2Str & tmp
End Function

[haipingma]

抱歉，理解錯誤
按szjhxu(天野)的思路是對的

[linwenfeng]

可以了,这行少了一个"="号,应该是"<="才行哦
If IP2Str(rs("ip1")) < IP2Str(IP) And IP2Str(rs("ip2")) >= IP2Str(IP) Then

谢谢了

[szjhxu]

对,我忘了.

[linwenfeng]

还有个问题,如果要查询address含有"广东"的记录语句该怎么写

[szjhxu]

strSQL = "select * from gdip where address like '%广东%'"