When value is selected in combobox requery database and display result

  • (2 Pages)
  • +
  • 1
  • 2

21 Replies - 6171 Views - Last Post: 03 August 2009 - 09:13 AM Rate Topic: -----

#1 codeman0013   User is offline

  • New D.I.C Head

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

When value is selected in combobox requery database and display result

Posted 31 July 2009 - 08:05 AM

Ok so i have got the first half of my code working with help from you guys and now i'm stuck.. How can i now take my selected value in cmbopartner and when one is picked refresh the form and put the value of ControlNumber(field in the datbase) into the text box?

  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;
using System.Web.UI.WebControls; 

namespace EDI_Updater
{
		
	public partial class frmmain : Form
	{
		public OleDbConnection myConn;
		public OleDbDataAdapter myDataAdapter;
		public string strpartner;
	  
	   
	
		public frmmain() 
		{
			InitializeComponent();
			CreateSqlConnection();

		}

		private void CreateSqlConnection()
		{
		  
		try 
			{
				//open the connection to the database
				myConn = new OleDbConnection();
				myConn.ConnectionString = "Persist Security Info=False;Integrated Security=SSPI;Provider = SQLOLEDB;database=gentranNT5;server=decsql02;Connect Timeout=30";
				myConn.Open();			
			}
			catch(OleDbException ex) 
			{
				MessageBox.Show("Error in connection ..."+ex.Message);
			}

		string sqlStr = "select distinct PartnerKEY, ControlNumber from FunctionalGroupControl_tb where ControlNumber <> '0'";
			
			//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");
			//MessageBox.Show("Returned Rows: " + myDataset.Tables["FunctionalGroupControl_tb"].Rows.Count.ToString());

		  
			
			foreach (DataRow row in myDataset.Tables["FunctionalGroupControl_tb"].Rows)
			{
				cmbopartner.Text = "Select Partner";
				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();
		}

		private void frmmain_Load(object sender, EventArgs e)
		{
			// TODO: This line of code loads data into the 'gentranNT5DataSet.FunctionalGroupControl_tb' table. You can move, or remove it, as needed.
			this.functionalGroupControl_tbTableAdapter.Fill(this.gentranNT5DataSet.FunctionalGroupControl_tb);

		}
	   

	   
	}
}
 


*edit SixOfEleven*
Fixed code tags

This post has been edited by SixOfEleven: 31 July 2009 - 08:16 AM


Is This A Good Question/Topic? 0
  • +

Replies To: When value is selected in combobox requery database and display result

#2 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1533
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: When value is selected in combobox requery database and display result

Posted 31 July 2009 - 08:56 AM

You can always take a look at the events for a control. Hit F4 to show the Properties window, then click the "Lightning bolt" icon. This will give you a list of events for the selected control.

You want the "selected value". There is a "SelectedValueChanged" event for a combobox. However, most people tend to use the "SelectedIndexChanged" event instead, just incase two Items in the combobox have the same value(ValueMember property).

So now, double-click on the "SelectedIndexChanged" event, or you can just double click on the control, since this event is the "default" event for a combobox.

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
      string text = comboBox1.Text;

      // your connection stuff.....

      string sqlStr = "select distinct ControlNumber from FunctionalGroupControl_tb where PartnerKEY ='" + text + "'"; 

      // SqlCommand stuff.....

      textBox1.Text = (string)cmd.ExecuteScalar();
}



something like that should work.
Was This Post Helpful? 0
  • +
  • -

#3 codeman0013   User is offline

  • New D.I.C Head

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

Re: When value is selected in combobox requery database and display result

Posted 31 July 2009 - 09:35 AM

private void cmbopartner_SelectedIndexChanged(object sender, EventArgs e)
		{
			{	  string text = cmbopartner.Text;
			try
			{
				//open the connection to the database
				myConn = new OleDbConnection();
				myConn.ConnectionString = "Persist Security Info=False;Integrated Security=SSPI;Provider = SQLOLEDB;database=gentranNT5;server=decsql02;Connect Timeout=30";
				myConn.Open();
			}
			catch (OleDbException ex)
			{
				MessageBox.Show("Error in connection ..." + ex.Message);
			}

			string sqlStr = "select distinct ControlNumber from FunctionalGroupControl_tb where PartnerKEY ='" + text + "'";   

			//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");
			 
				txtinvnum.Text = (string)cmd.ExecuteScalar();
			}
		}
	   

	   
	}




