database query populate combo box

  • (2 Pages)
  • +
  • 1
  • 2

26 Replies - 17492 Views - Last Post: 30 July 2009 - 12:22 PM Rate Topic: -----

#1 codeman0013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 28-July 09

database query populate combo box

Post icon  Posted 28 July 2009 - 12:44 PM

Hello everyone,

I'm new to c# and i'm trying to make a program that populates a box cmbopartner with results from a sql query.. the program runs with no errors but the box never populates... can anyone help me?

 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.Collections;
using System.Data.OleDb;


namespace EDI_Updater
{
		
	public partial class frmmain : Form
	{
		public OleDbConnection myConn;
		public OleDbDataAdapter myDataAdapter;
		public string strpartner;
		public string[] strArraylist = new string[1000];
		static int i = 0; //index for strArraylist
		public string strTemp;

		private void Form1_Load(object sender, System.EventArgs e)
		{
			CreateSqlConnection();
		}

		public frmmain() 
		{
			InitializeComponent();

		}

		private void CreateSqlConnection()
		{
			
		try 
			{
				//open the connection to the database
				myConn = new OleDbConnection();
				myConn.ConnectionString = "Persist Security Info=False;Integrated Security=SSPI;database=gentranNT5;server=decsql02;Connect Timeout=30";
				myConn.Open();			
			}
			catch(OleDbException ex) 
			{
				MessageBox.Show("Error in connection ..."+ex.Message);
			}
			
			string sqlStr ="select * from FunctionalGroupControl_tb;";
			
			//Instantiate a DataAdapter by passing the sqlStr and Connection.
			//now data in raw form
			myDataAdapter = new OleDbDataAdapter(sqlStr,myConn);

			//Instantiate a DataSet
			DataSet myDataset = new DataSet();
			
			// Populate the data table "student"
			myDataset.Clear();
			myDataAdapter.Fill(myDataset,"FunctionalGroupControl_tb");		
			
			foreach (DataRow myRow in myDataset.Tables["FunctionalGroupControl_tb"].Rows)
			{
				strpartner =(string) myRow["partnerkey"];			
				//put strTemp into string strArraylist with int i(index for strArraylist)
				this.strArraylist[i++]=strTemp;
				//display in the ComboBox only Partner ID
				this.cmbopartner.Items.Add(strpartner);
			}
			// Close the connection to the database.
			myConn.Close();

		}///////////////////////////////////////////////
	

		private void btnexit_Click(object sender, EventArgs e)
		{
			Application.Exit();
		}

	   
	}
}
 


Is This A Good Question/Topic? 0
  • +

Replies To: database query populate combo box

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: database query populate combo box

Posted 28 July 2009 - 12:50 PM

This is how I accomplish this task. Instead of an array I create a new ListItem and go from there

foreach (DataRow row in myDataset.Tables["FunctionalGroupControl_tb"].Rows)
{
	ListItem li = new ListItem(myRow["partnerkey"].ToString());
	cmbopartner.Items.Add(li);
}



Hope that helps :)
Was This Post Helpful? 0
  • +
  • -

#3 codeman0013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 28-July 09

Re: database query populate combo box

Posted 28 July 2009 - 01:48 PM

Now i'm getting the following error..

Error 4 The type or namespace name 'ListItem' could not be found (are you missing a using directive or an assembly reference?) \\Decfil01\DecHome\WilsonCT\My Documents\Visual Studio 2008\Projects\EDI Updater\EDI Updater\Form1.cs 63 35 EDI Updater
Was This Post Helpful? 0
  • +
  • -

#4 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1525
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: database query populate combo box

Posted 28 July 2009 - 02:07 PM

You need to add a reference to the System.Web namespace to your project.
Was This Post Helpful? 0
  • +
  • -

#5 codeman0013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 28-July 09

Re: database query populate combo box

Posted 28 July 2009 - 02:13 PM

View Posteclipsed4utoo, on 28 Jul, 2009 - 01:07 PM, said:

You need to add a reference to the System.Web namespace to your project.



added Using System.Web; and still no differences still same errors as last time..

updated code

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.Collections;
using System.Data.OleDb;
using System.Web;


namespace EDI_Updater
{
		
	public partial class frmmain : Form
	{
		public OleDbConnection myConn;
		public OleDbDataAdapter myDataAdapter;
		public string strpartner;
	   
		private void Form1_Load(object sender, System.EventArgs e)
		{
			CreateSqlConnection();
		}

		public frmmain() 
		{
			InitializeComponent();

		}

