Problems with ExportDataTable


#1

I am consistantly getting Input sting not valid when attempting to call ExportDataTable then updating my datatable.

heres my routine:


Try

mExcel.Open(txtImportFilePath.Text)

Dim workSheet As Aspose.excel.Worksheet = mExcel.Worksheets(0)

Dim datatable As DataTable = workSheet.Cells.ExportDataTable(0, 0, workSheet.Cells.MaxRow + 1, workSheet.Cells.MaxColumn + 1)

datatable.TableName = ConfigurationSettings.AppSettings(CONFIG_TABLE_NAME)

For iRowCounter As Integer = 1 To datatable.Rows.Count - 1

For iColumnCounter As Integer = 0 To datatable.Columns.Count - 1

sdaLosses.InsertCommand.Parameters.Item("@" & datatable.Rows(0)(iColumnCounter).ToString).Value = datatable.Rows(iRowCounter)(iColumnCounter).ToString()

datatable.Columns(iColumnCounter).ColumnName = datatable.Rows(0)(iColumnCounter).ToString

Next

sdaLosses.Update(datatable)

datatable.AcceptChanges()

Next

Catch ex As Exception

Throw New Exception(“Import to SQL has failed. This may have occurred due to the following reasons” & vbCrLf & " 1) You may have the Excel file open." & vbCrLf & " 2) The file may not have been generated using this tool.")

End Try





#2

The column data type of the exported data table may be string, integer, double or DateTime. Please make sure that the data type of each column is same as the type of SqlParameters.

You can also try to change your code to:

Dim datatable As DataTable = workSheet.Cells.ExportDataTableAsString(0, 0, workSheet.Cells.MaxRow + 1, workSheet.Cells.MaxColumn + 1)