Geek Answers Handbook

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

In the load event of the form, 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 causes the following error:

 ExecuteNonQuery: Connection property has not been initialized
+4
source share
5 answers

  • The purpose of the connections . You do not set the connection property of 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 processing . You also need to remove "MyConnection.Open" from your download handler. Just open it and close it in the click handler, as you are doing now. This does not cause an error.

  • Parameterized SQL . You need to use SQL parameters even though you are not using a stored procedure. This is not the cause of your error. As Conrad reminded me, your source code is outputting values ​​directly from the user to the SQL statement. Malicious users will steal your data if you do not 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)
+8
source

You need to set the Connection property in the command:

 myCommand.Connection = myConnection
+5
source

To a large extent, this means an error message - the Connection property of the SqlCommand object was not assigned to an open connection (in this case you called myConnection ).

In addition, advice is given here. Read some sql parameters - doing sql concatenation with user input without any conformance checks is a way to attack SQL injection .

This is one way to do this:

 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
+4
source

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

0
source

Please try to wrap using your connections (including only opening) inside the USING block. Assuming using 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 entered by the user .. please!

0
source

More articles:


All Articles