read/write excel using odbc

[Guest]

how can i write to an excel .xls file using odbc?
i've read in several places that its possible but i can't find an example.

i have managed to read an excel file using odbc

 

[Andrew Faust]

Pretty much the same way you would write data to any database using ODBC.
Found this example on The Code Project of doing this in c++. The language
is different, but the actual SQL statements would be the same for C#. One
thing to notice in the example is that the connection string has the
parameter READONLY=FALSE.

http://www.codeproject.com/database/excel_odbc_write.asp

Disclaimer: I haven't tested this example. However, Code Project has always
been a reliable source for me.

 

[Guest]

i couldn't translate that example at codeproject. i'm trying to use this code:

Dim ConnectionString As String = "Driver={Microsoft Excel Driver
(*.xls)};DriverId=790;Dbq=" & Application.StartupPath & "\test.xls;"

Dim Con As New OdbcConnection(ConnectionString)

Dim strSQL As String = "insert into [Sheet1$] ([Quantity],

,
[Product]) values (0, 'testtext2', 'testtext3')"

Dim cmd As New OdbcCommand(strSQL)
cmd.Connection = Con
Con.Open()
cmd.ExecuteNonQuery()


it works ok until it gets to cmd.ExecuteNonQuery() when it causes an error:

ERROR [HY000] [Microsoft][ODBC Excel Driver] Operation must use an
updateable query.

any ideas?






[QUOTE="Andrew Faust"]
Pretty much the same way you would write data to any database using ODBC.
Found this example on The Code Project of doing this in c++. The language
is different, but the actual SQL statements would be the same for C#. One
thing to notice in the example is that the connection string has the
parameter READONLY=FALSE.

http://www.codeproject.com/database/excel_odbc_write.asp

Disclaimer: I haven't tested this example. However, Code Project has always
been a reliable source for me.

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com


[QUOTE=".paul."]
how can i write to an excel .xls file using odbc?
i've read in several places that its possible but i can't find an
example.

i have managed to read an excel file using odbc[/QUOTE]
[/QUOTE]

 

