Line 1: Incorrect syntax near ','. Unclosed quotation mark before the character ...

**dougancil** · May 11 '10, 09:02 PM

Extra quotation being thrown by a line of code

I have the following code

Code:

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub
    Protected Sub Submit1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit1.Click
        Dim SaveLocation As String = Server.MapPath("Data") & "\upload.txt'"
        If UploadFile(SaveLocation) Then
            'the file was uploaded: now try saving it to the database
            SaveToDatabase(SaveLocation)
        End If
    End Sub
    Private Function UploadFile(ByVal SavePath As String) As Boolean
        Dim fileWasUploaded As Boolean = False 'indicates whether or not the file was uploaded

        'Checking if the file upload control contains a file
        If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then
            Try
                'checking if it was .txt file BEFORE UPLOADING IT!
                'You used to upload it first...but the file could be a virus
                If File1.FileName.EndsWith(".txt") = False Then
                    'The file is not the expected type...do not upload it
                    'just post the validation message
                    message.Text = "Please submit a text file."
                Else
                    'The file is a .txt file
                    'checking to see if the file exists already
                    'If it does exist Deleting the existing one so that the new one can be created
                    If IO.File.Exists(SavePath) Then
                        IO.File.Delete(SavePath)
                    End If

                    'Now upload the file (save it to your server)
                    File1.PostedFile.SaveAs(SavePath)

                    'After saving it check to see if it exists
                    If File.Exists(SavePath) Then
                        'Upload was sucessful
                        message.Text = "Thank you for your submission"
                        fileWasUploaded = True
                    Else
                        'the file was not saved
                        message.Text = "Unable to save the file"
                    End If
                End If

            Catch Exc As Exception
                'We encountered a problem
                message.Text = Exc.Message + " " + Exc.StackTrace
            End Try
        Else
            'No file was selected for uploading
            message.Text = "Please select a file to upload"
        End If
        Return fileWasUploaded
    End Function

    Private Sub SaveToDatabase(ByVal SavePath As String)
        Try
            ' and bulk import the data:   
            'If ConfigurationManager.ConnectionStrings("Dialerresults") IsNot Nothing Then
            'Dim connection As String = ConfigurationManager.ConnectionStrings("Dialerresults").ConnectionString
            Dim connection As String = "data source=10.2.1.40;initial catalog=IVRDialer;uid=sa;password=xxx;"
            Dim results As New DataTable

            Using con As New SqlConnection(connection)
                con.Open()

                ' execute the bulk import   
                Using cmd As SqlCommand = con.CreateCommand

                    cmd.CommandText = "bulk insert dialerresults from '" & SavePath & "' " & _
                    "with ( fieldterminator = ',', rowterminator = '\n' )"

                    cmd.ExecuteNonQuery()
                End Using
            End Using
            'Else
            'message.Text="ConfigurationManager.ConnectionStrings('Dialerresults') is Nothing!"
            'End If
        Catch ex As Exception
            message.Text = ex.Message + ex.StackTrace
        End Try
    End Sub

End Class

and when I try to upload to the page that this references I get the following error:

Line 1: Incorrect syntax near ','. Unclosed quotation mark before the character string ' )'. at System.Data.Sql Client.SqlConne ction.OnError(S qlException exception, Boolean breakConnection ) at System.Data.Sql Client.SqlInter nalConnection.O nError(SqlExcep tion exception, Boolean breakConnection ) at System.Data.Sql Client.TdsParse r.ThrowExceptio nAndWarning(Tds ParserStateObje ct stateObj) at System.Data.Sql Client.TdsParse r.Run(RunBehavi or runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleR esultSet bulkCopyHandler , TdsParserStateO bject stateObj) at System.Data.Sql Client.SqlComma nd.RunExecuteNo nQueryTds(Strin g methodName, Boolean async) at System.Data.Sql Client.SqlComma nd.InternalExec uteNonQuery(DbA syncResult result, String methodName, Boolean sendToPipe) at System.Data.Sql Client.SqlComma nd.ExecuteNonQu ery() at _Default.SaveTo Database(String SavePath) in C:\Inetpub\wwwr oot\Webfile1\De fault.aspx.vb:l ine 78

The line of code in question is this one:

cmd.ExecuteNonQ uery()

The syntax of the SQL query is correct. I'm not sure what I'm missing but there is something here that I must be missing. Can anyone offer any assistance?

**tlhintoq** · May 11 '10, 09:25 PM

Please don't double-post your questions. It divides attempts to help you in an organized and cohesive manner. Your threads have been merged

**Frinavale** · May 12 '10, 01:44 PM

I think your problem is on lines 75 and 76 in the above posted code:

Code:

 cmd.CommandText = "bulk insert dialerresults from '" & SavePath & "' " & _
                     "with ( fieldterminator = ',', rowterminator = '\n' )"

See Bulk Insert (Transact-SQL) for help with Bulk Insert.

-Frinny

**dougancil** · May 13 '10, 02:46 PM

Frinny,

I think that you're right but I have no idea what is causing the error. I understand that sometimes by having a single quotation mark that this is seen as a line break, I've tried manipulating this file in a lot of different ways and I don't see what I have to do to fix this. Can you give me any ideas as to what needs to be done here?

