10 Replies - 4504 Views - Last Post: 12 November 2012 - 04:19 AM Rate Topic: -----

#1 cobalt-rose   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 13-October 08

Data Binding/Linking my datagrid and showing all data?

Posted 30 December 2008 - 03:50 PM

Hello i have altered my method of displaying my data since my last post and instead of a listview i am now using a datagrid as i thought it would be easier to use for what i want.

I now am stuck in terms of i do not have a clue how i am going to display the rest of the data from my table?

What i need to have is when the user clicks on a row of data e.g. 'client' all records are show in the tabbed page area?

Picture :
Posted Image

I have used a dataset to show the data when client is clicked but i dont think i can use that same dataset as it only retrieves four recods.

How can i have the four records displayed on one side of the page and then when highlighted display all infomation about the client in the seperat tabbed area?

My Code so far which displays client (when clicked) and contractor (when clicked)


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 MySql.Data.MySqlClient;

namespace Deroche_Consultants
{
	public partial class main_screen : Form
	{
		string dataCategory = null;
		DataSet myDataSet;

		public main_screen()
		
		{
			InitializeComponent();	  
		}

		private void client_pic_Click(object sender, EventArgs e)
			{
				ClientDataLoad();
			}

		private void client_label_Click(object sender, EventArgs e)
			{
				ClientDataLoad();
			}

		private void client_buttonBox_Click(object sender, EventArgs e)
			{
				ClientDataLoad();
			}

		private void ClientDataLoad()
		{
		   // string dataCategory = client;
			string connectionString = "Data Source=localhost" + "; Database=_consultants" + "; User ID=root" + "; Password=password;";
			MySqlConnection MySqlConn = new MySqlConnection(connectionString);
			string strSQL = ("SELECT company_id, company_name, contact_fname, contact_sname, employee_count FROM client");
			MySqlCommand mysqlCommand = new MySqlCommand(strSQL, MySqlConn);
			MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(mysqlCommand);
			try
				{
					MySqlConn.Open();
					DataSet myDataSet = new DataSet();
					myDataSet.Clear();
					myDataAdapter.Fill(myDataSet, "client");
					dataGridView1.AutoGenerateColumns = true;
					dataGridView1.DataSource = myDataSet.Tables[0];
					MySqlConn.Close();
				}

			catch (Exception ex)
				{ 
					MessageBox.Show("Data Load Error "+ex.Message.ToString(),
					"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
					MySqlConn.Close();
				}
		}

		private void job_pic_Click(object sender, EventArgs e)
		{

		}

		private void job_label_Click(object sender, EventArgs e)
		{

		}

		private void job_boxButtons_Click(object sender, EventArgs e)
		{

		}

		//====================================== Contractor ======================================//

		private void contractor_pic_Click(object sender, EventArgs e)
			{
				ContractorDataLoad();
			}

		private void contractor_label_Click(object sender, EventArgs e)
			{
				ContractorDataLoad();
			}

		private void contractor_boxButton_Click(object sender, EventArgs e)
			{
				ContractorDataLoad();
			}

		private void ContractorDataLoad()
			{
			   // string dataCategory = contractor;
				string connectionString = "Data Source=localhost" + "; Database=_consultants" + "; User ID=root" + "; Password=password;";
				MySqlConnection MySqlConn = new MySqlConnection(connectionString);
				string strSQL = ("SELECT contractor_id, forename, surname, availability, daily_cost FROM contractor");
				MySqlCommand mysqlCommand = new MySqlCommand(strSQL, MySqlConn);
				MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(mysqlCommand); 
			try
				{
					MySqlConn.Open();
					DataSet myDataSet = new DataSet();
					myDataSet.Clear();
					myDataAdapter.Fill(myDataSet, "contractor");
					myDataAdapter.Fill(myDataSet);
					dataGridView1.AutoGenerateColumns = true;
					dataGridView1.DataSource = myDataSet.Tables[0];
					MySqlConn.Close();
				}
	
				catch (Exception ex)
					{ 
						MessageBox.Show("Data Load Error "+ex.Message.ToString(),
						"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
						MySqlConn.Close();
					}
			}

		private void button1_Click(object sender, EventArgs e)
		{
			Form2 addclient = new Form2();
			addclient.Show();
		}

		private void button3_Click(object sender, EventArgs e)
		{

		}
}
}

Thanks in advance,
C~Rose

This post has been edited by cobalt-rose: 30 December 2008 - 05:19 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Data Binding/Linking my datagrid and showing all data?

#2 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Re: Data Binding/Linking my datagrid and showing all data?

Posted 30 December 2008 - 05:13 PM

If I am understanding you correctly, you want when a user clicks on a row in the datagrid, to then populate all of that rows data in the tabpage...

To do this, just add a cell content click event like so...
private void yourDataGridView_CellContentClick(object sender, DataGridViewCellEventArgs e)
{

}


Then inside of it place some code like this to get the column id of the row that was selected...
string tempVar = yourDataGridView.Rows[e.RowIndex].Cells[0].FormattedValue.ToString();
ColumnID = int.Parse(tempVar);


Then just do a new ExecuteReader query using that ColumnID to return the results and populate the labels and/or textboxes or whatever on the tabpage...

The end result would look something similar to this...
private void yourDataGridView_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
string tempVar = yourDataGridView.Rows[e.RowIndex].Cells[0].FormattedValue.ToString();
ColumnID = int.Parse(tempVar);
string sSQL = "YourStoredProcedure";
MySqlConnection MySqlConn = new MySqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.Parameters.Clear();
cmd.CommandText = sSQL;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ColumnID", ColumnID);
cmd.Connection = MySqlConn;
MySqlConn.Open();
cmd.ExecuteNonQuery();
SqlDataReader rdr = null;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
	TextBox1.Text = (string)rdr["Column2"];
	TextBox2.Text = (string)rdr["Column3"];
	TextBox3.Text = (string)rdr["Column4"];
	TextBox4.Text = (string)rdr["Column5"];
}
MySqlConn.Close();
}


