vb.net - error in my vb program of cmd.executenonquery

Question

I am getting the following error:

Unhandled Exception: System.Data.SqlClient.SqlException: Incorrect syntax near '500'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DATACONSOLE.Module1.Main() in G:000000SRIKANTHProjectsVBdatabaseDATACONSOLEDATACONSOLEModule1.vb:line 70

My database table:

CREATE TABLE new
(
    [SNO] INT  PRIMARY KEY, 
    [SNAME] NCHAR(12) NULL, 
    [COURSE] NCHAR(10) NULL, 
    [TOTALMARKS] INT NULL DEFAULT 700, 
    [PERCENTAGEMARKS] DECIMAL(9, 2) NULL, 
    [GRADE] NCHAR(10) NULL
)

My code:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Sql

Module Module1

    Sub Main()

        Dim CON As SqlConnection
        Dim CMD As SqlCommand
        Dim SOURCE As String
        Dim COMMAND As String


        SOURCE = "Data Source=DESKTOP-20RTV69SQLEXPRESS;Initial Catalog=VBONE;Integrated Security=True;Pooling=False"

        CON = New SqlConnection(SOURCE)

        CON.Open()
        Dim SNO As Integer
        Dim NAME As String
        Dim COURSE As String
        Dim MARKS As Integer
        Dim TOTAL As Integer = 700
        Dim PERCENTAGE As Double
        Dim GRADE As String


        Console.Write("ENTER SNO : ")
        SNO = Convert.ToInt32(Console.ReadLine())

        Console.Write("ENTER NAME : ")
        NAME = Console.ReadLine()

        Console.Write("ENTER COURSE : ")
        COURSE = Console.ReadLine()

        Console.Write("ENTER MARKS : ")
        MARKS = Convert.ToInt32(Console.ReadLine())

        PERCENTAGE = (MARKS / TOTAL) * 100


        If (PERCENTAGE > 90) Then
            GRADE = "'A'"
        ElseIf (PERCENTAGE > 70) Then
            GRADE = "'B'"
        ElseIf (PERCENTAGE > 60) Then
            GRADE = "'C'"
        ElseIf (PERCENTAGE > 50) Then
            GRADE = "'D'"
        Else
            GRADE = "'F'"
        End If

        COMMAND = "INSERT INTO NEW(SNO,NAME,COURSE,MARKSOBTAINED,PERCENTAGEMARKS,GRADE)
VALUES(" & SNO & " , '" & NAME & "' , '" & COURSE & "' " & MARKS & "," & PERCENTAGE & ",'" & GRADE & "')"

        CMD = New SqlCommand(COMMAND, CON)
        CMD.ExecuteNonQuery()
        Console.WriteLine("---RECORD IS INSERTED---")
        Console.ReadLine()

    End Sub

End Module

Please could you find the solution to my problem and give me some suggestions for not repeating such mistakes.

Answer 1

What you need to do is use SQL parameters for the values that you want pass to the database.

This is not intended to be a code review, but in showing how to add SQL parameters I noted some other points:

• It is normal to use lowerCamelCase for variable names.
• Declare variables close to the point at which they are given values.
• Some sort of user input validation should be done.
• Code should be contained in areas of one purpose, e.g. you have CON.Open() a long way from the code which uses the connection.

• Using an SqlConnectionStringBuilder makes it easier to generate a correct connection string. There is no point to disabling connection pooling in this case.

• The Using construct in VB.NET is used to make sure that unmanaged resources (things outside the control of the .NET framework) are released after a resource is finished with (even if something goes wrong).

So, I came up with:

Option Infer On
Option Strict On

Imports System.Data.SqlClient

Module Module1

    Sub Main()

        ' Get the user input...
        Dim sno As Integer = -1
        Dim name As String = Nothing
        Dim course As String = Nothing
        Dim marksObtained As Integer = -1
        Dim maxPossibleMarks As Integer = 700

        While sno < 0
            Console.Write("ENTER SNO: ")
            Dim userInput = Console.ReadLine()
            If Not Integer.TryParse(userInput, sno) Then
                sno = -1
                Console.WriteLine("Please enter a number...")
            End If
        End While

        While String.IsNullOrWhiteSpace(name)
            Console.Write("ENTER NAME: ")
            name = Console.ReadLine()
            Console.WriteLine("Please enter the name...")
        End While

        While String.IsNullOrWhiteSpace(course)
            Console.Write("ENTER COURSE: ")
            course = Console.ReadLine()
            Console.WriteLine("Please enter the course...")
        End While

        While marksObtained < 0
            Console.Write("ENTER MARKS: ")
            Dim userInput = Console.ReadLine()
            If Not Integer.TryParse(userInput, marksObtained) Then
                marksObtained = -1
                Console.WriteLine("Please enter a number...")
            End If
        End While

        ' Process the user input...
        Dim percentageMarks = (marksObtained / maxPossibleMarks) * 100

        Dim grade As String

        If percentageMarks > 90 Then
            grade = "'A'"
        ElseIf percentageMarks > 70 Then
            grade = "'B'"
        ElseIf percentageMarks > 60 Then
            grade = "'C'"
        ElseIf percentageMarks > 50 Then
            grade = "'D'"
        Else
            grade = "'F'"
        End If

        ' Save the information to the database...
        Dim csb As New SqlConnectionStringBuilder With {
            .DataSource = "DESKTOP-20RTV69SQLEXPRESS",
            .InitialCatalog = "VBONE",
            .IntegratedSecurity = True}

        Using conn As New SqlConnection(csb.ConnectionString)
            Dim sql = "INSERT INTO NEW(SNO, NAME, COURSE, MARKSOBTAINED, PERCENTAGEMARKS, GRADE) VALUES(@sno, @name ,@course, @marksObtained, @percentageMarks, @grade)"

            Using sqlCmd As New SqlCommand(sql, conn)
                sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@sno", .SqlDbType = SqlDbType.Int, .Value = sno})
                sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@name", .SqlDbType = SqlDbType.NChar, .Size = 12, .Value = name})
                sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@course", .SqlDbType = SqlDbType.NChar, .Size = 10, .Value = course})
                sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@marksObtained", .SqlDbType = SqlDbType.Int, .Value = marksObtained})
                sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@percentageMarks", .SqlDbType = SqlDbType.Decimal, .Precision = 9, .Scale = 2, .Value = percentageMarks})
                sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@grade", .SqlDbType = SqlDbType.NChar, .Size = 10, .Value = grade})

                conn.Open()
                sqlCmd.ExecuteNonQuery()

            End Using

        End Using

        Console.WriteLine("---RECORD IS INSERTED---")
        Console.ReadLine()

    End Sub

End Module

There is an opportunity in the code to check that marksObtained <= maxPossibleMarks.

Incidentally, computed values shouldn't really be stored in a database (the PERCENTAGEMARKS and GRADE columns) - you would know from testing if there was some performance problem which required that. The maximum possible score should have been stored in the database, and probably the date on which the score was obtained.