c# - Select SQL Search String Based on Content of ASP.NET Text Field -

the goal of code-behind below select sql select statement in string variable based on whether or not text box or not null, execute statement. i've tried both using variable , directly executing select statement using

sqldatasource1.selectcommand = 'select...'  

unfortunately, neither method returning @ when put data in text fields checked.

using system; using system.collections.generic; using system.linq; using system.web; using system.web.ui; using system.web.ui.webcontrols; using system.data.sqlclient; using system.data.sql;  namespace myprogram {     public partial class webform1 : system.web.ui.page     {        string query = "";          protected void page_load(object sender, eventargs e)         {              sqldatasource1.selectcommand = query;             sqldatasource1.selectparameters.add("namelast", searchlast.tostring());             sqldatasource1.selectparameters.add("namefirst", searchfirst.tostring());             sqldatasource1.selectparameters.add("ssn", searchssn.tostring());             sqldatasource1.selectparameters.add("dob", searchdob.tostring());             sqldatasource1.selectparameters.add("case", searchcase.tostring());          }          protected void button1_click(object sender, eventargs e)         {                  if (searchcase.text != string.empty)                 {                     sqldatasource1.selectcommand = "select cltcas,cltlst+', '+cltfst,datefromparts(cltbyr,cltbmm,cltbdd),cltcty+', '+[ cltsta],cltssn,cltsex cltmst cltcas = @case";                 }                 if (searchssn.text != string.empty && searchssn.text.length == 4 && searchlast.text == string.empty && searchfirst.text == string.empty)                 {                     sqldatasource1.selectcommand = "select cltcas,cltlst+', '+cltfst,datefromparts(cltbyr,cltbmm,cltbdd),cltcty+', '+[ cltsta],cltssn,cltsex cltmst right(cltssn,4) = @ssn";                 }                 if (searchssn.text != string.empty && searchssn.text.length == 9 && searchlast.text != string.empty && searchfirst.text == string.empty)                 {                     query = "select cltcas,cltlst+', '+cltfst,datefromparts(cltbyr,cltbmm,cltbdd),cltcty+', '+[ cltsta],cltssn,cltsex cltmst cltssn = @ssn";                 }                 if (searchlast.text != string.empty && searchfirst.text != string.empty && searchdob.text == string.empty && searchssn.text == string.empty)                 {                     query = "select cltcas,cltlst+', '+cltfst,datefromparts(cltbyr,cltbmm,cltbdd),cltcty+', '+[ cltsta],cltssn,cltsex cltmst cltlst = @namelast , cltfst @namefirst+'%'";                 }                 if (searchlast.text != string.empty && searchfirst.text == string.empty && searchssn.text == string.empty && searchdob.text == string.empty)                 {                     query = "select cltcas,cltlst+', '+cltfst,datefromparts(cltbyr,cltbmm,cltbdd),cltcty+', '+[ cltsta],cltssn,cltsex cltmst cltlst = @namelast+'%'";                 }                 if (searchlast.text == string.empty && searchfirst.text == string.empty && searchssn.text == string.empty && searchdob.text != string.empty)                 {                     query = "select cltcas,cltlst+', '+cltfst,datefromparts(cltbyr,cltbmm,cltbdd),cltcty+', '+[ cltsta],cltssn,cltsex cltmst (cltbyr+'-'+cltbmm+'-'+cltbdd = @dob)";                 }                 if (searchlast.text != string.empty && searchfirst.text == string.empty && searchssn.text == string.empty && searchdob.text != string.empty)                 {                     query = "select cltcas,cltlst+', '+cltfst,datefromparts(cltbyr,cltbmm,cltbdd),cltcty+', '+[ cltsta],cltssn,cltsex cltmst cltlst = @namelast+'%' , (cltbyr+'-'+cltbmm+'-'+cltbdd = @dob)";                 }         }          protected void sqldatasource1_selecting(object sender, sqldatasourceselectingeventargs e)         {          }     } } 

i think better off doing declaratively, controlparameters. in markup, have <selectparameters> section sqldatasource1 declaration:

<asp:sqldatasource id="sqldatasource1" runate="server"     cancelselectonnullparameter="false" ...other stuff... >     ...other stuff...     <selectparameters>         <asp:controlparameter name="namelast" controlid="searchlast" propertyname="text"/>         <asp:controlparameter name="namefirst" controlid="searchfirst" propertyname="text"/>         <asp:controlparameter name="ssn" controlid="searchssn" propertyname="text"/>         <asp:controlparameter name="dob" controlid="searchdob" propertyname="text"/>         <asp:controlparameter name="case" controlid="searchcase" propertyname="text"/>     </selectparameters> </asp:sqldatasource> 

this way don't have deal adding them on page_load. notice added cancelselectonnullparameter property, , set false. way query execute, if of values empty.

you combine queries one, way don't need have if statements in click event:

select      cltcas,      cltlst+', '+cltfst,     datefromparts(cltbyr,cltbmm,cltbdd),      cltcty+', '+[ cltsta],     cltssn,     cltsex  cltmst       cltcas = @case     or right(cltssn,4) = @ssn     or cltssn = @ssn     or (cltlst = @namelast , cltfst @namefirst+'%')     or cltlst @namelast+'%'     or cltbyr+'-'+cltbmm+'-'+cltbdd = @dob     or (cltlst @namelast+'%' , (cltbyr+'-'+cltbmm+'-'+cltbdd = @dob)) 

---

as far original code goes, when add textbox values parameters, want add .text property default value:

sqldatasource1.selectparameters.add("namelast", searchlast.text);