Hello. I need some help here.

(INSERT)

        Dim sExcelFileName As String = "TDP Students List.xls"
        Dim i As Integer = 0
        Dim dt As New DataTable

        Dim sSQLTable As String = "StTable"

        ' retrieve the Select Command for the worksheet data
        Dim objCommand As New OleDbCommand()
        objCommand = ExcelConnection()

        Try
            strConnection = ConfigurationManager.ConnectionStrings("database").ToString

            Dim sbc As SqlBulkCopy = New SqlBulkCopy(strConnection)
            sbc.DestinationTableName = "StTable"
            sbc.ColumnMappings.Add("Student ID", "StudentID")
            sbc.ColumnMappings.Add("School Org", "FacultyID")
            sbc.ColumnMappings.Add("Name", "StudName")
       
            '  create a DataReader
            Dim reader As OleDbDataReader
            reader = objCommand.ExecuteReader()
     
            sbc.WriteToServer(reader) // Error occured here : Student ID cannot be null
            sbc.Close()
            'reader.Close()
            conn.Close()

But there are no null value in the data table! Help needed..

Im not sure if I have missed out any impt parts!

Thanks!

Not really sure, but try to remove the mappings in your codes and see what will happens..

For your reference then you can refer to this article: Performing a Single Bulk Copy Operation

Hey Vinz,

Thanks for the help.

I will try and see what it happens.

Just curious, how do I check if the record that have already been inserted and avoid inserting that record when running sqlbulkcopy.

I have an idea is to pull data from database, example the studentID from the database.

If the studentID exists, means record exists so skip. but bulkcopy works by transferring data in a bulk. So Im just wondering if this works.

germaine.tay:

Just curious, how do I check if the record that have already been inserted and avoid inserting that record when running sqlbulkcopy.

I have an idea is to pull data from database, example the studentID from the database.

If the studentID exists, means record exists so skip. but bulkcopy works by transferring data in a bulk. So Im just wondering if this works.

This short discussion may helps:

http://forums.asp.net/t/1220081.aspx

Hi.

I would also need to dynamically "detect" the sheet name from the excel file.

Any idea how to go about doing that>?

germaine.tay:

I would also need to dynamically "detect" the sheet name from the excel file.

To get the sheet names, you could take a look at this article:

http://www.codeproject.com/KB/aspnet/getsheetnames.aspx

Thanks.

Thanks! I have managed to insert the data during testing to database.

However, i have this problem that I am still facing. I have looked up many tutorials but none work. I will paste my codes here to see if Im doing it the right way.

I need to retrieve 2 things dynamically.

1. Excel File Name

what I did was this:

Dim strFileName As String
strFileName = Path.GetFileName(FileUploadExcel.FileName)
Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath(FileUploadExcel.PostedFile.FileName) + ";" & _
 "Extended Properties=Excel 8.0;"

to get the filename and also the path. but I could not get it to work. Because this path is incorrect, it cannot trace the worksheet name by the name of [Sheet1$] in the excel file.

2. 2nd thing that I need to retrieve dynamically is the worksheet name.

Dim objCommand As New OleDbCommand()
  objCommand.Connection = objXConn
  'accessing sheet

  Dim dtExcelSchema As DataTable
      dtExcelSchema = objXConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
   For i = 0 To dtExcelSchema.Rows.Count - 1
    Dim sheetName As String = dtExcelSchema.Rows(i)("TABLE_NAME").ToString
    objCommand.CommandText = "SELECT * FROM [" + sheetName + "]"
       Next
       da.SelectCommand = objCommand
       da.Fill(ds) - error occured here : Command text was not set for the command object.
       objXConn.Close()

Now, even though Im able to insert data, but without able to retrieve the 2 important parameters : Excel File Name & the Sheet Name. I am unable to do the SQLBULKCOPY.

Any advise? or my above codes are wrong?

Thank you,.

Hi germaine.tay,

Have you tried to debug the source code and check the file path? I guess there's some problem with the data source in the connection string, you'd better check it first. Or you could use an absolute path for testing.

Also, if you didn't rename the sheet, it's sheet1$ by default. You have no need to get the sheet name dynamically.

Thanks.

Hi Wencui Qian,

Oh yes, my connection string has nothing wrong:

<connectionStrings>
        <add name="TestingConnection" connectionString="Data Source=mycomputer;Initial Catalog=Testing;Persist Security Info=True;User ID="";Password="" providerName="System.Data.SqlClient"/>
    </connectionStrings>

Oh the sheet is Sheet1$ but because the excel file is not going to be entered by me. They usually renamed the sheet name thus I have to retrieve them dynamically..

Hi germaine.tay,

Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath(FileUploadExcel.PostedFile.FileName) + ";" & _
 "Extended Properties=Excel 8.0;"

I suggest you to check the referred connection string first and ensure the file path is correct. Also, to get the sheet name in excel, you could check the link above.

Thanks.