Click here to Skip to main content
12,250,675 members (43,693 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C#
hello friend i have three cheakedlistbox fist party name 2nd exchange type and third one item name ..i want to show data in datagrid view with the help of these 3 chklistbox cheaked item con u help what i have to do ????

how to write select query in C# using sql server2005
Posted 7-Dec-11 2:38am
Edited 7-Dec-11 2:39am
v2
Comments
omid.nazifi 7-Dec-11 8:44am
   
What is your mean? What do you want to show in gridview with checklists?
LAKHANP22@GMAIL.COM 7-Dec-11 8:49am
   
here is my query
SELECT ITEMCODE, BROKTYPE,BROKRATE,TRANRATE,TranType,uptostdt,MARTYPE,MARRATE,PARTYTYPE FROM PITBROK where...chklib1items....and ..chklib2items.....and..chklib3items......
now i wont to put all selected items in where clause
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

You should write if-else statement for all of the positions.

string subquery = "";
if(chk1.checked){
    subquery += " chklib1items = true";
}
else
    subquery += " chklib1items = false";
.
.
.
 
string sql = "SELECT ITEMCODE, BROKTYPE, BROKRATE, TRANRATE, TranType, uptostdt, MARTYPE, MARRATE, PARTYTYPE FROM PITBROK Where "+subquery ;
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

This smells like homework to me.

But in any case:

string sql = string.Format("SELECT [column1],[column2],[column3]FROM [table1] WHERE [column1] ={0} and [column2] = {1} and [column3] = {2}",value1,value2,value3);

Substitute column names, table name and value varaibles as needed.

In that case do the following:

StringBuilder sb = new StringBuilder("SELECT ITEMCODE, BROKTYPE,BROKRATE,TRANRATE,TranType,uptostdt,MARTYPE,MARRATE,PARTYTYPE FROM PITBROK ");
 
if(cheakedlistbox1.SelectedItems.Count > 0 | cheakedlistbox1.SelectedItems.Count > 0 | cheakedlistbox1.SelectedItems.Count > 0)
{
    string values = string.Empty;
    sb.Append("Where ");
 
    foreach (ListItem li in CheckBoxList1.Items)
    {
        
        if (li.Selected)
           values += li.Value + ","
 
    }
    sb.Append(string.Format("PARTYNAME in ({0}) AND ", values.Remove(values.Length -1,1));
    values=string.Empty
    foreach (ListItem li in CheckBoxList2.Items)
    {
        if (li.Selected)
          values += li.Value + ","
 
    }
    sb.Append(string.Format("EXCHANGETYPE in ({0}) AND ", values.Remove(values.Length -1,1));
    foreach (ListItem li in CheckBoxList3.Items)
    {
        if (li.Selected)
           values += li.Value + ","
 

    }
    sb.Append(string.Format("NAME in({0}) AND ", values.Remove(values.Length -1,1));
 
    string sql = sb.ToString();
    //trim trailing AND and whitespaces
    sql = sql.Remove(sql.Length-5,5); 
 

 
}
  Permalink  
v4
Comments
LAKHANP22@GMAIL.COM 7-Dec-11 9:00am
   
sorry but i know query

my problem is...........hello friend i have three cheakedlistbox fist party name 2nd exchange type and third one item name ..i want to show data in datagrid view with the help of these 3 chklistbox cheaked item con u help what i have to do ????

how to write select query in C# using sql server2005
min i want to show data (in datagridview )with these three cheakedlistbox Selected item
sucram 7-Dec-11 10:50am
   
Check out my update. If that is not what you are looking for you need to explain your question a bit better orgive us the select statement you want build.
LAKHANP22@GMAIL.COM 8-Dec-11 5:32am
   
ok........
in window from i have three different-2 criteria to filter data for thre i have three cheakesdlistbox(1st for partyname .2nd for exchange type and 3rd one for itemname) each contain number of items let if user select(by cheeking cheak box) 10 party name now he select 2 exhange type of the available exchange type and in third ithemname may he select all item or sum now as he press show button data should be fill in datagridview with the above selection .........now i want to pass these selected values in whare clause ..like..whrere partyanme="all value from partyname" and exchangetype="all selected exhange type"and itemname="selected itemname"....
please help me how to write selct query with multiple selected values....
LAKHANP22@GMAIL.COM 9-Dec-11 2:01am
   
thanks very-2 much for give me your time.... but i get one error .....Error 1 The type or namespace name 'ListItem' could not be found (are you missing a using directive or an assembly reference?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

here is complete solution of my problem.....

if (con.State == ConnectionState.Closed)
                  {
                        con.Open();
                  }
                  StringBuilder query = new StringBuilder("SELECT ITEMCODE, BROKTYPE,BROKRATE,TRANRATE,TranType,uptostdt,MARTYPE,MARRATE FROM PITBROK ");
                  query.AppendFormat("where      ");
                  for (int i = 0; i < checkedListBox1.CheckedItems.Count; i++)
                  {
                        int coun = checkedListBox1.CheckedItems.Count;
                        coun = coun - 1;
                        string val;
                        if (checkedListBox1.CheckedItems.Count > 0)
                        {
                              DataRow row;
                              row = ((DataRowView)this.checkedListBox1.CheckedItems[i]).Row;
                              val = (row[this.checkedListBox1.ValueMember]).ToString();
                              row = null;

                              query.AppendFormat("PITBROK.AC_CODE='{0}' ", val.ToString());

                              if (i < coun)
                              {
                                    query.AppendFormat(" or ");
                              }

                    
                        }

                    
                  }

                  if (checkedListBox3.CheckedItems.Count > 0)
                  {
                        query.Append("AND ");
                  }
                  for (int k = 0; k < checkedListBox3.CheckedItems.Count; k++)
                  {
                        int coun1 = checkedListBox3.CheckedItems.Count;
                        coun1 = coun1 - 1;
                        query.AppendFormat("PITBROK.ITEMCODE='{0}'", checkedListBox3.CheckedItems[k]);
                        if (k < coun1)
                        {
                              query.Append("or ");
                        }

                  }
                  query.Append("and compcode='" + Compcls.Gcomp_cd + "' ");

                  string sql = query.ToString();


                  SqlDataAdapter da = new SqlDataAdapter(sql, con);
                  DataSet ds1 = new DataSet();
                  da.Fill(ds1);
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160426.1 | Last Updated 2 Jan 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100