Undefined object reference for an object instance. '

Question

Hello,

In my code there is this error

System.Nullreferenceexception: 'Undefined object reference for an instance of an object.',

_oleCmd was null.

How do I fix it? (I’m a C beginner#)

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.OleDb;

namespace HoraExtra
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private OleDbConnection _olecon;
        private OleDbCommand _oleCmd;
        private static String _Arquivo = @"C:\Users\rgrto\OneDrive\Documentos\Visual Studio 2017\Projects\HoraExtra\database.xlsx";
        private String _StringConexao = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;ReadOnly=False';", _Arquivo);
        private String _Consulta;

        private void FormExcel_Load(object sender, EventArgs args)
        {
            try
            {
                _olecon = new OleDbConnection(_StringConexao);
                _olecon.Open();

                _oleCmd = new OleDbCommand();
                _oleCmd.Connection = _olecon;
                _oleCmd.CommandType = CommandType.Text;
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnEnviar_Click(object sender, EventArgs e)
        {
            _Consulta = "INSERT INTO [datab$] ([Nome], [Data]) VALUES (@Nome, @Data)";

            _oleCmd.CommandText = _Consulta;
            _oleCmd.Parameters.Add("@Nome", OleDbType.VarChar, 255).Value = txt_nome.Text.Trim();
            _oleCmd.Parameters.Add("@Data", OleDbType.VarChar, 255).Value = txt_data.Text.Trim();
            _oleCmd.ExecuteNonQuery();

            _oleCmd.Parameters.Clear();

            txt_nome.ResetText();
            txt_data.ResetText();

            MessageBox.Show("Inserido com sucesso!");
        }

        private void FormExcel_FormClosing(object sender, FormClosingEventArgs e)
        {
            if ( _oleCmd != null )
            {
                _oleCmd.Parameters.Clear();
                _oleCmd.Dispose();
            }
            _oleCmd = null;

            if(_olecon != null)
            {
                if(_olecon.State == ConnectionState.Open)
                {
                    _olecon.Close();
                    _olecon.Dispose();
                }

                _olecon = null;
            }
        }
    }
}

Answer 1

You must set the connection to the OleDbCommand. You have already set it in your code, but when initializing did not use the OleDbConnection to create the command:

_oleCmd = _olecon.CreateCommand();

Follow the complete passage with the correction:

private void FormExcel_Load(object sender, EventArgs args)
{
    try
    {
        _olecon = new OleDbConnection(_StringConexao);
        _olecon.Open();

        _oleCmd = _olecon.CreateCommand();
        _oleCmd.CommandType = CommandType.Text;
    }

    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}