none
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

    Thursday, May 13, 2010 6:34 PM

All replies

  • 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
    Thursday, May 13, 2010 8:41 PM
  • 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

    Friday, May 14, 2010 7:49 PM
  • 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]
    Monday, May 17, 2010 8:11 AM