Blackjack project fails to update Access database

Question

I have a simple blackjack game that I am trying to get to update a database by adding 2 simple values to it. I want it to add the username and the players' score to the database.

After watching an online tutorial this is the code I tried:

Imports System.Data.OleDb

Public Class Form1

    'for updating database
    Dim provider As String
    Dim dataFile As String
    Dim connString As String
    Dim myConnection As OleDbConnection = New OleDbConnection

   Private Sub ButtonSaveScore_Click(sender As Object, e    As EventArgs) Handles ButtonSaveScore.Click

        provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
        dataFile = "F:\Documents\Class Documents\CSC289 - K6A - Programming Capstone Project\Project\BlackJack\BlackJack\Scoreboard.accdb"
        connString = provider & dataFile
        myConnection.ConnectionString = connString
        myConnection.Open()
        Dim str As String
        str = "Update [Scores] set [UserName] = '" & playerName & "',[Score] =' " & wins & "' where [ID] = NEW"
        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)

        Try
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            myConnection.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

I get the error:

one or more variables is not provided

The first time I hit the save button. The second time it breaks the application and it says that I "cannot adjust the files current state it is already open. It then highlights myConnection.ConnectionString = connString.

You forgot the ' around `NEW` so it think it's a column name and for access all names it does not find in the table are parameters. — litelite, Jul 27 '17 at 12:43
Also, your code is at risk of [SQL Injection](https://stackoverflow.com/questions/601300/what-is-sql-injection) you should use prepared statements with proper parameters. — litelite, Jul 27 '17 at 12:44
for your second problem. It's because you must close the database before trying to reopen it. Currently the DB is only closed if the query did not throw anything. You should move the connection close into a [`Finally` block](https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/try-catch-finally-statement). Alternatively, you can use a [`Using` block](https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/using-statement) — litelite, Jul 27 '17 at 12:49

Bugs · Answer 1 · 2017-07-27T15:24:22.690

Consider implementing Using:

Sometimes your code requires an unmanaged resource, such as a file handle, a COM wrapper, or a SQL connection. A Using block guarantees the disposal of one or more such resources when your code is finished with them. This makes them available for other code to use.

Here is some sample code:

Using con As New OleDbConnection(connectionString),
      cmd As New OleDbCommand(commandString, con)

    con.Open()

    cmd.ExecuteNonQuery()

End Using

This will handle the closing and disposing of your objects.

I would also consider using parameters to avoid SQL injection. See Bobby Tables for more information on this.

Here is some sample code:

Using con As New OleDbConnection(connectionString),
      cmd As New OleDbCommand("UPDATE [Scores] SET [UserName] = ?, [Score] = ? WHERE [ID] = ?", con)

    con.Open()

    cmd.Parameters.Add("@Username", OleDbType.[Type]).Value = playerName
    cmd.Parameters.Add("@Score", OleDbType.[Type]).Value = wins

    'I've popped this into a parameter as I'm unsure what it is. I'll leave that for you to decide
    cmd.Parameters.Add("@ID", OleDbType.[Type]).Value = "NEW"

    cmd.ExecuteNonQuery()

End Using

Note that I have used OleDbType.[Type]. You will want to replace this with the data type you have specified for your columns.

With OleDb the order of your parameters is important, not the naming. Ensure that you create the parameters as they appear in your command as shown above.

Blackjack project fails to update Access database

1 Answers1