This post has been edited by webwired: 30 December 2008 - 05:15 PM

Was This Post Helpful? 0
  • +
  • -

#3 cobalt-rose   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 13-October 08

Re: Data Binding/Linking my datagrid and showing all data?

Posted 30 December 2008 - 05:29 PM

Hey thankyou for your reply...

That is kind of what i need but i shall explain in slightly more detail...

At current when the client button is clicked i have 'company_id, company_name, contact_fname, contact_sname, employee_count' from the "client" table in the datagrid

BUT

what i need to be displayed in the tabbed form on the right hand side is :
'company_id, company_name , contact_fname , contact_sname , current_systems , employee_count , address , post_code , tel_no , fax , e_mail , jobs_completed , website , notes'

from the "client" database when a row is clicked from the datagrid.

Im thinking that it will be difficult as the dataset from the datagrid only selects four fields so i cannot just copy the exsact data from that dataset.
Was This Post Helpful? 0
  • +
  • -

#4 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Re: Data Binding/Linking my datagrid and showing all data?

Posted 30 December 2008 - 05:53 PM

That is exactly what this will do... Just replace
	TextBox1.Text = (string)rdr["Column2"];
	TextBox2.Text = (string)rdr["Column3"];
	TextBox3.Text = (string)rdr["Column4"];


With...
TextBox1.Text = (int)rdr["company_id"];
TextBox2.Text = (string)rdr["company_name"];
TextBox3.Text = (string)rdr["company_fname"];
TextBox4.Text = (string)rdr["company_sname"];
TextBox5.Text = (string)rdr["current_systems"];
TextBox6.Text = (int)rdr["employee_count"];
TextBox7.Text = (string)rdr["address"];
TextBox8.Text = (int)rdr["post_code"];
TextBox9.Text = (string)rdr["tel_no"];
TextBox10.Text = (string)rdr["fax"];
TextBox11.Text = (string)rdr["e_mail"];
TextBox12.Text = (int)rdr["jobs_completed"];
TextBox13.Text = (string)rdr["website"];
TextBox14.Text = (string)rdr["notes"];


