Question The ConnectionString property has not been initialized

[andrewmanuja]

Hi All,
I am running a WindowForm application and got a dataGridView where I add a new column(type of boolean) into it, during the Form Load event to select the data rows.
The respective code is as follows;

//Add column for selection
                table.Columns.Add("Select Item", typeof(bool));

                //Bind to dataGrid
                dataGridViewOrderDetails.DataSource = table;
                dataGridViewOrderDetails.Columns["Select Item"].DisplayIndex = 0;

I have create the connection string in the "App.Config file and calling the connection as follows;

SqlConnection con = Connection.GetConnection();

I have also created a DataTable() within the public class of the window form.

private DataTable table = new DataTable();

Basically, my DataGridView will have multiple data records and user will select a few and once hit the "Transfer" button, few table in my SQL Server need to be updated accordingly.
The code is as follows;

  table.AcceptChanges();
            //Mark selected rows ready for insert
            foreach (DataRow row in table.Rows)
            {
                if (row["Select Item"] as bool? == true)
                {
                    row.SetAdded();
                }
            }

            //Save Data to Destination

            //Below section add the respective drug quantities to the respective cost centre drug available quantity
        
            using (var command = new SqlCommand(@"UPDATE tbl_CostCentreDrugStock SET tbl_CostCentreDrugStock.availableQuantity += @aissueQuantity
                                                            FROM tbl_CostCentreDrugStock
                                                            INNER JOIN tbl_CostCentreOrderStatus
                                                            ON tbl_CostCentreDrugStock.costCentreID = tbl_CostCentreOrderStatus.costCentreID
                                                            WHERE tbl_CostCentreOrderStatus.ccOrderID =@accOrderID AND tbl_CostCentreDrugStock.drugID =@adrugID ", con))

            using (var adapter = new SqlDataAdapter { InsertCommand = command })
            {
                command.Parameters.Add("@adrugID", SqlDbType.Int, 0, "drugID");
                command.Parameters.Add("@accOrderID", SqlDbType.Int, 0, "ccOrderID");
                command.Parameters.Add("@aissueQuantity", SqlDbType.Int, 0, "issueQuantity");

                try
                {
                    adapter.Update(table);
                    MessageBox.Show("Data Saved");
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }
                command.Parameters.Clear();
            }
            
            //Below section deduct the respective drug quantities from the master drug table
            using (var command = new SqlCommand(@"UPDATE tbl_DrugQuantity SET availableQuantity -= @aissueQuantity
                                                            WHERE drugID = @adrugID", con))

            using (var adapter = new SqlDataAdapter { InsertCommand = command })
            {
                command.Parameters.Add("@adrugID", SqlDbType.Int, 0, "drugID");
                command.Parameters.Add("@aissueQuantity", SqlDbType.Int, 0, "issueQuantity");

                try
                {
                    adapter.Update(table);
                    MessageBox.Show("Data Saved");
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }
                command.Parameters.Clear();
            }

I may be doing a silly mistake here. However, when I run the program and hit the Transfer button, I am getting the error, "The ConnectionString property has not been initialized".

Appreciate your valuable feedback please.

Kind regards,

Andrew

 

[jmcilhinney]

Where exactly do you think that you are setting that ConnectionString property? Have you put a breakpoint on that line to see whether it is hit and behaves as expected?

 

[NoUserHere]

How do you generally initialise an object?

 

[andrewmanuja]

Hi,

I am using similar steps in the Form Load event and it's loading the data as intended.

Further, I added a breakpoint and its working fine.

The only difference from the Form_Load event is I am updating multiple data tables in the SQL Server using a "DataTable()".

Any valuable suggestions please?

Thank you.

Andrew

 

[jmcilhinney]

Any valuable suggestions please?

Yeah, put a breakpoint on the line that you think sets the ConnectionString property and then debug your code. If that sounds familiar, it's because I already said that and you apparently chose to ignore it. If you're getting that error message it's because you haven't set that property. There's no magic solution. You just need to set that property.

 

[Skydiver]

This:

SqlConnection con = Connection.GetConnection();

suggests that con is a local variable that you only actually set in your Form_Load() method. I am guessing that the con that you are using in that last block of code you had in post #1, is a con that is in a different scope than your Form_Load(), and that con is currently null. As suggested above, set breakpoints and step through your code (instead of just pressing Ctrl-F5 to run). See what the value of con is for each of those SqlCommand that you are passing into the constructors.

 

[andrewmanuja]

Hi Skydiver,
Thanks for your comment.
It was a bit of a strange to me, that I have initialized the con variable outside the Form_Load() method and within the main class so that it could be access by other methods within the same class.
After adding breakpoints, found that the Button Transfer event does not get the "con" property for some reason. Thus, I created another instance of the connection property within the button transfer event and now it seems working fine (However, getting a different error which needs to be addressed).
Thank you very much all for your suggestions.
Kind regards,
Andrew

 

[Skydiver]

I have initialized the con variable outside the Form_Load() method and within the main class so that it could be access by other methods within the same class

In general, this is a bad idea with modern programming. Unlike back in the dBase and FoxPro days when opening database connections was expensive (and there was no built in connection pooling), the modern recommended practice is keeping a connection open only for the duration of the operation that you are doing. So instead of:

class MyForm
{
    SqlConnection _conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConnectionString"]);

    void LoadData()
    {
        using(var command = new SqlCommand("SELECT ...", _conn))
        {
            :
        }
    }

    void SaveData()
    {
        using(var command = new SqlCommand("UPDATE ...", _conn))
        {
            :
        }
    }
}

The recommended way is:

class MyDataAccessLayer
{
    string ConnectionString => ConfigurationManager.ConnectionStrings["DbConnectionString"]);

    public static IEnumerable<DataRecord> LoadData()
    {
        using(var conn = new SqlConnection(ConnectionString))
        using(var command = new SqlCommand("SELECT ...", conn))
        {
            :
        }
    }

    public static void SaveData(IEnumerable<DataRecord> data)
    {
        using(var conn = new SqlConnection(ConnectionString))
        using(var command = new SqlCommand("UPDATE ...", conn))
        {
            :
        }
    }
}

 

[Skydiver]

After adding breakpoints, found that the Button Transfer event does not get the "con" property for some reason.

Very likely you declared another con in a closer scope and didn't initialize it.

 

[andrewmanuja]

Hi Skydiver,
Thanks for the provided sample code for the recommended method of programming above.
Got a slight issue which does not capture by my tired eyes.
This is related to the same project discussed above and when updating a SQL table, I am getting a parameterized query error specifying that '@aissueQuantity' was not supplied.

Please find my respective code below;

SqlConnection connect = Connection.GetConnection();
            table.AcceptChanges();
            //Mark selected rows ready for insert
            foreach (DataRow row in table.Rows)
            {
                if (row["Select Item"] as bool? == true)
                {
                    row.SetAdded();
                    //row.SetModified();
                }
            }

            //Save Data to Destination
            
            //Below section add the respective drug quantities to the respective cost centre drug available quantity
        
            using (var command = new SqlCommand(@"UPDATE tbl_CostCentreDrugStock SET tbl_CostCentreDrugStock.availableQuantity += @aissueQuantity
                                                            FROM tbl_CostCentreDrugStock
                                                            INNER JOIN tbl_CostCentreOrderStatus
                                                            ON tbl_CostCentreDrugStock.costCentreID = tbl_CostCentreOrderStatus.costCentreID
                                                            WHERE tbl_CostCentreOrderStatus.ccOrderID =@accOrderID AND tbl_CostCentreDrugStock.drugID =@adrugID ", connect))

            using (var adapter = new SqlDataAdapter { InsertCommand = command })
            {
                command.Parameters.Add("@adrugID", SqlDbType.Int, 0, "drugID");
                command.Parameters.Add("@accOrderID", SqlDbType.Int, 0, "ccOrderID");
                command.Parameters.Add("@aissueQuantity", SqlDbType.Int, 0, "issueQuantity");

                try
                {
                    adapter.Update(table);
                    MessageBox.Show("Data Saved");
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }

The SQL table, tbl_CostCentreDrugStock got fields costCentreID, drugID and availableQuantity.

Appreciate your valuable feedback.

Kind regards,

Andrew

 

[Skydiver]

I'm kind of confused about you setting InsertCommand, but the command's SQL uses UPDATE. But then, I've never claimed to be a SQL expert. In fact, I'm in the NoSQL camp.

 

[andrewmanuja]

Hi,
I know what you are referring to.
However, I have tested updating a sql statement using the above format and it worked for me.
Not sure exactly the reason behind the parameterized query issue here.
Thank you.
Andrew

 

[jmcilhinney]

Hi Skydiver,
Thanks for the provided sample code for the recommended method of programming above.
Got a slight issue which does not capture by my tired eyes.
This is related to the same project discussed above and when updating a SQL table, I am getting a parameterized query error specifying that '@aissueQuantity' was not supplied.

Please find my respective code below;

SqlConnection connect = Connection.GetConnection();
            table.AcceptChanges();
            //Mark selected rows ready for insert
            foreach (DataRow row in table.Rows)
            {
                if (row["Select Item"] as bool? == true)
                {
                    row.SetAdded();
                    //row.SetModified();
                }
            }

            //Save Data to Destination
           
            //Below section add the respective drug quantities to the respective cost centre drug available quantity
       
            using (var command = new SqlCommand(@"UPDATE tbl_CostCentreDrugStock SET tbl_CostCentreDrugStock.availableQuantity += @aissueQuantity
                                                            FROM tbl_CostCentreDrugStock
                                                            INNER JOIN tbl_CostCentreOrderStatus
                                                            ON tbl_CostCentreDrugStock.costCentreID = tbl_CostCentreOrderStatus.costCentreID
                                                            WHERE tbl_CostCentreOrderStatus.ccOrderID =@accOrderID AND tbl_CostCentreDrugStock.drugID =@adrugID ", connect))

            using (var adapter = new SqlDataAdapter { InsertCommand = command })
            {
                command.Parameters.Add("@adrugID", SqlDbType.Int, 0, "drugID");
                command.Parameters.Add("@accOrderID", SqlDbType.Int, 0, "ccOrderID");
                command.Parameters.Add("@aissueQuantity", SqlDbType.Int, 0, "issueQuantity");

                try
                {
                    adapter.Update(table);
                    MessageBox.Show("Data Saved");
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }

The SQL table, tbl_CostCentreDrugStock got fields costCentreID, drugID and availableQuantity.

Appreciate your valuable feedback.

Kind regards,

Andrew

This has got nothing to do with the topic of this thread. If you have a question on a different topic then please start a new thread.

 

[jmcilhinney]

I know what you are referring to.
However, I have tested updating a sql statement using the above format and it worked for me.

Then you did it just as wrongly then as you're doing it now. Instead of continuing to do the wrong thing because it works under certain specific circumstances, you should learn how to do things properly so they work in all circumstances.

 

[andrewmanuja]

Hi,
Found the root cause of the parameterized issue.
Basically, I am loading the sql table data into a data table which got slightly different columns from the DataGridView when finally updating the data table to update the changes into the sql tables.
Thanks.
Andrew