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.

I would use parameters for this kind of work however I think the issue might lie with the trailing apostrophe at the end )' There might be more to it than that but it's difficult without debugging. — Bugs
– Bugs, Commented Oct 27, 2016 at 15:04
What kind of Type do your Question, Answer, Type and Mark properties have? The error might me solved if you use the .ToString() after then. @Scrub — Quima
– Quima, Commented Oct 27, 2016 at 15:13
The .mdf file you specify in your connection string is a proper SQL Server database file - not a SQL Server CE file (those would be .sdf) — marc_s
– marc_s, Commented Oct 27, 2016 at 15:13

Quima · Accepted Answer · 2016-10-28 12:03:18Z

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

edited Oct 28, 2016 at 12:03

answered Oct 27, 2016 at 15:37

Quima

92411 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Scrub Over a year ago

Thank you very much @AugustoQ. Question, Answer and Type are Var Char. Mark is Int. 'command.Parameters.Add("@Question", SqlDbType.VarChar, 150) command.Parameters.Add("@Answer", SqlDbType.VarChar, 150) command.Parameters.Add("@Type", SqlDbType.VarChar, 50) command.Parameters.Add("@Mark", SqlDbType.Int)' However, an error occurs at 'command.ExecuteNonQuery()'

Scrub Over a year ago

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll. Additional information: ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

Quima Over a year ago

@Scrub There is an error in my answer, use command.Transaction = command.Connection.BeginTransaction rather than Dim Transaction = command.Connection.BeginTransaction, sorry about that.

Collectives™ on Stack Overflow

Add multiple values to a SQL Server VB.NET Database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related