Having Issues with Select statements dealing with checkboxes

Mac Weigle

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 dont 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. The issue is within the btn_search.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nilesh Shah9y ago
You can create the query dynamically by some condition like this:
StringBuilder str = new StringBuilder("select * from engineering where (JobNumber like '%' + @search + '%' ");
            if (!String.IsNullOrEmpty(txtDateOrdered.Text))
                str.Append("AND DateOrdered like '%'" + txtDateOrdered.Text + "'%' ");
I suggested here to use string builder class, not string, because string builder is better in performance when string addition, etc. operations are to be done.
I added only one line, similarly you have to do for all inputs.

for breakpoint, just place mouse cursor at that line and press F9, it will put a breakpoint, then when you run project, execution will stop at that line and you can see values by placing mouse focus on a variable

for more I suggest you google on how to debug using visual studio
Mac Weigle9y ago
I am kind of understanding what you're saying here , but I am unsure how to use a debug breakpoint. You are correct in that different fields will have no inputsometimes, and I do not want to assume that there will always be input for each record so how would I go about doing this? I appreciate your answer.
Nilesh Shah9y ago
I downloaded your project and seen your code in btnSearch_Click

I think the obvious mistake you are making here is assuming everything will have input

i.e. you are not generating query based on input is supplied or not but you have created the query which expects value in each parameter.

I suggest you generate such that a textbox's value is only taken into consideration if some text is present.

The best option you have is to put a debug breakpoint here
SqlCommand xp = new SqlCommand(str, con);
and then see what's value in str, basically you should be able to execute the query in str variable as it is on SQL Server.