SQLBulkCopy not working IIS7

Question

I don't know which forum to post this to but could someone please tell me what I need to do to make this work in IIS7?  It was working fine in IIS6. Thanks!

            Try
  
                Dim sSQLTable As String = "tempContainer"
                Dim sExcelFileName As String 
                Dim sWorkbook As String           
                Select Case ddlfilter.selecteditem.text
                    Case "Receive"                     
                        sExcelFileName = "receive.xls"
                        sWorkbook = "[receive$]"
                    Case "Move"                     
                        sExcelFileName = "move.xls"
                       sWorkbook = "[move$]"
                    Case "Repair"                     
                        sExcelFileName = "repair.xls"
                        sWorkbook = "[repair$]"
                    Case "Ship"                     
                        sExcelFileName = "ship.xls"
                        sWorkbook = "[ship$]"                                                                        
                End Select  
    
                'Create our connection strings 
                Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(sExcelFileName) & ";Extended Properties=""Excel 8.0;HDR=NO;"""
                'Dim sSqlConnectionString As String = WebConfigurationManager.ConnectionStrings("DSN").ToString 
                 
                'Execute a query to erase any previous data from our temp destination table 
                Dim sClearSQL = "DELETE FROM " & sSQLTable 
                'Dim SqlConn As SqlConnection = New SqlConnection(sSqlConnectionString) 
                Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn) 
                SqlConn.Open() 
                SqlCmd.ExecuteNonQuery() 
                SqlConn.Close() 
                 
                'Series of commands to bulk copy data from the excel file into our SQL table 
                Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString) 
                Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn) 
                OleDbConn.Open() 
                Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader() 
                Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(ConfigurationSettings.AppSettings("DSN")) 
                bulkCopy.DestinationTableName = sSQLTable 
                bulkCopy.WriteToServer(dr) 
                OleDbConn.Close()

Answer 1

Hi MaryFran,

Your Question is Regarding SQL Server & IIS.

I would suggest you to try SQL Server forums for better discussion.

http://social.msdn.microsoft.com/Forums/en/category/sqlserver


Regards

Sophia

Answer 2

Hi moderators of this forum,

Please, could you move this thread towards http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/threads  (SQL Server Data Access ) in which problems related to DataReader or SqlBulkCopy are treated ?

For MaryFran, i would suggest to have a look at http://msdn.microsoft.com/en-us/library/434atets(VS.90).aspx . You have an example of the use of the WriteToServer method. You should include in your code the block related to the using statement. The try/catch/finally is always a good way of coding. I think that you have forgotten to close the DataReader in your code.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 3

Did you get any solution for this ?