Connection not open error Visual Studio c# OleDB

Question

I would like to put into a list the new ID-Machine (is not automatic), the name of the new machine and select the area (Bereich) where this type of machine belongs to. I try to change to string the ID-number (like in databease access) the ID to get the name of the area in the INSERT INTO query. But I dont know why VS shows that I didnt open the connection (ExecuteNonQuery requires an open and available Connection. The connection's current state is closed)... If anyone can help me, it will be great.

public void bttnSpeichern_Click_1(System.Object sender, EventArgs e)
        {
            Module.con.Open();

            int geraetid = (int)(Conversion.Val(lblGeraetid.Text));
            int bereichid = (int)(Conversion.Val(lblBereichID.Text));

            if (lblGeraetid.Text == "MaschineID" || lblGeraetid.Text == null || txtGeraetName.Text == null || lblGeraetid.Text == "BereichID" || lblBereichID.Text == null)
            {
                Interaction.MsgBox("Bitte füllen Sie die Faldern aus", Constants.vbInformation, "Hinweis");
            }
            else
            {
                string b = System.Convert.ToString(Interaction.MsgBox("Möchten Sie die Eingaben bestätigen?", (int)Constants.vbQuestion + Constants.vbYesNo, "Anlegen"));
                if ( b == Constants.vbYes.ToString())
                {
                    Module.con.Open();
                    OleDbCommand geraete_anlegen = new OleDbCommand("INSERT INTO tblMaschine(MaschineID,Maschine,BereichID) VALUES (\'"+
                     lblGeraetid.Text + "\',\'"+
                     txtGeraetName.Text + "\',\'" +
                     lblBereichID.Text + "\',\'", Module.con);
                    geraete_anlegen.ExecuteNonQuery();

                    Interaction.MsgBox("Gerät wurde erfolgreich angelegt!", Constants.vbInformation, "Neues Gerät");
                    //clear_text();
                    Module.con.Close();
                    display_geraete();

                }
            }



        }

Don't leave your connections open like that. Put those disposable objects into a using-block. Use parameters to avoid sql injection and formatting errors. — LarsTech, Aug 21 '19 at 18:32
@ Saman:using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.OleDb; namespace Geraetverleich { public class Module { public static OleDbConnection con = new OleDbConnection("Provider=MICROSOFT.ACE.OLEDB.12.0; Data Source=|DataDirectory|/Geraetverleih.accdb"); } } — Andrea Alfo, Aug 21 '19 at 18:57
@Lars, but where is this opened? I thougt I'm closing it with "Module.con.Close(); or not? — Andrea Alfo, Aug 21 '19 at 18:58
You aren't closing it `if (lblGeraetid.Text == "MaschineID" ...` — LarsTech, Aug 21 '19 at 19:20

Edney Holder · Accepted Answer · 2019-08-21T21:26:54.907

The first issue I see it that you are opening a connect twice. I commented out one and put a comment beside it.

I am not sure what Module contains so I codded a stand alone solution to the issue that you can take from.

I put the connection in a using clause which is always a good practice so you so not have to worry about closing it.

public void bttnSpeichern_Click_1(System.Object sender, EventArgs e)
{
    //Module.con.Open(); < -- Not needed it is done below

    int geraetid = (int)(Conversion.Val(lblGeraetid.Text));
    int bereichid = (int)(Conversion.Val(lblBereichID.Text));

    if (lblGeraetid.Text == "MaschineID" || lblGeraetid.Text == null || txtGeraetName.Text == null || lblGeraetid.Text == "BereichID" || lblBereichID.Text == null)
    {
        Interaction.MsgBox("Bitte füllen Sie die Faldern aus", Constants.vbInformation, "Hinweis");
    }
    else
    {
        string b = System.Convert.ToString(Interaction.MsgBox("Möchten Sie die Eingaben bestätigen?", (int)Constants.vbQuestion + Constants.vbYesNo, "Anlegen"));
        if ( b == Constants.vbYes.ToString())
        {
            string geraete_anlegen = new OleDbCommand("INSERT INTO tblMaschine(MaschineID,Maschine,BereichID) VALUES (\'"+
             lblGeraetid.Text + "\',\'"+
             txtGeraetName.Text + "\',\'" +
             lblBereichID.Text + "\',\'", Module.con);
            using (OleDbConnection connection = new OleDbConnection(connectionString)) //<-You need to supply a connection string
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand(geraete_anlegen, connection);
                command.ExecuteNonQuery();
            }

            Interaction.MsgBox("Gerät wurde erfolgreich angelegt!", Constants.vbInformation, "Neues Gerät");
            //clear_text();
            display_geraete();

        }
    }

}

Could you say something about your solution? Just another connection is opened! — Saman Gholami, Aug 21 '19 at 18:24
@SamanGholami, I've already try with: public static OleDbConnection ConnectTo() { //1. Objekt Verbindung erstellen OleDbConnection Verbindung = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ Application.StartupPath +"\\Geraetverleih.accdb"); return Verbindung; } and the connection in the query replacing the use of the class Module but I get the same error. — Andrea Alfo, Aug 21 '19 at 19:00
@Edney, it works now I have another error but it worked, Thank you. — Andrea Alfo, Aug 21 '19 at 19:09

Connection not open error Visual Studio c# OleDB

1 Answers1