OdbcDataApdater in vs.net2003

  • Thread starter Thread starter Florian Lutz
  • Start date Start date
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
 
did you ever get an answer, i have the same problem and i have primaty keys
in access. Is there a trick(?)
thanks
kes
 
Hi,

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

florian
 
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.
 
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
 
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.
 
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.
 
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
 
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.
 
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.
 
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
 
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.
 
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.
 
Back
Top