How to add a record to an access database

I made a quiz which should store the users high score in a database however I keep getting an error satying 'Not allowed to change the 'ConnectionString'

Here is my code:

Imports System.Data.OleDb
Public Class Pure

Dim pro As String
Dim connstring As String
Dim command As String
Dim myconnection As OleDbConnection = New OleDbConnection

And here is the rest of the code which is supposed to add the highscore to the access database

Private Sub btnSummary_Click(sender As Object, e As EventArgs) Handles btnSummary.Click
        pro = "provider=microsoft.ACE.OLEDB.12.0;Data Source=flashcard login.accdb" 'Establish connection with database
        connstring = pro
        myconnection.ConnectionString = connstring
        myconnection.Open() 'Open connection
        If lblScore11.Text > lblHighScore.Text Then 'If current socre is greater than high score
            lblScore11.Text = lblHighScore.Text
            command = " insert into results ([score]) values ('" & lblHighScore.Text & "')"
            Dim cmd As OleDbCommand = New OleDbCommand(command, myconnection) 'Establish connection
            cmd.Parameters.Add(New OleDbParameter("score", CType(lblHighScore.Text, String)))
        End If
    End Sub

I added this try-catch statement at the end (Inside the if statement)

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

CodePudding user response：

There are other issues, but the big one is you actually need to run the query:

cmd.ExecuteNonQuery()

Also, this query is still crazy-vulnerable to injection issues. This kind of string substitution is NEVER okay for an SQL string:

 values ('" & lblHighScore.Text & "')"

Here's a full re-write with better patterns:

Dim connString As String = "provider=microsoft.ACE.OLEDB.12.0;Data Source=flashcard login.accdb" 

Private Sub btnSummary_Click(sender As Object, e As EventArgs) Handles btnSummary.Click

    If lblScore11.Text <= lblHighScore.Text Then Return
    lblHighScore.Text = lblScore11.Text

    Using conn As New OleDbConnection(connSstring), _
          cmd As New OleDbCommand("INSERT INTO results ([score]) VALUES (?)", conn)
        'Use the actual OleDbType that maps to the database column and length here
        cmd.Parameters.Add("score", OleDbType.LongVarWChar).Value = lblScore11.Text
        conn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub