SQL Server Performance

Upload .xls file to SQL Server table w ASP.NET app

Discussion in 'SQL Server DTS-Related Questions' started by bubberz, Aug 9, 2005.

  1. bubberz New Member

    Here's what I have to do:
    1. Allow user to locate a .xls file on their machine
    2. Upload this .xls data into an existing table on a remote SQL Server

    I can pull the file from my local machine to another directory on the local machien, but can't figure out have to configure the saveas() to save on the remote db server.

    It seems you have to save the the db server first on the hard drive, then you can insert the .xls file data into the table.

    Here's my code so far that works to save on the local machine to another directory on that local machine:

    Dim getmyFile As HttpPostedFile = myfile.PostedFile
    If IsNothing(getmyFile) Then
    Label2.Text = "Please select a file to upload"
    Else
    If getmyFile.ContentLength = 0 Then
    Label2.Text = "Cannot upload zero length File"
    Else
    Dim ServerFileName As String = Path.GetFileName(myfile.PostedFile.FileName)
    getmyFile.SaveAs("C:TestSaving" & ServerFileName)
    Label2.Text = "Successful upload to C:TestSaving" & ServerFileName
    sCon1.Open()
    Dim strSQL As String
    Dim err As Integer
    strSQL = "Insert into ActivityTest Select * FROM OPENROWSET"
    strSQL &= "('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D: esting.xls;"
    strSQL &= "HDR = YES ','SELECT * FROM [Sheet1$]')"
    Label3.Text = strSQL.ToString()
    Dim cmd As New SqlCommand(strSQL, sCon1)
    Try
    cmd.ExecuteNonQuery()
    err = "Select @@Error"
    If err <> 0 Then
    Label4.Text = err.ToString()
    Else
    Label4.Text = "No Error...line 91!"
    End If
    Catch ex As Exception
    Label2.Text = "Line 82 Error Updating Table: "
    Label2.Text &= ex.Message
    Finally
    sCon1.Close()
    End Try
    End If

    Any book suggestions on this topic are appriciated too! I'll have to do more of this in the future!

    Thanks for the help in advance!!!!
  2. Madhivanan Moderator

    >>I can pull the file from my local machine to another directory on the local machien, but can't figure out have to configure the saveas() to save on the remote db server.

    Do you want to copy the Excel file to the Remote server?
    If so, it should be done by your ASP Application
    Post this question to ASP forum


    Madhivanan

    Failing to plan is Planning to fail

Share This Page