|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 忘得快 于 2016-10-6 23:24 编辑
各位老师:
学生在用窗体列表框查询SQL Server数据库时,加DISTINCT 后无数据显示,请教解决办法,谢谢!(实例在12楼)
Private Sub UserForm_Initialize()
Call t001
Me.StartUpPosition = 0
A = Array(130, 130, 40, 40, 40) '
Set cnn = New ADODB.Connection
' cnn.Open "provider=Microsoft.jet.OLEDB.4.0;data source=" & stpath1 & ";Jet OleDb:DataBase Password=123456"
cnn.Open "provider =SQLOLEDB;password=" & SJPW & " ; user ID= sa;data source =" & SJLJ & ";initial catalog=" & SJK & "; " 'Server语句
SQL = "select * from style "
Set rs = New ADODB.Recordset
rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
ReDim arr(1 To rs.Fields.Count)
On Error Resume Next
With ListView1
.ColumnHeaders.Clear
.View = lvwReport
.FullRowSelect = True
.Gridlines = True
For i = 0 To rs.Fields.Count - 1
If i > 0 Then
.ColumnHeaders.Add , , rs.Fields(i).Name, A(i), lvwColumnCenter '
Else
.ColumnHeaders.Add , , rs.Fields(i).Name, A(i)
End If
arr(i + 1) = rs.Fields(i).Name
Next i
End With
显示数据
rs.MoveFirst
Label1.Caption = "共查到" & ListView1.ListItems.Count & "记录"
End Sub
Private Sub 显示数据() '显示数据
Dim i&, j&, s$, t$, SQL$
For i = 1 To UBound(arr)
t = Me.Controls("TextBox" & i).Text
If Len(t) Then s = s & " and UCase(" & arr(i) & ") like '%" & UCase(t) & "%'"
Next
SQL = "select * from style" '
' SQL = "select DISTINCT 代码,描述,尺寸,单位 from style" '问题在这里
If Len(s) Then SQL = SQL & " where " & Mid(s, 6)
On Error Resume Next
Set rs = New ADODB.Recordset
rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
With ListView1
.ListItems.Clear
For i = 1 To rs.RecordCount
.ListItems.Add , , rs.Fields(0).Value
For j = 1 To rs.Fields.Count - 1
.ListItems(i).SubItems(j) = rs.Fields(j).Value '
Next
rs.MoveNext
Next
End With
rs.MoveFirst
End Sub
SQL = "select * from style" 语句改为
SQL = "select DISTINCT 代码,描述,尺寸,单位 from style" '没有数据显示
但是在查询Access数据库时,加DISTINCT 有数据显示:
|
|