Populating a listbox 2

[chmilz]

Hi All,

I was wondering if someone could help me with populating a listbox with values from a Database.

I am fairly new to C# and programming in general.

Basically, I have a table of values stored in an oracle database that I would like a listbox on my page to reflect.

I am assuming that I have to create a connection and use a select statement to get what I want from the database, bind the data to a datasource and then bind the datasoure to the listbox.

The problem is is that I don't know the syntax. Would someone be able to help me out at all?

Cheers

 

[sacheson]

OK - here's a partial answer for you. I have SQL Server on my local machine, and not Oracle. I mocked up a quick and dirty sample for you using SQL Server. You should be able to make the switch pretty easily, though.

Here's a little trick I learned a while back: create an empty text file on your computer (right click - New - Text File), then rename the text file <anything>.udl (eg: testCn.udl). Then right click on the udl file, go to properties and follow these steps to create your database connection:
1) select the providers tab, then select the appropriate provider
2) select the connection tab and fill in the rest.
3) Test the connection.
4) open the connection in any text editor (notepad, textpad, context) and you can copy and paste most of the string into your connection string in the code (note: SQL Server requires you to remove the first bit about Provider)

Also, don't get in the habit of data binding. It seems like an easy way to do things now, but as you get more into coding it will do little but cause frustrations.

Then, in your code, do this (using the Northwind database packaged with any MSFT db install):

on a form, I placed one control - a listbox named lstTest.

using System.Data.SqlClient;
...
private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cn = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=CO007WSXPPROD10");
    cn.Open();
    string cmdText = "SELECT FirstName, LastName FROM EMPLOYEES";
    SqlCommand cmd = new SqlCommand(cmdText, cn);
    SqlDataReader dr = cmd.ExecuteReader();
    while(dr.Read())
    {
        lstTest.Items.Add(dr[0].ToString() + " " + dr[1].ToString());
    }
}

that's a quick and dirty way to display the data

______________________________________________
When told, "goto hell", a programmer finds the method, not the destination as harmful.

 

[chmilz]

Thanks for the help Sacheson,

I have copied all of the code into Oracle format and the only problem is when I run the file I get an error at the

"OracleDataReader dr = cmd.ExecuteReader()"

the error says that the column or view doesn't exist... any thoughts?

Cheers

 

[sacheson]

are you accessing your own data? That Northwind sample I provided won't work with an oracle install, because the Northwind database is Microsoft.

I'd try wrapping the code where you're instantiating the DataReader object in a try{}...catch{} block, like this:

try
{
    OracleDataReader dr = cmd.ExecuteReader();
    while(dr.Read())
    {...}
}
catch(OracleException ex)
{
    MessageBox.Show(ex.ToString());
}

Would you mind putting a snippet of your code up?


I also did a google search for you and found this article for you. It's referenced in a couple google groups searches as well, so I'm assuming it's worth the read:

http://www.fawcette.com/vsm/2003_01/magazine/features/beauchemin/

Another suggestion I have is for you to purchase a beginning ADO.Net book, obtain the free release of Microsoft's MSDE database (basically, SQL Server with only 5 seats) and going through some samples. It will offer you a good ramp up, that you can use with the Oracle stuff you're working on.

Later.

______________________________________________
When told, "goto hell", a programmer finds the method, not the destination as harmful.

 

[chmilz]

nevermind,

PICNIC!!!(Problem in Chair not in computer)

I spelled the table name wrong in my select statement (DUH!!)

however, it worked great the first time, now I keep seeing duplicate values whenever I add something to the Database.

I have even tried deleting every row in that table and for some reason it still populates the list box with data when the page loads.... am I missing something??

thanks for all your help on this!

cheers

 

[sacheson]

hmmmm. Interesting.

try putting "lstTest.Items.Clear();" in the first line of Form_Load ... maybe it's just not clearing the contents when you're reloading the page?

______________________________________________
When told, "goto hell", a programmer finds the method, not the destination as harmful.

 

[chmilz]

hey hey,

