[baavgai]

I'm guessing that for a particular insert, there was no value in row.Cells["dd"].Value. So, how to you handle nulls?

Also, as stated before "using (SqlDataAdapter" is useless, because a DataAdapter opens and closes it's own connection. It seems particularly useless here, because you aren't using it at all.

Don't define that command inside the insert. There's no reason to keep recreating it:

using (SqlCommand command = new SqlCommand())  {
	command.CommandText = "INSERT INTO tb_plane_attributes(FK_plane_id, FK_attribute_id, value) VALUES(@FK_plane_id, @FK_attribute_id, @value)";

	// ar you sure, isn't this int?
	command.Parameters.AddWithValue("@FK_plane_id", FK_plane_id); 
	command.Parameters.Add("@FK_attribute_id", SqlDbType.Int);
	command.Parameters.Add("@value", SqlDbType.Int;
	foreach (DataGridViewRow row in GrdAllAttributes.Rows) {
		command.Parameters["@FK_attribute_id"]= row.Cells["@FK_attribute_id"].Value;
		command.Parameters["@value"] = row.Cells["dd"].Value;
		command.ExecuteNonQuery();
	}
}

[nighttrain]

Ok hmm so I don't need to use SqlDataAdapter here like u said, and I have to check if user stay someof Value row I think
We could use something like this to check but it's requires a new dataset which we don't want to.

DataRow Row = null;
int numeberOfRows = dataset.Tables[0].Rows.Count;
int i = 0;
for (i = numeberOfRows - 1; i >= 0; i += -1) {
	Row = dataset.Tables[0].Rows(i);
	if (Row["Value"]) == 0) 
        {
	Row.Delete();
	}
}

And I need an attribute_id to INSERT statment (in GrdAttributes are only attribute_names) how should i equal each attribute_name to attribute_id?

I really wonder how should it be, ai am so confused but I feel its not so hard...

This post has been edited by nighttrain: 29 September 2011 - 11:12 AM

[baavgai]

You don't need to delete rows from the data grid, just deal with them properly when you try to insert.

One way would just be to ignore them:

foreach (DataGridViewRow row in GrdAllAttributes.Rows) {
	object value = row.Cells["dd"].Value;
	if (value!=null) {
		command.Parameters["@FK_attribute_id"]= row.Cells["@FK_attribute_id"].Value;
		command.Parameters["@value"] = value;
		command.ExecuteNonQuery();
	}
}

[nighttrain]

I don't know is that the way is good, but anyway I got an error

private void btnOK_Click(object sender, EventArgs e)
        {
            SqlConnection connection = new SqlConnection(GetConnectionString());
            SqlCommand command = new SqlCommand();
            string FK_plane_id = CboPlains.SelectedValue.ToString();
            command = new SqlCommand("INSERT INTO tb_plane_attributes(FK_plane_id, FK_attribute_id, value) VALUES(@FK_plane_id, @FK_attribute_id, @value)", connection);
            command.Parameters.Add("@FK_plane_id", SqlDbType.Int);
            command.Parameters.Add("@FK_attribute_id", SqlDbType.Int);
            command.Parameters.Add("@value", SqlDbType.Int);
            
            try
            {

                connection.Open();

                foreach (DataGridViewRow row in GrdAllAttributes.Rows)
                {
                    object value = row.Cells["dd"].Value;
                    if (value != null)
                    {                         
                        command.Parameters["@FK_plane_id"].Value = FK_plane_id;
                        command.Parameters["@FK_attribute_id"].Value = row.Cells["attribute_id"].Value;
                        command.Parameters["@value"].Value = value;
                        command.ExecuteNonQuery();                      
                    }
                }
            }
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
            {
                connection.Close();
            }
        }

an error:

The parameterized query '(@FK_plane_id int,@FK_attribute_id int,@value int)INSERT INTO tb' expects the parameter '@FK_attribute_id', which was not supplied.

[baavgai]

And what does that mean? Probably that row.Cells["attribute_id"].Value doesn't contain what you think it does. You should be looking at all three values you're passing.

[nighttrain]

I did it working baavgai !!

I add to: PopulateGridByAttributes() this line:

GrdAllAttributes.Columns.Add("attribute_id", "Invisible column");

next i modify this:

GrdAllAttributes.Rows.Add(row[0]);

to this:

GrdAllAttributes.Rows.Add(row[0],row[1]);

So this code works fine :

private void btnOK_Click(object sender, EventArgs e)
        {
            SqlConnection connection = new SqlConnection(GetConnectionString());
            SqlCommand command = new SqlCommand();
            string FK_plane_id = CboPlains.SelectedValue.ToString();
            command = new SqlCommand("INSERT INTO tb_plane_attributes(FK_plane_id, FK_attribute_id, value) VALUES(@FK_plane_id, @FK_attribute_id, @value)", connection);
            command.Parameters.Add("@FK_plane_id", SqlDbType.Int);
            command.Parameters.Add("@FK_attribute_id", SqlDbType.Int);
            command.Parameters.Add("@value", SqlDbType.Decimal);
            
            try
            {
                connection.Open();

                foreach (DataGridViewRow row in GrdAllAttributes.Rows)
                {
                    object value = row.Cells["dd"].Value;
                    if (value != null)
                    {                         
                        command.Parameters["@FK_plane_id"].Value = FK_plane_id;
                        command.Parameters["@FK_attribute_id"].Value = row.Cells["attribute_id"].Value;
                        command.Parameters["@value"].Value = value;
                        command.ExecuteNonQuery();                      
                    }
                }
            }
            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
            {
                connection.Close();
            }

I just needed attribute_id in datagrid, so i add column for attribute_id and i do visible on false to this column
And it's working now.

I got questions:

1. Is that code btnOK_click could be more culture code I mean better write code or it's good?
2. I want for now validate values which user will write in column Value, I want the user can only write integers and decimals(10,2) how should i do that? I found this code which works but it only accept integers values when i write for example 10,2 it's block too cause there's ',' :

private void GrdAllAttributes_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
        {
            DataGridViewTextBoxCell cell = GrdAllAttributes[e.ColumnIndex, 2] as DataGridViewTextBoxCell;

            if (cell != null)
            {
                char[] chars = e.FormattedValue.ToString().ToCharArray();
                foreach (char c in chars)
                {
                    if (char.IsDigit(c) == false)
                    {
                        MessageBox.Show("You have to enter digits only");

                        e.Cancel = true;
                        break;
                    }
                }
            }
        }

This post has been edited by nighttrain: 30 September 2011 - 07:41 AM