CODEWITHSUNDEEP
vb.netoledbconnection
Frank Longo
Frank Longo

Reputation: 59

ExecuteNonQuery: Connection property has not been initialized

I'm getting the following error

ExecuteNonQuery: Connection property has not been initialized.

When the following code is run

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click

    Using con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Comp-296\Project1\Project1\Game_time.mdb")

        Using cmd = New OleDbCommand("UPDATE User-scores SET User_Name = @User_Name Game_Name = @Game_Name Score = @Score, [Time/Date] = @dt")

            con.Open()
            cmd.Parameters.Add("@User_Name", OleDbType.VarWChar).Value = txtUser.Text
            cmd.Parameters.Add("@Game_Name", OleDbType.VarWChar).Value = txtGame.Text
            cmd.Parameters.Add("@Score", OleDbType.VarWChar).Value = txtScore.Text
            cmd.Parameters.Add("@dt", OleDbType.Date).Value = Convert.ToDateTime(txtDate.Text)

            cmd.ExecuteNonQuery()

            MessageBox.Show("Data Update successfully")
        End Using
    End Using

End Sub

Did I forget to put something in the parameter?

Upvotes: 0

Views: 2010

Answers (4)

Hadi
Hadi

Reputation: 37358

You have to pass the connection as a parameter to the OledbCommand

Just use the following code

Using con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Comp-296\Project1\Project1\Game_time.mdb") 

    Using cmd = New OleDbCommand("UPDATE [User-scores] SET [User_Name] = ?, [Game_Name] = ?, Score = ?, [Time/Date] = ? WHERE id = 1", con) 
        con.Open() 
        cmd.Parameters.Add("@p1", OleDbType.VarWChar).Value = txtUser.Text 
        cmd.Parameters.Add("@p2", OleDbType.VarWChar).Value = txtGame.Text 
        cmd.Parameters.Add("@p3", OleDbType.VarWChar).Value = txtScore.Text 
        cmd.Parameters.Add("@p4", OleDbType.Date).Value = Convert.ToDateTime(txtDate.Text) 

        cmd.ExecuteNonQuery() 

        MessageBox.Show("Data Update successfully") 
    End Using 
End Using

It look like you have to use ? mark to pass parameter to an OledbCommand (your method is for SQLCommand)

Read more about Oledb Parameters

Upvotes: 1

Tushar Gupta
Tushar Gupta

Reputation: 15933

You forgot to add connection to command, Update cmd , add con like below

 Using cmd = New OleDbCommand("UPDATE User-scores SET User_Name = @User_Name Game_Name = @Game_Name Score = @Score, [Time/Date] = @dt",con)

Update : MISSING COMMA IN UPDATE

 @User_Name, Game_Name = @Game_NamE, Score = @Score

Upvotes: 0

Nkosi
Nkosi

Reputation: 247471

You can have the connect create the command with CreateCommand. That way the connection will already be associated with the created command. This means though, that you would have to set the command text on the CommandText property

Using con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Comp-296\Project1\Project1\Game_time.mdb")

    Using cmd = con.CreateCommand()
        con.Open()
        cmd.CommandText = "UPDATE User-scores SET User_Name = @User_Name, Game_Name = @Game_Name, Score = @Score, [Time/Date] = @dt"
        cmd.Parameters.Add("@User_Name", OleDbType.VarWChar).Value = txtUser.Text
        cmd.Parameters.Add("@Game_Name", OleDbType.VarWChar).Value = txtGame.Text
        cmd.Parameters.Add("@Score", OleDbType.VarWChar).Value = txtScore.Text
        cmd.Parameters.Add("@dt", OleDbType.Date).Value = Convert.ToDateTime(txtDate.Text)

        cmd.ExecuteNonQuery()


        MessageBox.Show("Data Update successfully")
    End Using
End Using

Upvotes: 0

ADyson
ADyson

Reputation: 62060

You haven't associated the Command with the Connection. The code doesn't know which connection (in theory you could have several) to run the command against.

You can associate a command with a particular connection in two different ways - using the constructor, or the property:

Using cmd = New OleDbCommand("UPDATE User-scores SET User_Name = @User_Name Game_Name = @Game_Name Score = @Score, [Time/Date] = @dt",con)

Or:

cmd.Connection = con

(on the line after you declare cmd)

Upvotes: 0

Related Questions