Problem with make relations between two tables

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 875 Views - Last Post: 30 September 2011 - 07:39 AM Rate Topic: -----

#1 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 289
  • Joined: 22-September 10

Problem with make relations between two tables

Posted 21 September 2011 - 12:03 PM

I have 3 tables:
tb_plane          tb_attributes        tb_as_plane_attributes
plane_id          attribute_id         plane_attribute_id
plane_name        attribute_name       FK_plane_id
                                       FK_attribute_id
                                       value



What I want to is to relate attributes to plane and set a value for each attribute. I did make a combo which i populate with all plane_name's:
private void PopulatePlainComboBox()
        {
            try
            {
            string ConnectionString = conSettings.ConnectionString;

                DataSet dataSet = new DataSet();
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand())
                    {
                        command.Connection = connection;
                        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                        {
                            command.CommandText = ("SELECT * FROM tb_plane");
                            dataAdapter.Fill(dataSet, "tb_plane");

                            command.CommandText = ("SELECT plane_id, plane_name FROM tb_plane");
                            dataAdapter.Fill(dataSet, "tb_plane");

                            CboPlains.DataSource = dataSet.Tables["tb_plane"];
                            CboPlains.DisplayMember = "plane_name";
                            CboPlains.ValueMember = "plane_id";
                        }
                    }
                    connection.Close();
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                string str;
                str = "Source:" + ex.Source;
                str += "\n" + "Message:" + ex.Message;
                MessageBox.Show(str, "Database Exception");
            }
            catch (System.Exception ex)
            {
                string str;
                str = "Source:" + ex.Source;
                str += "\n" + "Message:" + ex.Message;
                MessageBox.Show(str, "Generic Exception");
            }
            finally
            {
            }
        }



Now I want to populate a datagrid with all attributes by attribute_name's from tb_attributes. One coulmn for all attribute_name's and second for Column - Value. I want to set value to this attributes I want to relate with plane's and by clicking button to make relations and save to table tb_as_plane_attributes. rest attributes where I don't put values are not save in assocation table.

I try to make this work but I have problems. I try to implement datagrid for that:
private void PopulateGridByAttributes()
        {
            try
            {
            string ConnectionString = conSettings.ConnectionString;

                DataSet dataSet = new DataSet();
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand())
                    {
                        command.Connection = connection;
                        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                        {
                            command.CommandText = ("SELECT attribute_id, attribute_name FROM tb_attributes");
                            dataAdapter.Fill(dataSet, "tb_attributes");

                            GrdAllAttributes.Columns.Add("attribute_name", "name of attribute");
                            GrdAllAttributes.Columns.Add("dd", "Value");
                            int row = dataSet.Tables["tb_attributes"].Rows.Count - 1;
                            for (int r = 0; r <= row; r++)
                            {
                                GrdAllAttributes.Rows.Add();
                                GrdAllAttributes.Rows[r].Cells[0].Value = dataSet.Tables["tb_Atributtes"].Rows[r].ItemArray[1];   //attribute_name
                            }
                        }
                    }
                    connection.Close();
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                string str;
                str = "Source:" + ex.Source;
                str += "\n" + "Message:" + ex.Message;
                MessageBox.Show(str, "Database Exception");
            }
            catch (System.Exception ex)
            {
                string str;
                str = "Source:" + ex.Source;
                str += "\n" + "Message:" + ex.Message;
                MessageBox.Show(str, "Generic Exception");
            }
            finally
            {
            }
        }



How to do it?

This post has been edited by nighttrain: 21 September 2011 - 12:06 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Problem with make relations between two tables

#2 programmer_finchy  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 21
  • Joined: 25-November 10

Re: Problem with make relations between two tables

Posted 21 September 2011 - 09:39 PM

To Populate your datagridview do this:
     yourDataGridName.DataSource = PopulateGridByAttributes();



and in your PopulateGridByAttributes() just get the data for your DataGrid, like below:
private Datatable PopulateGridByAttributes()
	        {
	            try
	            {
	            string ConnectionString = conSettings.ConnectionString;
	 	                Datatable dt= new datatable();
	                using (SqlConnection connection = new SqlConnection(ConnectionString))
	                {
	                    connection.Open();
	                    using (SqlCommand command = new SqlCommand())
	                    {
	                        command.Connection = connection;
	                        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
	                        {
	                            command.CommandText = ("SELECT attribute_id, attribute_name FROM tb_attributes");
	                            dataAdapter.Fill(dt, "tb_attributes");
	                            return dt;
	                        }
	                    }
	                    
	                }
	            }
[/close]
 Also,always put your connection close () in finally statement;
[close]	            
	            finally
	            {
			connection.Close();
	            }
	        }


