VB.NET SQL Server Insert - ExecuteNonQuery: Connection property has not been initialized

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

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

And It produces the following error:

ExecuteNonQuery: Connecti开发者_开发问答on property has not been initialized

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)
   

2. 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.

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

You need to set the Connection property on the command:

myCommand.Connection = myConnection

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

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 injection attacks happen.

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

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

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:

    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!