Was This Post Helpful? 0
  • +
  • -

#5 cobalt-rose   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 13-October 08

Re: Data Binding/Linking my datagrid and showing all data?

Posted 02 January 2009 - 05:10 PM

Hello, thanks again for the reply... i have altered this to my code and fiddled wit it but still cannot manage to get it to work. When i run the prog and click on a field nothing is shown in the text boxes in the tabbed area???

is this because the tabbed area is in different section? Do i need to put like tabbedarea.textbox1??

I am using mysql instead of just sql so had to change the code a little but here is what i have so far:

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
		{
			int ColumnID;
			string tempVar = dataGridView1.Rows[e.RowIndex].Cells[0].FormattedValue.ToString();
			ColumnID = int.Parse(tempVar);
			string strSQL = ("Select client_id , company_name, contact_fname, employee_count, jobs_completed, address, post_code, tel_no, fax, e_mail, website, notes FROM client ");
			string connectionString = "Data Source=localhost" + "; Database=deroche_consultants" + "; User ID=root" + "; Password=password;";
			MySqlConnection MySqlConn = new MySqlConnection(connectionString);
			MySqlCommand mysqlCommand = new MySqlCommand(strSQL, MySqlConn);
		 
			mysqlCommand.Parameters.Clear();
			mysqlCommand.CommandText = strSQL;
			mysqlCommand.CommandType = CommandType.StoredProcedure;
			mysqlCommand.Parameters.AddWithValue("@ColumnID", ColumnID);
			mysqlCommand.Connection = MySqlConn;
			MySqlConn.Open();
			mysqlCommand.ExecuteNonQuery();
			MySqlDataReader reader = null;
			reader = mysqlCommand.ExecuteReader();
			while (reader.Read())
			{
				textBox2.Text = (string)reader["company_name"];
				textBox3.Text = (string)reader["company_fname"];
				textBox4.Text = (string)reader["company_sname"];

			}
			MySqlConn.Close();
			}


Also could you briefly explain the meaning of the "storedprocedure" and what it relates too.

Thank you in advance.
C~Rose
Was This Post Helpful? 0
  • +
  • -

#6 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Re: Data Binding/Linking my datagrid and showing all data?

Posted 02 January 2009 - 09:25 PM

Change this line ...
mysqlCommand.CommandType = CommandType.StoredProcedure;


To...
mysqlCommand.CommandType = CommandType.Text;



Because you are not using a stored procedure, you are using a prepared statement to execute your query.

A stored procedure is a separate procedure that contains SQL commands that you can call from your code... The benefit of being able to reuse the stored procedure and helping to prevent SQL injection attacks are a couple of the main benefits... There are however many more...

Quote

is this because the tabbed area is in different section? Do i need to put like tabbedarea.textbox1??


No, you do not have to specify that a control is in a tabbed area, the same as if it was in a group box or a panel...

Quote

textBox2.Text = (string)reader["company_name"];
textBox3.Text = (string)reader["company_fname"];
textBox4.Text = (string)reader["company_sname"];


Are those the names of your textboxes? Or did you forget to change the names of them from my example code?

EDIT: Just noticed also that in your initial query that populates your datagrid, you are not pulling your client_id ... So add that field to your query... you can hide that column on your datagrid if you don't want it to show...
Change this...
private void ClientDataLoad()
{
string strSQL = ("SELECT company_id, company_name, contact_fname, contact_sname, employee_count FROM client");
}