Was This Post Helpful? 0
  • +
  • -

#3 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 289
  • Joined: 22-September 10

Re: Problem with make relations between two tables

Posted 21 September 2011 - 11:18 PM

Thanks for the answer but i already populate grid - programataically by column attribute_name and column value. My question was how to when i fill rows of Value Column by some values send a result to association table by click for example button. This populate grid is not good i count on help with that.
Was This Post Helpful? 0
  • +
  • -

#4 programmer_finchy  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 21
  • Joined: 25-November 10

Re: Problem with make relations between two tables

Posted 21 September 2011 - 11:54 PM

hope this help you
http://www.switchont...w-to-a-database
Was This Post Helpful? 0
  • +
  • -

#5 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 289
  • Joined: 22-September 10

Re: Problem with make relations between two tables

Posted 24 September 2011 - 01:24 PM

i still stack on it it will be more simple when this wilo only be relarion without this value then i will make two datatables and make insert to third table but there is also a value cooumn in third table i dont know how do that using a datagrid. Please mayby some could help amd write some code.

If someone could help i add my project here:
http://dl.dropbox.co.../forum/proj.rar

and my database script for sql server:
http://dl.dropbox.co...forum/plane.rar

login is : a a

This post has been edited by nighttrain: 25 September 2011 - 01:58 AM

Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5746
  • View blog
  • Posts: 12,555
  • Joined: 16-October 07

Re: Problem with make relations between two tables

Posted 26 September 2011 - 04:09 AM

Try using the tools at your disposal.

Visual Studio will create typed datasets for you with just a drag and drop. Created a DataSet in your project. Open a server connection window, and drag the tables into the empty dataset. It should create not just the DataTable structure but also the relations and the basic DataAdapters.
Was This Post Helpful? 0
  • +
  • -

#7 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 289
  • Joined: 22-September 10

Re: Problem with make relations between two tables

Posted 26 September 2011 - 12:58 PM

Yes but i want to make it programatically not graphical dataset.

I did combobox populate by all plane_name's from tb_plane so the user could select a plane (so we have now plane_id need to association table FK_plane_id):

private void PopulatePlainComboBox()
        {
            try
            {
            string ConnectionString = conSettings.ConnectionString;

                DataSet dataSet = new DataSet();
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand())
                    {
                        command.Connection = connection;
                        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                        {
                            command.CommandText = ("SELECT * FROM tb_plane");
                            dataAdapter.Fill(dataSet, "tb_Plain");

                            command.CommandText = ("SELECT plane_id, plane_name FROM tb_plane");
                            dataAdapter.Fill(dataSet, "tb_plane");

                            CboPlains.DataSource = dataSet.Tables["tb_plane"];
                            CboPlains.DisplayMember = "plane_name";
                            CboPlains.ValueMember = "plane_id";
                        }
                    }
                    connection.Close();
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                ..
            }
            catch (System.Exception ex)
            {
                ..
            }
            finally
            {
                ..
            }
        }



And i try to prepare correctly this datagridview (that's what i do for now):
private void PopulateGridByAttributes()
        {
            try
            {
            string ConnectionString = conSettings.ConnectionString;

                DataSet dataSet = new DataSet();
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand())
                    {
                        command.Connection = connection;
                        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                        {
                            command.CommandText = ("SELECT attribute_id, attribute_name, attribute_color FROM tb_attributes");
                            dataAdapter.Fill(dataSet, "tb_attributes");

                            GrdAllAttributes.Columns.Add("attribute_name", "attribute_name");
                            GrdAllAttributes.Columns.Add("Value", "Value");
                            int row = dataSet.Tables["tb_attributes"].Rows.Count - 1;
                            for (int r = 0; r <= row; r++)
                            {
                                GrdAllAttributes.Rows.Add();
                                GrdAllAttributes.Rows[r].Cells[0].Value = dataSet.Tables["tb_attributes"].Rows[r].ItemArray[1];
                            }
                        }
                    }
                    connection.Close();
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                ..
            }
            catch (System.Exception ex)
            {
                ..
            }
            finally
            {
                ..
            }
        }



