locked
how to search multiple fields in sql database tabland i will show in grid view RRS feed

  • Question

  • User471152447 posted

    Hi Friends,

    any one knows how to search multiple fields insql database table .

    i have table like

    string str2 = "select cld_id,cld_name,cld_adrs,cld_ctno,cld_cmdty,cld_tng,cld_state,cld_dst,cld_dof,cld_pby,cld_moc from coldstorage";

    then i have checkbox list hear i have bind cld_name, dropdownlist hear i bind cld state and dropdown i bind cld_dst...

    so now i want to select one or two ckeckboxes and i select state and distric

    then i click searchbutton i want to show searching values in gridview

    please give any idea how to search multiple fileds sqlquery

    please any one helpm.

    Friday, July 29, 2011 3:45 AM

Answers

  • User-1138901951 posted

    Hi,

    This is a code to show how to approach the problem. There is much more fine tuning and refactoring can be possible within thhis code.

    string strBaseSql = "select cld_id,cld_name,cld_adrs,cld_ctno,cld_cmdty,cld_tng,cld_state,cld_dst,cld_dof,cld_pby,cld_moc from coldstorage ";
                string strWhereClause = "Where ";
                bool ifAndPart = false;
                string strAndPart = "";
                string strDynamicSql = "";
    
                for (int i = 0; i < checkboxlist1.Items.Count; i++)
                {
                    dynamicSql += "(";
                    if (checkboxlist1.Items[i].Selected)
                    {
                        ifAndPart = true;
                        dynamicSql += "cld_name = '" + checkboxlist1.Items[i].Text + "' OR";
                    }
                    dynamicSql += ")";
                }
    
                strDynamicSql.Replace(" OR)", ")"); // remove last orphan 'OR'
    
                if (ifAndPart)
                {
                    strAndPart = " AND ";
                }
    
                if (drop1.SelectedItem.Text != "Please Select")
                {
                    dynamicSql += strAndPart + "cld_state = '" + drop1.SelectedItem.Text + "'";
                    ifAndPart = true;
                }
    
                if (ifAndPart)
                {
                    strAndPart = " AND ";
                }
    
                if (drop2.SelectedItem.Text != "Please Select")
                {
                    dynamicSql += strAndPart + "cld_dst = '" + drop2.SelectedItem.Text + "'";
                }
    
                string strFinalSql = strBaseSql + strWhereClause + dynamicSql;

    I hope this helps you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 29, 2011 8:07 AM

All replies

  • User-1138901951 posted

    Hi,

    You can use if else block to build SQL statement. I am telling you the easiest way.

    Say, we have cld_name list check boxes, cld_state ddl, cld_dst ddl.

    Some time we select one or more cld_name and we may select cld_state ddl or cld_dst ddl.

    - put the base sql (the static part) in a variable
    - take another avariable(s) for "where clause" (the dynamic part)
    - prepare the "where clause" based on the selection (use conditional blocks for this)
    - then append it to the base select statement
    - then run the query

    Let me know if I can provide more help

    Friday, July 29, 2011 4:20 AM
  • User471152447 posted

    if u dont mind can u give me the sample query please.

    based on chekbox and drop1 and drop2

    Friday, July 29, 2011 4:33 AM
  • User-1138901951 posted

    Hi,

    This is a code to show how to approach the problem. There is much more fine tuning and refactoring can be possible within thhis code.

    string strBaseSql = "select cld_id,cld_name,cld_adrs,cld_ctno,cld_cmdty,cld_tng,cld_state,cld_dst,cld_dof,cld_pby,cld_moc from coldstorage ";
                string strWhereClause = "Where ";
                bool ifAndPart = false;
                string strAndPart = "";
                string strDynamicSql = "";
    
                for (int i = 0; i < checkboxlist1.Items.Count; i++)
                {
                    dynamicSql += "(";
                    if (checkboxlist1.Items[i].Selected)
                    {
                        ifAndPart = true;
                        dynamicSql += "cld_name = '" + checkboxlist1.Items[i].Text + "' OR";
                    }
                    dynamicSql += ")";
                }
    
                strDynamicSql.Replace(" OR)", ")"); // remove last orphan 'OR'
    
                if (ifAndPart)
                {
                    strAndPart = " AND ";
                }
    
                if (drop1.SelectedItem.Text != "Please Select")
                {
                    dynamicSql += strAndPart + "cld_state = '" + drop1.SelectedItem.Text + "'";
                    ifAndPart = true;
                }
    
                if (ifAndPart)
                {
                    strAndPart = " AND ";
                }
    
                if (drop2.SelectedItem.Text != "Please Select")
                {
                    dynamicSql += strAndPart + "cld_dst = '" + drop2.SelectedItem.Text + "'";
                }
    
                string strFinalSql = strBaseSql + strWhereClause + dynamicSql;

    I hope this helps you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 29, 2011 8:07 AM
  • User3866881 posted

    Hello venkatakilas:)

    It seems that you wanna do a multiple-filtering, This control fits you well:

    http://www.codeproject.com/KB/webforms/GridViewFilter.aspx

     

    Sunday, July 31, 2011 9:59 PM