To...
private void ClientDataLoad()
{
string strSQL = ("SELECT client_id, company_id, company_name, contact_fname, contact_sname, employee_count FROM client");
}


This post has been edited by webwired: 02 January 2009 - 09:48 PM

Was This Post Helpful? 1
  • +
  • -

#7 cobalt-rose   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 13-October 08

Re: Data Binding/Linking my datagrid and showing all data?

Posted 03 January 2009 - 03:53 AM

Quote

Are those the names of your textboxes? Or did you forget to change the names of them from my example code?


Yes they are the names of my textboxes as i had planed to alter them at a later date when the code starts to work.

Quote

EDIT: Just noticed also that in your initial query that populates your datagrid, you are not pulling your client_id ... So add that field to your query... you can hide that column on your datagrid if you don't want it to show...
Change this...
private void ClientDataLoad()
{
string strSQL = ("SELECT company_id, company_name, contact_fname, contact_sname, employee_count FROM client");
}


To...
private void ClientDataLoad()
{
string strSQL = ("SELECT client_id, company_id, company_name, contact_fname, contact_sname, employee_count FROM client");
}



This was a mistake on my half, client_id is supposed to be company_id. Thanks for pointing that out i just had to delete the client_id text from the query.

I have made the necessary changes that you suggested and run the prog but still when a cell is highligted nothing happens...

I set a variable to textbox1.text called 'hello' and put set it to display hello when the reader reads but when i select a row it does not eve display the text...

So i guessing there is sopmething with the way the code initiates the reader code?

		private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
		{
			string hello;

			hello = "hello";
			int ColumnID;
			string tempVar = dataGridView1.Rows[e.RowIndex].Cells[0].FormattedValue.ToString();
			ColumnID = int.Parse(tempVar);
			string strSQL = ("Select company_id , company_name, contact_fname, employee_count, jobs_completed, address, post_code, tel_no, fax, e_mail, website, notes FROM client ");
			string connectionString = "Data Source=localhost" + "; Database=_consultants" + "; User ID=root" + "; Password=password;";
			MySqlConnection MySqlConn = new MySqlConnection(connectionString);
			MySqlCommand mysqlCommand = new MySqlCommand(strSQL, MySqlConn);
		 
			mysqlCommand.Parameters.Clear();
			mysqlCommand.CommandText = strSQL;
			mysqlCommand.CommandType = CommandType.Text;
			mysqlCommand.Parameters.AddWithValue("@ColumnID", ColumnID);
			mysqlCommand.Connection = MySqlConn;
			MySqlConn.Open();
			mysqlCommand.ExecuteNonQuery();
			MySqlDataReader reader = null;
			reader = mysqlCommand.ExecuteReader();
			while (reader.Read())
			{
				textBox1.Text = hello;
				textBox2.Text = (string)reader["company_id"];
				textBox3.Text = (string)reader["company_name"];
				textBox4.Text = (string)reader["contact_fname"];
				textBox5.Text = (string)reader["employee_count"];
				textBox6.Text = (string)reader["jobs_completed"];
				textBox7.Text = (string)reader["address"];
				textBox8.Text = (string)reader["post_code"];
				textBox9.Text = (string)reader["tel_no"];
				textBox10.Text = (string)reader["fax"];
				textBox11.Text = (string)reader["e_mail"];
				textBox5.Text = (string)reader["website"];
				textBox5.Text = (string)reader["notes"];

			}
			MySqlConn.Close();
			}


Heres what my main screen looks like at the moment:
Posted Image

Also one more query...At present the way in which i have designed the form on the right hand side it is only designed for when the client button is clicked, and when i click the contractor button the form will still display boxes related to clients...

This obviously needs to dynamically change depending on which button is clicked from the navigation bar at the top.

So am i right in saying that i need to write seperate a methods that create an instance of a form depending on which button is clicked previously and the manually code the textbox positions and labels etc...?

Thanks again,
C~Rose
Was This Post Helpful? 0
  • +
  • -

