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

Question

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: Connection property has not been initialized

score 8 · Answer 1 · edited May 23 '17 at 11:52

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

Lets just hope little bobby tables doesn't author any books that end up in this db — Conrad Frix, Jun 15 '11 at 15:39
Are you against the concept of a rapidly user-enhanced evolving database? — Brian Webster, Jun 15 '11 at 15:44

score 5 · Answer 2 · answered Jun 15 '11 at 15:24

5

You need to set the Connection property on the command:

myCommand.Connection = myConnection

answered Jun 15 '11 at 15:24

Oded

489,969
99
883
1,009

score 4 · Answer 3 · answered Jun 15 '11 at 15:44

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

I think you need to add "@" to the front of the parameter names in the calls to AddWithValue — Conrad Frix, Jun 15 '11 at 15:48
A community comment at the bottom of the MSDN page on [SqlCommand.Parameters](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx) claimed it wasn't necessary. I didn't compile and test the above, so, YMMV. — Kilanash, Jun 15 '11 at 15:58
I checked and you're right you don't need to add the "@" Nice I learned something new. +1 — Conrad Frix, Jun 15 '11 at 16:11

score 0 · Answer 4 · answered Apr 24 '14 at 06:23

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

score 0 · Answer 5 · answered Aug 26 '14 at 21:45

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!

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

5 Answers5

Linked