C# ExecuteNonQuery：连接属性尚未初始化。
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10263094/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ExecuteNonQuery: Connection property has not been initialized.
提问by jladd
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:
代码编译得很好，没有错误或警告，但是当我运行它时，只要它到达 cmd.InsertCommand.ExecuteNonQuery(); 我收到以下错误：
ExecuteNonQuery: Connection property has not been initialized.
ExecuteNonQuery：连接属性尚未初始化。
Any ideas on what I missed?
关于我错过了什么的任何想法？
采纳答案by Tim Schmelter
You need to assign the connection to the SqlCommand, you can use the constructoror the property:
您需要将连接分配给SqlCommand，您可以使用构造函数或属性：
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-statementfor any type implementing IDisposablelike SqlConnection, it'll also close the connection:
我强烈建议using-statement对任何实现IDisposablelike 的类型使用SqlConnection，它也会关闭连接：
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 DataAdapterfor every entry in the foreach, even if creating, opening and closing a connection does notmean that ADO.NET will create, open and close a physicalconnection but just looks into the connection-poolfor an available connection. Nevertheless it's an unnecessary overhead.
除此之外，你并不需要创建一个新的连接，并DataAdapter在每个条目foreach，即使创建，打开和关闭连接并不能意味着ADO.NET将创建，打开和关闭一个物理连接，但只是眺望可用连接的连接池。然而，这是不必要的开销。
回答by Aaron Bertrand
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
SqlCommandinstead ofSqlDataAdapter?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).
您真的要为每个日志条目打开和关闭连接吗？
你不应该使用
SqlCommand而不是SqlDataAdapter吗？数据适配器（或
SqlCommand）正是需要错误消息告诉您它缺少的内容：活动连接。仅仅因为您创建了一个连接对象并不会神奇地告诉 C# 它是您要使用的对象（尤其是在您尚未打开连接的情况下）。
I highly recommend a C# / SQL Server tutorial.
我强烈推荐 C#/SQL Server 教程。
回答by Dev
just try this..
试试这个..
you need to open the connection using connection.open()on the SqlCommand.Connectionobject before executing ExecuteNonQuery()
在执行之前，您需要connection.open()在SqlCommand.Connection对象上使用打开连接ExecuteNonQuery()
回答by Prasad Bhosale
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);
回答by waseem
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);
回答by Adi
double click on your form to create form_load event.Then inside that event write command.connection = "your connection name";
双击您的表单以创建 form_load 事件。然后在该事件中写入 command.connection = "your connection name";
回答by Ryno Potgieter
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.
打开和关闭连接需要很多时间。并按照另一位成员的建议使用“使用”。我稍微更改了您的代码，但将 SQL 创建和打开和关闭放在您的循环之外。这应该会加快执行速度。
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
}
