ExecuteNonQuery: Connection property has not been initialized.

Question

Afternoon, So I have been at this one issue for hours and can't really get past this last hump. Below is the code for this program that I am writing:

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Diagnostics;  
using System.Data;  
using System.Data.SqlClient;  
using System.Configuration;  

namespace Test  
{  
  class Program  
  {  
    static void Main()  
    {  
      EventLog alog = new EventLog();  
      alog.Log = "Application";  
      alog.MachineName = ".";  
      foreach (EventLogEntry entry in alog.Entries)  
      {  
       SqlConnection connection1 = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=syslog2;Integrated Security=True");  
       SqlDataAdapter cmd = new SqlDataAdapter();  
       cmd.InsertCommand = new SqlCommand("INSERT INTO Application VALUES (@EventLog, @TimeGenerated, @EventType, @SourceName, @ComputerName, @InstanceId, @Message) ");  
       cmd.InsertCommand.Parameters.Add("@EventLog",SqlDbType.VarChar).Value = alog.Log;  
       cmd.InsertCommand.Parameters.Add("@TimeGenerated", SqlDbType.DateTime).Value = entry.TimeGenerated;  
       cmd.InsertCommand.Parameters.Add("@EventType", SqlDbType.VarChar).Value = entry.EntryType;  
       cmd.InsertCommand.Parameters.Add("@SourceName", SqlDbType.VarChar).Value = entry.Source;  
       cmd.InsertCommand.Parameters.Add("@ComputerName", SqlDbType.VarChar).Value = entry.MachineName;  
       cmd.InsertCommand.Parameters.Add("@InstanceId", SqlDbType.VarChar).Value = entry.InstanceId;  
       cmd.InsertCommand.Parameters.Add("@Message", SqlDbType.VarChar).Value = entry.Message;  
       connection1.Open();  
       cmd.InsertCommand.ExecuteNonQuery();  
       connection1.Close();  
      }   
    }  
  }  
} 

The Code compiles fine without error or warning but when i go to run it, as soon as it gets to cmd.InsertCommand.ExecuteNonQuery(); I get the following error:

ExecuteNonQuery: Connection property has not been initialized.

Any ideas on what I missed?

Answer 1

You need to assign the connection to the SqlCommand, you can use the constructor or the property:

cmd.InsertCommand = new SqlCommand("INSERT INTO Application VALUES (@EventLog, @TimeGenerated, @EventType, @SourceName, @ComputerName, @InstanceId, @Message) ");
cmd.InsertCommand.Connection = connection1;

I strongly recommend to use the using-statement for any type implementing IDisposable like SqlConnection, it'll also close the connection:

using(var connection1 = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=syslog2;Integrated Security=True"))
using(var cmd = new SqlDataAdapter())
using(var insertCommand = new SqlCommand("INSERT INTO Application VALUES (@EventLog, @TimeGenerated, @EventType, @SourceName, @ComputerName, @InstanceId, @Message) "))
{
    insertCommand.Connection = connection1;
    cmd.InsertCommand = insertCommand;
    //.....
    connection1.Open();
    // .... you don't need to close the connection explicitely
}

Apart from that you don't need to create a new connection and DataAdapter for every entry in the foreach, even if creating, opening and closing a connection does not mean that ADO.NET will create, open and close a physical connection but just looks into the connection-pool for an available connection. Nevertheless it's an unnecessary overhead.

Answer 2

You are not initializing connection.That's why this kind of error is coming to you.

Your code:

cmd.InsertCommand = new SqlCommand("INSERT INTO Application VALUES (@EventLog, @TimeGenerated, @EventType, @SourceName, @ComputerName, @InstanceId, @Message) ");

Corrected code:

cmd.InsertCommand = new SqlCommand("INSERT INTO Application VALUES (@EventLog, @TimeGenerated, @EventType, @SourceName, @ComputerName, @InstanceId, @Message) ",connection1);

Answer 3

A couple of things wrong here.

1. Do you really want to open and close the connection for every single log entry?

2. Shouldn't you be using SqlCommand instead of SqlDataAdapter?

3. The data adapter (or SqlCommand) needs exactly what the error message tells you it's missing: an active connection. Just because you created a connection object does not magically tell C# that it is the one you want to use (especially if you haven't opened the connection).

I highly recommend a C# / SQL Server tutorial.