Reputation: 368
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
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
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
Reputation: 36
double click on your form to create form_load event.Then inside that event write command.connection = "your connection name";
Upvotes: -1
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
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
Reputation: 1
just try this..
you need to open the connection using connection.open()
on the SqlCommand.Connection
object before executing ExecuteNonQuery()
Upvotes: -1
Reputation: 280510
A couple of things wrong here.
Do you really want to open and close the connection for every single log entry?
Shouldn't you be using SqlCommand
instead of SqlDataAdapter
?
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