明天答辩了跪求各位大虾指点在线等我有7个combobox,想要任意的组合查询符合条件的数据譬如我可以选择一个combobox查询,也可以是两个或三个这样的任意组合查询。。一点击查询就会出现“from 子句语法错误”。表格名称是“ygjibenxinxibiao”。代码如下:
Dim a, b, c, d, f, g, j As String
a = ComboBox1.Text
b = ComboBox2.Text
c = ComboBox3.Text
d = ComboBox4.Text
f = ComboBox5.Text
g = ComboBox6.Text
j = ComboBox7.Text
If a <> "" Then
strsql = "where xueli =a"
End If
If b <> "" Then
If strsql = "" Then
strsql = "where sex=b"
Else
strsql = strsql + "and sex = b"
End If
End If
If c <> "" Then
If strsql = "" Then
strsql = "where nation=c"
Else
strsql = strsql + "and nation =c"
End If
End If
If d <> "" Then
If strsql = "" Then
strsql = "where position=d"
Else
strsql = strsql + "and position =d"
End If
End If
If f <> "" Then
If strsql = "" Then
strsql = "where department=f"
Else
strsql = strsql + "department=f"
End If
End If
If g <> "" Then
If strsql = "" Then
strsql = "where province=g"
Else
strsql = strsql + "and province=g"
End If
End If
If j <> "" Then
If strsql = "" Then
strsql = "where zzmianmao=j"
Else
strsql = strsql + "and zzmianmao=j"
End If
End If
strsql1 = "select * from ygjibenxinxibiao + strsql"
conn = New OleDbConnection(strconn)
conn.Open()
da = New OleDbDataAdapter(strsql1, conn)
ds = New DataSet
da.Fill(ds, "ygjibenxinxibiao")
DataGridView1.DataSource = ds.Tables("ygjibenxinxibiao")
Dim a, b, c, d, f, g, j As String
a = ComboBox1.Text
b = ComboBox2.Text
c = ComboBox3.Text
d = ComboBox4.Text
f = ComboBox5.Text
g = ComboBox6.Text
j = ComboBox7.Text
If a <> "" Then
strsql = "where xueli =a"
End If
If b <> "" Then
If strsql = "" Then
strsql = "where sex=b"
Else
strsql = strsql + "and sex = b"
End If
End If
If c <> "" Then
If strsql = "" Then
strsql = "where nation=c"
Else
strsql = strsql + "and nation =c"
End If
End If
If d <> "" Then
If strsql = "" Then
strsql = "where position=d"
Else
strsql = strsql + "and position =d"
End If
End If
If f <> "" Then
If strsql = "" Then
strsql = "where department=f"
Else
strsql = strsql + "department=f"
End If
End If
If g <> "" Then
If strsql = "" Then
strsql = "where province=g"
Else
strsql = strsql + "and province=g"
End If
End If
If j <> "" Then
If strsql = "" Then
strsql = "where zzmianmao=j"
Else
strsql = strsql + "and zzmianmao=j"
End If
End If
strsql1 = "select * from ygjibenxinxibiao + strsql"
conn = New OleDbConnection(strconn)
conn.Open()
da = New OleDbDataAdapter(strsql1, conn)
ds = New DataSet
da.Fill(ds, "ygjibenxinxibiao")
DataGridView1.DataSource = ds.Tables("ygjibenxinxibiao")
这个有个比较常用的技巧,就是用where 1=1
strsql1 = "select * from ygjibenxinxibiao where 1=1 "+ strsql;
在拼strsql时,则简单的
If a <> "" Then
strsql = strsql +" and xueli =a"
End If
If b <> "" Then
strsql = strsql + " and sex=b"
end if
诸如此类的就可以了
2、“至少一个参数没被指定”,可能是其中某个字段名写错了,解析时会被数据库认为是参数
a,b,c,d之类的都已经是变量了,你咋还用
strsql = strsql + "and position =d"
至少得用
strsql = strsql + "and position ="+d
或
strsql = strsql + "and position ='"+d+"'"
才可以吧,
你显示一下自己最终拼出来的是SQL语句,一般就可以找到原因,否则的话,你还是想点别的招应付明天的考试吧
1=1 的主要用处是 不用判断什么时候加 and
加上这个后 之后的所有条件前都加上 and