[Elda]

Hi everyone..

I'm trying to imports excel data into sql database. I'm using excel 2007 connecting to sql 2005.
my code:

 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..
Thanks...

This post has been edited by Elda: 14 September 2011 - 08:29 PM

[Ionut]

1. Go to Start-> Programs - Microsoft SQL Server <edition> -> Configuration tools -> Sql Server Configuration Manager -> Sql Server Network configuration ->Protocols for <instance name> -> make sure you have TCP/IP enabled.
2. Go to Start-> Programs - Microsoft SQL Server <edition> -> Configuration tools -> Sql Server Configuration Manager -> Sql Server Network configuration ->SQL Native Client <version> Configuration -> make sure you have TCP/IP enabled.
3. Go to Start-> Programs - Microsoft SQL Server <edition> -> Configuration tools -> Sql Server Configuration Manager -> Sql Server <edition> services -> check the state of SQL Server Browser service. If it is stopped, right click and start.

<edition> = 2005; 2008; 2008 R2 depending on what you installed
<instance name> = if you use SqlExpress then instance name is SQL Express, if you installed the server with the default instance name, then instance name is MSSQLSERVER, otehrwise the name you gave it.