CODEWITHSUNDEEP
c#sql-serverinsert
jladd
jladd

Reputation: 368

ExecuteNonQuery: Connection property has not been initialized.

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?

Upvotes: 25

Views: 183469

Answers (7)

Tim Schmelter
Tim Schmelter

Reputation: 460238

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.

Upvotes: 57

Ryno Potgieter
Ryno Potgieter

Reputation: 11

Opening and closing the connection takes a lot of time. And use the "using" as another member suggested. I changed your code slightly, but put the SQL creation and opening and closing OUTSIDE your loop. Which should speed up the execution a bit.

  static void Main()
        {
            EventLog alog = new EventLog();
            alog.Log = "Application";
            alog.MachineName = ".";
            /*  ALSO: USE the USING Statement as another member suggested
            using (SqlConnection connection1 = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=syslog2;Integrated Security=True")
            {

                using (SqlCommand comm = new SqlCommand("INSERT INTO Application VALUES (@EventLog, @TimeGenerated, @EventType, @SourceName, @ComputerName, @InstanceId, @Message) ", connection1))
                {
                    // add the code in here
                    // AND REMEMBER: connection1.Open();

                }
            }*/
            SqlConnection connection1 = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=syslog2;Integrated Security=True");
            SqlDataAdapter cmd = new SqlDataAdapter();
            // Do it one line
            cmd.InsertCommand = new SqlCommand("INSERT INTO Application VALUES (@EventLog, @TimeGenerated, @EventType, @SourceName, @ComputerName, @InstanceId, @Message) ", connection1);
            // OR YOU CAN DO IN SEPARATE LINE :
            // cmd.InsertCommand.Connection = connection1;
            connection1.Open();

            // CREATE YOUR SQLCONNECTION ETC OUTSIDE YOUR FOREACH LOOP
            foreach (EventLogEntry entry in alog.Entries)
            {
                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;
                int rowsAffected = cmd.InsertCommand.ExecuteNonQuery();
            }
            connection1.Close(); // AND CLOSE IT ONCE, AFTER THE LOOP
        }

Upvotes: 0

Adi
Adi

Reputation: 36

double click on your form to create form_load event.Then inside that event write command.connection = "your connection name";

Upvotes: -1

Prasad Bhosale
Prasad Bhosale

Reputation: 722

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);

Upvotes: 18

waseem
waseem

Reputation: 21

Actually this error occurs when server makes connection but can't build due to failure in identifying connection function identifier. This problem can be solved by typing connection function in code. For this I take a simple example. In this case function is con your may be different.

SqlCommand cmd = new SqlCommand("insert into ptb(pword,rpword) values(@a,@b)",con);

Upvotes: 2

Dev
Dev

Reputation: 1

just try this..

you need to open the connection using connection.open() on the SqlCommand.Connection object before executing ExecuteNonQuery()

Upvotes: -1

Aaron Bertrand
Aaron Bertrand

Reputation: 280510

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.

Upvotes: 1

Related Questions