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

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.

Answer 1

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

Answer 2

User471152447 posted

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

based on chekbox and drop1 and drop2

Answer 3

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.

Answer 4

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