it complains that cmd does not exist in the current context do i need to adjust this?
Was This Post Helpful? 0
  • +
  • -

#4 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1533
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: When value is selected in combobox requery database and display result

Posted 31 July 2009 - 10:56 AM

that's because you aren't using the SqlCommand object. In this instance, you don't really need to create a dataset to get the data.

here is an example of running an inline query...

using (SqlConnection conn = new SqlConnection(yourConnectionString))
{
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.CommandText = "select distinct ControlNumber from FunctionalGroupControl_tb where PartnerKEY = @Key";

        cmd.Parameters.AddWithValue("@Key", text);

        conn.Open();

        textBox1.Text = (string)cmd.ExecuteScalar();
    }
}



That should work for you. remember to put your connection string inplace of "yourConnectionString", and replace "textBox1" with the name of your textbox.

Personally, I use this way to query a database rather than the dataset.
Was This Post Helpful? 0
  • +
  • -

#5 codeman0013   User is offline

  • New D.I.C Head

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

Re: When value is selected in combobox requery database and display result

Posted 31 July 2009 - 04:02 PM

		private void cmbopartner_SelectedIndexChanged(object sender, EventArgs e)
		{



			using (SqlConnection conn = new SqlConnection("select distinct ControlNumber from FunctionalGroupControl_tb where PartnerKEY ='" + text + "'"))
			{
				using (SqlCommand cmd = new SqlCommand())
				{
					cmd.Connection = conn;
					cmd.CommandType = System.Data.CommandType.Text;
					cmd.CommandText = "select distinct ControlNumber from FunctionalGroupControl_tb where PartnerKEY = @Key";

					cmd.Parameters.AddWithValue("@Key", text);

					conn.Open();

					txtinvnum.Text = (string)cmd.ExecuteScalar();
				}
			}



		}



I made it worse... Help!!!

Quote

Error 1 The type or namespace name 'SqlConnection' 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 94 20 EDI Updater
Error 2 The type or namespace name 'SqlConnection' 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 94 45 EDI Updater
Error 3 The name 'text' does not exist in the current context \\Decfil01\DecHome\wilsonct\My Documents\Visual Studio 2008\Projects\EDI Updater\EDI Updater\Form1.cs 94 144 EDI Updater
Error 4 The type or namespace name 'SqlCommand' 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 96 24 EDI Updater
Error 5 The type or namespace name 'SqlCommand' 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 96 45 EDI Updater
Error 6 The name 'text' does not exist in the current context \\Decfil01\DecHome\wilsonct\My Documents\Visual Studio 2008\Projects\EDI Updater\EDI Updater\Form1.cs 102 57 EDI Updater

Was This Post Helpful? 0
  • +
  • -

#6 PsychoCoder   User is offline

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

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

Re: When value is selected in combobox requery database and display result

Posted 31 July 2009 - 04:06 PM

Those errors are easy, you need a reference to the System.Data.SqlClient Namespace
Was This Post Helpful? 0
  • +
  • -

#7 codeman0013   User is offline

  • New D.I.C Head

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

Re: When value is selected in combobox requery database and display result

Posted 31 July 2009 - 04:21 PM

Now it complains text doesn't exist shouldn't it know this from the combobox??
Was This Post Helpful? 0
  • +
  • -

#8 PsychoCoder   User is offline

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

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

Re: When value is selected in combobox requery database and display result

Posted 31 July 2009 - 04:24 PM

I'm assuming you're referring to this line

using (SqlConnection conn = new SqlConnection("select distinct ControlNumber from FunctionalGroupControl_tb where PartnerKEY ='" + text + "'"))



Well, where is text being declared and set?
Was This Post Helpful? 0
  • +
  • -

#9 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1533
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: When value is selected in combobox requery database and display result

Posted 31 July 2009 - 04:37 PM

