如果combo1和datagrid已经连接ado。那么代码就可以这样: adodc1.recordsource="select * from table where " & combo1.text & combo2.text & "'" & text1.text & "'" adodc1.refresh
这就是写sql语句啊,依照上面的方法就可以了,俺就不说了1
Dim rssql As Recordset strcn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;password=symade;Initial Catalog=ClientData;Data Source=DATACENTER" Dim StrSql As String Dim Strquery As String Strquery = "select * from clientinfo"
Set cnsql = New ADODB.Connection With cnsql If .State = adStateOpen Then .Close End If .ConnectionString = strcn .CursorLocation = adUseClient .Open End With Set rssql = New ADODB.Recordset With rssql .ActiveConnection = cnsql .CursorLocation = adUseClient If Text1.Text = "" Then MsgBox "请输入要查询的值!", vbInformation + vbOKOnly, "提示:" Else StrSql = " where" & Combo1.Text & " & " & Combo2.Text & " '" & Trim(Text1.Text) & "' " End If Strquery = Strquery & StrSql
rssql.Open Strquery, , , , adCmdText Set DataGrid1.DataSource = rs End With 看看上面这个程序,运行到rssql.Open Strquery, , , , adCmdText这行代码时提示:‘&'附近有语法错误。运行时strquery的值是 select * from 表名where ...,也就是表名和where没有分开,怎样纠正这个错误,请兄弟指点
不知为什么,现在运行时strquery的值变成select * from 表名 wherecomob2.text...,也就是where与combo2并在一起,有什么办法可以让他们分开吗?
我以前编的一个程序中的一个查询的子程序: Private Sub Command1_Click() Dim SQLstr As String, tempsqlstr As String, msgstring As String Dim i As Integer
For i = 0 To 2 Call Check1_Click(i) Next i SQLstr = ""
If (Check1(0).Value = vbChecked) And Combo1(0).List(Combo1(0).ListIndex) <> Trim("模糊等于") Then SQLstr = "xm" & Trim(Combo1(0).Text) & "'" & Trim(Text1(0).Text) & "'" Else If (Check1(0).Value = vbChecked) And Combo1(0).List(Combo1(0).ListIndex) = Trim("模糊等于") Then SQLstr = "xm like '%" & Trim(Text1(0).Text) & "%'" Else SQLstr = "" End If End If
tempsqlstr = "jszh" & Trim(Combo1(1).Text) & "'" & Trim(Text1(1).Text) & "'" If Check1(1).Value = vbChecked Then If Trim(SQLstr) = "" Then SQLstr = tempsqlstr Else SQLstr = SQLstr & " And " & tempsqlstr End If End If
If Check1(2).Value = vbChecked Then If Trim(SQLstr) = "" Then SQLstr = tempsqlstr Else SQLstr = SQLstr & " And " & tempsqlstr End If End If
tempsqlstr = "czy" & Trim(Combo2.Text) & "'" & Trim(Combo3.Text) & "'" If Check2.Value = vbChecked Then If Trim(SQLstr) = "" Then SQLstr = tempsqlstr Else SQLstr = SQLstr & " And " & tempsqlstr End If End If
If Trim(SQLstr) = "" Then MsgBox "你没有设置查询条件,请重新设置查询条件!!!", vbOKOnly + vbExclamation, "提示" Exit Sub End If
On Error GoTo queryerror
Set rss = New ADODB.Recordset If Option1(0).Value = True Then SQLstr = "select * from readerinfo where " & SQLstr Set rss = ExecuteSQL(SQLstr, msgstring) Set DataGrid1(0).DataSource = rss DataGrid1(0).Refresh Else If Option1(1).Value = True Then SQLstr = "select * from delreaderinfo where " & SQLstr Set rss = ExecuteSQL(SQLstr, msgstring) Set DataGrid1(1).DataSource = rss DataGrid1(1).Refresh End If End If If rss.BOF And rss.EOF Then MsgBox "数据库中查无此记录!!!", vbOKOnly + vbInformation, "信息" Exit Sub Else rss.MoveLast rss.MoveFirst MsgBox "本次操作共查询到" & rss.RecordCount & "条记录!!!", vbOKOnly + vbInformation, "信息" End If
Exit Sub
queryerror: MsgBox "查询条件不能为空!!!", vbOKOnly + vbCritical, "错误" Exit Sub
Option Explicit Dim Mycn As New ADODB.Connection '数据库连接对象,用于打开数据库 Dim Myrs1 As New ADODB.Recordset '记录集对象,用于存入记录的Private Sub TableInit() MSHFlexGrid1.Rows = 0 MSHFlexGrid1.Cols = 24 End SubPrivate Sub Command7_Click() End End SubPrivate Sub Form_Load() MSHFlexGrid1.Rows = 0 MSHFlexGrid1.Cols = 24 MSHFlexGrid1.ColWidth(0) = 620 。 MSHFlexGrid1.ColWidth(23) = 740
Dim cnstring As String '用于存放连接字符串的 cnstring = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=dytd;PassWord=dytd;Data Source=dytd" Mycn.Open cnstring Mycn.CursorLocation = adUseClient Myrs1.Open "select * from JS_FYQK_JSYDZBGFTJB ", Mycn
MSHFlexGrid1.Clear TableInit While Myrs1.EOF = False MSHFlexGrid1.Rows = MSHFlexGrid1.Rows + 1 MSHFlexGrid1.Row = MSHFlexGrid1.Rows - 1 Dim TXT1 As Double 。 Dim TXT12 As Double
If Myrs1.RecordCount >= 1 Then While Myrs1.EOF = False If IsNull(Myrs1.Fields(0)) = False Then Combo1.AddItem Myrs1.Fields(0) End If Myrs1.MoveNext Wend
Myrs1.Close Else MsgBox "仓库中目前无余额信息,请添加进库信息后再试!", vbOKOnly + vbExclamation, "警告" Exit Sub
End If Mycn.Close cnstring = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=dytd;PassWord=dytd;Data Source=dytd" Mycn.Open cnstring Mycn.CursorLocation = adUseClient Myrs1.Open "select distinct trim(SFJ_DW) from JS_FYQK_JSYDZBGFTJB ", Mycn
If Myrs1.RecordCount >= 1 Then While Myrs1.EOF = False If IsNull(Myrs1.Fields(0)) = False Then Combo2.AddItem Myrs1.Fields(0) End If Myrs1.MoveNext Wend
Myrs1.Close Else MsgBox "仓库中目前无余额信息,请添加进库信息后再试!", vbOKOnly + vbExclamation, "警告" Exit Sub End If Mycn.Close End Sub Private Sub Check1_Click() If Check1.Value = vbChecked Then Combo1.Enabled = True Else Combo1.Enabled = False End If End SubPrivate Sub Check2_Click() If Check2.Value = vbChecked Then Combo2.Enabled = True Else Combo2.Enabled = False End If End Sub Private Sub Command6_Click() Dim cnstring As String '用于存放连接字符串的 cnstring = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=dytd;PassWord=dytd;Data Source=dytd" Mycn.Open cnstring Mycn.CursorLocation = adUseClient
If Combo1.Enabled = True And Combo2.Enabled = False Then Myrs1.Open "select * from JS_FYQK_JSYDZBGFTJB where trim(SFJ_XH)='" & Trim(Combo1.Text) & "'", Mycn End If If Combo2.Enabled = True And Combo1.Enabled = False Then Myrs1.Open "select * from JS_FYQK_JSYDZBGFTJB where trim(SFJ_DW)='" & Trim(Combo2.Text) & "'", Mycn End If If Combo2.Enabled = True And Combo1.Enabled = True Then Myrs1.Open "select * from JS_FYQK_JSYDZBGFTJB where trim(SFJ_DW)='" & Trim(Combo2.Text) & "' AND trim(SFJ_XH)='" & Trim(Combo1.Text) & "'", Mycn End If If Combo1.Enabled = False And Combo2.Enabled = False Then Myrs1.Open "select * from JS_FYQK_JSYDZBGFTJB", Mycn MsgBox "请设置查询方式!", vbOKOnly + vbExclamation, "警告" End If MSHFlexGrid1.Clear TableInit If Myrs1.RecordCount >= 1 Then While Myrs1.EOF = False
MSHFlexGrid1.Rows = MSHFlexGrid1.Rows + 1 MSHFlexGrid1.Row = MSHFlexGrid1.Rows - 1 Dim TXT1 As Double Dim TXT2 As Double 。。 Dim TXT12 As Double
MSHFlexGrid1.Col = 3 MSHFlexGrid1.Text = Myrs1.Fields("SFJ_GLFST") & "" If Myrs1.Fields("SFJ_GLFST") <> "" Then TXT1 = CDbl(Myrs1.Fields("SFJ_GLFST") & "") Else TXT1 = 0 End If MSHFlexGrid1.Col = 4 MSHFlexGrid1.Text = Myrs1.Fields("SFJ_GLFYC") & "" If Myrs1.Fields("SFJ_GLFYC") <> "" Then TXT2 = CDbl(Myrs1.Fields("SFJ_GLFYC") & "") Else TXT2 = 0 End If 。 MSHFlexGrid1.Col = 23 MSHFlexGrid1.Text = TXT1 + TXT2 + TXT3 + TXT4 + TXT5 + TXT6 + txt7 + TXT8 + TXT9 + TXT10 + TXT11 + TXT12 Myrs1.MoveNext Wend Myrs1.Close Else MsgBox "不存在这条记录!", vbOKOnly + vbExclamation, "信息提示" End If Mycn.Close End Sub Public Sub printReport(Rpt As CrystalReport, ByVal sql As String) Dim ReportFileName, ReportName As String Rpt.WindowState = crptMaximized ReportFileName = App.Path & "\glfjsyd\jsyd_fyqk_jsydzbgftjb_yi.rpt" Rpt.Connect = "dsn=dytd;uid=dytd;pwd=dytd" Rpt.ReportFileName = ReportFileName + ReportName Rpt.SQLQuery = sql Rpt.Action = 2 End SubPrivate Sub Command5_Click() Dim sql As String If Combo1.Enabled = True And Combo2.Enabled = False Then sql = "select * from JS_FYQK_JSYDZBGFTJB where trim(SFJ_XH)='" & Trim(Combo1.Text) & "'" End If If Combo2.Enabled = True And Combo1.Enabled = False Then sql = "select * from JS_FYQK_JSYDZBGFTJB where trim(SFJ_DW)='" & Trim(Combo2.Text) & "'" End If If Combo2.Enabled = True And Combo1.Enabled = True Then sql = "select * from JS_FYQK_JSYDZBGFTJB where trim(SFJ_DW)='" & Trim(Combo2.Text) & "' AND trim(SFJ_XH)='" & Trim(Combo1.Text) & "'" End If If Combo1.Enabled = False And Combo2.Enabled = False Then sql = "select * from JS_FYQK_JSYDZBGFTJB"End IfprintReport CrystalReport1, sql End Sub 查询打印全有你自己研究一下吧!有问题再问我:)我用的mshgrid用水晶报表7打印查询结果
if combo1.text<>"" then
str="where " & combo1.text & " & " & combo2.text & " & '" & text1.text & "' "
end if strsql=strsql & str
条件=combo1.value & combo2.value & text.text 'value 记得可能不够准确
select * from 表 where 条件
这样就可以组合出你的WHERE的条件了!
adodc1.recordsource="select * from table where " & combo1.text & combo2.text & "'" & text1.text & "'"
adodc1.refresh
strcn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;password=symade;Initial Catalog=ClientData;Data Source=DATACENTER"
Dim StrSql As String
Dim Strquery As String
Strquery = "select * from clientinfo"
Set cnsql = New ADODB.Connection
With cnsql
If .State = adStateOpen Then
.Close
End If
.ConnectionString = strcn
.CursorLocation = adUseClient
.Open
End With
Set rssql = New ADODB.Recordset
With rssql
.ActiveConnection = cnsql
.CursorLocation = adUseClient
If Text1.Text = "" Then
MsgBox "请输入要查询的值!", vbInformation + vbOKOnly, "提示:"
Else
StrSql = " where" & Combo1.Text & " & " & Combo2.Text & " '" & Trim(Text1.Text) & "' "
End If
Strquery = Strquery & StrSql
rssql.Open Strquery, , , , adCmdText
Set DataGrid1.DataSource = rs
End With
看看上面这个程序,运行到rssql.Open Strquery, , , , adCmdText这行代码时提示:‘&'附近有语法错误。运行时strquery的值是 select * from 表名where ...,也就是表名和where没有分开,怎样纠正这个错误,请兄弟指点
StrSql = " where " & Combo1.Text & " & " & Combo2.Text & " '" & Trim(Text1.Text) & "' "
Private Sub Command1_Click()
Dim SQLstr As String, tempsqlstr As String, msgstring As String
Dim i As Integer
For i = 0 To 2
Call Check1_Click(i)
Next i
SQLstr = ""
If (Check1(0).Value = vbChecked) And Combo1(0).List(Combo1(0).ListIndex) <> Trim("模糊等于") Then
SQLstr = "xm" & Trim(Combo1(0).Text) & "'" & Trim(Text1(0).Text) & "'"
Else
If (Check1(0).Value = vbChecked) And Combo1(0).List(Combo1(0).ListIndex) = Trim("模糊等于") Then
SQLstr = "xm like '%" & Trim(Text1(0).Text) & "%'"
Else
SQLstr = ""
End If
End If
tempsqlstr = "jszh" & Trim(Combo1(1).Text) & "'" & Trim(Text1(1).Text) & "'" If Check1(1).Value = vbChecked Then
If Trim(SQLstr) = "" Then
SQLstr = tempsqlstr
Else
SQLstr = SQLstr & " And " & tempsqlstr
End If
End If
tempsqlstr = "bzrq" & Trim(Combo1(2).Text) & "'" & Trim(Text1(2).Text) & "'"
If Check1(2).Value = vbChecked Then
If Trim(SQLstr) = "" Then
SQLstr = tempsqlstr
Else
SQLstr = SQLstr & " And " & tempsqlstr
End If
End If
tempsqlstr = "czy" & Trim(Combo2.Text) & "'" & Trim(Combo3.Text) & "'"
If Check2.Value = vbChecked Then
If Trim(SQLstr) = "" Then
SQLstr = tempsqlstr
Else
SQLstr = SQLstr & " And " & tempsqlstr
End If
End If
If Trim(SQLstr) = "" Then
MsgBox "你没有设置查询条件,请重新设置查询条件!!!", vbOKOnly + vbExclamation, "提示"
Exit Sub
End If
On Error GoTo queryerror
Set rss = New ADODB.Recordset
If Option1(0).Value = True Then
SQLstr = "select * from readerinfo where " & SQLstr
Set rss = ExecuteSQL(SQLstr, msgstring)
Set DataGrid1(0).DataSource = rss
DataGrid1(0).Refresh
Else
If Option1(1).Value = True Then
SQLstr = "select * from delreaderinfo where " & SQLstr
Set rss = ExecuteSQL(SQLstr, msgstring)
Set DataGrid1(1).DataSource = rss
DataGrid1(1).Refresh
End If
End If
If rss.BOF And rss.EOF Then
MsgBox "数据库中查无此记录!!!", vbOKOnly + vbInformation, "信息"
Exit Sub
Else
rss.MoveLast
rss.MoveFirst
MsgBox "本次操作共查询到" & rss.RecordCount & "条记录!!!", vbOKOnly + vbInformation, "信息"
End If
Exit Sub
queryerror:
MsgBox "查询条件不能为空!!!", vbOKOnly + vbCritical, "错误"
Exit Sub
End Sub
Dim Mycn As New ADODB.Connection '数据库连接对象,用于打开数据库
Dim Myrs1 As New ADODB.Recordset '记录集对象,用于存入记录的Private Sub TableInit()
MSHFlexGrid1.Rows = 0
MSHFlexGrid1.Cols = 24
End SubPrivate Sub Command7_Click()
End
End SubPrivate Sub Form_Load() MSHFlexGrid1.Rows = 0
MSHFlexGrid1.Cols = 24 MSHFlexGrid1.ColWidth(0) = 620
。
MSHFlexGrid1.ColWidth(23) = 740
Dim cnstring As String '用于存放连接字符串的
cnstring = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=dytd;PassWord=dytd;Data Source=dytd"
Mycn.Open cnstring
Mycn.CursorLocation = adUseClient Myrs1.Open "select * from JS_FYQK_JSYDZBGFTJB ", Mycn
MSHFlexGrid1.Clear
TableInit
While Myrs1.EOF = False
MSHFlexGrid1.Rows = MSHFlexGrid1.Rows + 1
MSHFlexGrid1.Row = MSHFlexGrid1.Rows - 1
Dim TXT1 As Double
。
Dim TXT12 As Double
MSHFlexGrid1.Col = 0
MSHFlexGrid1.Text = Myrs1.Fields("SFJ_XH") & ""
MSHFlexGrid1.Col = 1
MSHFlexGrid1.Text = Myrs1.Fields("SFJ_DW") & ""
。
MSHFlexGrid1.Col = 20
MSHFlexGrid1.Text = Myrs1.Fields("SFJ_SLJSJJ") & ""
MSHFlexGrid1.Col = 21
MSHFlexGrid1.Text = Myrs1.Fields("SFJ_QS") & ""
MSHFlexGrid1.Col = 22
MSHFlexGrid1.Text = Myrs1.Fields("SFJ_QT") & ""
MSHFlexGrid1.Col = 23
MSHFlexGrid1.Text = TXT1 + TXT2 + TXT3 + TXT4 + TXT5 + TXT6 + txt7 + TXT8 + TXT9 + TXT10 + TXT11 + TXT12
Myrs1.MoveNext
Wend
Myrs1.Close
Mycn.Close
cnstring = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=dytd;PassWord=dytd;Data Source=dytd"
Mycn.Open cnstring
Mycn.CursorLocation = adUseClient
Myrs1.Open "select distinct trim(SFJ_XH) from JS_FYQK_JSYDZBGFTJB ", Mycn
If Myrs1.RecordCount >= 1 Then
While Myrs1.EOF = False
If IsNull(Myrs1.Fields(0)) = False Then
Combo1.AddItem Myrs1.Fields(0)
End If
Myrs1.MoveNext
Wend
Myrs1.Close
Else
MsgBox "仓库中目前无余额信息,请添加进库信息后再试!", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
Mycn.Close
cnstring = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=dytd;PassWord=dytd;Data Source=dytd"
Mycn.Open cnstring
Mycn.CursorLocation = adUseClient
Myrs1.Open "select distinct trim(SFJ_DW) from JS_FYQK_JSYDZBGFTJB ", Mycn
If Myrs1.RecordCount >= 1 Then
While Myrs1.EOF = False
If IsNull(Myrs1.Fields(0)) = False Then
Combo2.AddItem Myrs1.Fields(0)
End If
Myrs1.MoveNext
Wend
Myrs1.Close
Else
MsgBox "仓库中目前无余额信息,请添加进库信息后再试!", vbOKOnly + vbExclamation, "警告"
Exit Sub
End If
Mycn.Close
End Sub
Private Sub Check1_Click()
If Check1.Value = vbChecked Then
Combo1.Enabled = True
Else
Combo1.Enabled = False
End If
End SubPrivate Sub Check2_Click()
If Check2.Value = vbChecked Then
Combo2.Enabled = True
Else
Combo2.Enabled = False
End If
End Sub
Private Sub Command6_Click()
Dim cnstring As String '用于存放连接字符串的
cnstring = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=dytd;PassWord=dytd;Data Source=dytd"
Mycn.Open cnstring
Mycn.CursorLocation = adUseClient
If Combo1.Enabled = True And Combo2.Enabled = False Then
Myrs1.Open "select * from JS_FYQK_JSYDZBGFTJB where trim(SFJ_XH)='" & Trim(Combo1.Text) & "'", Mycn
End If
If Combo2.Enabled = True And Combo1.Enabled = False Then
Myrs1.Open "select * from JS_FYQK_JSYDZBGFTJB where trim(SFJ_DW)='" & Trim(Combo2.Text) & "'", Mycn
End If
If Combo2.Enabled = True And Combo1.Enabled = True Then
Myrs1.Open "select * from JS_FYQK_JSYDZBGFTJB where trim(SFJ_DW)='" & Trim(Combo2.Text) & "' AND trim(SFJ_XH)='" & Trim(Combo1.Text) & "'", Mycn
End If
If Combo1.Enabled = False And Combo2.Enabled = False Then
Myrs1.Open "select * from JS_FYQK_JSYDZBGFTJB", Mycn
MsgBox "请设置查询方式!", vbOKOnly + vbExclamation, "警告"
End If
MSHFlexGrid1.Clear
TableInit
If Myrs1.RecordCount >= 1 Then
While Myrs1.EOF = False
MSHFlexGrid1.Rows = MSHFlexGrid1.Rows + 1
MSHFlexGrid1.Row = MSHFlexGrid1.Rows - 1
Dim TXT1 As Double
Dim TXT2 As Double
。。
Dim TXT12 As Double
MSHFlexGrid1.Col = 0
MSHFlexGrid1.Text = Myrs1.Fields("SFJ_XH") & ""
MSHFlexGrid1.Col = 1
MSHFlexGrid1.Text = Myrs1.Fields("SFJ_DW") & ""
MSHFlexGrid1.Col = 3
MSHFlexGrid1.Text = Myrs1.Fields("SFJ_GLFST") & ""
If Myrs1.Fields("SFJ_GLFST") <> "" Then
TXT1 = CDbl(Myrs1.Fields("SFJ_GLFST") & "")
Else
TXT1 = 0
End If
MSHFlexGrid1.Col = 4
MSHFlexGrid1.Text = Myrs1.Fields("SFJ_GLFYC") & ""
If Myrs1.Fields("SFJ_GLFYC") <> "" Then
TXT2 = CDbl(Myrs1.Fields("SFJ_GLFYC") & "")
Else
TXT2 = 0
End If
。
MSHFlexGrid1.Col = 23
MSHFlexGrid1.Text = TXT1 + TXT2 + TXT3 + TXT4 + TXT5 + TXT6 + txt7 + TXT8 + TXT9 + TXT10 + TXT11 + TXT12
Myrs1.MoveNext
Wend
Myrs1.Close
Else
MsgBox "不存在这条记录!", vbOKOnly + vbExclamation, "信息提示"
End If
Mycn.Close
End Sub
Public Sub printReport(Rpt As CrystalReport, ByVal sql As String)
Dim ReportFileName, ReportName As String
Rpt.WindowState = crptMaximized
ReportFileName = App.Path & "\glfjsyd\jsyd_fyqk_jsydzbgftjb_yi.rpt"
Rpt.Connect = "dsn=dytd;uid=dytd;pwd=dytd"
Rpt.ReportFileName = ReportFileName + ReportName
Rpt.SQLQuery = sql
Rpt.Action = 2
End SubPrivate Sub Command5_Click()
Dim sql As String
If Combo1.Enabled = True And Combo2.Enabled = False Then
sql = "select * from JS_FYQK_JSYDZBGFTJB where trim(SFJ_XH)='" & Trim(Combo1.Text) & "'"
End If
If Combo2.Enabled = True And Combo1.Enabled = False Then
sql = "select * from JS_FYQK_JSYDZBGFTJB where trim(SFJ_DW)='" & Trim(Combo2.Text) & "'"
End If
If Combo2.Enabled = True And Combo1.Enabled = True Then
sql = "select * from JS_FYQK_JSYDZBGFTJB where trim(SFJ_DW)='" & Trim(Combo2.Text) & "' AND trim(SFJ_XH)='" & Trim(Combo1.Text) & "'"
End If
If Combo1.Enabled = False And Combo2.Enabled = False Then
sql = "select * from JS_FYQK_JSYDZBGFTJB"End IfprintReport CrystalReport1, sql
End Sub
查询打印全有你自己研究一下吧!有问题再问我:)我用的mshgrid用水晶报表7打印查询结果