CODEWITHSUNDEEP
c#oracle-databasedbconnection
Wizard
Wizard

Reputation: 11295

Oracle connection does not work on second try

public void comboboxes()
{
    using (OracleConnection conn = new OracleConnection("Data Source=localhost;Persist Security Info=True;User ID=kursinis1;Password=1234;Unicode=True"))
    {
        try
        {
            conn.Open();
            comboBox1.Items.Clear();
            DataSet dsetas1 = new DataSet();
            OracleDataAdapter data1 = new OracleDataAdapter("select aut_id, (aut_vardas || ' ' || aut_pavarde) AS autorius from autoriai", conn);
            data1.SelectCommand.CommandType = CommandType.Text;
            data1.Fill(dsetas1);
            dsetas1.Dispose();
            data1.Dispose();
            conn.Close();

            comboBox1.DataSource = dsetas1.Tables[0];
            comboBox1.DisplayMember = "autorius";
            comboBox1.ValueMember = "aut_id";

        }
        catch (Exception ex)
        {
            MessageBox.Show("Can not open connection ! ");
        }
    }
}

When I call function first time, it works (fills data to the checkbox), but when I try to call the function a second time (by button click). It shows an error that my connection is not open:

Error: Items collection cannot be modified when the DataSource is set.

What is the problem?

Upvotes: 0

Views: 347

Answers (2)

Doug
Doug

Reputation: 281

You are disposing the DataSet before you use it to bind to the comboBox. The data set must have a lifetime equal to the comboBox.

You either need to make the DataSet a member of the class that owns the comboBox (and dispose when the class is disposed), or copy the data from the table into another table or object list that is a class member before disposing the DataSet, and bind to that object instead.

public partial class MainForm : Form
{
    private DataTable dataTable = new DataTable();

    public MainForm()
    {
        InitializeComponent();

        comboBox1.DataSource = dataTable;
        comboBox1.DisplayMember = "autorius";
        comboBox1.ValueMember = "aut_id";
    }

    public void comboboxes()
    {
        using (OracleConnection conn = new OracleConnection("Data Source=localhost;Persist Security Info=True;User ID=kursinis1;Password=1234;Unicode=True"))
        {
            conn.Open();
            using (OracleDataAdapter data1 = new OracleDataAdapter("select aut_id, (aut_vardas || ' ' || aut_pavarde) AS autorius from autoriai", conn))
            {
                data1.SelectCommand.CommandType = CommandType.Text;
                data1.Fill(dataTable);
            }
        }
    }
}

(not tested, but something like this)

Upvotes: 1

Will Marcouiller
Will Marcouiller

Reputation: 24152

First, you don't even need to clear the ComboBox.Items property while using DataBinding.

Second, you're disposing both your DataSet and DataAdapter before binding the result set to your ComboBox control.

Third, the use of the using block defines the scope for which your variables shall be used, and outside which they shall be disposed automatically.

I commented out the lines you don't need and added a finally clause which gets executed anytime your try...catch block ends.

public void comboboxes() {
    using (OracleConnection conn = new OracleConnection("Data Source=localhost;Persist Security Info=True;User ID=kursinis1;Password=1234;Unicode=True")) {
        try {
            conn.Open();
            //comboBox1.Items.Clear();
            DataSet dsetas1 = new DataSet();
            OracleDataAdapter data1 = new OracleDataAdapter("select aut_id, (aut_vardas || ' ' || aut_pavarde) AS autorius from autoriai", conn);
            data1.SelectCommand.CommandType = CommandType.Text;
            data1.Fill(dsetas1);
            //dsetas1.Dispose();
            //data1.Dispose();
            //conn.Close();

            comboBox1.DataSource = dsetas1.Tables[0];
            comboBox1.DisplayMember = "autorius";
            comboBox1.ValueMember = "aut_id";
        } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); }
        finally {
            if (ConnectionState.Open == conn.State) conn.Close();
        }
    }
}

Should you wish to make sure both your DataSet and DataAdapter gets disposed as soon as they are no longer needed, put them in using blocks.

using (var cnx = new OracleConnection(connectionString)) 
    using (var ds = new DataSet())
        using (var da = new OracleDataAdapter(query)) {
            da.SelectCommand.CommandType = CommandType.Text;

            if (ConnectionState.Closed == cnx.State) cnx.Open();

            da.Fill(ds);

            comboBox1.DataSource = ds.Tables[0];
            comboBox1.DisplayMember = "autorius";
            comboBox1.ValueMember = "aut_id";                                 
        }

All three: your connection, data set and data adapter will be disposed automatically when your code exits the using blocks.

Or else, if your prefer, you may as well surround your connection's openings in a try...catch...finally block, and make sure your connection gets closed whatever happens.

Furthermore, it is preferable if you let the exception bubble up your application in ordre to get the full stack trace and proper exception when it occurs, so that you get all the required details to solve your problem.

You try...catch exceptions when they are handled gracefully. Take logging, for instance, and then probably simply rethrown to you GUI so that you may handle it grafully with a proper error message and such.

Upvotes: 1

Related Questions