Sending data to acess database

Question

I'm trying to Send data to an access database but it keeps giving the same error (ExecuteNonQuery: Connection property has not been initialized), I try to find answers of related topics but I seem to not see where the problem is, help please, thanks. Code below:

    public static string[] questions = new string[7];

    private void SaveData_Load(object sender, EventArgs e)
    {
        //Selecting data from login
        string fileName = @"U:\Projects\Visual Studio\Extras resources\login.xlsx";
        string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", fileName);

        using (OleDbConnection cn = new OleDbConnection(connectionString))
        {
            try
            {
                cn.Open();
                OleDbCommand command = new OleDbCommand("select * from [lineAudit$]", cn);
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        string row1Col0 = Convert.ToString(dr[0]);
                        string row1cl1 = Convert.ToString(dr[1]);
                        dateTime_bx.Text = row1Col0;
                        auditor_bx.Text = row1cl1;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to" + ex.Message);
            }
            cn.Close();
        }

        //Selecting data from datawindow
        string fileName2 = @"U:\Projects\Visual Studio\Extras resources\dataWindow.xlsx";
        string connectionString2 = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", fileName2);

        using (OleDbConnection cn = new OleDbConnection(connectionString2))
        {
            try
            {
                cn.Open();
                OleDbCommand command = new OleDbCommand("select * from [lineAudit$]", cn);
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        string row1Col0 = Convert.ToString(dr[0]);
                        string row1Col1 = Convert.ToString(dr[1]);
                        string row1Col2 = Convert.ToString(dr[2]);
                        string row1Col3 = Convert.ToString(dr[3]);
                        opId_bx.Text = row1Col0;
                        shopOrder_bx.Text = row1Col1;
                        partNumber_bx.Text = row1Col2;
                        opNumber_bx.Text = row1Col3;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to" + ex.Message);
            }
            cn.Close();
        }

        //Selecting data from documentation
        string fileName3 = @"U:\Projects\Visual Studio\Extras resources\documentation.xlsx";
        string connectionString3 = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", fileName3);

        using (OleDbConnection cn = new OleDbConnection(connectionString3))
        {
            try
            {
                cn.Open();
                OleDbCommand command = new OleDbCommand("select * from [lineAudit$]", cn);
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        string row1Col0 = Convert.ToString(dr[0]);
                        string row1Col1 = Convert.ToString(dr[1]);
                        q1_bx.Text = row1Col0;

                        //boolean to check if string is empty
                        Documentation doc = new Documentation();
                        if (a == 2)
                        {
                            questions[0] = " Q1- " + row1Col1;

                        }
                        else
                        {
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to" + ex.Message);
            }
            cn.Close();
        }

        //Selecting data from materials
        string fileName4 = @"U:\Projects\Visual Studio\Extras resources\materials.xlsx";
        string connectionString4 = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", fileName4);

        using (OleDbConnection cn = new OleDbConnection(connectionString4))
        {
            try
            {
                cn.Open();
                OleDbCommand command = new OleDbCommand("select * from [lineAudit$]", cn);
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        string row1Col0 = Convert.ToString(dr[0]);
                        string row1Col1 = Convert.ToString(dr[1]);
                        q2_bx.Text = row1Col0;

                        //boolean to check if string is empty
                        if (b == 2)
                        {
                            questions[1] = " Q2- " + row1Col1;
                        }
                        else
                        {
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to" + ex.Message);
            }
            cn.Close();
        }

        //Selecting data from procedure
        string fileName5 = @"U:\Projects\Visual Studio\Extras resources\comformity.xlsx";
        string connectionString5 = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", fileName5);

        using (OleDbConnection cn = new OleDbConnection(connectionString5))
        {
            try
            {
                cn.Open();
                OleDbCommand command = new OleDbCommand("select * from [lineAudit$]", cn);
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        string row1Col0 = Convert.ToString(dr[0]);
                        string row1Col1 = Convert.ToString(dr[1]);
                        q3_bx.Text = row1Col0;

                        //boolean to check if string is empty
                        if (c == 2)
                        {
                            questions[2] = " Q3- " + row1Col1;
                        }
                        else
                        {
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to" + ex.Message);
            }
            cn.Close();
        }

        //Selecting data from No_procedure
        string fileName6 = @"U:\Projects\Visual Studio\Extras resources\proceadure.xlsx";
        string connectionString6 = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", fileName6);

        using (OleDbConnection cn = new OleDbConnection(connectionString6))
        {
            try
            {
                cn.Open();
                OleDbCommand command = new OleDbCommand("select * from [lineAudit$]", cn);
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        string row1Col0 = Convert.ToString(dr[0]);
                        string row1Col1 = Convert.ToString(dr[1]);
                        q4_bx.Text = row1Col0;

                        //boolean to check if string is empty
                        if (d == 2)
                        {
                            questions[3] = " Q4- " + row1Col1;
                        }
                        else
                        {
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to" + ex.Message);
            }
            cn.Close();
        }

        //Selecting data from calibration
        string fileName7 = @"U:\Projects\Visual Studio\Extras resources\calibration.xlsx";
        string connectionString7 = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", fileName7);

        using (OleDbConnection cn = new OleDbConnection(connectionString5))
        {
            try
            {
                cn.Open();
                OleDbCommand command = new OleDbCommand("select * from [lineAudit$]", cn);
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        string row1Col0 = Convert.ToString(dr[0]);
                        string row1Col1 = Convert.ToString(dr[1]);
                        q5_bx.Text = row1Col0;

                        //boolean to check if string is empty
                        if (f == 2)
                        {
                            questions[4] = " Q5- " + row1Col1;
                        }
                        else
                        {
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to" + ex.Message);
            }
            cn.Close();
        }

        //Selecting data from safety
        string fileName8 = @"U:\Projects\Visual Studio\Extras resources\safety.xlsx";
        string connectionString8 = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", fileName8);

        using (OleDbConnection cn = new OleDbConnection(connectionString8))
        {
            try
            {
                cn.Open();
                OleDbCommand command = new OleDbCommand("select * from [lineAudit$]", cn);
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        string row1Col0 = Convert.ToString(dr[0]);
                        string row1Col1 = Convert.ToString(dr[1]);
                        q6_bx.Text = row1Col0;

                        //boolean to check if string is empty
                        if (g == 2)
                        {
                            questions[5] = " Q6- " + row1Col1;
                        }
                        else
                        {
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to" + ex.Message);
            }
            cn.Close();
        }

        //Selecting data from packing
        string fileName9 = @"U:\Projects\Visual Studio\Extras resources\packing.xlsx";
        string connectionString9 = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", fileName9);

        using (OleDbConnection cn = new OleDbConnection(connectionString9))
        {
            try
            {
                cn.Open();
                OleDbCommand command = new OleDbCommand("select * from [lineAudit$]", cn);
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        string row1Col0 = Convert.ToString(dr[0]);
                        string row1Col1 = Convert.ToString(dr[1]);
                        q7_bx.Text = row1Col0;

                        //boolean to check if string is empty
                        if (h == 2)
                        {
                            questions[6] = " Q7- " + row1Col1;

                        }
                        else
                        {
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to" + ex.Message);
            }
            cn.Close();
        }

        // Call the methods.
        string result1 = ConvertStringArrayToString(questions);
        noteStr_bx.Text = result1;
    }

    string ConvertStringArrayToString(string[] questions)
    {

        // Concatenate all the elements into a StringBuilder.
        StringBuilder builder = new StringBuilder();
        foreach (string value in questions)
        {
            builder.Append(value);
            builder.Append('.');
        }
        return builder.ToString();
    }
    private void BTNSave_Click(object sender, EventArgs e)
    {
        //sending data to database
        OleDbConnection connect = new OleDbConnection();
        connect.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=U:\Projects\Visual Studio\Extras resources\line_Audits.accdb";
        string QueryText = "INSERT INTO lineAudit (dateTime,auditor,operator,partNumber,shopOrder,operationNumber,documentationAndRevision,labeling,conformity,procedure,calibration,safety,packing,Notes) values (@dateTime,@auditor,@operator,@partNumber,@shopOrder,@operationNumber,@documentationAndRevision,@labeling,@conformity,@procedure,@calibration,@safety,@packing,@Notes)";
        connect.Open();
        using (OleDbCommand command = new OleDbCommand(QueryText))
        {
            try
            {
                OleDbDataAdapter da = new OleDbDataAdapter("INSERT INTO lineAudit", connect);

                String dateTime = dateTime_bx.Text;
                String auditor = auditor_bx.Text;
                String operador = opId_bx.Text;
                String partNumber = partNumber_bx.Text;
                String shopOrder = shopOrder_bx.Text;
                String operationNumber = opNumber_bx.Text;
                String q1 = q1_bx.Text;
                String q2 = q2_bx.Text;
                String q3 = q3_bx.Text;
                String q4 = q4_bx.Text;
                String q5 = q5_bx.Text;
                String q6 = q6_bx.Text;
                String q7 = q7_bx.Text;
                String note = noteStr_bx.Text;

                command.Parameters.AddWithValue("@dateTime", this.dateTime_bx.Text);
                command.Parameters.AddWithValue("@auditor", this.auditor_bx.Text);
                command.Parameters.AddWithValue("@operator", this.opId_bx.Text);
                command.Parameters.AddWithValue("@partNumber", this.partNumber_bx.Text);
                command.Parameters.AddWithValue("@shopOrder", this.shopOrder_bx.Text);
                command.Parameters.AddWithValue("@operationNumber", this.opNumber_bx.Text);
                command.Parameters.AddWithValue("@documentationAndRevision", this.q1_bx.Text);
                command.Parameters.AddWithValue("@labeling", this.q2_bx.Text);
                command.Parameters.AddWithValue("@conformity", this.q3_bx.Text);
                command.Parameters.AddWithValue("@procedure", this.q4_bx.Text);
                command.Parameters.AddWithValue("@calibration", this.q5_bx.Text);
                command.Parameters.AddWithValue("@safety", this.q6_bx.Text);
                command.Parameters.AddWithValue("@packing", this.q7_bx.Text);
                command.Parameters.AddWithValue("@Notes", this.noteStr_bx.Text);

                command.ExecuteNonQuery();
                //connect.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                connect.Close();
            }
        }

    }

    private void dateTime_bx_TextChanged(object sender, EventArgs e)
    {
    }
    }
}

The part that send the data to the data base is at the bottom of the code, it has a comment line for "//Sending data to database" — dreamheart
– dreamheart, Commented Jan 31, 2017 at 20:10
You need to pass the connection to the constructor for OleDbCommand. By the way, why do you have both a OleDbCommand and a OleDbDataAdapter? I don't see the adapter being used for anything. — John Wu
– John Wu, Commented Jan 31, 2017 at 20:14
Quite the wall of code if the problem is at the bottom of the code — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jan 31, 2017 at 20:16
Possible duplicate of ExecuteNonQuery: Connection property has not been initialized. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jan 31, 2017 at 20:18
@JohnWu So do you think that I should use OleDbCommand instead of OleDbDataAdapter?, I'm new on C# so I'm not very sure when to use one or the other. — dreamheart
– dreamheart, Commented Jan 31, 2017 at 20:27

Charles May · Accepted Answer · 2017-01-31 20:30:25Z

1

Tell your OleDbCommand what connection to use after the QueryText
Instead of:

using (OleDbCommand command = new OleDbCommand(QueryText))

use:

using (OleDbCommand command = new OleDbCommand(QueryText, connect))

answered Jan 31, 2017 at 20:30

Charles May

1,7591 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dreamheart Over a year ago

Thanks for the answer @Charles May I had to do some other changes but it worked! (My main problem was when I did the database I select some columns as integer and I was sending it through the code as string, I also had to add [] to some of the names on my insert string. Thanks all of you for your replies :D

ASH · Accepted Answer · 2017-02-03 15:25:37Z

Something like this should work for you.

using ExcelToAccessLib; 
using System; 
using System.IO; 
using System.Windows.Forms; 

namespace WindowsFormsApplication1 
{ 
    public partial class Form1 : Form 
    { 
        private string AccessFile = Path.Combine( 
            AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb"); 

        private string ExcelFile = Path.Combine( 
            AppDomain.CurrentDomain.BaseDirectory, "Customers.xlsx"); 

        public Form1() 
        { 
            InitializeComponent(); 
        } 

        private void button1_Click(object sender, EventArgs e) 
        { 
            Importer ImportData = 
                new Importer( 
                    new ExcelInfo 
                    { 
                        FileName = ExcelFile, 
                        HasHeaders = true, 
                        SheetName = "Customers" 
                    }, 
                    new AccessInfo 
                    { 
                        FileName = AccessFile, 
                        TableName = "Customers1", 
                        FieldNames = "CompanyName,ContactName" 
                    }); 

            if (ImportData.Run()) 
            { 
                MessageBox.Show("Import complete"); 
            } 
        } 
    } 
}

Collectives™ on Stack Overflow

Sending data to acess database

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related