ASP.NET - list box

Asked By vicky nehru on 13-Sep-10 07:00 AM
In my project listbox contain more than one datas ,how to i compare the datas to database to retrive a listbox equilant values in grid view. . .give me a solutions. . .
Kirtan Patel replied to vicky nehru on 13-Sep-10 07:48 AM
 private void button1_Click(object sender, EventArgs e)
    {
      
      DataTable dt = new DataTable();
      dt.Columns.Add("Username");
      dt.Columns.Add("Password");
 
      /* process each entry of list box */
      foreach (string str in listBox1.Items)
      {
        string UsernametoDelete = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
        SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True");
        con.Open();
        SqlCommand comm = new SqlCommand("select  * from Test where username='"+str+"'", con);
        SqlDataReader reader = comm.ExecuteReader();
        while(reader.Read())
        {
          /* add rows resulted into datatable */
          DataRow dr = dt.NewRow();
          dr[0] = reader["Username"].ToString();
          dr[1] = reader["Password"].ToString();
          dt.Rows.Add(dr);
        }
      }
 
      /* Bind Table Containing Result from Each Result to DataGridView */
      dataGridView1.DataSource = dt;
    }
Sasha Kotlo replied to vicky nehru on 13-Sep-10 08:09 AM
Your question isn't quite clear but if I understand correctly you want to display the DB values where the display text and the value are equal to the currently selected ones in the dropdownlist? Here it goes:

string connString = "YOUR CONNECTION STRING HERE";
using(SqlConnection conn = new SqlConnection(connString))
{
string queryString = "SELECT * FROM someTable WHERE SomeID=@SomeID, SomeText=@SomeText";
SqlCommand comm = new SqlCommand(queryString, conn);
comm.Parameters.AddWithValue("SomeID", youridvaluehere);
comm.Parameters.AddWithValue("SomeText", yourtextvaluehere);
SqlDataAdapter da = new SqlDataAdapter(comm);
DataTable ds = new DataTable();
da.Fill(ds);

GridView1.DataBind();
}

Now call this code on SelectedIndexChanged event of the dropdownlist.

Regards.
vicky nehru replied to Sasha Kotlo on 13-Sep-10 08:17 AM
mysql table formate is product contains (milk,curd,apple),producttype,customername.

list box contains 3 values like Milk,curd,apple. . .i want to  compare the listbox values to mysql database to retrive the remaining head like producttype and customername display the gridview. . .
Sasha Kotlo replied to vicky nehru on 13-Sep-10 08:27 AM
then my code above will do the job just fine as well for that case too.
Super Man replied to vicky nehru on 13-Sep-10 08:48 AM

 

      string values = string.Empty;

 

      // get the all values in string .

      // so we use it in  "in clause of sql query".

 

 

      foreach (ListItem ls in ListBox1.Items)

      {

        values += ls.Text.ToString();

      }

 

      SqlConnection con = new SqlConnection("connection string here");

      SqlCommand cmd = new SqlCommand("select * from employee where e_id in (" + values + ")", con);

      con.Open();

      SqlDataAdapter sd = new SqlDataAdapter();

      DataTable dt = new DataTable();

      sd.Fill(dt);

      GridView1.DataSource = dt;

      GridView1.DataBind();

 

 

 

Goniey N replied to vicky nehru on 13-Sep-10 09:33 AM
--Use Below Code :

Here I use Query Like Below :

Select * FROM Student WHERE product in (milk,curd,apple);



01.private void button1_click(object sender,EventArgs e)
02.{
03.  string MyList = "";
04.  for (int i = 0; i < ListBox1.Items.Count; i++)
05.  {
06.    //Here It Will Add Listbox Values in The string Variable...
07.    MyList+=ListBox1.Items[i].ToString()+",";
08.  }
09.  SqlConnection conn = new SqlConnection("<<Your Connection String>>");
10.  conn.Open();
11.  SqlCommand cmd = new SqlCommand("SELECT * FROM Student WHERE product in("+ MyList +")", conn);
12.  DataTable dt = new DataTable();
13.  SqlDataAdapter adp = new SqlDataAdapter(cmd);
14.  adp.Fill(dt);
15.  GridView1.DataSource = dt.DefaultView;
16.  GridView1.DataBind();
17.  conn.Close();
18.}


-- It Will Work 100%....

-- Hope This Will Help You....
vicky nehru replied to Goniey N on 14-Sep-10 03:49 AM
Is Not working. . .
How i compare a checkboxlist selected text in Mysql database to retrive a equilant datas in grid view, i am checked using the below coding is not working give me a solutions. . .

 protected void Button1_Click(object sender, EventArgs e)
    {
      string MyList = string.Empty;    
     
        for (int i = 0; i < CheckBoxList1.Items.Count; i++)
        {
         if (CheckBoxList1.Items[i].Selected)
         {
          MyList += CheckBoxList1.Items[i].ToString()+ ",";
           
         }
        }

      DataSet ds = new DataSet();
      MySqlHelper sqlhelpobj = new MySqlHelper();

      string str = "SubCheckedProduct";

      string obj = MyList;

      //int ReturnValue = 0;
      ds = sqlhelpobj.GetDatasetByCommand("SubChecked_Grid", str, obj);

      if (ds.Tables[0].Rows.Count > 0)
      {
        Purchase.DataSourceID = "";
        Purchase.DataSource = ds;
        Purchase.DataBind();
        //showClick(true);

      }
      else
      {
        Purchase.DataSourceID = "";
        ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
        //dt.Rows.Add(dt.NewRow());
        Purchase.DataSource = ds;
        Purchase.DataBind();

        int TotalColumns = Purchase.Rows[0].Cells.Count;
        Purchase.Rows[0].Cells.Clear();
        Purchase.Rows[0].Cells.Add(new TableCell());
        Purchase.Rows[0].Cells[0].ColumnSpan = TotalColumns;
        Purchase.Rows[0].Cells[0].Text = "No Record Found";

        // showClick(false);
      }
    }