ExecuteNonQuery: CommandText property has not been initialized

Question

I have a web page that is a bulk insert to a SQL database. The code for the SQL Bulk insert has been tested and works. The page is as follows:

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=xxxxxx;password=xxxxxxx;"
            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
 

The original SQL query is as follows:

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

I am getting the following error:

ExecuteNonQuery: CommandText property has not been initialized at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, 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

This has something to do with how that sql bulk insert is written in the aspx.vb page but I'm not sure what it is. Can someone help me find the problem?

Thank you

Doug

Answer 1

In your SaveToDataBase method you just don't set any command so the sql server does not know what to execute. I believe there may be just a typo - you set label (DebugLabel) properties  instead of properties of cmd object. Probably you need a line similar to the one I added (the red one)

  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.CommandText = DebugLabel.Text

  
   cmd.ExecuteNonQuery()
  End Using

Pawel

Answer 2

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
      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=xxxxx;password=xxxxx;"

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

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

Above is the code that I have now. Whats happening is that I'm receiving the error :

Could not bulk insert because file 'C:\Inetpub\wwwroot\Webfile1\Data\upload.txt' could not be opened. Operating system error code 3

My web server is not the same machine as my sql server. I have checked the permissions on the web server and on the sql server and I can open/edit/delete the file from both the web server and the sql server. I modified the machine.config of the web server to log in as the admin (rather than the aspnet user) on the web server and this still doesn't seem to have corrected the problem. This is driving me crazy. Can anyone offer any kind of help with this?

Thank you

Doug

Answer 3

Hi,

If I undestand it correctly your ASP.NET code is creating a file which you then "import" into a SQL server. Since the ASP.NET runs on a different machine than the SQL Server one of them will have to write/read the file over a network, but all your paths seem to be local file paths. You need to specify a path to the SQL command which is accessible from the SQL Server machine (and C:\Inetpub is probably not).

Anyway, this forum is for WCF Data Services, your question is actually about either ASP.NET or SQL Server. Please try to post your question to the right forum, so that you get the right people to look at it. For ASP.NET questions try one of the forums here: http://forums.asp.net/

Thanks,

---

Vitek Karas [MSFT]