It's look like this:
Posted Image

I want to user can select some plane - done. And have all attributes in datagridview, and column value so he can write values for attributes the user want to and the click button to insert it to association table.

I don;t know how dto do that, please of help.
Was This Post Helpful? 0
  • +
  • -

#8 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5746
  • View blog
  • Posts: 12,555
  • Joined: 16-October 07

Re: Problem with make relations between two tables

Posted 26 September 2011 - 03:41 PM

Very well. Write less code, not more. If you're using dynamic data adapters, don't open the connection; it will do that. In fact, you need none of those usings on a fraction of those creates.
private SqlDataAdapter CreateDataAdapter(string sql) {
	return new SqlDataAdapter(sql, conSettings.ConnectionString);
}

private DataSet GetDataSet(string tableName, string sql) {
	DataSet dataSet = new DataSet();
	CreateDataAdapter(selectCommandText).Fill(dataSet, tableName);
	return dataSet;
}

private DataTable GetDataTable(string sql) {
	DataTable dt = new DataTable();
	CreateDataAdapter(selectCommandText).Fill(dt);
	return dt;
}

private void PopulatePlainComboBox() {
	try {
		CboPlains.DataSource = GetDataTable("SELECT plane_id, plane_name FROM tb_plane");
		CboPlains.DisplayMember = "plane_name";
		CboPlains.ValueMember = "plane_id";
	} catch (System.Exception ex) {
		string str = "Source:" + ex.Source + "\n" + "Message:" + ex.Message;
		MessageBox.Show(str, "Generic Exception");
	}
}

private void PopulateGridByAttributes() {
	DataTable dataTable = GetDataTable("SELECT attribute_id, attribute_name FROM tb_attributes");
	// it's unclear what kind of object this is
	GrdAllAttributes.Columns.Add("attribute_name", "name of attribute");
	GrdAllAttributes.Columns.Add("dd", "Value");
	// int row = dataSet.Tables["tb_attributes"].Rows.Count - 1;
	// for (int r = 0; r <= row; r++) {
	foreach(DataRow row in dataTable.Rows) {
		GrdAllAttributes.Rows.Add(); // add what?  This is probably wrong
		//GrdAllAttributes.Rows[r].Cells[0].Value = dataSet.Tables["tb_Atributtes"].Rows[r].ItemArray[1]; 
		object value = row.ItemArray[1];   
		GrdAllAttributes.Rows[r].Cells[0].Value = value;   
	}
}


Was This Post Helpful? 0
  • +
  • -

#9 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 289
  • Joined: 22-September 10

Re: Problem with make relations between two tables

Posted 27 September 2011 - 01:23 AM

Thanks for the reply.
I modify the code:
//GrdAllAttributes.Rows.Add();   // I did comment that
//GrdAllAttributes.Rows[r].Cells[0].Value = value; // and here's not now 'r' so i put 0 :
GrdAllAttributes.Rows[0].Cells[0].Value = value;


private void PopulateGridByAttributes()
        {
            DataTable dataTable = GetDataTable("SELECT attribute_id, attribute_name FROM tb_attributes");
            // it's unclear what kind of object this is
            GrdAllAttributes.Columns.Add("attribute_name", "name of attribute");
            GrdAllAttributes.Columns.Add("dd", "Value");
            // int row = dataSet.Tables["tb_attributes"].Rows.Count - 1;
            // for (int r = 0; r <= row; r++) {
            foreach (DataRow row in dataTable.Rows)
            {
                //GrdAllAttributes.Rows.Add(); // add what?  This is probably wrong
                //GrdAllAttributes.Rows[r].Cells[0].Value = dataSet.Tables["tb_Atributtes"].Rows[r].ItemArray[1]; 
                object value = row.ItemArray[1];
                GrdAllAttributes.Rows[0].Cells[0].Value = value;
            }
        }



But now is the result, something is wrong:
Posted Image

This post has been edited by nighttrain: 27 September 2011 - 01:33 AM

Was This Post Helpful? 0
  • +
  • -

#10 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5746
  • View blog
  • Posts: 12,555
  • Joined: 16-October 07

Re: Problem with make relations between two tables

Posted 27 September 2011 - 04:07 AM

You're always adding to the same row?
GrdAllAttributes.Rows[0].Cells[0].Value = value;



I'm now guessing we're using this control? http://msdn.microsof...dview.rows.aspx

