System.InvalidOperationException: 'The ConnectionString property has not been initialized.' - C#

Question

The code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;

namespace LaboratoryGroupings
{
    public partial class CRUD : Form
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnect"].ConnectionString);
        char action;

        public CRUD() 
        {
            InitializeComponent();
        }

        private void CRUD_Load(object sender, EventArgs e)
        {
            LoadData();
            buttonSetup("enable");
        }

        public void LoadData()
        {
            con.Open();
            using (con)
            {
                SqlDataAdapter sda = new SqlDataAdapter("Select * from tbl_User", con);
                DataTable dt = new DataTable();
                sda.Fill(dt);

                DGUserData.DataSource = dt;
            }

            con.Close();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            con.Open();
            SqlCommand cmd;
            switch (action)
            {
                case 'a':
                    cmd = new SqlCommand("INSERT INTO tbl_User (userName, userPass) Values (@username, @userpass)", con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@username", txtUN.Text.Trim());
                    cmd.Parameters.AddWithValue("@userpass", txtPW.Text.Trim());
                    cmd.ExecuteNonQuery();

                    MessageBox.Show("Added new User Successfully!", "User Maintenance", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    break;
                case 'u':
                    break;
                case 'd':
                    break;
            }
            con.Close();
            LoadData();
        }
    }
}

In the startup of the form it works fine without the error, but when I input and save the data this error shows up for the con.open();:

System.InvalidOperationException: 'The ConnectionString property has not been initialized.'

I tried to initialize the connectionString inside the case, but the con.Open() in the LoadData() method shows the same error this time even though it starts up just fine without the error.

This is the content of my App.config, I already added the connectionString inside:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
    </startup>

    <connectionStrings>
    <add name="dbConnect" connectionString="data source=DESKTOP-UT4DEFE\MSSQLSERVER01; initial catalog=LabDB; 
         integrated security=true" providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>

Does `ConfigurationManager.ConnectionStrings["dbConnect"].ConnectionString` contain what you expect it to contain? Did you try to `Messagebox.Show` it? — Heinzi, Jan 12 '22 at 10:22
So, you are unable to both read and write data? How does your config look, have you defined a connectionstring there with a matching name...? — Culme, Jan 12 '22 at 10:23
Does this answer your question? [C# Using one SqlConnection for multiple queries](https://stackoverflow.com/questions/49022069/c-sharp-using-one-sqlconnection-for-multiple-queries) — Filburt, Jan 12 '22 at 10:23
The error is pretty clear and the connection string is almost certainly missing from `app.config`. The code itself though is unsafe and prone to blocking, because any locks acquired by a query remain active until the connection or transaction is closed. Connections should be short lived, created right before use and closed immediately afterwards. That's why all examples show connections created inside `using` blocks. You gain nothing by making the connection a field. Connection pooling in ADO.NET means that closing and reopening a connection costs nothing — Panagiotis Kanavos, Jan 12 '22 at 10:26
@PanagiotisKanavos I already added it in the app.config, I edited my question details — Richard Johnson, Jan 12 '22 at 10:28
Finally, storing a password in cleartext is a major security problem. Besides, you don't even need that table - you're already connecting to the database using *the current user's account*. The database already knows who the user is, there's no reason to try again with a different username/password. Unless you want a user to log in with another user's credentials. — Panagiotis Kanavos, Jan 12 '22 at 10:30
`added it in the app.config` does the correct config file exist at runtime though? `app.config` will be translated to `myappname.exe.config` during compilation. Does that file exist on the machine where the error occurs? — Panagiotis Kanavos, Jan 12 '22 at 10:31
As @PanagiotisKanavos mentioned; make sure the config file gets copied to the build output folder. Sometimes you need to make sure it has the build action "Copy always" set, in order for that to work. — Culme, Jan 12 '22 at 10:35
This is just a testing form, not an official software or system. so the content can be like that. I am just trying the syntax. And yes it exist. the initialization is the problem even though it is already initialized, but it works fine even during the startup only — Richard Johnson, Jan 12 '22 at 10:36
Since this is a testing form, change the code. The syntax is simply wrong. Don't reuse the connection. What does `it works fine even during the startup only` mean? That `CRUD_Load` but `btnSave_Click` throws? What's the actual full exception text? Are you sure you haven't modified the connection string, or replaced the connection object? Clearly there's missing code in the form. The way to avoid this is again to *not* store the connection in a field. In general objects should have limited scope to avoid accidental modifications. Especially connections — Panagiotis Kanavos, Jan 12 '22 at 10:42
To eliminate one possible source of error, you could test temporarily adding the connectionstring directly into the code, thereby skipping potentiall issues with reading it from config. — Culme, Jan 12 '22 at 10:46
What your debugger shows about con after the end of `using (con) {}` block? — Arvo, Jan 12 '22 at 11:25
Try escaping the backslash in the connection string: `connectionString="data source=DESKTOP-UT4DEFE\\MSSQLSERVER01; initial catalog=LabDB; integrated security=true` — haldo, Jan 12 '22 at 11:50
Bit of a mess here. You create a persistent connection and open it yourself; you use the adapter, which will open and close the connection, but only sometimes; and you're using a `using` block with the connection, which will dispose it. I strongly suggest you find an code example that uses the adapter properly and try to follow its structure to the letter. There is no reason to try to persist the connection object in your application-- .NET will pool it for you. — John Wu, Jan 25 '23 at 00:40

score 2 · Answer 1 · answered Jan 12 '22 at 22:33

The problem is that you are reusing the SqlConnection object, and you have already disposed it. Once it is disposed it cannot be reused. The specific error you got was because the connection string had been cleared, but there would have been other issues. You can see from this dotnetfiddle what is happening.

You must not cache the connection object. Create a new one when you need it, dispose immediately.

Note that SqlCommand and SqlAdapter also need disposing, and that SqlAdapter will automatically open and close the connection for you.

     public void LoadData()
     {
         using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnect"].ConnectionString))
         using (var sda = new SqlDataAdapter("Select * from tbl_User", con))
         {
             DataTable dt = new DataTable();
             sda.Fill(dt);

             DGUserData.DataSource = dt;
         }
     }

     private void btnSave_Click(object sender, EventArgs e)
     {
         switch (action)
         {
             case 'a':
                 using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnect"].ConnectionString))
                 using (var cmd = new SqlCommand("INSERT INTO tbl_User (userName, userPass) Values (@username, @userpass)", con)
                 {
                     CommandType = CommandType.Text
                 })
                     cmd.Parameters.Add("@username", SqlDbType.NVarChar, 100).Value = txtUN.Text.Trim();
                     cmd.Parameters.Add("@userpass", SqlDbType.NVarChar, 100).Value = txtPW.Text.Trim();
                     con.Open();
                     cmd.ExecuteNonQuery();
                 }
                 MessageBox.Show("Added new User Successfully!", "User Maintenance", MessageBoxButtons.OK, MessageBoxIcon.Information);
                 break;
             case 'u':
             case 'd':
                 break;
         }
         LoadData();
     }

score -1 · Answer 2 · answered Jan 25 '23 at 00:18

-1

I encountered this issue in .NETCore application. `apparently I had ConnectionString instead of ConnectionStrings in my appsettings.json

I hope this helps.

answered Jan 25 '23 at 00:18

Isaac

1
2

System.InvalidOperationException: 'The ConnectionString property has not been initialized.' - C#

2 Answers2