Thank you,

Doug

**Frinavale** · May 13 '10, 02:50 PM

Honestly, I've never used this command before.

I would refer to the link I posted for you to come up with an answer for you....if you scroll down there are examples of how use this SQL command. Maybe they can help you?

I'll ask the SQL experts to take a look at this.

-Frinny

**dougancil** · May 13 '10, 03:03 PM

Frinny,

If you take the sql bulk insert string as just a query, it works fine. I wrote that long before I started this quest. The query itself executes correctly. I'm pretty sure that it may be the syntax on how it's written and inserted into the asp.net code here for this page that is causing this problem. I'm just not sure where. If you could ask the experts, I'd greatly appreciate it.

Doug

**Megalog** · May 13 '10, 03:16 PM

If it's a problem with the single quote being misinterpreted, you can always swap them with a unique character set while building the string, and then do a replace on it afterwards with the double quotes. I've used your post, with carats as the replaced character.

Code:

cmd.CommandText = Replace("bulk insert dialerresults from ^" & SavePath & "^ " & _ 
                 "with ( fieldterminator = ^,^, rowterminator = ^\n^ )","^","""")

This will return:

Code:

bulk insert dialerresults from "C:\Folder" with ( fieldterminator = ",", rowterminator = "\n" )

**dougancil** · May 13 '10, 03:28 PM

I tried with the carat's replacing the single quotes, and still get the same error:

Line 1: Incorrect syntax near '^'. Unclosed quotation mark before the character string '^ with ( fieldterminator = ^,^, rowterminator = ^\n^ )'. at System.Data.Sql Client.SqlConne ction.OnError(S qlException exception, Boolean breakConnection ) at System.Data.Sql Client.SqlInter nalConnection.O nError(SqlExcep tion exception, Boolean breakConnection ) at System.Data.Sql Client.TdsParse r.ThrowExceptio nAndWarning(Tds ParserStateObje ct stateObj) at System.Data.Sql Client.TdsParse r.Run(RunBehavi or runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleR esultSet bulkCopyHandler , TdsParserStateO bject stateObj) at System.Data.Sql Client.SqlComma nd.RunExecuteNo nQueryTds(Strin g methodName, Boolean async) at System.Data.Sql Client.SqlComma nd.InternalExec uteNonQuery(DbA syncResult result, String methodName, Boolean sendToPipe) at System.Data.Sql Client.SqlComma nd.ExecuteNonQu ery() at _Default.SaveTo Database(String SavePath) in C:\Inetpub\wwwr oot\Webfile1\De fault.aspx.vb:l ine 78

here's the line of code after I modified it:

cmd.CommandText = "bulk insert dialerresults from ^" & SavePath & "^ " & _
"with ( fieldterminator = ^,^, rowterminator = ^\n^ )"

**Megalog** · May 13 '10, 03:36 PM

You need to add the replace function to that to swap the carats back to double quotes.

Look at the first code block from my last post.

**dougancil** · May 13 '10, 03:48 PM

I used the replace and the code now looks as follows:

Code:

     cmd.CommandText = Replace("bulk insert dialerresults from ^" & SavePath & "^ " & _"with ( fieldterminator = ^,^, rowterminator = ^\n^ )", "^", """")

and I get this back:

Code:

Line 1: Incorrect syntax near ','. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at _Default.SaveToDatabase(String SavePath) in C:\Inetpub\wwwroot\Webfile1\Default.aspx.vb:line 78

So it does appear to be a problem with the syntax for the query itself. Here's the query as it was originally written:

Code:

bulk insert dialerresults
from '\\MSBWEB3\data\upload.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

That query works if you run it from the SQL query analyzer. So I'm not sure what syntax asp.net needs to see it in for it to work.

**Frinavale** · May 13 '10, 04:24 PM

Could you post the contents of your SaveLocation string at run time?

**NeoPa** · May 13 '10, 04:25 PM

You have shown the error message in detail, but not the actual resolved string from the Replace() function. I would post that, and even compare it with the string (original command) that you know works well.

See below for some general instructions when working with SQL in code (It was written with VBA in mind but I'm sure it could be made to work for you easily).

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

**Frinavale** · May 13 '10, 04:31 PM

Just so you know, this is an ASP.NET application and so Message Box and Debug.Print are not a viable option.

Please use an ASP.NET Label or Localize control instead of the Message Box or Debug.Print.

For example:

Code:

debugLabel.Text = SaveLocation
debugLabel.Text = debugLabel.Text +  "<br />" +  Replace("bulk insert dialerresults from ^" & SavePath & "^ " & _"with ( fieldterminator = ^,^, rowterminator = ^\n^ )", "^", """")

Where debugLabel is defined as Label in the ASPX page.

-Frinny

**dougancil** · May 13 '10, 04:44 PM

Frinny,

So on the aspx page, I should just put a label somewhere on the page and define the debugLabel.Text to it?

Line 1: Incorrect syntax near ','. Unclosed quotation mark before the character ...

Line 1: Incorrect syntax near ','. Unclosed quotation mark before the character ...

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment