I'm trying to imports excel data into sql database. I'm using excel 2007 connecting to sql 2005.
Dim myPath As String = "c:\sample.xlsx" Dim dsData As New DataSet Dim dt As New DataTable 'Dim objAdapter As SqlDataAdapter Dim strConnection As New SqlConnection("Data Source='" & myPath & " '; " & "Extended Properties=Excel 8.0;") 'strConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & myPath & " '; " & "Extended Properties=Excel 8.0;") strConnection.Open() Dim expr As String = "SELECT * FROM [Sheet1$]" Dim objCmdSelect As SqlCommand = New SqlCommand(expr, strConnection) Dim objDR As sqlDataReader Dim SQLconn As New SqlConnection() Dim ConnString As String = "Data Source=ISD-ELDA;Initial Catalog=Employees;User ID=sa;Password=sa" SQLconn.ConnectionString = ConnString SQLconn.Open() Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn) bulkCopy.DestinationTableName = "EmployeeInfo" Try objDR = objCmdSelect.ExecuteReader bulkCopy.WriteToServer(objDR) strConnection.Close() 'objDR.Close() SQLconn.Close() Catch ex As Exception MsgBox(ex.ToString) End Try End Using
When I run it I got this error.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
What's wrong with it? Please help me..
This post has been edited by Elda: 14 September 2011 - 08:29 PM