Listview - Search and auto update the LV

Question

lightshift 0 Newbie Poster

13 Years Ago

Hey,

Basically I have a text box to search data from my SQL DB from, and I want the information to then display in a listview underneath. I have been trying for a while now and still can't get it to work, but on the button click it should display the data from the textbox in the listview via a selective query.

SqlDataAdapter myDataAdapter = new SqlDataAdapter();
        private void searchCoffee_Load(object sender, EventArgs e)
        {

            myDataSet = new DataSet();
            string connection = @"Data Source=.\SQLEXPRESS;AttachDbFilename=E:\UCP\C And ASPNET\Coffee.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            SqlConnection cn = new SqlConnection(connection);
            try
            {
                cn.Open();
            }
            catch (Exception)
            {
                MessageBox.Show("Did not connect");
            }
            string strSQL = "SELECT * FROM CoffeeTypes";
            myDataAdapter = new System.Data.SqlClient.SqlDataAdapter(strSQL, cn);

        }

private void button1_Click(object sender, EventArgs e)    
        {
            DataTable dtable = myDataSet.Tables["CoffeeTypes"];
            //Display items in the listview ctrl.
            foreach (DataRow drow in myDataSet.Tables)
            {
                DataRow dRow = dtable.Rows[10];
                    //define the list items
                    ListViewItem lvi = new ListViewItem(dRow["CoffeeID"].ToString());
                    lvi.SubItems.Add(dRow["coffeeName"].ToString());
                    lvi.SubItems.Add(dRow["coffeePrice"].ToString());
                    lvi.SubItems.Add(dRow["coffeeStrength"].ToString());
                    lvi.SubItems.Add(dRow["Origin"].ToString());
                    lvi.SubItems.Add(dRow["QuantityInStock"].ToString());

                   //add the list items to the listview

                    listView1.Items.Add(lvi);
               
            }
        }

4 Contributors
11 Replies
148 Views
1 Month Discussion Span
Latest Post 13 Years Ago Latest Post by kitjo

Mitja Bonca 557 Nearly a Posting Maven

13 Years Ago

So, what is the problem here?

This has got to work:

//clear the items if they exist:
            ListView.Items.Clear();

            DataTable dtable = myDataSet.Tables["CoffeeTypes"];
            //Display items in the listview ctrl.
            foreach (DataRow drow in myDataSet.Tables)
            {
                ListViewItem lvi = new ListViewItem(drow["CoffeeID"].ToString());
                lvi.SubItems.Add(drow["coffeeName"].ToString());
                lvi.SubItems.Add(drow["coffeePrice"].ToString());
                lvi.SubItems.Add(drow["coffeeStrength"].ToString());
                lvi.SubItems.Add(drow["Origin"].ToString());
                lvi.SubItems.Add(drow["QuantityInStock"].ToString());
                listView1.Items.Add(lvi);

            }

abelLazm 183 Postaholic

13 Years Ago

check this

ListView.Items.Clear();
foreach (DataRow drow in myDataSet.Tables["Table_name"])

{

ListViewItem lvi = new ListViewItem(drow.ItemArray[0].ToString());
lvi.SubItems.Add(drow.ItemArray[1].ToString()); 
lvi.SubItems.Add(drow.ItemArray[2].ToString());
lvi.SubItems.Add(drow.ItemArray[3].ToString());
lvi.SubItems.Add(drow.ItemArray[4].ToString());
lvi.SubItems.Add(drow.ItemArray[5].ToString());

listView1.Items.Add(lvi);

}

Edited 13 Years Ago by abelLazm because: n/a

Mitja Bonca 557 Nearly a Posting Maven

13 Years Ago

Better check what your dataTable holds. Check if there is data inside of it 8in datatable of the dataSet). If its empty, then surely can happen much. If there are data, it has to be shown in listView (with using my code):

