OdbcDataApdater in vs.net2003

F

Florian Lutz

hello,

i have an access file that i included as an odbc source in my system.
when i try to create an OdbcDataApdater in vs.net2003 an use something
like:
SELECT *
FROM table
WHERE (user = @user)

vs.net can't create the insert/update methods. something like

SELECT *
FROM table
WHERE (user = "lalala")

works.

how come?

thanks

florian
 
G

Guest

did you ever get an answer, i have the same problem and i have primaty keys
in access. Is there a trick(?)
thanks
kes
 
F

Florian Lutz

Hi,

instead of using the odbc connector, i switched to the ole jet driver,
which works just as well.

florian
 
D

David Sceppa

Kurt and Florian,

The ODBC.NET Data Provider supports the ODBC-style parameter marker
"?". Try "SELECT * FROM table WHERE (user = ?)" instead of "SELECT * FROM
table WHERE (user = @user)".

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 
G

Guest

Thanks for replying.
I am able to do this if i write it by hand. however vs.net will not create
the update and delete commands if i use odbc.
thanks
kes
 
D

David Sceppa

Kurt,

It sounds like VS.NET can't get the key information necessary to build
UPDATE and DELETE queries. Some drivers are unable to return this
information. What ODBC driver are you using?

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 
D

David Sceppa

Kurt,

If the message in the dialog says "Could not determine which columns
uniquely identify the rows for <YourTableHere>", you'll need to define a
primary key on your table. The wizard requires a primary key to ensure
that UPDATE and DELETE queries the DataAdapter executes to submit pending
changes and deletions affect at most one row.

If you received a different message, what was it?

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 
G

Guest

That was the message. My primary ket is an autonumber, or identity. It has
the little kety symbol when i edit the table. If i create an OledbDataAdapter
using the same table i do not have a problem. It see's that there is a
primary key. My question is: if it works with a oledb why not odbc? If i
create the items without the code designer i can get it to update and delete
without a problem using the primary key. But, The Designer will not reconize
the primary key if i use the odbc namespace.

Most everyone here just says, "I use oledb and it works ok." That's fine,
but not always the easist solution if you are working with a hosting company.

My question is still this: Is there a problem with the odbc namespace or is
it being depreciated? Has anyone been able to build the statements using the
VS.net codedesigner when the odbc namespace is being used?

thanks
kes
 
D

David Sceppa

Kurt,

That's the first I've heard of the problem in VS.NET 2003. I created
an OdbcDataAdapter against the sample Northwind table with "SELECT * FROM
Orders WHERE CustomerID = ?" and didn't see a problem. Could you give that
a shot and let me know what you see?

The System.Data.Odbc namespace is not being deprecated.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 
D

David Sceppa

Kurt,

I tried a SQL Server 2000 Northwind database but am more interested in
your scenario. If you're working with an Access database, I would strongly
recommend using the Jet 4.0 OLE DB provider rather than the ODBC driver.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 
G

Guest

that's what most people say (in fact all). I am using the oledbdata namespace
now. However, the same question is is there an issue with the odbc driver for
msaccess. In sql server i'd use the sqldbnamespace, because there is no need
for path info. and it's faster.
thank
kes
 
D

David Sceppa

Kurt,

With the Jet ODBC driver, I saw the "Could not determine which columns
uniquely identify the rows for "YourTableHere" error. With the Jet OLE DB
provider, the DataAdapter Wizard was able to build updating logic for
"SELECT * FROM Orders WHERE CustomerID = ?".

Good call regarding using SqlClient for SQL Server. You get better
performance and more features.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 
D

David Sceppa

Kurt,

No worries.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - retrieve data on last create named range from previous inserted named range 0
dbo. in vs generated code for stored procedures. 2
Data form wizard 1
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
ASP.NET: dollar sign ($) on the page changed to not displayable character 4
Is a second class factory the only way to get an IDbDataAdapter reference? 1
VS.NET2003 & emulator problem! 1
Provider independent data access: problem with Excel ODBC source 3

Top