是这样的,我要做一个组合条件的查询,以下的代码是根据文本框的text1和text2的两个条件查询,
等一下我要写一个以6个文本框作为的组合条件查询,那么就要写64条判断语句,写到手都快软了,请问家有什么更好的方法?
Private Sub Command1_Click()
dim cnn as New ADODB.Connection
dim rst as New ADODB.Recordset
rst.CursorLocation = adUseClient cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=cf;Data Source=TOMMYSKY"
If Text1 = "" And Text2 = "" Then '0,0
rst.Open "select * from T_unit where fscode='' and fsunit=''", cnn, adOpenKeyset, adLockOptimistic
ElseIf Text1 = "" And Text2 <> "" Then '0,1
rst.Open "select * from T_unit where fsunit='" & Text2 & "'", cnn, adOpenKeyset, adLockOptimistic
ElseIf Text1 <> "" And Text2 = "" Then '1,0
rst.Open "select * from T_unit where fscode='" & Text1 & "'", cnn, adOpenKeyset, adLockOptimistic
ElseIf Text1 <> "" And Text2 <> "" Then '1,1
rst.Open "select * from T_unit where fscode='" & Text1 & "'and fsunit='" & Text2 & "'", cnn, adOpenKeyset, adLockOptimistic
End if
set datagrid1.datasource=rstEnd Sub
等一下我要写一个以6个文本框作为的组合条件查询,那么就要写64条判断语句,写到手都快软了,请问家有什么更好的方法?
Private Sub Command1_Click()
dim cnn as New ADODB.Connection
dim rst as New ADODB.Recordset
rst.CursorLocation = adUseClient cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=cf;Data Source=TOMMYSKY"
If Text1 = "" And Text2 = "" Then '0,0
rst.Open "select * from T_unit where fscode='' and fsunit=''", cnn, adOpenKeyset, adLockOptimistic
ElseIf Text1 = "" And Text2 <> "" Then '0,1
rst.Open "select * from T_unit where fsunit='" & Text2 & "'", cnn, adOpenKeyset, adLockOptimistic
ElseIf Text1 <> "" And Text2 = "" Then '1,0
rst.Open "select * from T_unit where fscode='" & Text1 & "'", cnn, adOpenKeyset, adLockOptimistic
ElseIf Text1 <> "" And Text2 <> "" Then '1,1
rst.Open "select * from T_unit where fscode='" & Text1 & "'and fsunit='" & Text2 & "'", cnn, adOpenKeyset, adLockOptimistic
End if
set datagrid1.datasource=rstEnd Sub
case
请说具体一点,怎么 select case法呀?
我给你写个方法,但是最好建议用控件数组,
6个文本框,text1-text6dim sql as string
dim re as ADODB.Recordset
sql="select * from T_unit where 1=1"
if text1.text<>"" then sql=sql & " and fscode='" & text1.text & "'"
if text2.text<>"" then sql=sql & " and fsunit='" & text1.text & "'"
if text3.text<>"" then sql=sql & " and fscode='" & text1.text & "'"
if text4.text<>"" then sql=sql & " and fscode='" & text1.text & "'"
if text5.text<>"" then sql=sql & " and fscode='" & text1.text & "'"
if text6.text<>"" then sql=sql & " and fscode='" & text1.text & "'"
set re=exsql(sql)Public Sub ExSql(sql As String)
'执行sql语句
Dim com As ADODB.Command
Set com = New ADODB.Command
dim cn as string
cn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=cf;Data Source=TOMMYSKY"On Error Resume Next
com.ActiveConnection = cn
com.CommandText = sql
com.Execute
End Sub
是不是下面这样呀,但 and 怎么处理呀,SQL语句会执行错误的dim sql as string
dim re as ADODB.Recordset
sql="select * from T_unit where "
if text1.text<>"" then sql=sql & " and 字段1='" & text1.text & "'"
if text2.text<>"" then sql=sql & " and 字段2='" & text2.text & "'"
if text3.text<>"" then sql=sql & " and 字段3='" & text3.text & "'"
if text4.text<>"" then sql=sql & " and 字段4='" & text4.text & "'"
if text5.text<>"" then sql=sql & " and 字段5='" & text5.text & "'"
if text6.text<>"" then sql=sql & " and 字段6='" & text6.text & "'"
set re=exsql(sql)Public Sub ExSql(sql As String)
'执行sql语句
Dim com As ADODB.Command
Set com = New ADODB.Command
dim cn as string
cn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=cf;Data Source=TOMMYSKY"On Error Resume Next
com.ActiveConnection = cn
com.CommandText = sql
com.Execute
End Sub
sql="select * from T_unit where TRUE "
For i = 1 To 64
if text1(i)>"" then sql=sql & " AND 字段" & i & "='" & text1(i) & "'"
Next i
每个字段名都是不同的
dim strWhere as string
dim re as ADODB.Recordsetsql="select * from 表名 "if text1.text<>"" then
strWhere=" Where 字段1='" & text1.text & "'"
end if
if text2.text<>"" then
if strwhere <>"" then
strwhere=strwhere & " and 字段2='" & text2.text & "'"
else
strWhere=" Where 字段2='" & text2.text & "'"
end if
end if
if text3.text<>"" then
if strwhere <>"" then
strwhere=strwhere & " and 字段3='" & text3.text & "'"
else
strWhere=" Where 字段3='" & text3.text & "'"
end if
end if
if text4.text<>"" then
if strwhere <>"" then
strwhere=strwhere & " and 字段4='" & text4.text & "'"
else
strWhere=" Where 字段4='" & text4.text & "'"
end if
end if
if text5.text<>"" then
if strwhere <>"" then
strwhere=strwhere & " and 字段5='" & text5.text & "'"
else
strWhere=" Where 字段5='" & text5.text & "'"
end if
end if
if text6.text<>"" then
if strwhere <>"" then
strwhere=strwhere & " and 字段6='" & text6.text & "'"
else
strWhere=" Where 字段6='" & text6.text & "'"
end if
end if
if strwhere<>"" then
sql=sql & strwhere
end if
set re=exsql(sql)Public Sub ExSql(sql As String)
'执行sql语句
Dim com As ADODB.Command
Set com = New ADODB.Command
dim cn as string
cn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=cf;Data Source=TOMMYSKY"On Error Resume Next
com.ActiveConnection = cn
com.CommandText = sql
com.Execute
End Sub
dim re as ADODB.Recordset
sql=""
if text1.text<>"" then sql=sql & " and 字段1='" & text1.text & "'"
if text2.text<>"" then sql=sql & " and 字段2='" & text2.text & "'"
if text3.text<>"" then sql=sql & " and 字段3='" & text3.text & "'"
if text4.text<>"" then sql=sql & " and 字段4='" & text4.text & "'"
if text5.text<>"" then sql=sql & " and 字段5='" & text5.text & "'"
if text6.text<>"" then sql=sql & " and 字段6='" & text6.text & "'"
if sql<>"" then
sql=mid(sql,instr(sql,"and")+3)
sql="select * from T_unit where " & sql
set re=exsql(sql)
end if
dim sql as string
dim re as ADODB.Recordset
sql="select * from T_unit where 1=1"'注意这里,我主要处理了这里,必须加上1=1
if text1.text<>"" then sql=sql & " and 字段1='" & text1.text & "'"
if text2.text<>"" then sql=sql & " and 字段2='" & text2.text & "'"
if text3.text<>"" then sql=sql & " and 字段3='" & text3.text & "'"
if text4.text<>"" then sql=sql & " and 字段4='" & text4.text & "'"
if text5.text<>"" then sql=sql & " and 字段5='" & text5.text & "'"
if text6.text<>"" then sql=sql & " and 字段6='" & text6.text & "'"
set re=exsql(sql)Public Sub ExSql(sql As String)
'执行sql语句
Dim com As ADODB.Command
Set com = New ADODB.Command
dim cn as string
cn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=cf;Data Source=TOMMYSKY"On Error Resume Next
com.ActiveConnection = cn
com.CommandText = sql
com.Execute
End Sub
......................
if trim(strSQL)<>"" then strSQL=" Where " & mid(strSQL,1,Len(strSQL)-4)
strSQL="Select * from TableName " & strSQL