Here is sample code behind that I created few days ago. It imports data from excel to 2005 should be same for 2000 I belive.
If Not Me.FileUpload1.PostedFile Is Nothing And FileUpload1.PostedFile.ContentLength > 0 Then
Dim fn As String = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName)
Dim SaveLocation As String = Server.MapPath("Data") & "\" & fn (point to folder where you want to save the data)
Try
FileUpload1.PostedFile.SaveAs(SaveLocation)
Dim excelConnectionString As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath("Data/" & fn) _
& ";" & "Extended Properties=Excel 8.0;")
Using connection As OleDbConnection = New OleDbConnection(excelConnectionString)
Dim command As OleDbCommand = New OleDbCommand("Select * FROM [Sheet1$]", connection) (This will query on excel Sheet1 change name if name is different if name mismact it will not work)
Try
connection.Open()
Catch ex As Exception
Response.Write("Error: " & ex.Message)
End Try
' Create DbDataReader to Data Worksheet
Using dr As System.Data.OleDb.OleDbDataReader = command.ExecuteReader()
' SQL Server Connection String
Dim sqlConnectionString As String = "Data Source=yourserver;Initial Catalog=your databse;Persist Security Info=True;User ID=username;Password=password"
' Bulk Copy to SQL Server
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlConnectionString)
bulkCopy.DestinationTableName = "database table name where you want to copy"
bulkCopy.WriteToServer(dr)
connection.Close()
File.Delete(SaveLocation)
End Using
End Using
End Using
Catch Exc As Exception
Response.Write("Error: " & Exc.Message)
End Try
Else
Response.Write("Please select a file to upload.")
End If
lblMessage.Text = "The file has been uploaded and inserted to the Database Successfully."