VB.NET SQL Server插入-ExecuteNonQuery:连接属性尚未初始化 [英] VB.NET SQL Server Insert - ExecuteNonQuery: Connection property has not been initialized

问题描述

在表单加载事件中，我连接到SQL Server数据库:

In the form load event, I connect to the SQL Server database:

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

在Insert事件中，我使用以下代码:

Here in the Insert event, I use the following code:

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

它会产生以下错误:

ExecuteNonQuery: Connection property has not been initialized

推荐答案

1. 连接分配-您未设置SQLCommand的连接属性.您无需添加任何代码即可执行此操作. 这是导致您出错的原因.

1. 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)

连接处理-您还需要从负载处理程序中删除"MyConnection.Open".只需打开它，然后按照您的当前操作在单击处理程序"中将其关闭即可. 这不会导致错误.

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.

参数化SQL -尽管您没有使用存储过程，但仍需要使用SQL参数. 这不是造成您错误的原因.正如 Conrad 提醒我的那样，您的原始代码将用户的值直接转储到SQL语句中.除非您使用SQL参数，否则恶意用户将窃取您的数据.

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 Conrad reminded 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)

这篇关于VB.NET SQL Server插入-ExecuteNonQuery:连接属性尚未初始化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！