CODEWITHSUNDEEP
vb.net
FoxLeCredule
FoxLeCredule

Reputation: 43

Having the ExecuteNonQuery : connection property has not been initialized

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

Answers (2)

FoxLeCredule
FoxLeCredule

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

Pete -S-
Pete -S-

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

Related Questions