		private void CreateSqlConnection()
		{
			
		try 
			{
				//open the connection to the database
				myConn = new OleDbConnection();
				myConn.ConnectionString = "Persist Security Info=False;Integrated Security=SSPI;database=gentranNT5;server=decsql02;Connect Timeout=30";
				myConn.Open();			
			}
			catch(OleDbException ex) 
			{
				MessageBox.Show("Error in connection ..."+ex.Message);
			}
			
			string sqlStr ="select * from FunctionalGroupControl_tb;";
			
			//Instantiate a DataAdapter by passing the sqlStr and Connection.
			//now data in raw form
			myDataAdapter = new OleDbDataAdapter(sqlStr,myConn);

			//Instantiate a DataSet
			DataSet myDataset = new DataSet();
			
			// Populate the data table "student"
			myDataset.Clear();
			myDataAdapter.Fill(myDataset,"FunctionalGroupControl_tb");

			foreach (DataRow row in myDataset.Tables["FunctionalGroupControl_tb"].Rows)
			{
				ListItem li = new ListItem(myRow["partnerkey"].ToString());
				cmbopartner.Items.Add(li);
			}

			// Close the connection to the database.
			myConn.Close();

		}///////////////////////////////////////////////
	

		private void btnexit_Click(object sender, EventArgs e)
		{
			Application.Exit();
		}

	   
	}
}
 

Was This Post Helpful? 0
  • +
  • -

#6 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: database query populate combo box

Posted 28 July 2009 - 02:38 PM

My bad (My apologies) for some reason I gave an ASP.NET example, give me a few to find the Windows forms example
Was This Post Helpful? 0
  • +
  • -

#7 codeman0013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 28-July 09

Re: database query populate combo box

Posted 28 July 2009 - 03:09 PM

No problem thanks for all of the help again much appreciated I'm a vb6 guy trying to learn c#..
Was This Post Helpful? 0
  • +
  • -

#8 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1525
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: database query populate combo box

Posted 28 July 2009 - 05:11 PM

That's not adding a reference. Right click on the project, then add reference, then find the system.web reference. It's toward the bottom of the list.

This post has been edited by eclipsed4utoo: 28 July 2009 - 05:12 PM

Was This Post Helpful? 0
  • +
  • -

#9 codeman0013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 28-July 09

Re: database query populate combo box

Posted 29 July 2009 - 07:10 AM

i need the windwos form version exlipsed not asp.net sorry waiting on a response from psychocoder hopefully that will do it..
Was This Post Helpful? 0
  • +
  • -

#10 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1525
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: database query populate combo box

Posted 29 July 2009 - 07:17 AM

View Postcodeman0013, on 29 Jul, 2009 - 10:10 AM, said:

i need the windwos form version exlipsed not asp.net sorry waiting on a response from psychocoder hopefully that will do it..


If that is a requirement for your application, then that is fine. But if it's not, using the ListItem in a windows form works just fine. It's what I use whether it's for ASP.Net or Windows Forms.

This post has been edited by eclipsed4utoo: 29 July 2009 - 07:19 AM

Was This Post Helpful? 0
  • +
  • -

#11 CrazedAzn  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 26-September 07

Re: database query populate combo box

Posted 29 July 2009 - 07:44 AM

This is what I do, although probably not optimal:

		public void loadData(ComboBox theBox, string table, string name) {
			List<string> theList = new List<string>();
			theList.Add("");
			MySqlConnection connection = connector.getConnection();
			connection.Open();
			MySqlCommand commander = connector.getCommand("SELECT " + name + " FROM " + table, connection);
			MySqlDataReader reader = commander.ExecuteReader();
			while (reader.Read()) {
				if(!theList.Contains(reader.GetString(0))) 
					theList.Add(reader.GetString(0));
			}
			reader.Close();
			connection.Close();
			theBox.DataSource = theList;
		}



Of course you'll have to modify it to suit your purposes. I wrote a method that will do it for any given ComboBox and any given table / field. I connected to a database with a separate class I created.
Was This Post Helpful? 0
  • +
  • -

#12 codeman0013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 28-July 09

Re: database query populate combo box

Posted 29 July 2009 - 10:51 AM

ok so i added the web reference and it still doesnt like...

   foreach (DataRow row in myDataset.Tables["FunctionalGroupControl_tb"].Rows)
			{
				ListItem li = new ListItem(myRow["partnerkey"].ToString());
				cmbopartner.Items.Add(li);
			}

			// Close the connection to the database.
			myConn.Close();



says:

