sql - VB.NET SQL Server 插入 - ExecuteNonQuery : Connection property has not been initialized

Question

在表单加载事件中，我连接到 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

在 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

它产生以下错误:

ExecuteNonQuery: Connection property has not been initialized

Answer 1

连接分配 - 您没有设置 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)

连接处理 - 您还需要从 Load Handler 中删除“MyConnection.Open”。只需打开它并在您的 Click Handler 中关闭它，就像您目前所做的那样。这不会导致错误。

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

Dim CMD As New SqlCommand("Select * from MyTable where BookID = @BookID")
CMD.Parameters.Add("@BookID", SqlDbType.Int).Value = CInt(TXT_BookdID.Text)