hmmm... there must be something else going on. Even with the Items.Clear(), I still get values popping up in the list box when the page loads. If I comment out all the code however, the list box appears empty.

I am inclined to think that it is something wrong with the database but when I do a Select statement on that table it is empty (no rows selected).

Weird

 

[sacheson]

Yeah, that's odd.

Are you sure you have the database name specified correctly? And the table?

______________________________________________
When told, "goto hell", a programmer finds the method, not the destination as harmful.

 

[chmilz]

yup Positive....

I managed to get the list box to clear by commenting out everything except the Clear(), running it, then un-commenting the rest of the code.

After that, I added a file (added just fine) but when I hit the refresh button it keeps adding another value to the box.

How do I post my code in those neat little code boxes and I will send you what it looks like.

Cheers

 

[sacheson]

to get your code to display like mine, click on the Process TGML link below the window you type your reply into. It will give you a help sheet for the TGML tags.

It should look like this when you're typing:

 ... <your code goes here> ...

Regarding the values still being there, did you commit the delete transaction on the table?

______________________________________________
When told, "goto hell", a programmer finds the method, not the destination as harmful.

 

[chmilz]

Hello,

no I don't think I did.... I basically connected to the Database with SQLPlus and manually deleted everything from the log_file table (The table where the data I want to populate resides)..

here is the code

private void Page_Load(object sender, System.EventArgs e)
		{
			// Put user code to initialize the page here
			lstFiles.Items.Clear();
			string cnstr = "Data Source=awbsl;User ID=awbsl;Password=awbsl;"; 
			OracleConnection con = new OracleConnection(cnstr);
			try
			{
				con.Open();

				string cmdText = "Select ID, NAME from log_file";

				OracleCommand cmd = new OracleCommand(cmdText, con);
				OracleDataReader dr = cmd.ExecuteReader();

				while (dr.Read())
				{
					lstFiles.Items.Add(dr[0].ToString()+ " " + dr[1].ToString());

				}
			}
			finally
			{
				con.Close();
			}
			
		}

 

[sacheson]

in sql plus, you might want to try ...

TRUNCATE TABLE log_file;
COMMIT;

You can also
DELETE FROM log_file;
COMMIT;

the big diff is that you can rollback a DELETE, and can not rollback a TRUNCATE - AND TRUNCATE is much faster (esp on big tables) because of the rollback with the DELETE.

______________________________________________
When told, "goto hell", a programmer finds the method, not the destination as harmful.

 

[chmilz]

Bleh,

To add to everything I just found out that I will have to get the values from a Datatable (its the way my project manager wants it done ). So... basically, I have a user control that allows a user to "attach" (insert) files into our oracle data base using a User Control on a webpage.

I have the attaching part done. But before I can do the "delete" and "download" buttons I have to have something in the listbox to delete or download....

In comes my original question.. how do I populate a listbox with values.

here is what has to happen.... The Page_Load method of the user control calls a list Method from another file:

private void Page_Load(object sender, System.EventArgs e)
		{
			// Put user code to initialize the page here
			AWBSL.FileSet FileSet = new AWBSL.FileSet();
			DataTable dt = new DataTable();

			FileSet.List(out dt);

			lstFiles.DataSource = dt;
			
			
		}

then, the "list" method performs the query to the Database:

public void List(out DataTable dt)
		{
			
			dt = new DataTable();
			string query = "SELECT id, name FROM Log_file";
			DataElement.DefaultConnectionString = "Data Source=awbsl; User ID=awbsl; Password=awbsl;";
			dt = DataElement.ExecuteQuery(query);

		}

This method just calls a Method of a "DataElement" class to get the data from the database. The ExecuteQuery method returns a DataTable value:

public static DataTable ExecuteQuery(string ConnectionString, string query)
		{
			DataTable result = new DataTable();
			OracleConnection conn = new OracleConnection(ConnectionString);

			try
			{
				conn.Open();

				if (DataElement.PrefixSQL != String.Empty)
				{
					OracleCommand cmd1 = new OracleCommand(DataElement.PrefixSQL, conn);
					cmd1.ExecuteNonQuery();
				}
				
				OracleCommand cmd2 = new OracleCommand(query, conn);
				OracleDataAdapter adp2 = new OracleDataAdapter(cmd2);
				adp2.Fill(result);
			}
			finally
			{
				conn.Close();
			}

			return result;
		}

