表中有多条合适的记录,可查询结果只显示一条,sql语句在查询分析器中验证是正确的
具体代码如下:
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strsql As String
Dim i As Integer
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=jc;Data Source=SXQ"
strsql = "select jcsju.planid,jcsju.bh,guige,fabric,wd,sd,js1,ws1,js2,ws2 ,cpname,unit,bnumber,test,H11,H12,H13,B11,B12,B13,J11,J12,J13 from jcsju,stylebook where jcsju.bh=stylebook.bh and jcsju.date2 >='" & CDate(DTPicker1.Value) & "' and jcsju.date2<= '" & CDate(DTPicker2.Value) & "' group by jcsju.planid,guige,fabric,wd,sd,js1,ws1,js2,ws2 ,cpname,unit,bnumber,test,jcsju.bh,H11,H12,H13,B11,B12,B13,J11,J12,J13,jcsju.date2 order by jcsju.planid,jcsju.date2"
rs.Open strsql, cn, adOpenKeyset, adLockReadOnly
If rs.RecordCount = 0 Then
MsgBox "没有适合条件的记录"
Exit Sub
Else
MSHFlexGrid1.Rows = rs.RecordCount + 1
rs.MoveFirst
For i = 1 To rs.RecordCount
Do While rs.EOF = False
MSHFlexGrid1.TextMatrix(i, 0) = rs.Fields("planid")
MSHFlexGrid1.TextMatrix(i, 1) = rs.Fields("bh")
MSHFlexGrid1.TextMatrix(i, 2) = rs.Fields("guige")
MSHFlexGrid1.TextMatrix(i, 3) = rs.Fields("cpname")
MSHFlexGrid1.TextMatrix(i, 4) = rs.Fields("fabric")
MSHFlexGrid1.TextMatrix(i, 5) = rs.Fields("unit")
MSHFlexGrid1.TextMatrix(i, 6) = rs.Fields("bnumber")
MSHFlexGrid1.TextMatrix(i, 7) = rs.Fields("test")
MSHFlexGrid1.TextMatrix(i, 8) = rs.Fields("wd")
MSHFlexGrid1.TextMatrix(i, 9) = rs.Fields("sd")
MSHFlexGrid1.TextMatrix(i, 10) = rs.Fields("js1")
MSHFlexGrid1.TextMatrix(i, 11) = rs.Fields("ws1")
MSHFlexGrid1.TextMatrix(i, 12) = rs.Fields("js2")
MSHFlexGrid1.TextMatrix(i, 13) = rs.Fields("ws2")
MSHFlexGrid1.TextMatrix(i, 14) = rs.Fields("H11")
MSHFlexGrid1.TextMatrix(i, 15) = rs.Fields("H12")
MSHFlexGrid1.TextMatrix(i, 16) = rs.Fields("H13")
MSHFlexGrid1.TextMatrix(i, 17) = rs.Fields("B11")
MSHFlexGrid1.TextMatrix(i, 18) = rs.Fields("B12")
MSHFlexGrid1.TextMatrix(i, 19) = rs.Fields("B13")
MSHFlexGrid1.TextMatrix(i, 20) = rs.Fields("J11")
MSHFlexGrid1.TextMatrix(i, 21) = rs.Fields("J12")
MSHFlexGrid1.TextMatrix(i, 22) = rs.Fields("J13")
MSHFlexGrid1.TextMatrix(i, 23) = Round((Round(MSHFlexGrid1.TextMatrix(i, 14), 4) + Round(MSHFlexGrid1.TextMatrix(i, 15), 4) + Round(MSHFlexGrid1.TextMatrix(i, 16), 4)) / 3, 2)
MSHFlexGrid1.TextMatrix(i, 24) = Round((Round(MSHFlexGrid1.TextMatrix(i, 17), 4) + Round(MSHFlexGrid1.TextMatrix(i, 18), 4) + Round(MSHFlexGrid1.TextMatrix(i, 19), 4)) / 3, 2)
MSHFlexGrid1.TextMatrix(i, 25) = Round((Round(MSHFlexGrid1.TextMatrix(i, 20), 4) + Round(MSHFlexGrid1.TextMatrix(i, 21), 4) + Round(MSHFlexGrid1.TextMatrix(i, 22), 4)) / 3, 2)
rs.MoveNext
Loop
Next
rs.Close
End If
具体代码如下:
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strsql As String
Dim i As Integer
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=jc;Data Source=SXQ"
strsql = "select jcsju.planid,jcsju.bh,guige,fabric,wd,sd,js1,ws1,js2,ws2 ,cpname,unit,bnumber,test,H11,H12,H13,B11,B12,B13,J11,J12,J13 from jcsju,stylebook where jcsju.bh=stylebook.bh and jcsju.date2 >='" & CDate(DTPicker1.Value) & "' and jcsju.date2<= '" & CDate(DTPicker2.Value) & "' group by jcsju.planid,guige,fabric,wd,sd,js1,ws1,js2,ws2 ,cpname,unit,bnumber,test,jcsju.bh,H11,H12,H13,B11,B12,B13,J11,J12,J13,jcsju.date2 order by jcsju.planid,jcsju.date2"
rs.Open strsql, cn, adOpenKeyset, adLockReadOnly
If rs.RecordCount = 0 Then
MsgBox "没有适合条件的记录"
Exit Sub
Else
MSHFlexGrid1.Rows = rs.RecordCount + 1
rs.MoveFirst
For i = 1 To rs.RecordCount
Do While rs.EOF = False
MSHFlexGrid1.TextMatrix(i, 0) = rs.Fields("planid")
MSHFlexGrid1.TextMatrix(i, 1) = rs.Fields("bh")
MSHFlexGrid1.TextMatrix(i, 2) = rs.Fields("guige")
MSHFlexGrid1.TextMatrix(i, 3) = rs.Fields("cpname")
MSHFlexGrid1.TextMatrix(i, 4) = rs.Fields("fabric")
MSHFlexGrid1.TextMatrix(i, 5) = rs.Fields("unit")
MSHFlexGrid1.TextMatrix(i, 6) = rs.Fields("bnumber")
MSHFlexGrid1.TextMatrix(i, 7) = rs.Fields("test")
MSHFlexGrid1.TextMatrix(i, 8) = rs.Fields("wd")
MSHFlexGrid1.TextMatrix(i, 9) = rs.Fields("sd")
MSHFlexGrid1.TextMatrix(i, 10) = rs.Fields("js1")
MSHFlexGrid1.TextMatrix(i, 11) = rs.Fields("ws1")
MSHFlexGrid1.TextMatrix(i, 12) = rs.Fields("js2")
MSHFlexGrid1.TextMatrix(i, 13) = rs.Fields("ws2")
MSHFlexGrid1.TextMatrix(i, 14) = rs.Fields("H11")
MSHFlexGrid1.TextMatrix(i, 15) = rs.Fields("H12")
MSHFlexGrid1.TextMatrix(i, 16) = rs.Fields("H13")
MSHFlexGrid1.TextMatrix(i, 17) = rs.Fields("B11")
MSHFlexGrid1.TextMatrix(i, 18) = rs.Fields("B12")
MSHFlexGrid1.TextMatrix(i, 19) = rs.Fields("B13")
MSHFlexGrid1.TextMatrix(i, 20) = rs.Fields("J11")
MSHFlexGrid1.TextMatrix(i, 21) = rs.Fields("J12")
MSHFlexGrid1.TextMatrix(i, 22) = rs.Fields("J13")
MSHFlexGrid1.TextMatrix(i, 23) = Round((Round(MSHFlexGrid1.TextMatrix(i, 14), 4) + Round(MSHFlexGrid1.TextMatrix(i, 15), 4) + Round(MSHFlexGrid1.TextMatrix(i, 16), 4)) / 3, 2)
MSHFlexGrid1.TextMatrix(i, 24) = Round((Round(MSHFlexGrid1.TextMatrix(i, 17), 4) + Round(MSHFlexGrid1.TextMatrix(i, 18), 4) + Round(MSHFlexGrid1.TextMatrix(i, 19), 4)) / 3, 2)
MSHFlexGrid1.TextMatrix(i, 25) = Round((Round(MSHFlexGrid1.TextMatrix(i, 20), 4) + Round(MSHFlexGrid1.TextMatrix(i, 21), 4) + Round(MSHFlexGrid1.TextMatrix(i, 22), 4)) / 3, 2)
rs.MoveNext
Loop
Next
rs.Close
End If
数据库为SQL:
strsql=" select * from tablename where 日期字段 between '"& 日期变量1 &"' and '"& 日期变量2 &"'"
数据库为ACCESS:
strsql=" select * from tablename where 日期字段 between #"& 日期变量1 &"# and #"& 日期变量2 &"#"
reco = "select * from bwmain where inday =" & Chr(35) & Text1.Text & Chr(35) '指定两日期之间
reco = "select * from bwmain where inday between " & Chr(35) & Text1.Text & "# and #" & Text2.Text & Chr(35)
改为
Str(DTPicker1.Value)因为CDate不是文字你无法相加
rs.MoveFirst
For i = 1 To rs.RecordCount
Do While rs.EOF = False
...
rs.MoveNext
Loop
Next
代码很奇怪,i=1的时候Do While...Loop都已经把记录取完了,i=2 to rs.RecordCount 还能取到数据吗?
改成这样试试看rs.MoveFirst
i = 1
Do While rs.EOF = False
...
rs.MoveNext
i = i + 1
Loop
For i = 0 To rs.RecordCount -1
Do While rs.EOF = False
......
rs.MoveNext
Loop
Next
循环有问题.多了一层吧?直接这样:
Do While rs.EOF = False
......
rs.MoveNext
i = i + 1
Loop 5楼说的对,i=1时,已经读到rs.eof