很简单,,,你SQL语句后面的条件错了嘻嘻,想写一个简单的SQL生成界面是吗???看我过去写的一个小代码***********************************Private Sub Form_Load()
Dim fildType() As String Set db = OpenDatabase(App.Path & "\db2.mdb") Set rs = db.OpenRecordset("biao") ReDim fildType(rs.Fields.Count) Combo1.AddItem "*", 0 For i = 1 To rs.Fields.Count Combo1.AddItem rs.Fields(i - 1).Name, i 'Combo11.AddItem rs.Fields(i - 1).Name, i-1 GoSub FixType fildType(i - 1) = FieldType
Next ComboL.AddItem "*" For i = 0 To rs.Fields.Count - 1 ComboL.AddItem rs.Fields(i).Name Next
End Select End With Private Sub Command1_Click() sql = "select "With MSFlexGrid2 If Option1.Value = True Then .Col = 0 .Row = 1 sql = sql & .Text For i = 2 To .Rows - 1 .Row = i If .Text <> "" Then sql = sql & ", " & .Text End If
Next End If If Option2.Value = True Then .Col = 0 .Row = 1 sql = sql & .Text & ", Count(" & .Text & ") as CNumber " End If End With sql1 = sql sql = "" sql = sql & " from biao "With MSFlexGrid1 .Col = 1 .Row = 1 If .Text <> "" Then sql = sql & " where "
For i = 1 To .Rows - 1 .Row = i .Col = 1 If .Text <> "" Then For j = 1 To .Cols - 1 .Col = j Select Case j Case 1 sql = sql & .Text & " " Case 2 cbstr = .Text Case 3 sql = sql & cB2(cbstr, .Text) Case 4 sql = sql & ifStr(.Text) End Select Next End If Next End WithIf Option2.Value = True Then sql = sql & " GROUP BY " & MSFlexGrid2.Text sql2 = sqlTextSql = sql1 & sql2 MsgBox "查询语句已成功生成!!" End Sub
txtfind.Text为空时,会出现上述错误提示; 因为"select * from Maintenance where ''"是一条错误语句。
If cmbfield.Text <> "" And cmbcompare <> "" And txtdata <> "" Then Adodc1.RecordSource = "select * from Maintenance where "& txtfind.Text Adodc1.Refresh End If 改成: Adodc1.RecordSource = "select * from Maintenance where 字段名 = " & "'" & txtfind.Text & "'"测试连接是否成功
Dim fildType() As String
Set db = OpenDatabase(App.Path & "\db2.mdb")
Set rs = db.OpenRecordset("biao")
ReDim fildType(rs.Fields.Count)
Combo1.AddItem "*", 0
For i = 1 To rs.Fields.Count
Combo1.AddItem rs.Fields(i - 1).Name, i
'Combo11.AddItem rs.Fields(i - 1).Name, i-1
GoSub FixType
fildType(i - 1) = FieldType
Next
ComboL.AddItem "*"
For i = 0 To rs.Fields.Count - 1
ComboL.AddItem rs.Fields(i).Name
Next
Combo2.AddItem "="
Combo2.AddItem "包含"
Combo2.AddItem "左包含"
Combo2.AddItem "右包含"
Combo2.AddItem "精确比较"
Combo2.AddItem ">"
Combo2.AddItem "<"
Combo2.AddItem ">="
Combo2.AddItem "<="
Combo2.AddItem "<>"
Combo3.AddItem "并且"
Combo3.AddItem "或者"
Combo3.AddItem "非"
With MSFlexGrid2
.ColWidth(0) = 2150
.Row = 0
.Col = 0
.Text = "字段名"
End With
With MSFlexGrid1
.Row = 1
.Col = 0
.Text = "条件"
.ColWidth(1) = 2100
.Row = 0
.Col = 1
.Text = "字段名"
.ColWidth(2) = 1000
.Row = 0
.Col = 2
.Text = "运算符"
.ColWidth(3) = 3000
.Row = 0
.Col = 3
.Text = "条件表达式"
.ColWidth(4) = 1000
.Row = 0
.Col = 4
.Text = "关系"
End With
Private Sub MSFlexGrid1_Click()
Dim c As Integer, r As Integer
With MSFlexGrid1
c = .Col: r = .Row
Select Case c
Case 1
Combo1.left = .left + .ColPos(c)
Combo1.top = .top + .RowPos(r)
Combo1.Width = .ColWidth(c)
Combo1.Text = .Text
Combo1.Visible = True
Combo1.SetFocus
Case 2
Combo2.left = .left + .ColPos(c)
Combo2.top = .top + .RowPos(r)
Combo2.Width = .ColWidth(c)
Combo2.Text = .Text
Combo2.Visible = True
Combo2.SetFocus
Case 3
Text1.left = .left + .ColPos(c)
Text1.top = .top + .RowPos(r)
Text1.Width = .ColWidth(c)
Text1.Height = .RowHeight(r)
Text1 = .Text
Text1.Visible = True
Text1.SetFocus
Case 4
Combo3.left = .left + .ColPos(c)
Combo3.top = .top + .RowPos(r)
Combo3.Width = .ColWidth(c)
Combo3.Text = .Text
Combo3.Visible = True
Combo3.SetFocus
End Select
End With
Private Sub Command1_Click()
sql = "select "With MSFlexGrid2
If Option1.Value = True Then
.Col = 0
.Row = 1
sql = sql & .Text
For i = 2 To .Rows - 1
.Row = i
If .Text <> "" Then
sql = sql & ", " & .Text
End If
Next
End If
If Option2.Value = True Then
.Col = 0
.Row = 1
sql = sql & .Text & ", Count(" & .Text & ") as CNumber "
End If
End With
sql1 = sql
sql = ""
sql = sql & " from biao "With MSFlexGrid1
.Col = 1
.Row = 1
If .Text <> "" Then sql = sql & " where "
For i = 1 To .Rows - 1
.Row = i
.Col = 1
If .Text <> "" Then
For j = 1 To .Cols - 1
.Col = j
Select Case j
Case 1
sql = sql & .Text & " "
Case 2
cbstr = .Text
Case 3
sql = sql & cB2(cbstr, .Text)
Case 4
sql = sql & ifStr(.Text)
End Select
Next
End If
Next
End WithIf Option2.Value = True Then sql = sql & " GROUP BY " & MSFlexGrid2.Text
sql2 = sqlTextSql = sql1 & sql2
MsgBox "查询语句已成功生成!!"
End Sub
因为"select * from Maintenance where ''"是一条错误语句。
where 查询结果 = '" & txtfind.Text & "'"
我用断点调试发现程序执行到select语句没有问题另外txtxfind.text不会为空(前面有容错语句,没贴出来)还有谁知道怎么回事?
太感谢你了
问题解决了
一句话 茅塞顿开
给分
谢谢
别的帮忙的也有
谢谢大家!!!
If cmbfield.Text <> "" And cmbcompare <> "" And txtdata <> "" Then
Adodc1.RecordSource = "select * from Maintenance where "& txtfind.Text
Adodc1.Refresh
End If
改成:
Adodc1.RecordSource = "select * from Maintenance where 字段名 = " & "'" & txtfind.Text & "'"测试连接是否成功