CODEWITHSUNDEEP
sqlvb.net
H97
H97

Reputation: 51

Program not writing data into database VB

I am attempting to have the program copy the grade achieved by the user and write it into a database. The problem is it keeps coming up with the following error:

System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.'

The code to calculate the grade is:

Private Sub StatsCalc()

    Dim Grade As String
    Dim sqlString As String

    If NoQ = 5 Then
        If Score = "0" Then
            Grade = "U"
        Else
            If Score = "1" Then
                Grade = "E"
            Else
                If Score = "2" Then
                    Grade = "D"
                Else
                    If Score = "3" Then
                        Grade = "C"
                    Else
                        If Score = "4" Then
                            Grade = "B"
                        Else
                            Grade = "A"
                        End If
                    End If
                End If
            End If
        End If

        sqlString = "INSERT INTO loginDetails (ArGrade) WHERE UserName = '" & username & "'"
        sqlString = sqlString & " VALUES ('"
        sqlString = sqlString & Grade & "')" & ";"

        runSQL(sqlString)

    End If

    Percentage = (Score / NoQ) * 100
End Sub

runSQL is in a separate module and is as follows:

Module LoginModule

    Public Function runSQL(ByVal query As String) As DataTable

        Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=login.accdb") 
        Dim DataAdapter As OleDb.OleDbDataAdapter 

        Connection.Open() 
        DataAdapter = New OleDb.OleDbDataAdapter(query, Connection)
        dt.Clear() 
        DataAdapter.Fill(dt) 
        Connection.Close() 
        Return dt 

    End Function
End Module

Thanks for any help

Upvotes: 0

Views: 97

Answers (3)

H97
H97

Reputation: 51

Managed to get it working by:

    Using cn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=login.accdb"),
            cmd As New OleDb.OleDbCommand("UPDATE loginDetails Set ArGrade ='" & Grade & "', ArPercentage='" & Percentage & "', ArMarks='" & Score & "'" & "WHERE UserName ='" & username & "'", cn)
        cn.Open()
        cmd.ExecuteNonQuery()
    End Using

Upvotes: 0

Mary
Mary

Reputation: 15091

Always use parameters to avoid errors in concatenating strings and, most important, avoid Sql Injection (can destroy your database).

Your runSql function is not adequate to handle parameters. Also since you are running an Insert command, no records are returned so there will either be an empty DataTable or Nothing.

Database objects need to be closed and disposed. Using...End Using blocks handle this for you even if there is an error.

Access does not care about parameter names. I use them just to make the code easier to read. The parameters in Access must be added in the same order that they appear in the Sql command.

I wasn't sure if you mean to Insert a new record or Update an existing record. You show an Insert command but add a Where clause which doesn't make sense. I gave your both Functions so you can figure out what you are doing. Notice that order of the parameters added to the command are reversed because they appear in a different order in the Sql command.

Private Sub StatsCalc()
    Dim NoQ As Integer '= Get the value from somewhere
    Dim Score As String = "Get the value form Somewhere"
    Dim Percentage As Double
    Dim Grade As String
    Dim user As String = "Get the value from somewhere"

    If NoQ <> 5 Then
        MessageBox.Show("No database update. NoQ does not equal 5.")
        Return
    End If

    Select Case Score
        Case "0"
            Grade = "U"
        Case "1"
            Grade = "E"
        Case "2"
            Grade = "D"
        Case "3"
            Grade = "C"
        Case "4"
            Grade = "B"
        Case "5"
            Grade = "A"
        Case Else
            Grade = "X" 'Invalid entry
    End Select

    'Dim retVal = InsertGrade(user, Grade)
    'or
    Dim retVal = UpdateGrade(user, Grade)
    If retVal = 1 Then
        MessageBox.Show("Successful update")
    Else
        MessageBox.Show("Error in update")
    End If

    Percentage = (CInt(Score) / NoQ) * 100
End Sub
Public Function UpdateGrade(ByVal username As String, grade As String) As Integer
    Dim retVal As Integer
    Using Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=login.accdb"),
            cmd As New OleDb.OleDbCommand("Update loginDetails  Set ArGrade = @Grade Where UserName = @UserName;", Connection)
        cmd.Parameters.Add("@Grade", OleDbType.VarChar, 10).Value = grade
        cmd.Parameters.Add("@UserName", OleDbType.VarChar, 100).Value = username
        Connection.Open()
        retVal = cmd.ExecuteNonQuery
    End Using
    Return retVal
End Function

Public Function InsertGrade(ByVal username As String, grade As String) As Integer
    Dim retVal As Integer
    Using Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=login.accdb"),
            cmd As New OleDb.OleDbCommand("Insert Into loginDetails  (UserName, ArGrade) Values(@UserName,@Grade);", Connection)
        cmd.Parameters.Add("@UserName", OleDbType.VarChar, 100).Value = username
        cmd.Parameters.Add("@Grade", OleDbType.VarChar, 10).Value = grade
        Connection.Open()
        retVal = cmd.ExecuteNonQuery
    End Using
    Return retVal
End Function

Upvotes: 1

zip
zip

Reputation: 4061

sqlString = "INSERT INTO loginDetails (ArGrade) WHERE UserName = '" & username & "'"
sqlString = sqlString & " VALUES ('"
sqlString = sqlString & Grade & "')" & ";"

with

sqlString = "INSERT INTO loginDetails (ArGrade, UserName )"
sqlString = sqlString & " VALUES ('" & Grade & "', '" & username & "')" & ";"

I don't know how your table was created but I'd make sure that the field UserName is not of text type but numeric

Upvotes: 0

Related Questions