ListView.Items.Clear();
 
            DataTable dtable = myDataSet.Tables["CoffeeTypes"];
            //Display items in the listview ctrl.
            foreach (DataRow drow in myDataSet.Tables[0]) //you have to specify which table (or its name ot its index)
            {
                ListViewItem lvi = new ListViewItem(drow["CoffeeID"].ToString());
                lvi.SubItems.Add(drow["coffeeName"].ToString());
                lvi.SubItems.Add(drow["coffeePrice"].ToString());
                lvi.SubItems.Add(drow["coffeeStrength"].ToString());
                lvi.SubItems.Add(drow["Origin"].ToString());
                lvi.SubItems.Add(drow["QuantityInStock"].ToString());
                listView1.Items.Add(lvi);
 
            }

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

lightshift 0 Newbie Poster · Answer 1 · 2011-03-23T01:46:57+00:00

It does nothing, I click the button and nothing happens.

lightshift 0 Newbie Poster · Answer 2 · 2011-03-28T18:34:05+00:00

Well it is "working" more now, however still being silly. It displays the listview grid blank with all the "IDs" from the table, e.g. 1, 2, 3, 4 etc.

listView1.Items.Clear();//first clear out all item entries

            //now call the SQL query to fill the dataSet
            this.myDataAdapter.Fill(
                this.coffeeDataSet11.CoffeeTypes);

            foreach (DataRow dRow in coffeeDataSet11.CoffeeTypes.Rows)
            {
                if (dRow.RowState != DataRowState.Deleted)
                {
                    ListViewItem lstItem = new ListViewItem(dRow["CoffeeID"].ToString());
                    lstItem.SubItems.Add(dRow["CoffeeName"].ToString());
                    lstItem.SubItems.Add(dRow["CoffeePrice"].ToString());
                    lstItem.SubItems.Add(dRow["CoffeeStrength"].ToString());
                    lstItem.SubItems.Add(dRow["Origin"].ToString());
                    lstItem.SubItems.Add(dRow["QuantityInStock"].ToString());
                    lstItem.SubItems.Add(dRow["Pic"].ToString());
                    listView1.Items.Add(lstItem);
                }
            }

That is the code for it. This is the same as I did on another form, and it errored - I guess the other form messed up some how?

lightshift 0 Newbie Poster · Answer 3 · 2011-03-28T18:39:42+00:00

I also copied code to another form, where I want to search for customer orders and ran it to recieve the error "The SelectCommand property has not been initialized before calling 'Fill'."

Again, same code. That errored on the line:

this.myDataAdapter.Fill(this.coffeeDataSet11.CoffeeTypes);

Mitja Bonca 557 Nearly a Posting Maven · Answer 4 · 2011-03-28T19:56:22+00:00

Did you set the View property of gridView?

listView.View = View.Details;

hope it something like it (Iam telling you by heart)
Mitja

Mitja Bonca 557 Nearly a Posting Maven · Answer 5 · 2011-03-28T19:57:52+00:00

To fill the dataTable you have to do:

string connection = @"Data Source=.\SQLEXPRESS;AttachDbFilename=E:\UCP\C And ASPNET\Coffee.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            DataTable table = new DataTable("MyTable");
            SqlConnection cn = new SqlConnection(connection);
            //no need of try catch, just make sure the connString is ok!
            cn.Open();
            string strSQL = "SELECT * FROM CoffeeTypes";
            SqlCommand cmd = new SqlCommand(strSQL, cn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(table);

This will work 100%

Mitja

lightshift 0 Newbie Poster · Answer 6 · 2011-03-28T22:01:06+00:00

Thanks for that, I can see what I was doing wrong however it still doesn't like it and I don't know why. I will post the entire form code up, so you can see the structure.

I must be doing something wrong, but think this will be beneficial in case it is something outside of my button click event.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace MainAssignment
{
    public partial class coffeeOrders : Form
    {
        public coffeeOrders()
        {
            InitializeComponent();
        }

        DataSet orderDataSet = new DataSet();
         SqlDataAdapter myDataAdapter = new SqlDataAdapter();
        private void coffeeOrders_Load(object sender, EventArgs e)
        {
            
            string connection = @"Data Source=.\SQLEXPRESS;AttachDbFilename=H:\UCP\C And ASPNET\Coffee.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            DataTable table = new DataTable("Orders");
            SqlConnection cn = new SqlConnection(connection);
            try
            {
                cn.Open();
            }
            catch (Exception)
            {
                MessageBox.Show("Did not connect");
            }
            string strSQL = "SELECT * FROM CustomerOrders";
            SqlCommand cmd = new SqlCommand(strSQL, cn);
            SqlDataAdapter myDataAdapter = new SqlDataAdapter(cmd);
            myDataAdapter.Fill(table);
            myDataAdapter = new System.Data.SqlClient.SqlDataAdapter(strSQL, cn);
        }
       private void btnDisplayAll_Click(object sender, EventArgs e)
        {

            listView1.Items.Clear();//first clear out all item entries

            //now call the SQL query to fill the dataSet
            this.myDataAdapter.Fill(
                this.coffeeDataSet11.customerOrders);
           
            foreach (DataRow dRow in coffeeDataSet11.customerOrders.Rows)
            {
                if (dRow.RowState != DataRowState.Deleted)
                {
                    ListViewItem lstItem = new ListViewItem(dRow["OrderID"].ToString());
                    lstItem.SubItems.Add(dRow["CoffeeName"].ToString());
                    lstItem.SubItems.Add(dRow["CoffeeGrind"].ToString());
                    lstItem.SubItems.Add(dRow["Price"].ToString());
                    lstItem.SubItems.Add(dRow["Quantity"].ToString());
                    //lstItem.SubItems.Add(dRow["QuantityInStock"].ToString());
                    //lstItem.SubItems.Add(dRow["Pic"].ToString());
                    listView1.Items.Add(lstItem);
                }
            }


            
        }


       //private void listView1_SelectedIndexChanged(object sender, EventArgs e)
       //{

       //}
    }
}

Mitja Bonca 557 Nearly a Posting Maven · Answer 7 · 2011-03-28T22:23:42+00:00

Do:

public partial class coffeeOrders : Form
    {
        public coffeeOrders()
        {
            InitializeComponent();
        }
 
        DataSet orderDataSet = new DataSet();
        private void coffeeOrders_Load(object sender, EventArgs e)
        {
 
            string connection = @"Data Source=.\SQLEXPRESS;AttachDbFilename=H:\UCP\C And ASPNET\Coffee.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            DataTable table = new DataTable("Orders");
            //add  table to dataSet:
            orderDataSet.Tables.Add(table);

            SqlConnection cn = new SqlConnection(connection);             
            string strSQL = "SELECT * FROM CustomerOrders";
            SqlCommand cmd = new SqlCommand(strSQL, cn);
            SqlDataAdapter myDataAdapter = new SqlDataAdapter(cmd);
            myDataAdapter.Fill(table);
        }
        private void btnDisplayAll_Click(object sender, EventArgs e)
        { 
            foreach (DataRow dRow in orderDataSet.Tables["Orders"].Rows)
            {
                //CHECK FOR THE CORRECT NAMES OF THE COLUMNS IN DAATABLE CALLED: ("table")!!!!
                
                ListViewItem lstItem = new ListViewItem(dRow["OrderID"].ToString());
                lstItem.SubItems.Add(dRow["CoffeeName"].ToString());
                lstItem.SubItems.Add(dRow["CoffeeGrind"].ToString());
                lstItem.SubItems.Add(dRow["Price"].ToString());
                lstItem.SubItems.Add(dRow["Quantity"].ToString());
                //lstItem.SubItems.Add(dRow["QuantityInStock"].ToString());
                //lstItem.SubItems.Add(dRow["Pic"].ToString());
                listView1.Items.Add(lstItem);                
            } 
        }
 
 
       //private void listView1_SelectedIndexChanged(object sender, EventArgs e)
       //{
 
       //}
    }

And please dont use that try, catch blocks in there - no needed at all (just make sure that connString is valid!!
And always will be OK!! Understood?

Hope this helps,
Mitja

kitjo -3 Light Poster · Answer 8 · 2011-05-03T12:59:23+00:00

any way some thing here can also work you need to call the getvalue identifier in your code as you
additem.listview(declaration)getvalue(index)