#8 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Re: Data Binding/Linking my datagrid and showing all data?

Posted 03 January 2009 - 07:45 AM

Ok, first things first... I'm so used to using Stored Procedures that I didn't notice a couple other things... Change your code to look like this...
		private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
		{
			string hello;

			hello = "hello";
			int ColumnID;
			string tempVar = dataGridView1.Rows[e.RowIndex].Cells[0].FormattedValue.ToString();
			ColumnID = int.Parse(tempVar);
			string strSQL = ("Select company_id , company_name, contact_fname, employee_count, jobs_completed, address, post_code, tel_no, fax, e_mail, website, notes FROM client WHERE company_id = 'ColumnID'");
			string connectionString = "Data Source=localhost" + "; Database=_consultants" + "; User ID=root" + "; Password=password;";
			MySqlConnection MySqlConn = new MySqlConnection(connectionString);
			MySqlCommand mysqlCommand = new MySqlCommand(strSQL, MySqlConn);

			mysqlCommand.CommandText = strSQL;
			mysqlCommand.CommandType = CommandType.Text;
			mysqlCommand.Connection = MySqlConn;
			MySqlConn.Open();
			MySqlDataReader reader = null;
			reader = mysqlCommand.ExecuteReader();
			while (reader.Read())
			{
				textBox1.Text = hello;
				textBox2.Text = (string)reader["company_id"];
				textBox3.Text = (string)reader["company_name"];
				textBox4.Text = (string)reader["contact_fname"];
				textBox5.Text = (string)reader["employee_count"];
				textBox6.Text = (string)reader["jobs_completed"];
				textBox7.Text = (string)reader["address"];
				textBox8.Text = (string)reader["post_code"];
				textBox9.Text = (string)reader["tel_no"];
				textBox10.Text = (string)reader["fax"];
				textBox11.Text = (string)reader["e_mail"];
				textBox5.Text = (string)reader["website"];
				textBox5.Text = (string)reader["notes"];

			}
			MySqlConn.Close();
		}


Notice that I added the WHERE clause to your SQL query and that I deleted the Parameters sections, since you won't be using those... I also found an ExecuteNonQuery statement in your code, not sure how that got in there... Deleted that as well...

Quote

Also one more query...At present the way in which i have designed the form on the right hand side it is only designed for when the client button is clicked, and when i click the contractor button the form will still display boxes related to clients...

This obviously needs to dynamically change depending on which button is clicked from the navigation bar at the top.

So am i right in saying that i need to write seperate a methods that create an instance of a form depending on which button is clicked previously and the manually code the textbox positions and labels etc...?


If you need to retain information from one procedure to the next, it would be best to use class level variables... Let's get this working first and then work on the next issue...

This post has been edited by webwired: 03 January 2009 - 07:53 AM

Was This Post Helpful? 0
  • +
  • -

#9 cobalt-rose   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 13-October 08

Re: Data Binding/Linking my datagrid and showing all data?

Posted 09 February 2009 - 12:22 PM

Hello again, very sorry that i have not replied for a long time but i have had to focus on another project up until now but am ready to concentrate on finishing this one.

I have tried to get the latest code edit working but it still does not display the values in the textboxes when i highlight a row? Is this the only way to to this (using :
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
?
Thank you in advance
C~Rose
Was This Post Helpful? 0
  • +
  • -

#10 cobalt-rose   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 13-October 08

Re: Data Binding/Linking my datagrid and showing all data?

Posted 14 February 2009 - 08:58 AM

Can anybody help?
Was This Post Helpful? 0
  • +
  • -

#11 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6248
  • View blog
  • Posts: 24,016
  • Joined: 23-August 08

Re: Data Binding/Linking my datagrid and showing all data?

Posted 12 November 2012 - 04:19 AM

Closing this topic as it appears to have become a spam magnet for an unscrupulous software company.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1