How to populate a ListView's ListItem and SubItem from data in a SQL database - C# Discussion Boards

Re: How to populate a ListView's ListItem and SubItem from data in a SQL database

5-Aug-09 15:55

Dear Sirs:
I have searched numerous articles online and in the built-in help file but can't find an answer to this anywhere. Confused | :confused:

I have a C# WinForms application with a ListView control named "lstVwAddress", containing 4-columns, i.e., "FirstName", "LastName", "Email", and "Phone".

My form also has 4-textboxes with the same names, i.e., "txtFirstName", "txtLastName", "txtEmail", and "txtPhone", for which the user can enter data and save it to the database.

My application has a SQL 2008 Express database with a simple "AddressBook" table with an autoincremented column, i.e., "AddressID" and columns for "FirstName", "LastName", "Email", and "PhoneNumber" fields.

It also has an "AddressBookDataSet" and an "AddressesTableAdapter".

What I would like to know is how can I loop thru and retrieve data from each row/column in the database and use it to populate the ListView's ListItem and SubItem's. I don't need to retrieve the autoincremented ID column, just the remaining ones.
I'm also not using any type of data-bound controls.

I can successfully Insert data into the table but need to know how to retrieve and populate the ListView's ListItems and subitems. Also, can I use the existing TableAdapter, or do I have to declare separate data objects to retrieve this. My TableAdapter's query is "SELECT FirstName, LastName, Email, PhoneNumber FROM dbo.Addresses"

I have tried researching this on the Internet but can't find any good examples. The examples listed were either for a data-bound control like the DataGrid or were for VB.Net and not for C#. The examples found in the built-in help file all showed manually populating the items at design time instead of pulling the data from a SQL database. Please help.

Thank you in advance.

Christian Graus5-Aug-09 16:00

Re: How to populate a ListView's ListItem and SubItem from data in a SQL database

5-Aug-09 16:00

For each row you read, build a list view item and populate the sub items, one row at a time.

Christian Graus

Driven to the arms of OSX by Vista.

Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.

Richard A. Arnold6-Aug-09 5:38

Re: How to populate a ListView's ListItem and SubItem from data in a SQL database

6-Aug-09 5:38

Hello Christian:

Thank you for responding to my post. I have 2-questions for you regarding your suggestion, i.e.,
"For each row you read, build a list view item and populate the sub items, one row at a time."

Can you supply an example of the syntax I might use to accomplish this?

Can I use the existing TableAdapter "AddressesTableAdapter" to open the connection and run my Select query, or do I have to work with different data objects, i.e., connectionString, dataTable, dataRow, even possibly a dataReader object, etc.

Thank you again.

Richard

Richard A. Arnold6-Aug-09 6:57

Re: How to populate a ListView's ListItem and SubItem from data in a SQL database

6-Aug-09 6:57

Ok, here is what I tried and it DOES populate my listview's ListItems and SubItems.

private void myRemoteAddressBookForm_Load(object sender, EventArgs e)
{
      displayDataInListView();
}

private void displayDataInListView()
{
      this.addressesTableAdapter.Fill(
            this.addressBookDataSet.Addresses);

      foreach (DataRow dRow in addressBookDataSet.Addresses.Rows)
      {
            foreach (DataColumn dCol in addressBookDataSet.Addresses.Columns)
            {
                  if (dRow.RowState != DataRowState.Deleted)
                  {
                        ListViewItem lstItem = new ListViewItem(dRow["FirstName"].ToString());
                        lstItem.SubItems.Add(dRow["LastName"].ToString());
                        lstItem.SubItems.Add(dRow["Email"].ToString());
                        lstItem.SubItems.Add(dRow["PhoneNumber"].ToString());
                        lstVwAddress.Items.Add(lstItem);
                  }
            }
      }
}

However, for testing purposes I added only 2-entries to my database, but the listview displays 5-duplicate entries for each of the 2-database entries.

I know I must be close; at least I'm populating the ListView now.

From this code, can you see what is causing the duplicates to appear in the ListView?

Thanks again for your suggestions.

Richard Hmmm | :|

Richard A. Arnold6-Aug-09 7:37

Database Data Entry problem

6-Aug-09 7:37

Alright, I finally figured it out.

