We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >
Select statement only searches by check boxes when kept in the where statement.[Answered]
2 replies
Last post Jun 19, 2017 08:47 PM by bbcompent1
None
0 Points
1 Post
Select statement only searches by check boxes when kept in the where statement.
Jun 19, 2017 08:20 PM|Mweigle|LINK
I have 21 text boxes and 25 check boxes in a windows form and I want to be able to type in these text boxes and and check check boxes and have the corresponding records returned when the statement is executed. At this point whenever I have a select statement with no check boxes referenced in the where clause I can search by any text box and have my records returned normally, however when I add in a search by checkbox , no records are returned except ones with the certain check boxes checked. So if I typed anything in the text boxes and didn't check a checkbox , I would still get the no record found message.
{ con.Open(); string str = "select * from engineering where (JobNumber like '%' + @search + '%' AND DateOrderedlike '%' + @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 + '%'
AND DesignBuild like " + DesignBuild + " )"; 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; 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(); }
However even still when I do not include the checkbox in the where clause I still get the "no record found message" , the n once I click out of that message box my results are shown. Any help with either of these two issues would be great.
All-Star
35218 Points
9955 Posts
Moderator
Re: Select statement only searches by check boxes when kept in the where statement.
Jun 19, 2017 08:46 PM|bbcompent1|LINK
Ok, first off looking at this query, you have to remember when you see the operand AND, it means that all conditions must be met for a result to be returned. What I would do is try using the SQL Manager and run queries using that command stack to see what results you will expect. If you get no results, the query is too restrictive. Have you considered using OR instead?
All-Star
35218 Points
9955 Posts
Moderator
Re: Select statement only searches by check boxes when kept in the where statement.
Jun 19, 2017 08:47 PM|bbcompent1|LINK
Also, since you are calling these as either True or False, you might want to get rid of the wildcard since logic is not going to be text, it will most likely be 1 or 0.