Insert data from excel to SQL server -- vb.net data

<Localbar>
6/27/2005 12:00:00 AM

Dear all,

I try to run the code as the following:

Dim ExcelConnection As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Inventory\book1.xls;Extended Properties=""Excel 8.0;HDR=No;"";")

Try
ExcelConnection.Open()
Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("INSERT
INTO [ODBC;Driver={SQL
Server};Server=(local);Database=Inventory;Trusted_Connection=yes].[temp]
SELECT * FROM [temp$];", ExcelConnection)

ExcelCommand.ExecuteNonQuery()
ExcelConnection.Close()
Catch ex As Exception
TextBox1.Text = ex.ToString()
End Try

now the error happen on ...ExcelCommand.ExecuteNonQuery()
It said that

System.Data.OleDb.OleDbException: INSERT INTO ³¯z¦¡¥]§t¥H¤U¥¼ª¾ªºÄæ¦ì¦WºÙ
'F1'¡C½T©w±z¤w¥¿½T¦aÁä¤J¦WºÙ¡AµM«á¦A¸Õ¤@¦¸¡C
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at Acct.test.Button1_Click(Object sender, EventArgs e) in
C:\Inventory\Acct\test.vb:line 92

But there is only 4 column in excel file.."A,B,C and D". Therefore, It
should be nothing in column "F1". Anyone have idea that what's the problem
...

Thanks
Localbar

Douglas Laudenschlager [MS]
7/8/2005 3:29:34 PM

What was the text of the error message, please? I only see the stack trace.

Since you specified HDR=No, the Excel driver assigns the columns names as
F1, F2, and so on. F1 would be column A in your spreadsheet.

-Doug

[quoted text, click to view]

vb.net data : Insert data from excel to SQL server