ExecuteNonQuery: Connection property has not been

问题:

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?

回答1:

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

回答2:

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)

回答3:

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

回答4:

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