我在做VB搜索框进行数据库查询时,遇到一个奇怪的问题:一个Text框,一个Combo1,一个按钮command1,单击查找Access数据库内容。 当我直接输入一个字符串进行搜索的时候,可以正常的搜索出结果。然而当我直接从Excel复制这个字符串时,却搜索不到结果(Excel内的字符串没有比输入的多任何一个字符,比如空格之类); 下面是我的代码,请大侠们帮忙看看:
Private Sub Command1_Click()
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\list.mdb;" & ";Persist Security Info=False;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:database password='123456'"
Conn.CursorLocation = adUseClient
If Conn.State <> adStateClosed Then Conn.Close
Conn.Open strConn
If rs.State <> adStateClosed Then rs.Close
rs.Open "Select * from ID where " & Combo1.Text & " like '%" & Trim(Text1.Text) & "%'", Conn, adOpenKeyset, adLockPessimistic
Set DataGrid1.DataSource = rs
End Sub
Private Sub Command1_Click()
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\list.mdb;" & ";Persist Security Info=False;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:database password='123456'"
Conn.CursorLocation = adUseClient
If Conn.State <> adStateClosed Then Conn.Close
Conn.Open strConn
If rs.State <> adStateClosed Then rs.Close
rs.Open "Select * from ID where " & Combo1.Text & " like '%" & Trim(Text1.Text) & "%'", Conn, adOpenKeyset, adLockPessimistic
Set DataGrid1.DataSource = rs
End Sub
很有可能后面有回车或者chr(0)之类的不能显示的东西。
replace (str, char(13),"")
Private Sub Text1_Change()
Static bSelfChange As Boolean
Dim s As String
If bSelfChange Then Exit Sub
bSelfChange = True
s = Replace(Text1.Text, vbCr, vbNullString)
s = Replace(s, vbLf, vbNullString)
If Len(s) <> Len(Text1.Text) Then
Text1.Text = s
End If
bSelfChange = False
End Sub
Private Sub Command1_Click() '复制EXCEL中的ABC(不双击的复制)到文本框里,单击它
Dim iH As String For i = 1 To Len(Text1)
iH = iH & Format(Asc(Mid(Text1, i, 1)))
Next
Debug.Print iH '这样可以看出多的13和10,也就是回车换行
'----------------------------------------------------
iH = Replace(Text1, vbCrLf, "") '这样处理一下就可以了.
Debug.Print iH; Format(Len(iH))
End Sub