I have written the following code to import data from excel to sql server. But the problem is when i try to insert new data into the table it just reports an error . I am using winfows forms and following code in vb.net

Dim si As String = ListBox1.SelectedItem

Dim fn As String = fname '"290000..xls" 'fname

'Dim connectionstring As String = "Data Source=.\sqlexpress;Initial Catalog=stats.mdf;integrated security=true;"

Dim connectionstring As String = "Server=aabc;Database=statlogs;integrated security=true"

Dim connection As SqlClient.SqlConnection = New SqlClient.SqlConnection(connectionstring)

Dim cmdstr As String

Try

Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("Insert", connection)

command.CommandText = "select * INTOxyz FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," + "'Excel 8.0;Database=" + fname + "'" + ",'SElect * FROM [" + ListBox1.Text.ToString + "$]" + "')"

Catch ex As Exception

Finally

End Try

MessageBox.Show("File successfully inserted")

Hi,

With this error information, it’s much easier to diagnose the problem.

In some cases, a Command object has parameters which contain information needed for the execution of this command. These command parameters must be specified in the code. The error exactly asks you to give parameters to the command object.

I hope this document Configuring Parameters (ADO.NET) could help you.

Regards,

Jacob

Hi,

I can't reproduce the scenario since I don't know thecontent of the EXCEL file and the schema of the statlogs.xyz table. The following are some general guidanceof trouble shooting your problem.

1. Check if you can run the insert SQL commend alone in the SQL Server Management Studio by doing this: set a break point at command.ExecuteNonQuery and run your program in the debug mode. When the program pauses at the break point, copy the value of the command.CommandText object and paste the text string in the SQL Server and execute this command seperately.

a. If this command executes successfully, itmeans that the syntax of this command is correct.The data in the Excel file are compatible with the schema of your database. The insertion doesn't violates any database constraints.

b. If this command fails, check that line of code.

2. Show us the detail information of the exception thrown by your application.

Regards,

Jacob

HI!

I have tried all of the things you have written above but i am getting the following error:

Awais Omar

Hi,

With this error information, it’s much easier to diagnose the problem.

In some cases, a Command object has parameters which contain information needed for the execution of this command. These command parameters must be specified in the code. The error exactly asks you to give parameters to the command object.

I hope this document Configuring Parameters (ADO.NET) could help you.

Regards,

Jacob

but how do i can configure command objects because i dun't how to do this??

Hi,

If you read the link I gave you, you will find sample code telling you how to deal with command objects and their parameters. Although the sample code uses the SQLCommands and SQLParameters, but they all inherit from the Command and Parameter base class. That is to say, the Command and Parameter class for OLEDB function the same.

You will do something like this:

command.Parameters.Add(yourParameters);

Regards,

Jacob