importing/exporting excel and SQL database

[manared]

I am using vb.net 2005 and SQL 2005. I want to be able to export a SQL table into a Microsoft Excel File using .net. I also want to be able to upload an Excel file and put it into a SQL database. How would I go about doing this? Do I have to convert to CVS or something at all? What's the easiest/best way? Thank you in advance.

 

[vis781]

You should be able to use a query like the following to exprt data into an excel worksheet....

strSQL = "SELECT * INTO [Sheet1] IN '" & App.Path & _
"\book1.xls' 'Excel 8.0;' FROM TableName"

 

[manared]

So all I need to do is write that query exactly how it is (changing the table name) and then run an execute query?
When I tried this last time, it says that App is not defined. How do I need to change that?

 

[vis781]

App.Path as in the path to the excel workbook/sheet

 

[manared]

This is my code right now:

Dim strSQL As String
strSQL = "SELECT * INTO [Sheet1] IN 'C:\book1.xls' 'Excel 8.0;' FROM Issues"
Dim command As New SqlCommand
conn.Open()
command.Connection = conn
command.CommandText = strSQL
command.ExecuteReader()
conn.Close()

The error states "Incorrect syntax near the keyword 'IN'"

 

[vis781]

There's a number of ways to skin this cat, i thought that you were further along than you are, i suggest you start at msdn here's a couple of links

http://support.microsoft.com/?kbid=319951

This may also be worth a look.

http://www.vbdotnetheaven.com/Uploa...rticleID=f55ad8bb-b9d8-4e71-a278-3b0d9665abdc

 

[manared]

thanks. going to the SQL server and running the wizard, I can get the data to export to Excel. I have to actually be on the server to do this because I need SP1 on my local machine to do it. I may have to do that at some point. But now I need to figure out how to do it using vb.net, like have the user press a button and then the data just goes to excel. I'm working on the second link you gave me. Trying to figure out how to update it to the 2005 version. I'm just having trouble with the Table.column and table.row in the loops.