I remmed out the lines dealing with the DataColumn and it works properly now, without any duplicates.
When I studied it I discovered that by having the lines:

foreach (DataColumn dCol in addressBookDataSet.Addresses.Columns), for which there were 5-columns including the autoincremented ID column, I was actually telling it to return the resultset times the number of columns.

Oh well, one learns as they go along.

Thanks again Christian Graus

Your suggestion was the key to getting this to work.

Muchas gracias

Richard

jestgr5-Aug-09 12:41

jestgr

5-Aug-09 12:41

I am making a basic inventory tracking program for my wife's small business. I am looking for a way to keep track of each sale she makes. Right now, I have a form that pops up asking for customer name and date of sale. I want her to be able to pick how ever many products she wants from a list of products that I have stored in a table in the same database. Then I need to take all of those products and prompt the user to enter the number purchased and prices for each. I am struggling with the best way to do this. As you might suspect, I am learning C# solely for this purpose, so I am very beginner. Thanks for any help.

Re: Database Data Entry problem

Christian Graus5-Aug-09 13:21

5-Aug-09 13:21

Show the items in a listview or a gridview. They will have events for double click, and properties for selected items

Christian Graus

Driven to the arms of OSX by Vista.

Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.

HELp needed

unlucky_dreamer5-Aug-09 11:58

5-Aug-09 11:58

dear all im wondering how to use the openCv libray with c#
could any one help me please..

Luc Pattyn5-Aug-09 12:23

Luc Pattyn

5-Aug-09 12:23

Hi,

this site has a large collection of articles and a reasonably effective search engine. You can easily find what you want in one try.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

unlucky_dreamer5-Aug-09 12:26

5-Aug-09 12:26

thanks alot for your help

Christian Graus5-Aug-09 12:24

5-Aug-09 12:24

You write code. If you want a more specific answer, ask a more specific question and show some signs of caring enough about the answer to have tried to find information yourself.

Christian Graus

Driven to the arms of OSX by Vista.

Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.

unlucky_dreamer5-Aug-09 12:29

5-Aug-09 12:29

well actually iam trying to make a program that will track the lips of human then read this lips..
so when i was reading its said that i should use openCv library in order to track the lips using special methode,
so my question is how to use this library with c#2003 or 2005...

Christian Graus5-Aug-09 13:20

5-Aug-09 13:20

I am willing to bet that you're not going to be able to do this.

In any case, it's a dumb question to ask here. You should find a forum dedicated to this library and ask it there.

Christian Graus

Driven to the arms of OSX by Vista.

Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.

unlucky_dreamer6-Aug-09 11:31

Assigning a SQL query result to a variable

6-Aug-09 11:31

well,thanks for you answer..and i think its better to motivate people instead of pushing them down,,anyway..once u choose hope anything is gonna be possible

JohnQuar15-Aug-09 11:44

JohnQuar1

5-Aug-09 11:44

Hello,

I have a very simple query SELECT newid(), and I want to take the result of this query and assign it a variable so I can use it as a unique ID and use that again in another query. I am having trouble saving the actual variable. Can anyone help? Here is what I have so far. I am also pretty sure that my ID.Close() is in the wrong location. Let me kno what you think.

Session["userName"] = "JMQ";
// create a new SqlConnection object with the appropriate connection string
SqlConnection sqlConn = new SqlConnection("Data Source=localhost;Initial Catalog=Cost_Model;Integrated Security=True");

//Open the connection
sqlConn.Open();

String result;

//Create unique ID
SqlCommand sqlComm1 = new SqlCommand("SELECT newid()", sqlConn);
SqlDataReader ID = sqlComm1.ExecuteReader(CommandBehavior.CloseConnection);

//Close the connection
ID.Close();

sqlConn.Open();
SqlCommand sqlComm2 = new SqlCommand(
    "SELECT * INTO ##tempFAR15_" + Session["userName"] + "_" + ID + " FROM FN_FAR15(1)",
    sqlConn);
SqlDataReader r = sqlComm2.ExecuteReader(CommandBehavior.CloseConnection);

r.Close();

Response.Redirect("FAR15.aspx");

I want to take the result from the 1st query which I (probably innapropriately have labeled as ID) and then use ID in teh second query. Thanks ALOT!!

Re: Assigning a SQL query result to a variable

Christian Graus5-Aug-09 12:25