If so, perhaps:
foreach (DataRow row in dataTable.Rows) {
	GrdAllAttributes.Rows.Add(row[1]);
}


Was This Post Helpful? 1
  • +
  • -

#11 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 289
  • Joined: 22-September 10

Re: Problem with make relations between two tables

Posted 27 September 2011 - 07:36 AM

Yes, we using Datagridview

Ok, so we got this code:
private void PopulateGridByAttributes()
        {
            DataTable dataTable = GetDataTable("SELECT attribute_id, attribute_name FROM tb_attributes");
            GrdAllAttributes.Columns.Add("attribute_name", "name of attribute");
            GrdAllAttributes.Columns.Add("dd", "Value");

            foreach (DataRow row in dataTable.Rows)
            {
                GrdAllAttributes.Rows.Add(row[1]);
            }
        }



And it look likes good for now, so if now user select a plane from combo and write some values to attributes like on picture, how to do the button click event to insert to association table (tb_plane_attributes)?
Posted Image

This post has been edited by nighttrain: 27 September 2011 - 07:38 AM

Was This Post Helpful? 0
  • +
  • -

#12 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5746
  • View blog
  • Posts: 12,555
  • Joined: 16-October 07

Re: Problem with make relations between two tables

Posted 27 September 2011 - 08:34 AM

You would have to read the values not contained in the object and insert them into the database.
Was This Post Helpful? 0
  • +
  • -

#13 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 289
  • Joined: 22-September 10

Re: Problem with make relations between two tables

Posted 27 September 2011 - 09:15 AM

baavgai so the code above it's all good? Now it's only depends to create button click event with insert statment, i am right? If yes from where i have to read?
Was This Post Helpful? 0
  • +
  • -

#14 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5746
  • View blog
  • Posts: 12,555
  • Joined: 16-October 07

Re: Problem with make relations between two tables

Posted 27 September 2011 - 09:40 AM

All the elements needed are in front of you, yes.

You wrote the values into GrdAllAttributes.Rows. Perhaps check GrdAllAttributes.Rows to see values is contains after the user has entered data.
Was This Post Helpful? 1
  • +
  • -

#15 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 289
  • Joined: 22-September 10

Re: Problem with make relations between two tables

Posted 27 September 2011 - 12:34 PM

I am working on it, but I got problems. First I'll show the code for now:
private void btnOK_Click(object sender, EventArgs e)
        {
            string ConnectionString = conSettings.ConnectionString;
            string FK_plane_id = CboPlains.SelectedValue.ToString();
            
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand())
                    {
                        command.Connection = connection;
                        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                        {
                            foreach (DataGridViewRow row in GrdAllAttributes.Rows)
                            {
                                command.CommandText = "INSERT INTO tb_plane_attributes(FK_plane_id, FK_attribute_id, value) VALUES(@FK_plane_id, @FK_attribute_id, @value)";

                                command.Parameters.AddWithValue("@FK_plane_id", FK_plane_id);
                                command.Parameters.Add("@FK_attribute_id", SqlDbType.Int, 2, "@FK_attribute_id").Value = row.Cells["@FK_attribute_id"].Value;
                                command.Parameters.Add("@value", SqlDbType.Int, 3, "@value").Value = row.Cells["dd"].Value;

                                command.ExecuteNonQuery();
                                command.Parameters.Clear();
                            }
                        }
                    }
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                string str;
                str = "Source:" + ex.Source;
                str += "\n" + "Message:" + ex.Message;
                MessageBox.Show(str, "Database Exception");
            }
            catch (System.Exception ex)
            {
                string str;
                str = "Source:" + ex.Source;
                str += "\n" + "Message:" + ex.Message;
                MessageBox.Show(str, "Generic Exception");
            }
            finally
            {
            }
        }



So first problem is i need attribute_id value, but in datagrid I have only attribute_name - there's not attribute_id
command.Parameters.Add("@FK_attribute_id", SqlDbType.Int, 2, "@FK_attribute_id").Value = row.Cells["@FK_attribute_id"].Value;



Second problem is I got exception error:

Source:.NET SQLClient Data Provider
Message: The parametrized query '(@FK_plane_id nvarachar(1), @FK_attribute_id nvarchar(1), @value in' expects the parameter '@value', which was not supplied.


This post has been edited by nighttrain: 27 September 2011 - 12:35 PM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2