c# connectionstring property has not been initialized

Question

I have build a DataGridView and read a sql table fine. But the data could not be update back to SQL Server by SqlDataAdapter.Update(), I get error:

connectionstring property has not been initialized

This is my 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.Data.SqlClient;

namespace test_2
{
  public partial class Form1 : Form
  {
    SqlDataAdapter sda;
    DataSet ds;
    BindingSource bind1 = new BindingSource();
    SqlCommandBuilder scb;

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        using (SqlConnection cn = new     SqlConnection(Properties.Settings.Default.ConnString))
        {
            ShowData();
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(sda);
            bind1.DataSource = ds;
        }
    }

    private void ShowData()
    {
        using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.ConnString))
        {
           sda = new SqlDataAdapter("select key_seq, po_no, ref_no from mpo_master", cn);
            ds = new DataSet();
            sda.Fill(ds, "MPO");
            dataGridView1.DataSource = ds.Tables["MPO"]; 

        }
    }

    private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.ConnString))
            {
        scb = new SqlCommandBuilder(sda);
                sda.Update(ds, "MPO");
                MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
  }
}

this is the config file:

 <?xml version="1.0" encoding="utf-8" ?>
 <configuration>
     <configSections>
         <sectionGroup name="applicationSettings"       type="System.Configuration.ApplicationSettingsGroup, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
        <section name="VGB_Purchase.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    </sectionGroup>
</configSections>
<startup> 
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
</startup>
<applicationSettings>
    <VGB_Purchase.Properties.Settings>
        <setting name="connString" serializeAs="String">
            <value>Data Source=vgb-angus;Initial Catalog=VGB_Purchase;Persist Security Info=True;User ID=sa;Password=jessie</value>
        </setting>
    </VGB_Purchase.Properties.Settings>
</applicationSettings>

Please share the connection string as entered into your config file. Of course you can very carefully mask sensitive information, but we'll want to see exactly how you structured the string. — sfuqua, Sep 19 '15 at 03:36

Rachel Ambler · Answer 1 · 2015-09-20T12:59:06.190

You've a few problems with your code - unused instances of your connection, but, most importantly, you're not opening the connection!

e.g.

private void ShowData()
{
    using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.ConnString))
    {
       cn.Open();
       sda = new SqlDataAdapter("select key_seq, po_no, ref_no from mpo_master", cn);
       ds = new DataSet();
       sda.Fill(ds, "MPO");
       dataGridView1.DataSource = ds.Tables["MPO"]; 
       cn.Close();
    }
}

Revisit all your functions and remove the cn initialization where you're not using it, e.g.

private void Form1_Load(object sender, EventArgs e)
{
    ShowData();
    SqlCommandBuilder commandBuilder = new SqlCommandBuilder(sda);
    bind1.DataSource = ds;
}

In addition I assume sda is a class variable? Not easily ascertained from your code, my example above assumes it is.

Finally, you absolutely need exception handling; my example simply inserts your missing Open call. However you need to handle times when the connection can't be opened.

NB: This is just one example on how you should handle your DB calls according to your code structure given. Adapt accordingly for the Update.

Binesh Nambiar C · Answer 2 · 2015-09-20T14:54:54.893

-1

The above coding style not seeing like that much great

In your code the connection property is not setting even though connection object is creating in update

If you are trying to create a class level object Don't use "using" keyword while creating connection object in form_load(Not a good way-But will solve your problem for now- You should go for some architecture)

To destory the object you may have to call dispose on page unload(This also not a good way)

edited Sep 20 '15 at 14:54

answered Sep 19 '15 at 12:43

Binesh Nambiar C

152
1
2
9

