注册 登录
编程论坛 → VB6论坛

如何简化优化代码

yuk_yu 发布于 2014-03-25 17:04， 349 次点击
程序代码：
    If Me.Combo1(0) <> "" And Me.Combo1(1) <> "" And Me.Combo1(2) <> "" And Me.Combo1(3) <> "" Then
        SQL = "select * from DataList"
        SQL = SQL + " where [UploadSN] ='" & Me.Combo1(0) & "' and [BuyerName] ='" & Me.Combo1(1) & "' and [MaterialPart] ='" & Me.Combo1(2) & "' and [YesOrNo] ='" & Me.Combo1(3) & "'"

    ElseIf Me.Combo1(0) <> "" And Me.Combo1(1) = "" And Me.Combo1(2) = "" And Me.Combo1(3) = "" Then
        SQL = "select * from DataList where [UploadSN] ='" & Me.Combo1(0) & "' "

    ElseIf Me.Combo1(0) <> "" And Me.Combo1(1) <> "" And Me.Combo1(2) = "" And Me.Combo1(3) = "" Then
        SQL = "select * from DataList where [UploadSN] ='" & Me.Combo1(0) & "' and [BuyerName] ='" & Me.Combo1(1) & "' "

    ElseIf Me.Combo1(0) <> "" And Me.Combo1(1) <> "" And Me.Combo1(2) <> "" And Me.Combo1(3) = "" Then
        SQL = "select * from DataList where [UploadSN] ='" & Me.Combo1(0) & "' and [BuyerName] ='" & Me.Combo1(1) & "' and [MaterialPart] ='" & Me.Combo1(2) & "' "

    ElseIf Me.Combo1(0) = "" And Me.Combo1(1) = "" And Me.Combo1(2) = "" And Me.Combo1(3) <> "" Then
        SQL = "select * from DataList where [YesOrNo] ='" & Me.Combo1(3) & "'"

    ElseIf Me.Combo1(0) = "" And Me.Combo1(1) = "" And Me.Combo1(2) <> "" And Me.Combo1(3) <> "" Then
        SQL = "select * from DataList where [MaterialPart] ='" & Me.Combo1(2) & "' and [YesOrNo] ='" & Me.Combo1(3) & "'"

    ElseIf Me.Combo1(0) = "" And Me.Combo1(1) <> "" And Me.Combo1(2) <> "" And Me.Combo1(3) <> "" Then
        SQL = "select * from DataList where [BuyerName] ='" & Me.Combo1(1) & "' and [MaterialPart] ='" & Me.Combo1(2) & "' and [YesOrNo] ='" & Me.Combo1(3) & "'"

    ElseIf Me.Combo1(0) <> "" And Me.Combo1(1) = "" And Me.Combo1(2) <> "" And Me.Combo1(3) = "" Then
        SQL = "select * from DataList"
        SQL = SQL + " where [UploadSN] ='" & Me.Combo1(0) & "' and [MaterialPart] ='" & Me.Combo1(2) & "'"
    ElseIf Me.Combo1(0) = "" And Me.Combo1(1) = "" And Me.Combo1(2) <> "" And Me.Combo1(3) = "" Then
        SQL = "select * from DataList"
        SQL = SQL + " where [MaterialPart] ='" & Me.Combo1(2) & "'"

    ElseIf Me.Combo1(0) <> "" And Me.Combo1(1) = "" And Me.Combo1(2) = "" And Me.Combo1(3) <> "" Then
        SQL = "select * from DataList"
        If Me.Combo1(3).Text = "Null" Then
            SQL = SQL + " where [UploadSN] ='" & Me.Combo1(0) & "' and [YesOrNo] is null"
        Else
            SQL = SQL + " where [UploadSN] ='" & Me.Combo1(0) & "' and [YesOrNo] ='" & Me.Combo1(3) & "'"
        End If

    ElseIf Me.Combo1(0) = "" And Me.Combo1(1) = "" And Me.Combo1(2) = "" And Me.Combo1(3) = "" Then
        MsgBox "Pls selct one condition", vbCritical, "Caution"
        Exit Sub
    End If
4 回复
#2
lowxiong2014-03-25 17:38
dim a as string,i as integer,SQL as string
SQL = "select * from DataList "
a=""
for i=0 to 3
  combo1(i)=trim(combo1(i))
  a=a & combo1(i)
next
if a<>"" then
  sql=sql & " where "
  if combo1(0)<>"" then sql=sql & "[UploadSN] ='" & (0) & "' and "
  if combo1(1)<>"" then sql=sql & "[BuyerName] ='" & (1) & "' and "
  if combo1(2)<>"" then sql=sql & "[MaterialPart] ='" & (2) & "' and "
  if combo1(3)<>"" then sql=sql & "[YesOrNo] ='" & (3) & "' "
  if right(sql,4)="and " then sql=left(sql,len(sql)-4)
else
  MsgBox "Pls selct one condition", vbCritical, "Caution"
  Exit Sub
End If
   




[ 本帖最后由 lowxiong 于 2014-3-25 17:39 编辑 ]
#3
yuk_yu2014-03-25 18:44
回复 2楼 lowxiong
谢谢版主
#4
风吹过b2014-03-26 08:53
dim a as string,i as integer,SQL as string

SQL = "select * from DataList "

'如果存在，则拼上条件去
  if combo1(0)<>"" then a=a  & "and [UploadSN] ='" & (0) & "'"
  if combo1(1)<>"" then a=a  & "and [BuyerName] ='" & (1) & "'"
  if combo1(2)<>"" then a=a  & "and [MaterialPart] ='" & (2) & "'"
  if combo1(3)<>"" then a=a  & "and [YesOrNo] ='" & (3) & "' "

'检查拼起来的条件，如果有内容，则生成 SQL 命令
if len(a)>0 then
  sql=sql & " where " & mid(a,4)            '如果有内容，那么开头 and 一定是多余的，直接去掉就可了。
else
  MsgBox "Pls selct one condition", vbCritical, "Caution"
  Exit Sub
End If
#5
yuk_yu2014-03-26 15:07
回复 4楼 风吹过b
谢谢斑竹，太厉害了，学习ing...
1
©2025, BC-CN.NET
电脑版