VB.NET SQL Server 插入 - ExecuteNonQuery：连接属性尚未初始化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6360153/
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
VB.NET SQL Server Insert - ExecuteNonQuery: Connection property has not been initialized
提问by Tepken Vannkorn
In the form load event, I connect to the SQL Server database:
在表单加载事件中，我连接到 SQL Server 数据库：
Private Sub AddBook_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
myConnection = New SqlConnection("server=.\SQLEXPRESS;uid=sa;pwd=123;database=CIEDC")
myConnection.Open()
End Sub
Here in the Insert event, I use the following code:
在 Insert 事件中，我使用以下代码：
Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
Try
myConnection.Open()
myCommand = New SqlCommand("INSERT INTO tblBook(BookCode, BookTitle, Author, PublishingYear, Price, EnterDate, CatID, RackID, Amount) VALUES('" & txtBookCode.Text & "','" & txtTitle.Text & "','" & txtAuthor.Text & "','" & txtPublishYear.Text & "','" & txtPrice.Text & "', #" & txtEnterDate.Text & "#, " & txtCategory.Text & "," & txtRack.Text & "," & txtAmount.Text & ")")
myCommand.ExecuteNonQuery()
MsgBox("The book named '" & txtTitle.Text & "' has been inseted successfully")
ClearBox()
Catch ex As Exception
MsgBox(ex.Message())
End Try
myConnection.Close()
End Sub
And It produces the following error:
它产生以下错误：
ExecuteNonQuery: Connection property has not been initialized
回答by Brian Webster
Connection Assignment- You aren't setting the connection property of the SQLCommand. You can do this without adding a line of code. This is the cause of your error.
myCommand = New SqlCommand("INSERT INTO tblBook(BookCode, BookTitle, Author, PublishingYear, Price, EnterDate, CatID, RackID, Amount) VALUES('" & txtBookCode.Text & "','" & txtTitle.Text & "','" & txtAuthor.Text & "','" & txtPublishYear.Text & "','" & txtPrice.Text & "', #" & txtEnterDate.Text & "#, " & txtCategory.Text & "," & txtRack.Text & "," & txtAmount.Text & ")", MyConnection)Connection Handling- You also need to remove `MyConnection.Open' from your Load Handler. Just open it and close it in your Click Handler, as you are currently doing. This is not causing the error.
Parameterized SQL- You need to utilize SQL Parameters, despite the fact that you are not using a Stored Procedure. This is not the cause of your error. As Conradreminded me, your original code dumps values straight from the user into a SQL Statement. Malicious users will steal your data unless you use SQL Parameters.
Dim CMD As New SqlCommand("Select * from MyTable where BookID = @BookID") CMD.Parameters.Add("@BookID", SqlDbType.Int).Value = CInt(TXT_BookdID.Text)
连接分配- 您没有设置 SQLCommand 的连接属性。您无需添加一行代码即可完成此操作。这是你的错误的原因。
myCommand = New SqlCommand("INSERT INTO tblBook(BookCode, BookTitle, Author, PublishingYear, Price, EnterDate, CatID, RackID, Amount) VALUES('" & txtBookCode.Text & "','" & txtTitle.Text & "','" & txtAuthor.Text & "','" & txtPublishYear.Text & "','" & txtPrice.Text & "', #" & txtEnterDate.Text & "#, " & txtCategory.Text & "," & txtRack.Text & "," & txtAmount.Text & ")", MyConnection)连接处理- 您还需要从负载处理程序中删除“MyConnection.Open”。只需打开它并在您的 Click Handler 中关闭它，就像您目前所做的那样。这不会导致错误。
参数化 SQL- 您需要使用 SQL 参数，尽管您没有使用存储过程。这不是您出错的原因。正如康拉德提醒我的那样，您的原始代码将值直接从用户转储到 SQL 语句中。除非您使用 SQL 参数，否则恶意用户将窃取您的数据。
Dim CMD As New SqlCommand("Select * from MyTable where BookID = @BookID") CMD.Parameters.Add("@BookID", SqlDbType.Int).Value = CInt(TXT_BookdID.Text)
回答by Oded
You need to set the Connectionproperty on the command:
您需要Connection在命令上设置属性：
myCommand.Connection = myConnection
回答by Kilanash
Pretty much what the error message implies - the Connection property of the SqlCommand object hasn't been assigned to the connection you opened (in this case you called it myConnection).
几乎错误消息所暗示的内容 - SqlCommand 对象的 Connection 属性尚未分配给您打开的连接（在这种情况下，您将其称为myConnection）。
Also, a word of advice here. Do some reading on sql parameters - doing sql concatenation from user input without any sanity checks is the way SQL injectionattacks happen.
另外，这里有一句忠告。对 sql 参数进行一些阅读 - 从用户输入进行 sql 连接而不进行任何完整性检查是SQL 注入攻击发生的方式。
This is one way to do it:
这是一种方法：
Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
Try
myConnection.Open()
myCommand = New SqlCommand( _
"INSERT INTO tblBook(BookCode, BookTitle, Author, PublishingYear, Price, " & _
" EnterDate, CatID, RackID, Amount) " & _
"VALUES(@bookCode, @bookTitle, @author, @publishingYear, @price, @enterDate, " & _
" @catId, @rackId, @amount)")
myCommand.Connection = myConnection
with myCommand.Parameters
.AddWithValue("bookCode", txtBookCode.Text)
.AddWithValue("bookTitle", txtTitle.Text)
.AddWithValue("author", txtAuthor.Text)
.AddWithValue("publishingYear", txtPublishYear.Text)
.AddWithValue("price", txtPrice.Text)
.AddWithValue("enterDate", txtEnterDate.Text)
.AddWithValue("catId", txtCategory.Text)
.AddWithValue("rackId", txtRack.Text)
.AddWithValue("amount", txtAmount.Text)
end with
myCommand.ExecuteNonQuery()
MsgBox("The book named '" & txtTitle.Text & "' has been inseted successfully")
ClearBox()
Catch ex As Exception
MsgBox(ex.Message())
End Try
myConnection.Close()
End Sub
回答by bunny
Module Module1 Public con As System.Data.SqlClient.SqlConnection Public com As System.Data.SqlClient.SqlCommand Public ds As System.Data.SqlClient.SqlDataReader Dim sqlstr As String
Module Module1 Public con As System.Data.SqlClient.SqlConnection Public com As System.Data.SqlClient.SqlCommand Public ds As System.Data.SqlClient.SqlDataReader Dim sqlstr As String
Public Sub main()
con = New SqlConnection("Data Source=.....;Initial Catalog=.....;Integrated Security=True;")
con.Open()
frmopen.Show()
'sqlstr = "select * from name1"
'com = New SqlCommand(sqlstr, con)
Try
com.ExecuteNonQuery()
'MsgBox("success", MsgBoxStyle.Information)
Catch ex As Exception
MsgBox(ex.Message())
End Try
'con.Close()
'MsgBox("ok", MsgBoxStyle.Information, )
End Sub
End Module
终端模块
回答by Jason Conville
Please try to wrap the use of your connections (including just opening) inside a USING block. Assuming the use of web.config for connection strings:
请尝试将您的连接的使用（包括仅打开）包装在一个 USING 块中。假设使用 web.config 作为连接字符串：
Dim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("web.config_connectionstring").ConnectionString)
Dim query As New String = "select * from Table1"
Dim command as New SqlCommand(query, connection)
Using connection
connection.Open()
command.ExecuteNonQuery()
End Using
And PARAMETERIZE anything user-entered.. please!
并参数化用户输入的任何内容..拜托！
