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.
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?
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.
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
35208 Points
9948 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
35208 Points
9948 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.