[Rad [Visual C# MVP]]

how can i write to an excel .xls file using odbc?
i've read in several places that its possible but i can't find an example.

i have managed to read an excel file using odbc

Is there any particular reason you're using ODBC?

 

[sloan]

A couple of things:

1. Try to avoid ODBC, and use OleDB if you can.
Google "IDataReader Excel" and you'll find connection strings for that.

2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find out.
This will help determine if your connection string is off, or you're ONLY
having an insert issue.

3. I believe (long term memory working here) there is something about a
column having to have a "primary key" on it (yes, I"m talking about excel)
before you can do update/insert commands on an excel spreadsheet.
You'll have to search, I don't know any links for key phrases on that one.

...

Please post your resolution when you're done so other can learn from the
experience.

 

[Guest]

there was a problem with the connection string.
i changed it to this and it worked:

Dim ConnectionString As String = "Driver={Microsoft Excel Driver
(*.xls)};READONLY=FALSE;DriverId=790;Dbq=" &
Directory.GetParent(Directory.GetParent(My.Application.Info.DirectoryPath).ToString).ToString & "\joblog.xls;"

A couple of things:

1. Try to avoid ODBC, and use OleDB if you can.
Google "IDataReader Excel" and you'll find connection strings for that.

2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find out.
This will help determine if your connection string is off, or you're ONLY
having an insert issue.

3. I believe (long term memory working here) there is something about a
column having to have a "primary key" on it (yes, I"m talking about excel)
before you can do update/insert commands on an excel spreadsheet.
You'll have to search, I don't know any links for key phrases on that one.

...

Please post your resolution when you're done so other can learn from the
experience.

how can i write to an excel .xls file using odbc?
i've read in several places that its possible but i can't find an example.

i have managed to read an excel file using odbc

 

[sloan]

Which is why I asked.

Getting Excel connection strings is ..... very very tricky sometimes.

; space quotes, they'll screw you.

..................

You still may want to look at the OleDB provider, there is no reason to use
ODBC anymore.

http://support.microsoft.com/kb/316934
(except dont hack together a presentation/datalayer spaghetti like they did
on the sample)

there was a problem with the connection string.
i changed it to this and it worked:

Dim ConnectionString As String = "Driver={Microsoft Excel Driver
(*.xls)};READONLY=FALSE;DriverId=790;Dbq=" &
Directory.GetParent(Directory.GetParent(My.Application.Info.DirectoryPath).ToString).ToString
& "\joblog.xls;"

A couple of things:

1. Try to avoid ODBC, and use OleDB if you can.
Google "IDataReader Excel" and you'll find connection strings for that.

2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find
out.
This will help determine if your connection string is off, or you're ONLY
having an insert issue.

3. I believe (long term memory working here) there is something about a
column having to have a "primary key" on it (yes, I"m talking about
excel)
before you can do update/insert commands on an excel spreadsheet.
You'll have to search, I don't know any links for key phrases on that
one.

...

Please post your resolution when you're done so other can learn from the
experience.

how can i write to an excel .xls file using odbc?
i've read in several places that its possible but i can't find an
example.

i have managed to read an excel file using odbc

 

[Andrew Faust]

You still may want to look at the OleDB provider, there is no reason to

use ODBC anymore.

That's pretty extreme. If you happen to need to connect to the largest
number of different databases ODBC is still hugely valuable. While all the
major DBs support OleDB these days, there are a number I've needed to read
from that only had ODBC. Of course it's no big deal simple use
DBConnection, DBReader, etc and instantiate the appropriate class

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com

Which is why I asked.

Getting Excel connection strings is ..... very very tricky sometimes.

; space quotes, they'll screw you.

.................

You still may want to look at the OleDB provider, there is no reason to
use ODBC anymore.

http://support.microsoft.com/kb/316934
(except dont hack together a presentation/datalayer spaghetti like they
did on the sample)

there was a problem with the connection string.
i changed it to this and it worked:

Dim ConnectionString As String = "Driver={Microsoft Excel Driver
(*.xls)};READONLY=FALSE;DriverId=790;Dbq=" &
Directory.GetParent(Directory.GetParent(My.Application.Info.DirectoryPath).ToString).ToString
& "\joblog.xls;"

A couple of things:

1. Try to avoid ODBC, and use OleDB if you can.
Google "IDataReader Excel" and you'll find connection strings for that.

2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find
out.
This will help determine if your connection string is off, or you're
ONLY
having an insert issue.

3. I believe (long term memory working here) there is something about
a
column having to have a "primary key" on it (yes, I"m talking about
excel)
before you can do update/insert commands on an excel spreadsheet.
You'll have to search, I don't know any links for key phrases on that
one.

...

Please post your resolution when you're done so other can learn from
the
experience.





how can i write to an excel .xls file using odbc?
i've read in several places that its possible but i can't find an
example.

i have managed to read an excel file using odbc

 

[sloan]

Let me rephrase.

There's no reason to add the extra layer of ODBC, when a more native
provider exists.

Aka, you can read excel without odbc.

But yeah, you want to keep the ODBC around for those who don't have a more
native provider.

...

Like , it would be kinda silly to use ODBC for Sql Server.

But for .. I know know.. Paradox or something , ODBC is a viable tool.

//Rephrased
You still may want to look at the OleDB provider, there isn't alot of good
reason to
use ODBC anymore WITH Excel because there is a better option FOR Excel.
Extra layers slow the performance slightly.

You still may want to look at the OleDB provider, there is no reason to
use ODBC anymore.

That's pretty extreme. If you happen to need to connect to the largest
number of different databases ODBC is still hugely valuable. While all the
major DBs support OleDB these days, there are a number I've needed to read
from that only had ODBC. Of course it's no big deal simple use
DBConnection, DBReader, etc and instantiate the appropriate class

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com

Which is why I asked.

Getting Excel connection strings is ..... very very tricky sometimes.

; space quotes, they'll screw you.

.................

You still may want to look at the OleDB provider, there is no reason to
use ODBC anymore.

http://support.microsoft.com/kb/316934
(except dont hack together a presentation/datalayer spaghetti like they
did on the sample)

there was a problem with the connection string.
i changed it to this and it worked:

Dim ConnectionString As String = "Driver={Microsoft Excel Driver
(*.xls)};READONLY=FALSE;DriverId=790;Dbq=" &
Directory.GetParent(Directory.GetParent(My.Application.Info.DirectoryPath).ToString).ToString
& "\joblog.xls;"


:


A couple of things:

1. Try to avoid ODBC, and use OleDB if you can.
Google "IDataReader Excel" and you'll find connection strings for that.

2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find
out.
This will help determine if your connection string is off, or you're
ONLY
having an insert issue.

3. I believe (long term memory working here) there is something about
a
column having to have a "primary key" on it (yes, I"m talking about
excel)
before you can do update/insert commands on an excel spreadsheet.
You'll have to search, I don't know any links for key phrases on that
one.

...

Please post your resolution when you're done so other can learn from
the
experience.





how can i write to an excel .xls file using odbc?
i've read in several places that its possible but i can't find an
example.

i have managed to read an excel file using odbc

 

[Rad [Visual C# MVP]]

That's pretty extreme. If you happen to need to connect to the largest
number of different databases ODBC is still hugely valuable. While all the
major DBs support OleDB these days, there are a number I've needed to read
from that only had ODBC. Of course it's no big deal simple use
DBConnection, DBReader, etc and instantiate the appropriate class

But for some applications performance is a critical issue -- ODBC is
slower than OLEDDB

 

[Andrew Faust]

Not disputing it. I totally agree you should use the other providers when
available. I was just pointing out that there are databases for which there
is only ODBC, thus there is sometimes a reason to use ODBC.

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com

 

[sloan]

Gotcha.

I should have had "With Excel" at the end of it to avoid the ambuguity.

Not disputing it. I totally agree you should use the other providers when
available. I was just pointing out that there are databases for which
there is only ODBC, thus there is sometimes a reason to use ODBC.

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com

But for some applications performance is a critical issue -- ODBC is
slower than OLEDDB