None of this is an answer to the issue though. True the coding style isn't great but you didn't answer the question here as to why the error was occurring. In addition your response is no way leads to the answer "But will solve your problem for now" is 100% incorrect. – Rachel Ambler Sep 19 '15 at 16:12
The connection propery disposes immediatly form load finishes(since "using" keyword used). So In update part its not available. This is the reason. In update he creates another object of the connection which is not assigning to the object "sda". But he is using sda to run update. This makes the problem. Why the answer is incorrect? In my suggestion i'm trying to tell him either don't dispose connection object which creates form_load(Surely form_initialize can't use the object) so that you can use the same connection in button click. If doing like this. call connection dispose on form unload – Binesh Nambiar C Sep 20 '15 at 05:55
Other way is assign the connection object he created in button click(Its worst than previous one) – Binesh Nambiar C Sep 20 '15 at 05:57
The connection is not opened is another issue. But it will tell properly "no opened connection error". The error statement here is "connectionstring property has not been initialized" – Binesh Nambiar C Sep 20 '15 at 07:15
Yes - and my answer addresses that. If you look at the original code you'll notice that the connection is not used ANYWHERE inside the Command - therefore the attached SqlConnection inside the SqlCommand will be not initialized. My solution First Opens the connection, then passes the now fully initialized and open connection to the SqlCommand. – Rachel Ambler Sep 20 '15 at 08:22
Your answer is not attending the place where prticular error msg comes. It comes near update call. I'm not telling your correction is not required (opening connection requires). But to solve his issue (pirticular error message he pointed out) needs to handle in the code button_Clik event. Not the other places. I'm thinking my answer points to the question than yours. And not finding any reason for voting down – Binesh Nambiar C Sep 20 '15 at 12:47
Can you be any more pedantic? I gave my answer as an example to show one of the locations the code, as supplied, would fail. If the rest of the code is ANYTHING like the first call, then that's where it would fail. How did your answer point to anything? All you brought up was evil rule #1 - where you muted that the OP might want to think about making the connection a class object;That's AWFUL coding design. My answer gave one such example where the OP should handle the connection AND opening it. The OP can then use the same technique elsewhere. Hows about YOU let the OP decide on the answer? – Rachel Ambler Sep 20 '15 at 12:54
I'm not going to get into any further discussion with you about this because this is NOT a chat room or a place for a brawl; the answers are up there - it's up to the OP and the OP ALONE to pick which one they want to accept. If the OP wishes to accept yours over mine then so be it. What matters most though is that they have an answer - which is exactly what SO is all about. – Rachel Ambler Sep 20 '15 at 12:57
can you try your suggestion? same error will come out. Its because of "using" keyword . open & close won't solve this. To solve the above error either you shouldn't dispose the object of connection just after form load. Other wise in each function you should create new connection. – Binesh Nambiar C Sep 20 '15 at 13:01
Tested and works perfectly: using (SqlConnection cn = new SqlConnection("Server=DBServer;Database=Master;Trusted_Connection=True;")) { cn.Open(); SqlDataAdapter sda = new SqlDataAdapter("select * From sys.objects", cn); DataSet ds = new DataSet("Test"); sda.Fill(ds, "Test"); object x = ds.Tables[0].Rows[0]; cn.Close(); } – Rachel Ambler Sep 20 '15 at 13:20
And with this I am really done with you. I have no more time to waste on this. Goodbye. – Rachel Ambler Sep 20 '15 at 13:21
thanks Sfuqua, Rachel and Binesh. I took your advice. give up the "using" and built a connection inside ShowData() as below: – Angus Cheng Sep 21 '15 at 08:25
1

cn = new SqlConnection(); cn.ConnectionString = @"Data Source=vgb-angus;Initial Catalog=VGB_Purchase;Persist Security Info=True;User ID=sa;Password=jessie"; cn.Open(); – Angus Cheng Sep 21 '15 at 08:25
I can do update / insert / delete inside the DataGuidView (no further coding needed. but I have to give up getting connection detail from a config file. – Angus Cheng Sep 21 '15 at 08:30
You can still use config file. See http://stackoverflow.com/questions/6134359/read-connection-string-from-web-config – Binesh Nambiar C Sep 22 '15 at 16:44

c# connectionstring property has not been initialized

2 Answers2