none
On Button Click, problem with excel import to sql database RRS feed

  • Question

  • Hi, completely new to VB and the forums so sorry if this has sort of problem has been answered already but...

     

    I have a SQL express database as part of my project. I have a stored procedure that will bulk import from an excel file. When I run the sp from database explorer the records import no problem.

     

    Running [dbo].[dataImport] ( @path = c:\downloads\fyp\eod\, @filename = lse_20080128.csv ).

    (7654 row(s) affected)

    (0 row(s) returned)

    @RETURN_VALUE = 0

    Finished running [dbo].[dataImport].

     

    I've tried to use LINQ to execute the sp on button click with the below code:

     

    Code Snippet

    Dim db As New DataClasses1DataContext

    db.Log = Console.Out

    db.Connection.Open()

    Console.WriteLine("Rows: " + _

    db.dataImport("C:\downloads\fyp\eod\", "LSE_20080128.csv").ToString)

    db.SubmitChanges()

    db.Connection.Close()

     

     

    However on click the sp appears to run but no rows are inserted:

     

    EXEC @RETURN_VALUE = [dbo].[dataImport] @path = @p0, @filename = @p1

    -- @p0: Input VarChar (Size = 21; Prec = 0; Scale = 0) [C:\downloads\fyp\eod\]

    -- @p1: Input VarChar (Size = 16; Prec = 0; Scale = 0) [LSE_20080128.csv]

    -- @RETURN_VALUE: Output Int (Size = 0; Prec = 0; Scale = 0) [Null]

    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

    Rows: 0

     

    This is my sp:

     

    Code Snippet

    ALTER PROC [dbo].[dataImport] (@path as varchar(200),

    @filename as varchar(200)

    ) as

    begin

    declare @src as varchar(255), @formatfile as varchar(255)

    set @formatfile = 'C:\Downloads\fyp\eod\test.txt'

    set @src = @path+@filename

     

    DECLARE @SQL varchar(2000)

    set @SQL = 'BULK INSERT tmpPrices FROM '''+@src+''' WITH (FIRSTROW = 2, formatfile='''+@formatfile+''')'

    EXEC (@SQL)

     

    insert into tickerPrices

    select left(symbol, len(symbol)-2),

    cast(date as datetime),

    cast([open] as numeric(18,10)),

    cast(high as numeric(18,10)),

    cast(low as numeric(18,10)),

    cast([close] as numeric(18,10)),

    cast(left(volume, len(volume)-1) as int)

    from tmpPrices

    delete from tmpPrices

    end

     

     

    Can I use LINQ to perform a bulk insert? And if not what should I look at using to run the sp instead?
    Thanks in advance.

    Sunday, April 13, 2008 4:11 PM

Answers

  • As there has been no further discussion on this issue I am closing the thread but if you wish to continue solving the problem feel free to reopen it.

     

    [)amien

    Friday, June 13, 2008 5:15 PM
    Moderator

All replies

  • The debug information would indicate that the stored procedure should be executing correctly so I'm not sure where the error could be other than perhaps the connection string given to LINQ to SQL might not have permission to this stored procedure?

     

    I would also be very careful with passing in strings to an SP that you then concatenate to EXEC - it means this procedure is open to SQL injection.

     

    [)amien

    Friday, May 30, 2008 6:13 PM
    Moderator
  • As there has been no further discussion on this issue I am closing the thread but if you wish to continue solving the problem feel free to reopen it.

     

    [)amien

    Friday, June 13, 2008 5:15 PM
    Moderator