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

[dougancil]

Can someone please review my code and see where I can be missing information. 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

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

Here is the line of code in question:

cmd.ExecuteNonQ uery()

Is this not the correcy syntax?

[dougancil]

I have the following 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]

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]

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

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

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

-Frinny

[dougancil]

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]

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]

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]

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.

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

This will return:

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

[dougancil]

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]

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.