Update - help putting two bits of code together

I'm trying to update a table (learning) via code behind.

I've used this method of connection to a database before:

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        string SQL = string.Format("SELECT * FROM tbl_StockControl_GroupItems;");
        System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();

And I've since found the following code to update a table which (to me) looks simple and clean:

        DataTable dt = ds.Tables["tbl_StockControl_GroupItems"];

        // Insert 
        DataRow dr = dt.NewRow();
        dr[0] = 4;
        dr[1] = "New value";
        dr[2] = "New value";
        dr[3] = 3;
        dr[4] = 3000;
        dr[5] = DateTime.Parse("8/14/1999");
        dt.Rows.Add(dr);
        da.Update(ds, "tbl_StockControl_GroupItems");
        ds.AcceptChanges();

... And I'd like to join the two.

Thing is, I realise it's not complete, and I need to tweak the syntax a little to get it working.

I'd really appreciate some help.

Many thanks
Rich

Re: Update - help putting two bits of code together

Hi,

Where exactly are you facing the problem.

You have prepared the connection object well.

The only part missing between the two code is fetching the data in the dataset using that query and connection. Correct me if I am getting it wrong.

You can use this code to fetch the data.

SqlDataAdapter da = new SqlDataAdapter(SQL, conn);

        DataSet ds = new DataSet();
        da.Fill(ds);

Re: Update - help putting two bits of code together

Thanks. I've added your code:

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        string SQL = string.Format("SELECT * FROM tbl_StockControl_GroupItems;");
        System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();

        SqlDataAdapter da = new SqlDataAdapter(SQL, conn);
        DataSet ds = new DataSet();
        da.Fill(ds);

        DataTable dt = ds.Tables["tbl_StockControl_GroupItems"];

        // Insert
        DataRow dr = dt.NewRow();

        dt.Rows.Add(dr);
        da.Update(ds, "tbl_StockControl_GroupItems");
        ds.AcceptChanges();

... but I get an error: DataRow dr = dt.NewRow(); -

Object reference not set to an instance of an object

Re: Update - help putting two bits of code together

Hi,

Just change your line

DataTable dt = ds.Tables["tbl_StockControl_GroupItems"];

 DataTable dt = ds.Tables[0];

Re: Update - help putting two bits of code together

Perfect! Many thanks.