VB.Net and SQL Command Unhandled Exception

Question

I am building a simple program for bulk load into SQL. However I cannot figure out this error. The raw code is below, then the translated code without the textbox references.

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles insert.Click

    Dim SQLCONN As New SqlConnection
    Dim SQLCMD As New SqlCommand
    SQLCONN = New SqlConnection("Server=" + server.Text + ";Database=" + database.Text + ";Integrated security=True")
    SQLCONN.Open()
    SQLCMD = New SqlCommand("BULK INSERT " + table.Text +
        " FROM " + path.Text +
        "  With (FIRSTROW = '" + firstrow.Text + "',
            FIELDTERMINATOR = '" + seperator.Text + "',
            ROWTERMINATOR= '\n');", SQLCONN)
    SQLCMD.ExecuteNonQuery()
    SQLCONN.Close()

End Sub

Here is what the SQL portion would translate to

SQLCMD = New SqlCommand("BULK INSERT test1
         FROM  'C:\Program Files\Servers\FFA\csgo\maplist.txt'
          With (FIRSTROW = '2',
            FIELDTERMINATOR = ' ',
            ROWTERMINATOR= '\n')";, SQLCONN)

Here is the error I get:

System.Data.SqlClient.SqlException: 'Incorrect syntax near 'C:'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.'

Can anyone help me figure out why this is erroring out?

Answer 1

You need to remove the double quote from your FIRSTROW value

SQLCMD = New SqlCommand("BULK INSERT test1
         FROM  'C:\Program Files\Servers\FFA\csgo\maplist.txt'
          With (FIRSTROW = 2,
            FIELDTERMINATOR = ' ',
            ROWTERMINATOR= '\n')";, SQLCONN)

Your class will be like as follow

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles insert.Click

    Dim SQLCONN As New SqlConnection
    Dim SQLCMD As New SqlCommand
    SQLCONN = New SqlConnection("Server=" + server.Text + ";Database=" + database.Text + ";Integrated security=True")
    SQLCONN.Open()
    SQLCMD = New SqlCommand("BULK INSERT " + table.Text +
        " FROM " + path.Text +
        "  With (FIRSTROW = " + firstrow.Text + ",
            FIELDTERMINATOR = '" + seperator.Text + "',
            ROWTERMINATOR= '\n');", SQLCONN)
    SQLCMD.ExecuteNonQuery()
    SQLCONN.Close()

End Sub