SQL row update issue.

[forensic]

Hi all,

I'm currently make my final year project program at university, and have been puzzled by this problem for sometime now.

The issues I am having is that when I try to update a value in a specific column of a specif row. The program has a paddy and decides to fall over at *.

I am notified with the following message;

InvalidOperationException was unhandled

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

After looking up this error message, I am no further to solving my problem. Can anyone please spot the issue please. Ive provided my code below;

private void updateCaseDB(int rowID, int newCategory)
        {
            con = new System.Data.SqlClient.SqlConnection();
            dsCaseUpdate = new DataSet();

            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=" + CaseDBLocation + ";Integrated Security=True;Connect Timeout=30;User Instance=True";

            con.Open();

            string caseUpdateQuery = "SELECT * From CaseDBTemp";
            daCaseUpdate = new System.Data.SqlClient.SqlDataAdapter(caseUpdateQuery, con);
            daCaseUpdate.Fill(dsCaseUpdate, "CaseDBUpdate");
            con.Close();
            
            System.Data.SqlClient.SqlCommandBuilder cbupdate;
            cbupdate = new System.Data.SqlClient.SqlCommandBuilder(daCaseUpdate);
            
            System.Data.DataRow dRow = dsCaseUpdate.Tables["CaseDBUpdate"].Rows[rowID];

            dRow[7] = newCategory;

            MessageBox.Show("row: "+ rowID + " - New Category Value: " + newCategory);

            daCaseUpdate.Update(dsCaseUpdate, "CaseDBUpdate");//*this is the problem line*//
            
            MessageBox.Show("row updated");
        }

[Munnazz]

HI forensic,
I think it is better to use an update command to update your dataset.
Example:
UPDATE (table name) SET (column name)= 1,
WHERE (primary key)= (primarykeyid)

[prvnkmr194]

You can't able to update the record if connection is closed.
remove the line number 13 that is

con.Close();

and paste this line at the last line, i mean to say just before } after line number 26

I hope it will help you, Best of luck

[forensic]

Thank you Munnazz.

Your solution of using an UPDATE statement worked first time.

[Munnazz]

thats ok...... Had it completed without any problem