locked
Select statement only searches by check boxes when kept in the where statement. RRS feed

  • Question

  • User353407995 posted

    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 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 + '%'
    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.

    Monday, June 19, 2017 8:20 PM

Answers

  • User-718146471 posted

    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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 19, 2017 8:46 PM

All replies

  • User-718146471 posted

    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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 19, 2017 8:46 PM
  • User-718146471 posted

    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.

    Monday, June 19, 2017 8:47 PM