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?

That should end up asFIRSTROW = 2, i.e. without the quotes. Also, the string concatenation operator in VB.NET is &, not +. — Andrew Morton
– Andrew Morton, Commented Apr 8, 2020 at 8:11

Caius Jard · Accepted Answer · 2020-04-08 09:00:49Z

Your SQL has quotes around the filename but the VB version does not (unless the textbox itself contains quotes, but I think it unlikely given the error message):

Your SQL would be a lot more readable if you use string interpolation and do some preprocessing outside of building the string:

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()

    Dim p = path.Text.Replace("'","''")
    Dim f = seperator.Text.Replace("'","''")

    SQLCMD = New SqlCommand($"BULK INSERT QUOTENAME({table.Text})
                FROM '{p}'
                WITH (FIRSTROW = {firstrowNumericUpdown.Value}
                FIELDTERMINATOR = '{f}',
                ROWTERMINATOR= '\n')", SQLCONN)
    SQLCMD.ExecuteNonQuery()
    SQLCONN.Close()

End Sub

You should also do your utmost to prevent SQL injection with this; you're handing the user several textboxes into which they type something and that could be very dangerous if they decide to write some SQL in the textbox

Consider using QUOTENAME the table name
Use a NumericUpDown for your FIRSTROW
Consider replacing ' with '' on other fields
Consider limiting the length of the seperator textbox to be MaxLength=1

If you don't know what SQL Injection Hacking is, take a read of http://bobby-tables.com

Esat Erkec · Accepted Answer · 2020-04-08 08:24:38Z

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

Darin Johns · Accepted Answer · 2020-04-09 03:09:16Z

0

Thanks guys, all of your responses helped me quite a bit. I'm deciding to go a different route with project, thanks again!

answered Apr 9, 2020 at 3:09

Darin Johns

51 bronze badge

Collectives™ on Stack Overflow

VB.Net and SQL Command Unhandled Exception

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related