Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2002
    Posts
    3

    Question Unanswered: Bulk Insert not working in ASP

    ----------------------------------------------------------------------------------------

    I have the following ASP code:

    'load the conversion_dump_daily table
    Response.Write("<br>Load Conversion_Dump_Daily data for " & theDate & ".")
    Response.Write("<br>&nbsp;&nbsp;&nbsp;File Name: " & inputFile & ".")
    Response.flush
    cmd.CommandText = "AddConversionDumpDaily"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Append cmd.CreateParameter("InFile", adVarChar, , 1000, "D:\DataSources\ConversionBuilder\test.txt")
    On Error Resume Next
    cmd.Execute
    cmd.Parameters.Delete("InFile")

    ...... asp error checking follows

    ----------------------------------------------------------------------------------------

    The stored procedure is as follows:

    CREATE PROCEDURE AddConversionDumpDaily @InFile varchar(1000)
    AS
    DECLARE @ErrorSave int
    SET @ErrorSave = 0
    -- Create a transaction so that a rollback can be performed in case of an error.
    BEGIN TRAN
    -- Bulk insert Conversion data from csv file.
    EXEC('BULK INSERT Conversion_Dump_Daily FROM ''' + @InFile + ''' WITH (FORMATFILE=''D:\FormatFiles\ConversionBuilder.fmt '') ')

    -- Check to see if the insert was successful
    if @@error <> 0
    begin
    SET @ErrorSave = @@error -- Store the error code in a variable.
    rollback tran
    goto endOfBatch
    end
    COMMIT TRAN
    endOfBatch:
    RETURN @ErrorSave -- Return the error code to determine success or failure of the process.
    GO

    ----------------------------------------------------------------------------------------


    The stored procedure works fine from the Query Analyzer, but nothing is inserted from the ASP page. I don't get any errors... it is just that nothing appears!

    Any ideas anyone?

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    I am not sure if you left out the code, but where are you creating the command object and adding the connection information. Also, you will not receive an error when you are using "On Error Resume Next" before your execute statement. By removing this, you will see the problem.

    Good luck.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •