Add multiple values to a SQL Server VB.NET Database

Question

I am attempting to add multiple values to a SQL Server database using VB.NET.

I have included the following namespaces:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe

On start-up, I have declared the SQL connection:

con.ConnectionString = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=""G:\Program\X\Database1.mdf"";Integrated Security=True"

I have created a VB Windows Form that enables the user to add, edit and remove questions for a Question Paper.

When the user clicks the "Save Questions" Button, the questions are saved a .txt file.

This should then call the InsertQuestion subroutine:

 con.Open()
 InsertQuestion(con)
 con.Close()

InsertQuestion subroutine:

Sub InsertQuestion(ByVal con As SqlConnection)

    Using con

    Dim command As New SqlCommand(("INSERT INTO Table VALUES('" & Collection(0).Question & "','" & Collection(0).Answer & "','" & Collection(0).Type & "','" & Collection(0).Mark & "')'"), con)                                                         

        command.ExecuteNonQuery()

        con.Close()

    End Using

End Sub

This should add this data to the table. The table has five columns - ID, Question, Answer, Type, Mark. ID is the number of the question, which is set to auto-increment.

From the first element of the array, Question from index(0) should be added to column 2 (under Question), Answer from index(0) should be added to column 3 (under Answer)...and so forth.

However, when the program is run, and the user clicks "Save Questions", an error occurs:

An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll

Additional information: Operator '&' is not defined for string "INSERT INTO QuestionTable VALUES" and type 'RuntimeType'.

I thus, would greatly appreciate advice on how I would go about fixing this command/code to enable the data to be added to the table.

Additionally, how would I go about adding further questions to the table from index 1 and ect...

Many thanks.

Answer 1

As mentioned in the comments, creating a parameterized command can avoid errors in SQL statements.

Also, answering your other question, creating a transaction is a way of inserting to the database many values at a time:

Sub InsertQuestion(ByVal con As SqlConnection)

Using con

    Dim command As New SqlCommand("INSERT INTO Table VALUES(@Question, @Answer, @Type, @Mark)", con)                                                         

    command.Parameters.Add("@Question", YourType)
    command.Parameters.Add("@Answer", YourType)
    command.Parameters.Add("@Type", YourType)
    command.Parameters.Add("@Mark", YourType)

    command.Transaction = command.Connection.BeginTransaction

    For i = 0 To Collection.Count - 1
        command.Parameters("@Question").Value = Collection(i).Question
        command.Parameters("@Answer").Value = Collection(i).Answer
        command.Parameters("@Type").Value = Collection(i).Type
        command.Parameters("@Mark").Value = Collection(i).Mark
        command.ExecuteNonQuery()
    Next

    command.Transaction.Commit()

    con.Close()

End Using

End Sub