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.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(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

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

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.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(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

The line of code in question is this one:

cmd.ExecuteNonQuery()

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.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(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

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.

[dougancil]

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

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

and I get this back:

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:

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]

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

[NeoPa]

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]

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:

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]

Frinny,

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

[Frinavale]

Yup, drag a Label control from the Toolbox onto the ASPX page and give it an ID..."debugLabel".

You'll delete it after you're finished debugging.

[dougancil]

here's the error message I get back when I insert the debugLabel.Text

ExecuteNonQuery: CommandText property has not been initialized at System.Data.SqlClient.SqlCommand.ValidateCommand(S tring method, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(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

also here is the line of debug code from the save path:

C:\Inetpub\wwwroot\Webfile1\Data\upload.txt'
bulk insert dialerresults from "C:\Inetpub\wwwroot\Webfile1\Data\upload.txt'" with ( fieldterminator = ",", rowterminator = "\n" )

[Frinavale]

Hmm I think you have an extra/unnecessary '
In your SavePath...See it? It's at the end...
C:\Inetpub\wwwroot\Webfile1\Data\upload.txt'

[dougancil]

I took that extra quotation mark out and now get this error:

ExecuteNonQuery: CommandText property has not been initialized at System.Data.SqlClient.SqlCommand.ValidateCommand(S tring method, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(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

[Frinavale]

I can't guess what your current code is....
Could you please post your most recent code responsible for updating the database.

-Frinny

PS. Please post code in code tags.

[dougancil]

Frinny,

Here is my current code for that page.

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
 
                    DebugLabel.Text = SavePath
                    DebugLabel.Text = DebugLabel.Text + "<br />" + Replace("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

[Frinavale]

Dougancil,
You are not supplying a SQL command to the SqlCommand Object you are using to connect to the database.

Declare a string that is going to contain your SQL command text:

Dim sqlQueryText As String = _
  "BULK INSERT dialerresults " + _
  "FROM '"& SavePath & "' " + _
  "WITH ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n' )"
 

You need to make sure that there aren't any extra apostrophes (') that shouldn't be there....like I was mentioning before. I'm not sure what you did to "fix" the problem before...but anyways.

The other thing you must do...which you have actually been told by the exception details...is provide the SQL command Text to the SqlCommand object you're using to connect to the database...see the code below:

  Try
    Dim sqlQueryText As String = _
      "BULK INSERT dialerresults " + _
      "FROM '"& SavePath & "' " + _
      "WITH ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n' )"
 
 
    ' 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;"
 
'I removed the DataTable declaration because you're not using it.
    Using con As New SqlConnection(connection)
      con.Open()
      ' execute the bulk import   
        Using cmd As New  SqlCommand(sqlQueryText, con)
          cmd.ExecuteNonQuery()
        End Using
      End Using
 
    'Else
      'message.Text="ConfigurationManager.ConnectionStrings('Dialerresults') is Nothing!"
    'End If
    Catch ex As Exception
      message.Text = sqlQueryText + "<br />"+ ex.Message + "<br />" + ex.StackTrace
    End Try
 

See the MSDN documentation on the SqlCommand class for a more detailed explanation of how to use the SqlCommand.

[dougancil]

Frinny,

Ok so I'm now back to this error:

Could not bulk insert because file 'C:\Inetpub\wwwroot\Webfile1\Data\upload.txt' could not be opened. Operating system error code 3(The system cannot find the path specified.). at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(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 82

I've modified the machine.config to impersonate the admin for the web server. Evidently that's not what I need to do. The Default Application Pool for that server is logged in as a Network Service, but I was under the impression that by impersonating the admin in the machine.config file that it overwrote how the asp.net page would log in on that server. Is that not correct?

[Frinavale]

The error is:
"Operating system error code 3(The system cannot find the path specified.)"

Are you sure that this is the correct path?
C:\Inetpub\wwwroot\Webfile1\Data\upload.txt

-Frinny

[Frinavale]

Hey!
Line 10:

Dim SaveLocation As String = Server.MapPath("Data") & "\upload.txt'"

See the extra apostrophe?

Change line 10 to:

Dim SaveLocation As String = Server.MapPath("Data") & "\upload.txt"

[dougancil]

I'm absolutely sure.

[dougancil]

I removed that, sorry. That's not there anymore.

[dougancil]

here's what I put in my system.web portion of machine.config and I know that it's being used because if I put

identity impersonate="true" that it stops working

<system.web>
<processModel enable="true"
userName="MSB\administrator"
password="xxxxxxx"/>

[Frinavale]

You probably should not be modifying the system.config nor the machine.config files....

Like I said, I've never used the bulk insert stuff before...I have no way of testing it so I can't tell you what I find either.


How about you try creating a test application that is a Win Forms or a Console type of application. Try doing a bulk insert from there.

These applicatoins run under your Windows User account and so if you as a user have access to the file your application should also have access to the file.


-Frinny

[dougancil]

Frinny,

As this is a "sandbox" server ... (since I only want this page to run correctly) I have no fear in modifying the machine.config file. My question would be if it's not that, then what is it? How can I modify what user that the asp.net application tries to log in as? Because evidently that's what's causing the issue here.

[Frinavale]

Are you Sure that's what's causing the issue?
I still recommend you try the Win Forms or Console application....it won't take long...just copy paste the bulk insert code and manually supply the path (instead of using Server.MapPath)

[dougancil]

Frinny,

I'm just going by the power of deductive reasoning here. I can see that the file is being uploaded to the correct folder on the server. The SQL query is written correctly, so to me all of the other mechanics are worked out. If it's not a permissions issue with opening the file, I'm not sure what else it could be.

[Frinavale]

Ok I went back to the Bulk Insert article I posted you a link to earlier.

I found a few things:

MSDN BULK INSERT (Transact-SQL) Article:
data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name. A UNC name has the form \\Systemname\ShareName\Path\FileName. For example, \\SystemX\DiskZ\Sales\update.txt.

And this bit is about permissions required to be set up with the SQL Server:

MSDN BULK INSERT (Transact-SQL) Article:
<Bulk Insert> Requires INSERT and ADMINISTER BULK OPERATIONS permissions. Additionally, ALTER TABLE permission is required if one or more of the following is true:

* Constraints exist and the CHECK_CONSTRAINTS option is not specified.
ms188365.note(en-us,SQL.100).gifNote:
Disabling constraints is the default behavior. To check constraints explicitly, use the CHECK_CONSTRAINTS option.

* Triggers exist and the FIRE_TRIGGER option is not specified.
ms188365.note(en-us,SQL.100).gifNote:
By default, triggers are not fired. To fire triggers explicitly, use the FIRE_TRIGGER option.

* You use the KEEPIDENTITY option to import identity value from data file.

-Frinny

[dougancil]

Frinny,

I've tried the UNC and by mapping the path as follows:

\\MSBWEB3\wwwroot\Webfile1\Data and MSBWEB3\\wwwroot\Webfile1\Data\upload.txt and received the following error:

Could not find a part of the path 'C:\Inetpub\wwwroot\Webfile1\MSBWeb3\wwwroot\Webfi le1\Data\MSBWeb3\wwwroot\Webfile1\Data\upload.txt'

When I press upload without the UNC, the file does get uploaded to the correct folder, so that's not the issue. Secondly, when I run the bulk insert command from the sql query analyzer, this bulk insert works fine, which tells me that the permissions on the table for bulk insertion is correct, otherwise I wouldn't be able to do that. I'm not using any constraints as this is a pretty simple query as well as I'm not using any triggers or the keep identity. This is really a WYSIWYG in the sense that the file is uploaded, checked to verify it's a particular type of file, sent to a directory on the web server and then bulk inserted to the sql table. That's it. Nothing else goes on behind the scenes and there's no further contraints or conditions behind it.

[Frinavale]

So your SQL Server is not on the same computer as the Web Server hosting this application?

[dougancil]

no it's not ... its in my network though.

[Frinavale]

The SQL Server cannot open the file because the file is not on the same computer......

Try to Share the folder that you are upload the file to so that both computers on the network can access the file.

This is probably not a good thing to do when you release this application (because security issues allowing people to upload files to a directory that is shared on the network is a pretty scary thing to do)...but for now just try sharing the folder so that both computers can access the file.

You could consider using a different means to copy the file to a location where the SQL Server has access to the file later.

-Frinny

PS you have to use the Universal Naming Convention (UNC) to specify the location of the file in your SQL command text............

[dougancil]

I have shared the file so that I can open it from both computers. I can open/edit/delete from both. So what about if I saved the file on the SQL server rather than on the web server. Would that work?

[Frinavale]

I think so.

[dougancil]

so then I'd have to modify this path to reflect the sql server path correct?

Dim SaveLocation As String = Server.MapPath("Data") & "\upload.txt"

[Frinavale]

Well here's the thing, I'm not sure if the ASPNET user account has privileges to copy files from one computer across the network to another computer...I've never tried to do this before.

So yes, you'll have to save the file to the SQL Server...
You will have to make 2 modifications:

1) to the SaveLocation variable so that the ASP.NET application can upload/save the file to the computer hosting the SQL Server

2) to the SQL command text....specifying the path to the file on the SQL server

[dougancil]

So let me understand this ... if I want to move files from SQL server to a web server (according to MS) it's best to have both my asp.net architecture as well as my sql server be one in the same machine? This seems really convoluted to do such a "simple" task. I know that all of this can be done with "stored procedures" but the ONLY difference is that I'm not using a sp but rather an actual bulk insert from a web server to a sql server.

I've modified the savelocation variable but the sql command text, this:

Using cmd As New SqlCommand(sqlQueryText, con)

is all I have using the SqlCommand.

[Frinavale]

I'm not certain that it is best to have your SQL server and your ASP.NET application on the same computer....but it would make life a lot easier for dealing with files that are used by both applications.

I'm sorry I don't have any straight answers for you because I've never used the BULK INSERT command before.

In this case all I can do is recommend trying a few different things...it may help to have both the SQL Server and the ASP.NET application on the same computer in this case....especially if the ASPNET user account cannot copy files across the network (which I'm not certain of so you'd have to test it)

-Frinny

[NeoPa]

dougancil: So let me understand this ... if I want to move files from SQL server to a web server (according to MS) it's best to have both my asp.net architecture as well as my sql server be one in the same machine? This seems really convoluted to do such a "simple" task.

Doug, as far as web servers go, if you think it's convoluted to require services be available natively to the web service, then you possibly need to give some consideration to the whole concept of the world wide web itself.

To allow members of the public to access web servers generally, there must be a logical security basis. Web servers generally, and for good logical reasons, give access only to those local resources that are deemed appropriate. If the account that the web service ran under had network rights then the whole thing would be almost impossible to administer.

It is possible (though can be tricky) to get around these restrictions, but not recommended for anyone who doesn't, or can't, grasp the complexities of the situation overall. I don't believe this has anything to do with any MS specific implementations.