Update SQL Server database from datagridview

Question

I need to update my database on a local PC with SqlDataAdapter. The project is Windows Forms. This method selects information from database and returns a DataTable to be used as data source for a DataGridView :

    static string querySelect = "select * from [customers_Sizes] where ID_customers = @ID_customers";
    private  DataSet recivedData;
    static public SqlDataAdapter adapterSize = new SqlDataAdapter(querySelect, connectionString);
    SqlCommandBuilder cmdBuilder;
    public DataTable clientSizes(int ID)
    {
        DataTable table = new DataTable();
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            SqlCommand commSelect = new SqlCommand(querySelect, conn);
            commSelect.Parameters.AddWithValue("@ID_customers", ID);
            adapterSize = new SqlDataAdapter(commSelect);
            cmdBuilder = new SqlCommandBuilder(adapterSize);
            recivedData = new DataSet();
            conn.Open();
            adapterSize.Fill(recivedData);
            table = recivedData.Tables[0];
        }
        return table;
    }

This code is for update:

public void setNewSizes()
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        adapterSize.UpdateCommand = cmdBuilder.GetUpdateCommand();
        conn.Open();
        adapterSize.Update(receivedData.Tables[0]);
    }
}

In receivedData I have exactly the table need to update. I get an error :

connection string property has not been initialized

So, how can I fix it ? Thanks.

Steve · Accepted Answer · 2017-04-18 08:59:49Z

2

You are redefining the variable adapterSize inside the clientSizes method effectively hiding the global variable defined at the class scope.
In this way the global variable defined at the class scope has no info about the parameter ID added to the internal variable and when you try to use it the exception is raised.

Just remove the declaration of adapterSize and use the global variable

// SqlDataAdapter adapterSize = new SqlDataAdapter(commSelect);
adapterSize = new SqlDataAdapter(commSelect);

Side notes.

It is not clear why you have defined all those globals variable with the static keyword. If there is no compelling reason to do that, I suggest to remove the static keyword and just have class local private variables.
There is no need to initialize the SqlDataAdapter when you declare it and then again inside the clientSizes method, just use the initialization inside the method (of course you need to be sure that nowhere else you use that adapter before calling clientSizes
You are calling the adapterSize.Fill two times. Again there is no need of the second call. The fill of the DataSet is enough to read the data

edited Apr 18, 2017 at 8:59

answered Apr 18, 2017 at 8:53

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Сергей Over a year ago

sorry, but this not help me. need to declare scalar variable "@ID"

Сергей Over a year ago

Thanks for your side notes, but this did not solve the error.

Steve Over a year ago

Try to move the creation of the SqlCommandBuilder after the creation of the SqlDataAdapter. Leave only the declaration of the SqlCommandBuilder at the global scope

Сергей Over a year ago

what do you mean? SqlCommandBuilder is after the SqlDataAdapter.

Steve Over a year ago

Yes, but inside clientSizes you pass an sql command to the adapter and while it has the same commandtext nevertheless is a different command from the previous one initially built by the adapter. However, after a bit of test the position of the SqlCommandBuilder is not the problem. Are you sure that you don't change in any way the Adapter between the clientSizes and SetSizes calls

|

Collectives™ on Stack Overflow

Update SQL Server database from datagridview

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related