View Postcodeman0013, on 31 Jul, 2009 - 07:21 PM, said:

Now it complains text doesn't exist shouldn't it know this from the combobox??


it seems that you deleted that line of code when you did the new code.
Was This Post Helpful? 0
  • +
  • -

#10 codeman0013   User is offline

  • New D.I.C Head

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

Re: When value is selected in combobox requery database and display result

Posted 31 July 2009 - 04:37 PM

I don't think it is I think this is the problem is this a temp variable sorry I am learning as we go like I said sorry



View PostPsychoCoder, on 31 Jul, 2009 - 03:24 PM, said:

I'm assuming you're referring to this line

using (SqlConnection conn = new SqlConnection("select distinct ControlNumber from FunctionalGroupControl_tb where PartnerKEY ='" + text + "'"))



Well, where is text being declared and set?

Was This Post Helpful? 0
  • +
  • -

#11 codeman0013   User is offline

  • New D.I.C Head

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

Re: When value is selected in combobox requery database and display result

Posted 31 July 2009 - 06:01 PM

[code]

using (SqlConnection conn = new SqlConnection("select distinct ControlNumber from FunctionalGroupControl_tb where PartnerKEY ='" + text + "'"))

[\code]

now when i run the program this line says Keyword not supported? I'm totally lost guys thanks again for everything you have helped me with so far..
Was This Post Helpful? 0
  • +
  • -

#12 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1533
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: When value is selected in combobox requery database and display result

Posted 31 July 2009 - 09:16 PM

View Postcodeman0013, on 31 Jul, 2009 - 09:01 PM, said:

[code]

using (SqlConnection conn = new SqlConnection("select distinct ControlNumber from FunctionalGroupControl_tb where PartnerKEY ='" + text + "'"))

[\code]

now when i run the program this line says Keyword not supported? I'm totally lost guys thanks again for everything you have helped me with so far..


why are you putting the query as the connection string? The constructor of the SqlConnection object accepts a connection string...not the actual sql query

This post has been edited by eclipsed4utoo: 31 July 2009 - 09:24 PM

Was This Post Helpful? 0
  • +
  • -

#13 codeman0013   User is offline

  • New D.I.C Head

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

Re: When value is selected in combobox requery database and display result

Posted 03 August 2009 - 06:30 AM

 using (SqlConnection conn = new SqlConnection("Persist Security Info=False;Integrated Security=SSPI;Provider = SQLOLEDB;database=gentranNT5;server=decsql02;Connect Timeout=30"))
		   



now it complains that provider isnt a valid keyword? Am i still putting the incorrect information in here?
Was This Post Helpful? 0
  • +
  • -

#14 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1533
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: When value is selected in combobox requery database and display result

Posted 03 August 2009 - 06:43 AM

you don't need Provider. Here is a valid connection string...

SqlConnection cn = new SqlConnection(@"Data Source=MyServerName;Initial Catalog=MyDatabaseName;Persist Security Info=True;User ID=MyUserID;Password=MyPassword");



that is the connection string that I always use when connecting to SQL Server.
Was This Post Helpful? 0
  • +
  • -

#15 codeman0013   User is offline

  • New D.I.C Head

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

Re: When value is selected in combobox requery database and display result

Posted 03 August 2009 - 07:02 AM

  private void cmbopartner_SelectedIndexChanged(object sender, EventArgs e)
		{


			string text = cmbopartner.Text;

			using (SqlConnection conn = new SqlConnection(@"Data Source=decsql02;Initial Catalog=gentranNT5;Persist Security Info=True;");
			{
				using (SqlCommand cmd = new SqlCommand())
				{
					cmd.Connection = conn;
					cmd.CommandType = System.Data.CommandType.Text;
					cmd.CommandText = "select distinct ControlNumber from FunctionalGroupControl_tb where PartnerKEY = @Key";

					cmd.Parameters.AddWithValue("@Key", text);

					conn.Open();

					txtinvnum.Text = (string)cmd.ExecuteScalar();
				}
			}



I just keep making this worse... I tried changing it like yorus and now it says that conn does not exist?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2