The way the solution is setup is that all the database querying is done from one file, all the file access stuff is done from one file and the only thing the GUI does is call the methods it needs.

Anyway, that is how I have it set up right now. But I am not getting any records populating my Database. Am I binding the datasource wrong?

Thanks so much for all the help and feedback!

 

[sacheson]

what happens when you do this ...

private void Page_Load(object sender, System.EventArgs e)
        {
            // Put user code to initialize the page here
            AWBSL.FileSet FileSet = new AWBSL.FileSet();
            DataTable dt = new DataTable();

            FileSet.List(out dt);

            lstFiles.DataSource = dt;
            // new code ...
            lstTest.DisplayMember = dt.Columns[0].ToString();
            lstTest.ValueMember = dt.Columns[0].ToString();
            // end new code
            
        }

you should be able to put a break point in your code after the datatable is filled and interrogate the table using some of the tools from within the .Net IDE: Command, Autos, and Locals windows.

Also, when you're referring to "files", I'm pretty sure you're working with "classes"; and it's actually the correct way to perform the connection, retreival, display, and update. It's called encapsulation (designing your code so separate functionality resides in self contained bodies of code), and is a core principle of object oriented programming. To best exploit C# and the .Net framework, getting comfortable with OO techniques will help you more than you'll know ... it'll also take a LOT of time and commitment.

Really, I think you should buy a book and spend some time. What you're doing is definitely frustrating right now, but you're going to learn a lot more by spending a horrible amount of time working through it on your own. I'm definitely here to help, but I don't want to do this for you. ;-) You should also get ramped up with Google Groups, and the MSDN help that should be installed on your machine. They will prove to be priceless resources when you're stuck on something.

______________________________________________
When told, "goto hell", a programmer finds the method, not the destination as harmful.

 

[chmilz]

Hey Sacheson,

thanks so much for your help! I definitely never wanted to do it all for me but I really appreciate your assistance.

I have a book on order already that hopefully will help me get to where I need to be. I did a lot of C++ programming in school but it has been 3 1/2 years since I even looked at a line of code. Plus, I have never worked with C# before. I took the job I am in b/c I wanted to push my comfort zone and try to learn something new while polishing up my coding.. if definitely needs a lot of work !

Thanks again for all your help!

 

[sacheson]

chmilz -

Hey, definitely no worries! It's actually fun to help. And referring to you getting some books, I'm talking more about you learning than complaining about me helping. When I first made the plunge into C#, it was on a high-priority, fixed deadline project - I spent 3 months of 12+ hour days figuring crap out. That was nearly 3 years ago, and I'm still racking my brain over stuff.

You have my gmail address. Please feel free to send a question any time. C# and .Net is cool, but there is a learning curve.

Wrox Press C# Professional, 3rd edition is a must have book; very comprehensive with some great samples.

Also, congrats on being able to find a job that pushes you to C#. In this market, those are often tough to find.

Later.
Sam

______________________________________________
When told, "goto hell", a programmer finds the method, not the destination as harmful.

 

[chmilz]

Hi Sacheson!!

Good news!! I figured it out!!! Here is what I ended up doing:

private void LoadList()
		{
			lstFiles.Items.Clear();
			AWBSL.FileSet FileSet = new AWBSL.FileSet();
			DataTable dt = new DataTable();
			FileSet.List(out dt);


			foreach(DataRow row in dt.Rows)
			{
				ListItem listItem = new ListItem();
				listItem.Value = row["ID"].ToString();
				listItem.Text = row["NAME"].ToString();
				lstFiles.Items.Add(listItem);
			}
		}

I call the LoadList() method on Page Load, when a file is added and when a file is deleted.

Thanks again for all of your help!

 

[sacheson]

Awesome! Good job.

______________________________________________
When told, "goto hell", a programmer finds the method, not the destination as harmful.