The ConnectionString property has not been initialized. with using

Question

I have a using statement to initialize adapter and Fill(). I want to update adapter in different function but it gives me error since connection closed. How to do this with using or should I not use using in this case?

SqlDataAdapter adt;
DataTable dt;

private void myTestAdapterUpdate()
{
    using (SqlConnection connection = new SqlConnection(conString))
    {
        dt = new DataTable();

        string query = "SELECT * FROM dbo.Vendor_GUI_Test_Data";
        SqlCommand cmd = new SqlCommand(query, connection);
        connection.Open();
        adt = new SqlDataAdapter(cmd);
        adt.Fill(dt);

        DataRow toInsert = dt.NewRow();
        toInsert[0] = "MYTESTVENDOR2";
        toInsert[1] = "4";
        toInsert[2] = "89";
        toInsert[3] = "89";

        dt.Rows.InsertAt(toInsert, 0);
        DataRow r = dt.Rows[3];
        r.Delete();

        SqlCommandBuilder builder = new SqlCommandBuilder(adt);
    }
}

private void applyTestAdapterUpdate()
{
    adt.Update(dt); //error here
}

Can you tell us what line this error happens? Do you have a stack trace or exception information? — Trevor, Mar 04 '21 at 20:57
The reason for this error is because you're disposing the connection to the `SqlDataAdapter`. Therefore when calling `adt.Update(dt)` there's no valid connection anymore. To fix this issue, you would need to create another connection and then assign this to the `adt` and it should be fine. — Trevor, Mar 04 '21 at 21:03
@Codexer yes but I don't get how to not dispose since using does it automatically. I need to call Update in another function. — Lightsout, Mar 04 '21 at 21:05
@bakalolo please read my comment again. You can wrap the `adt.Update(dt);` in a using statement that creates a new connection and then assign it. — Trevor, Mar 04 '21 at 21:07
@Codexer Could you write a answer? I'm not sure hwo to assign new connection to adt without creating a new SqlDataAdapter. — Lightsout, Mar 04 '21 at 21:11
@bakalolo please see https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldataadapter.-ctor?view=dotnet-plat-ext-5.0 in which your case seems to be better for what you want to do. I appologize, I thought you could assign a connection to a property within the current instance of the adapter, but it seems you can't. My assertions are still correct though, the error is because your adapter doesn't have an active connection. — Trevor, Mar 04 '21 at 21:12
The `SqlDataAdapter` takes care of a lot of things that you are already doing, so perhaps you don't need it. You can use the [`DataTable.Load`](https://stackoverflow.com/a/23221607/2791540) method instead (in place of `SqlDataAdapter.Fill`). — John Wu, Mar 04 '21 at 21:33
When yo u exit a using statement the object of the using is automatically disposed. So either you need to move more code inside the using block or remove the using block. I wold put the SQL Connection in a Try/Catch instead of a using block. — jdweng, Mar 04 '21 at 22:24

score 0 · Answer 1 · answered Mar 05 '21 at 09:58

0

you must set data adapter insert and delete command from command builder then set adt.InsertCommand.Connection and adt.DeleteCommand.Connection to new sqlconnection before update

updating with data adapters

answered Mar 05 '21 at 09:58

gunhal

11
2

The ConnectionString property has not been initialized. with using

1 Answers1