Reputation: 43
I'm trying to delete an entry from my database. But when the ExecuteNonQuery has to do it's job it can't find the enabled connection and give me this error :
System.InvalidOperationException :'ExecuteNonQuery : connection property has not been initialized'
Here is what I did :
Dim delete As New OleDbCommand
Dim da As OleDbDataAdapter
Dim ds As DataSet
Dim dt As DataTable
initConnectionDtb(pathDtb)
openConnection()
If TextBox2.Text <> "" Then
delete.CommandText = "delete FROM USERS WHERE NAME = '" & TextBox2.Text & "'"
delete.CommandType = CommandType.Text
delete.ExecuteNonQuery()
MsgBox("USER HAS BEEN DELETED")
Else
MsgBox("ERROR")
End If
I could check if it was properly connected to the Database thanks to connectionName.State
I also enterily rewrote the connetion to the database in the function but ExecuteNonQuery still couldn't connect even though the connection was opened
I saw that i'm not the only one on this website but none of the previous answers have helped me.
Upvotes: 0
Views: 264
Reputation: 43
Here's what i used to initialize my connection :
Public Function initConnectionDtb(ByVal path As String) As Boolean
initConnectionDtb = True
Connection = New OleDbConnection
Try
Connection.ConnectionString = "provider=microsoft.jet.oledb.4.0;" & "data source= " & path & ";"
Catch ex As Exception
Return False
End Try
End Function
Public Function openConnection() As Boolean
openConnection = True
Try
Connection.Open()
MsgBox(Connection.State) 'to test if my connection really openned in my previous post
Catch ex As Exception
MsgBox(ex.Message)
Return False
End Try
End Function
Public Sub closeConnection()
If Not IsNothing(Connection) Then
If Connection.State = ConnectionState.Open Then
Connection.Close()
End If
MsgBox(Connection.State)
Connection.Dispose()
Connection = Nothing
End If
End Sub
So far it worked for everything i tried (adding someone to the database for exemple)
Upvotes: 0
Reputation: 562
@Filburt pointed out, how are you assigning your connection to your command object. Here is an example :
Using connection As OleDbConnection = New OleDbConnection(connectionString)
connection.Open()
Dim command As OleDbCommand = New OleDbCommand(queryString, connection)
command.ExecuteNonQuery()
End Using
In your code, you need to assign the connection object to your command object. We can't see what code you have in initConnectionDtb(pathDtb)
or openConnection()
To adapt this to your code:
delete.Connection = <<your connection object here>>
delete.CommandText = "delete FROM USERS WHERE NAME = '" & TextBox2.Text & "'"
delete.CommandType = CommandType.Text
delete.ExecuteNonQuery()
Another note: look into parameterizing your query strings instead of hand stringing the values. This will prevent issues with TextBox2.Text
having a value like O'Toole which will cause a syntax error as well as SQL Injection.
Upvotes: 2