Dynamic SQL issue - C# Forms (Datagridview)

Question

I am currently trying to update my DataGridView to my database. I want to be able to update it with the enter key. But I am getting this error:

"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

Here is a picture of the code(as well its below in code snippet) Some of the code

Here is how the Datagridview is loaded:

        private void RampBoardLoader()
    {
        SqlConnection connection = new SqlConnection(ConnectionLoader.ConnectionString("Threshold"));
        connection.Open();
        SqlCommand selectRampBoard = new SqlCommand("Select_Ramp_Data", connection);
        selectRampBoard.CommandType = CommandType.StoredProcedure;
        selectRampBoard.Parameters.AddWithValue("@DateID", dateTimeRamp.Value.Date);
        dt = new DataTable();
        dataAdapter = new SqlDataAdapter(selectRampBoard);
        dset = new DataSet();
        dataAdapter.Fill(dset, "Ramp_Board");
        dgvRampBoard.DataSource = dset.Tables[0];
        connection.Close();

    }

Here is where I'm trying to update the datagridview:

        private void dgvRampBoard_KeyUp(object sender, KeyEventArgs e)
    {
        if (e.KeyCode == Keys.Enter)
        {
            e.Handled = true;

            scb = new SqlCommandBuilder(dataAdapter);
            dgvRampBoard.EndEdit();
            this.dataAdapter.Update(dset, "Ramp_Board");

        }

    }

I've been looking around to try to find the answer to this. Every post I see everyone says that you need to make sure your table has a primary key. My table does have a primary key. It has 2 primary keys. "Flight Number" and "DateID". I'm not sure if it's because I have 2 primary keys, if that's why I'm getting this issue.

Attached is image of Database of my stored procedure. You can also see the columns, I have 2 primary keys Stored Procedure

Yep; you're using a stored procedure to deliver your data - the command builder isn't going to be able to work out how to do and UPDATE from that. Configure the update manually (set UpdateCommand yourself). It's quite boring/tedious doing so; you should make a strongly typed dataset and then VS will write this code for you — Caius Jard, Apr 23 '22 at 06:45

score 0 · Answer 1 · answered Apr 23 '22 at 06:54

How to make life easy:

(Note; this only works in a net framework project; the designer has bugs in net core and Microsoft have disabled it. There are tricks you can pull if you want to work in core, but they basically entail using a net framework project to do the editing and a netcore to do the running)

add a dataset type file to your project
open it, right click its surface, choose add tableadapter
Set up your connection
Choose to use stored procedures to get your data
Choose your sprocs (I assume you have one for updating ?)
Name the Fill/GetData methods appropriately (FillByDateId ?)
Save the dataset
Go to a new form
Open DataSources window on View menu, other windows
Drag the node representing your table, onto your form. You should see a grid, binding source, dataset and navigator appear

That's it, you should be able to just run the app now, enter some date Id in the box, hit fill, change the data, hit save..

You can find the code the designer write for you in the normal code behind and in the FormXx.Designer.cs files if you're curious how it works. A tableadaptermanager will call tableadapter.Update(datatable) on "parent, child" order for every table that needs updating. Table adapter's Update uses the InsertCommand, UpdateCommand and DeleteCommand as appropriate to persist each row change according to what the row state is

Dynamic SQL issue - C# Forms (Datagridview)

1 Answers1