Searching data with 25 checkboxes and 21 textboxes , cannot get corresponding results when searching both text and checkboxes.

Question

0.00/5 (No votes)

See more:

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.

C#

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();









}

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.

Posted 20-Jun-17 7:53am

Member 13269845

Updated 11-Aug-17 20:41pm

Add a Solution

Comments

[no name] 20-Jun-17 14:09pm

I suggest you to try first with one Checkbox and one TextBox and enter values for which you are sure you get a result. In case you are succesfull increase no of to 2, than three. After this whould be also successfull, you should be able to extend to full Scenario....
... even I think the full scenaroio is crazy. Think about it, 25 checkboxes and 21 textboxes... a lot of Information a user of your Software has to handle.

[Edit]
After overfly your SQL, from what I know from Interbase SQL Server your Parameter should be @param and you should assign the value'%xyz%' to it and _not_ like you do it '%'+@param+'%', which most probably would also be true for mssql

Graeme_Grant 9-Aug-17 19:20pm

5+ ;)

an0ther1 20-Jun-17 18:30pm

Use SQL Profiler to capture the query being passed into SQL & paste it into SQL Management Studio then verify the query.
From looking at your statement above the Parameter @DesignBuild is not included.
Additionally, your exception handler (catch) says "No Records Found". That is not an exception handler - an exception handler should catch the error and provide feedback to the user about what happened and why.
Kind Regards

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

S Douglas · Answer 1 · 2017-08-09T13:10:00

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.

Badal Kumar · Answer 2 · 2017-08-11T20:41:00

Solution 2

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.

Posted 11-Aug-17 20:41pm

Badal Kumar