SQL Command Builder Query values

Question

I have a SQLDataAdapter, in my query i am fetching two fields, ID(PK), Name.

I registered a sql command builder to my data adapter so i don't have to write queries to update table in database.

when I call the da.update() method, sql throws error that cannot insert null into DimensionID, due to this error, i have to select this field too in my dataset, and then i filled this field in grid with appropriate value. then da.update() worked.

Now the problem is that I don't want this field to appear in my gird, when i set its visible property to false, command builder omits this column in query. To cater this issue I have to set the column width to 0, but there is still a tiny line in my grid.

Is there a better way to handle this situation ? except that I manually write queries.

Below is Code to Populate Grid;

 private void frmAttributes_Load(object sender, EventArgs e)
    {
        ds.Tables.Add("Attributes");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        cmd.CommandText = "select ID,Attribute,Code,DimensionID from DimensionAttribute where dimensionid = " + SelectedAttribute;
        da.SelectCommand = cmd;
        cb.DataAdapter = da;

        da.Fill(ds,"Attributes");

        this.dgvAttributes.DataSource = ds.Tables["Attributes"];
        this.dgvAttributes.Columns["ID"].Visible = false;
        this.dgvAttributes.Columns["DimensionID"].Width = 0;







    }

and here is the code behind Updated Button:

 private void btnOk_Click(object sender, EventArgs e)
    {

        if (ds.HasChanges())
        {
            DialogResult d = new DialogResult();
            d = MessageBox.Show("Are you sure you want to save changes to database?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (d == DialogResult.Yes)
            {
                try
                {
                    fillDimensionID();
                  da.UpdateCommand =  cb.GetUpdateCommand();
                    da.InsertCommand = cb.GetInsertCommand();
                    da.DeleteCommand = cb.GetDeleteCommand();

                    da.Update(ds,"Attributes");

                    this.DialogResult = DialogResult.OK;
                    this.Close();




                }
                catch (Exception)
                {

                    throw;
                }


            }
            else
            {
                return;
            }


        }


    }

Changing a columns visibility should have no effect on generated commands by SqlCommandBuilder. You are missing something. — Yiğit Yener, Mar 21 '13 at 07:15
the ID should be a `DataKeyNames` and not a column. you should be able to write something like: this.dgvAttributes.DataKeyNames... — noobob, Mar 21 '13 at 07:18
@yigit Yener, but it is effecting, you can try this code yourself. — alphaprolix, Mar 21 '13 at 08:05
@noobob, what would be the effect of this change ? will this resolve my problem ? — alphaprolix, Mar 21 '13 at 08:06

score 0 · Answer 1 · answered Mar 21 '13 at 08:01

This is a problem with AutoGeneratedCommands. They require every attribute assigned a proper value before update is triggered.

You can adopt either of the following:

Modify the column DimensionID to accept null values; or
Write your own update SP in the database and register it as UpdateCommand with your data adapter.

hope this will show you the path.

SQL Command Builder Query values

1 Answers1