Warning 1 Invalid search path 'C:\Program Files\Microsoft Visual Studio\VC98\mfc\lib' specified in 'LIB environment variable' -- 'The system cannot find the path specified. ' EDI Updater
Warning 2 Invalid search path 'C:\Program Files\Microsoft Visual Studio\VC98\lib' specified in 'LIB environment variable' -- 'The system cannot find the path specified. ' EDI Updater
Error 3 The type or namespace name 'ListItem' could not be found (are you missing a using directive or an assembly reference?) \\Decfil01\DecHome\WilsonCT\My Documents\Visual Studio 2008\Projects\EDI Updater\EDI Updater\Form1.cs 64 17 EDI Updater
Error 4 The type or namespace name 'ListItem' could not be found (are you missing a using directive or an assembly reference?) \\Decfil01\DecHome\WilsonCT\My Documents\Visual Studio 2008\Projects\EDI Updater\EDI Updater\Form1.cs 64 35 EDI Updater
Error 5 The name 'myRow' does not exist in the current context \\Decfil01\DecHome\WilsonCT\My Documents\Visual Studio 2008\Projects\EDI Updater\EDI Updater\Form1.cs 64 44 EDI Updater
Was This Post Helpful? 0
  • +
  • -

#13 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1525
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: database query populate combo box

Posted 29 July 2009 - 11:21 AM

View Postcodeman0013, on 29 Jul, 2009 - 01:51 PM, said:

ok so i added the web reference and it still doesnt like...

   foreach (DataRow row in myDataset.Tables["FunctionalGroupControl_tb"].Rows)
			{
				ListItem li = new ListItem(myRow["partnerkey"].ToString());
				cmbopartner.Items.Add(li);
			}

			// Close the connection to the database.
			myConn.Close();



says:

Warning 1 Invalid search path 'C:\Program Files\Microsoft Visual Studio\VC98\mfc\lib' specified in 'LIB environment variable' -- 'The system cannot find the path specified. ' EDI Updater
Warning 2 Invalid search path 'C:\Program Files\Microsoft Visual Studio\VC98\lib' specified in 'LIB environment variable' -- 'The system cannot find the path specified. ' EDI Updater
Error 3 The type or namespace name 'ListItem' could not be found (are you missing a using directive or an assembly reference?) \\Decfil01\DecHome\WilsonCT\My Documents\Visual Studio 2008\Projects\EDI Updater\EDI Updater\Form1.cs 64 17 EDI Updater
Error 4 The type or namespace name 'ListItem' could not be found (are you missing a using directive or an assembly reference?) \\Decfil01\DecHome\WilsonCT\My Documents\Visual Studio 2008\Projects\EDI Updater\EDI Updater\Form1.cs 64 35 EDI Updater
Error 5 The name 'myRow' does not exist in the current context \\Decfil01\DecHome\WilsonCT\My Documents\Visual Studio 2008\Projects\EDI Updater\EDI Updater\Form1.cs 64 44 EDI Updater


your foreach is creating the object "row", but you are trying to use "myRow".

Do you have the "using System.Web;" at the top of the form?
Was This Post Helpful? 0
  • +
  • -

#14 codeman0013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 28-July 09

Re: database query populate combo box

Posted 29 July 2009 - 11:26 AM

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.Collections;
using System.Data.OleDb;
using System.Web;


namespace EDI_Updater
{
		
	public partial class frmmain : Form
	{
		public OleDbConnection myConn;
		public OleDbDataAdapter myDataAdapter;
		public string strpartner;
	   
		private void Form1_Load(object sender, System.EventArgs e)
		{
			CreateSqlConnection();
		}

		public frmmain() 
		{
			InitializeComponent();

		}

		private void CreateSqlConnection()
		{
			
		try 
			{
				//open the connection to the database
				myConn = new OleDbConnection();
				myConn.ConnectionString = "Persist Security Info=False;Integrated Security=SSPI;database=gentranNT5;server=decsql02;Connect Timeout=30";
				myConn.Open();			
			}
			catch(OleDbException ex) 
			{
				MessageBox.Show("Error in connection ..."+ex.Message);
			}
			
			string sqlStr ="select * from FunctionalGroupControl_tb;";
			
			//Instantiate a DataAdapter by passing the sqlStr and Connection.
			//now data in raw form
			myDataAdapter = new OleDbDataAdapter(sqlStr,myConn);

			//Instantiate a DataSet
			DataSet myDataset = new DataSet();
			
			// Populate the data table "student"
			myDataset.Clear();
			myDataAdapter.Fill(myDataset,"FunctionalGroupControl_tb");

			foreach (DataRow row in myDataset.Tables["FunctionalGroupControl_tb"].Rows)
			{
				ListItem li = new ListItem(row["partnerkey"].ToString());
				cmbopartner.Items.Add(li);
			}

			// Close the connection to the database.
			myConn.Close();

		}///////////////////////////////////////////////
	

		private void btnexit_Click(object sender, EventArgs e)
		{
			Application.Exit();
		}

	   
	}
}




Yes i fixed the row thing and now it just complains about ListItem any suggestions?
Was This Post Helpful? 0
  • +
  • -

#15 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1525
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: database query populate combo box

Posted 29 July 2009 - 11:44 AM

let's try...

using System.Web.UI.WebControls;


Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2