在搜索文本和复选框时，使用25个复选框和21个文本框搜索数据无法获得相应的结果。 [英] Searching data with 25 checkboxes and 21 textboxes , cannot get corresponding results when searching both text and checkboxes.
问题描述
我正在尝试创建一个访问Microsoft SQL Database Studio的表单，并允许用户添加，搜索，更新和删除记录。但是我在处理复选框时遇到了麻烦。每当我只搜索文本并且在where子句中没有复选框引用时，我会返回正确的结果以及正在检查的正确复选框。但是，如果我在where子句中添加一个复选框引用，则每个sql搜索都会关注复选框部分，因此如果我在文本框中键入任何内容而不检查复选框，则不会得到任何结果。知道我在这里做错了什么吗？即使在where子句中没有复选框，我也会得到正确的结果和复选框，但是在获得结果之前没有得到没有找到记录消息。
I am trying to create a form that accesses Microsoft SQL Database Studio and allows a user to add, search, update and delete records. I am having trouble dealing with checkboxes however. Whenever I search by just text and have no checkbox reference in the where clause , I get back the correct results along with the correct check boxes being checked. If I add a checkbox reference in the where clause however , every sql search focuses on the checkbox part , so if I type anything in the text boxes and don't check checkboxes , I get no results. Any idea what I am doing wrong here? Also even with no checkboxes in the where clause , I get back the correct results and checkboxes , but not without getting the "no record found" message before getting the results.
private void btnSearch_Click(object sender, EventArgs e)
{
con.Open();
string str = "select * from engineering where (JobNumber like '%' + @search + '%' AND DateOrdered like '%' + @search1 + '%' AND Title like '%' + @search2 + '%' AND PhysicalAddressComplete like '%' + @search3 + '%' AND County like '%' + @search4 + '%' AND Client like '%' + @search5 + '%' AND Contact like '%' + @search6 + '%' AND ContactTitle like '%' + @search7 + '%' AND MailingAddressComplete like '%' + @search8 + '%' AND BusinessPhone like '%' + @search9 + '%' AND CellPhone like '%' + @search10 + '%' AND Email like '%' + @search11 + '%' AND OpenStatus like '%' + @search12 + '%' AND CloseStatus like '%' + @search13 + '%' AND Cabinet like '%' + @search14 + '%' AND Roll like '%' + @search15 + '%' AND Drawer like '%' + @search16 + '%' AND ConstructionDrawings like '%' + @search17 + '%' AND Fee like '%' + @search18 + '%' AND ConstructionCost like '%' + @search19 + '%' AND ProjectDescription like '%' + @search20 + '%' )";
SqlCommand xp = new SqlCommand(str, con);
xp.Parameters.Add("@search", SqlDbType.NVarChar).Value = txtProjectNumber.Text;
xp.Parameters.Add("@search1", SqlDbType.NVarChar).Value = txtDateOrdered.Text;
xp.Parameters.Add("@search2", SqlDbType.NVarChar).Value = txtProjectName.Text;
xp.Parameters.Add("@search3", SqlDbType.NVarChar).Value = txtProjectAddress.Text;
xp.Parameters.Add("@search4", SqlDbType.NVarChar).Value = txtCounty.Text;
xp.Parameters.Add("@search5", SqlDbType.NVarChar).Value = txtClient.Text;
xp.Parameters.Add("@search6", SqlDbType.NVarChar).Value = txtClientContact.Text;
xp.Parameters.Add("@search7", SqlDbType.NVarChar).Value = txtContactTitle.Text;
xp.Parameters.Add("@search8", SqlDbType.NVarChar).Value = txtBillingAddress.Text;
xp.Parameters.Add("@search9", SqlDbType.NVarChar).Value = txtBusinessPhone.Text;
xp.Parameters.Add("@search10", SqlDbType.NVarChar).Value = txtCellPhone.Text;
xp.Parameters.Add("@search11", SqlDbType.NVarChar).Value = txtEmail.Text;
xp.Parameters.Add("@search12", SqlDbType.NVarChar).Value = txtOpenStatus.Text;
xp.Parameters.Add("@search13", SqlDbType.NVarChar).Value = txtCloseStatus.Text;
xp.Parameters.Add("@search14", SqlDbType.NVarChar).Value = txtCabinet.Text;
xp.Parameters.Add("@search15", SqlDbType.NVarChar).Value = txtRoll.Text;
xp.Parameters.Add("@search16", SqlDbType.NVarChar).Value = txtDrawer.Text;
xp.Parameters.Add("@search17", SqlDbType.NVarChar).Value = txtDrawings.Text;
xp.Parameters.Add("@search18", SqlDbType.NVarChar).Value = txtFee.Text;
xp.Parameters.Add("@search19", SqlDbType.NVarChar).Value = txtCost.Text;
xp.Parameters.Add("@search20", SqlDbType.NVarChar).Value = txtProjectDescription.Text;
xp.Parameters.AddWithValue("@DesignBuild", (chkDesign.Checked ? 1 : 0));
try
{
da = new SqlDataAdapter();
da.SelectCommand = xp;
da.Fill(ss);
Showdata(pos);
if (ss.Rows.Count >0)
{
this.chkEducational.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Education"] == 1);
this.chkDesign.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["DesignBuild"] == 1);
this.chkMedical.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Medical"] == 1);
this.chkReligious.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Religious"] == 1);
this.chkMulti.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["MultiFamily"] == 1);
this.chkStudent.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Student"] == 1);
this.chkAssisted.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Assisted"] == 1);
this.chkSingleFamily.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Single"] == 1);
this.chkBridge.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Bridge"] == 1);
this.chkIntersection.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Intersection"] == 1);
this.chkRoadway.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Roadway"] == 1);
this.chkTransOther.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["TransportationOther"] == 1);
this.chkRetailSmall.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["SmallRetail"] == 1);
this.chkRetailLarge.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["LargeRetail"] == 1);
this.chkParks.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Parks"] == 1);
this.chkIndustrial.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Industrial"] == 1);
this.chkUtility.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Utility"] == 1);
this.chkGCSmall.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["GCSmall"] == 1);
this.chkGCLarge.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["GCLarge"] == 1);
this.chkOffice.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Office"] == 1);
this.chkOther.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Other"] == 1);
this.chkMunicipal.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Municipal"] == 1);
this.chkPrivate.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Privates"] == 1);
this.chkInstitutional.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Institutional"] == 1);
this.chkMilitary.Checked = ((int)ss.AsEnumerable().FirstOrDefault()["Military"] == 1);
}
}
catch
{
MessageBox.Show("No Record Found");
}
con.Close();
}
以上是我的搜索按钮的代码。上面的工作方式似乎很好，除了我总是得到否记录找到消息，即使结果返回。然而，一旦我添加AND DesignBuild = @DesignBuild（这是一个复选框）之类的东西，那么在文本框中输入的任何内容都会被忽略，并且总是给出无结果。
我尝试了什么：
我在WHERE子句中尝试了AND和OR的多种变体，但没有像我想要的那样返回它是。我不知道错误是否仅在我的sql语句中。任何帮助将不胜感激。
Above is the code to my search button.The way it is above works seemingly well except I always get the "no record found" message even if results are returned. However once I add something like "AND DesignBuild = @DesignBuild " (this is a checkbox) ,then anything entered in the textboxes is ignored and always giving "no results".
What I have tried:
I have tried multiple variations of AND and OR in my WHERE clauses yet nothing is returned like I want it to be. I don't know if the error is only in my sql statement or not. Any help would be greatly appreciated.
推荐答案
如果我正确理解您的问题，如果您没有复选框作为where子句的一部分，您的查询将按预期工作。鉴于此，我看到了几个问题。
1）缺少where子句DesignBuild =？
2）作为内存服务，ASP。 NET将复选框中的值视为True和False，我猜在表中，它们被定义为BIT或INT。只是一个数据类型问题。
希望有所帮助。
If I understand your issues correctly, your query works as desired if you do not have the check boxes as part of the where clause. Given that, I see a couple of issues.
1) The where clause is missing DesignBuild = ?
2) As memory serves, ASP.NET treats the values from check boxes as "True" and "False", I would guess that in the table, they are defined as BIT or INT. Just a data type issue.
Hope that helps.
只看你的where子句条件有问题，只是尝试放置如下所述的条件。 br $>
Seems only problem with your where clause condition, just try to put conditions as mentioned below.
AND ((@search1 = null) OR (DateOrdered like '%' + @search1 + '%'))
需要像这样指定每个搜索条件，因此如果没有提供某些搜索条件，语句将忽略。
Need to specify each search condition like this, so statement will ignore if some search condition not provided.
这篇关于在搜索文本和复选框时，使用25个复选框和21个文本框搜索数据无法获得相应的结果。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
