Click here to Skip to main content
Rate this: bad
good
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 at 7-Dec-11 8:44am
   
What is your mean? What do you want to show in gridview with checklists?
LAKHANP22@GMAIL.COM at 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 at 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 at 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 at 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 at 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
0 Sergey Alexandrovich Kryukov 446
1 OriginalGriff 380
2 Yogesh Kumar Tyagi 304
3 Prakriti Goyal 200
4 Maciej Los 165
0 OriginalGriff 6,472
1 Sergey Alexandrovich Kryukov 5,404
2 Maciej Los 3,434
3 Peter Leow 3,259
4 DamithSL 2,495


Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 2 Jan 2012
Copyright © CodeProject, 1999-2014
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