Retrieving @@Identity value in ado.net
in [General]
|
Prev: Switchboard in Access 2010
Next: SendObject format
From: David W. Fenton on 19 Mar 2010 14:26 "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in news:#Q4tvS2xKHA.4492(a)TK2MSFTNGP05.phx.gbl: > "Mark Rae [MVP]" <mark(a)markrae.net> wrote in message > news:ekUtT3RxKHA.1548(a)TK2MSFTNGP02.phx.gbl... > >> IMO, Jet has *never* been a viable RDBMS for websites, no matter >> how small. It's simply not designed for that scenario... > > That's ridiculous. There are probably thousands of small websites > tunning on Jet databases. I know of several, one of which ran for > 8 years getting as many as 5,000 hits a day. Eventually that > company sold out and the buyer incorporated the data into a much > larger website. As recently as 1 year ago I created a website > running a Jet database, and it has been running fine. > > What Jet cannot do is handle very high traffic sites. Although > I've seen claims to the contrary, my own experience is that Jet > cannot handle multiple complex queries in high volumes. That's > definitely a SQL-Server realm. But for low traffic work, Jet does > just fine. There are a number of issues with Jet behind a web server. 1. Jet is not thread-safe, which can be a problem for web servers, from what I understand. ADO somehow gets around that, according to Michael Kaplan, so you'd want to be sure you're using ADO/OLEDB to access your data (not ODBC). 2. you can't share the database with interactive client users. If somebody opens the web back end Access, the web server is locked out. 3. compacting is problematic, as the web server keeps the back end open all the time. You might have to shut down your HTTP agent to be able to compact. #1 has a workaround, if it's supported. #2 may not be a problem as most websites aren't going to be sharing a data file with interactive users. But it does make it impossible to do the right thing and present the same data to web users as that seen by Access users. #3 is not problematic for a read-only (or near read-only) web app, but for anything with writes, it's going to be an issue. Perhaps not a big issue (it may be sufficient to stop the web server, compact and restart the web server once a month or so), but still an issue. None of these things is an issue with a proper server database behind your website. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Gregory A. Beamer on 30 Mar 2010 15:40 "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message news:Xns9D3F70CE4CD43f99a49ed1d0c49c5bbb2(a)74.209.136.82... > "Gregory A. Beamer" <NoSpamMgbworld(a)comcast.netNoSpamM> wrote in > news:F39B0569-8841-43C7-A20D-CEF147E0FC71(a)microsoft.com: > >> "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message >> news:OkqjH4JxKHA.1796(a)TK2MSFTNGP02.phx.gbl... >>> Using @@identity is what's wrong. That's a SQL-Server method. >>> Once you've inserted the record, the Value property of the Key is >>> what you query. >> >> This is true if he wants to use 2 queries. He has the option of >> chaining queries or using a stored procedure. Selecting >> MAX(keyvalue) can be off, esp. if you do not lock the table and it >> has any volume in transactions. I would prefer SCOPE_IDENTITY(). > > Arvin is wrong about SELECT @@IDENTITY. > > And you are wrong about batching SQL statements. Jet/ACE does not > and never has supported that, and the very first sentence of the > post asking the question says that it's a Jet/ACE database, not SQL > Server. > > So none of your advice is applicable at all. I assumed the @@IDENTITY meant the user was against SQL Server and rolled from there. If he is not using SQL Server, @@IDENTITY will NEVER work. But if you goal was to publicly state "you're wrong", I am not sure it was really worth the time, unless you are actually going to help with a solution. Just my two cents. Your mileage may vary. -- Peace and Grace, Greg Twitter: @gbworld Blog: http://gregorybeamer.spaces.live.com ************************************************ | Think outside the box! | ************************************************
From: Banana on 30 Mar 2010 15:57 Gregory A. Beamer wrote: > I assumed the @@IDENTITY meant the user was against SQL Server and > rolled from there. If he is not using SQL Server, @@IDENTITY will NEVER > work. As a matter of fact, several RDBMS, including Microsoft Jet/ACE as of version 4.0 support @@IDENTITY - it's hardly exclusive to SQL Server.
From: David W. Fenton on 1 Apr 2010 00:01 "Gregory A. Beamer" <NoSpamMgbworld(a)comcast.netNoSpamM> wrote in news:26F96CC8-F290-4523-854B-9549121815B6(a)microsoft.com: > "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message > news:Xns9D3F70CE4CD43f99a49ed1d0c49c5bbb2(a)74.209.136.82... >> "Gregory A. Beamer" <NoSpamMgbworld(a)comcast.netNoSpamM> wrote in >> news:F39B0569-8841-43C7-A20D-CEF147E0FC71(a)microsoft.com: >> >>> "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message >>> news:OkqjH4JxKHA.1796(a)TK2MSFTNGP02.phx.gbl... >>>> Using @@identity is what's wrong. That's a SQL-Server method. >>>> Once you've inserted the record, the Value property of the Key >>>> is what you query. >>> >>> This is true if he wants to use 2 queries. He has the option of >>> chaining queries or using a stored procedure. Selecting >>> MAX(keyvalue) can be off, esp. if you do not lock the table and >>> it has any volume in transactions. I would prefer >>> SCOPE_IDENTITY(). >> >> Arvin is wrong about SELECT @@IDENTITY. >> >> And you are wrong about batching SQL statements. Jet/ACE does not >> and never has supported that, and the very first sentence of the >> post asking the question says that it's a Jet/ACE database, not >> SQL Server. >> >> So none of your advice is applicable at all. > > I assumed the @@IDENTITY meant the user was against SQL Server and > rolled from there. And you assumed wrong. > If he is not using SQL Server, @@IDENTITY will NEVER work. You are wrong here, too. Jet has supported it starting with Jet 4.0, released in 1999. That's over 10 years, by the way. > But > if you goal was to publicly state "you're wrong", I am not sure it > was really worth the time, unless you are actually going to help > with a solution. Just my two cents. Your mileage may vary. You made erroneous assumptions and posted an erroneous answer. You should check your assumptions before posting. That's all I was suggesting. When you decide to post an answer, try reading the question. If something in the question contradicts your assumptions about the answer to the question (such as the user specifying Jet/ACE and you thinking @@IDENTITY was limited to SQL Server), you should ask for clarification, do some investigating of your own, or just hold your tongue. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Risse on 2 Apr 2010 02:42
"Gregory A. Beamer" <NoSpamMgbworld(a)comcast.netNoSpamM> kirjoitti viestiss�:26F96CC8-F290-4523-854B-9549121815B6(a)microsoft.com... > > > "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message > news:Xns9D3F70CE4CD43f99a49ed1d0c49c5bbb2(a)74.209.136.82... >> "Gregory A. Beamer" <NoSpamMgbworld(a)comcast.netNoSpamM> wrote in >> news:F39B0569-8841-43C7-A20D-CEF147E0FC71(a)microsoft.com: >> >>> "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message >>> news:OkqjH4JxKHA.1796(a)TK2MSFTNGP02.phx.gbl... >>>> Using @@identity is what's wrong. That's a SQL-Server method. >>>> Once you've inserted the record, the Value property of the Key is >>>> what you query. >>> >>> This is true if he wants to use 2 queries. He has the option of >>> chaining queries or using a stored procedure. Selecting >>> MAX(keyvalue) can be off, esp. if you do not lock the table and it >>> has any volume in transactions. I would prefer SCOPE_IDENTITY(). >> >> Arvin is wrong about SELECT @@IDENTITY. >> >> And you are wrong about batching SQL statements. Jet/ACE does not >> and never has supported that, and the very first sentence of the >> post asking the question says that it's a Jet/ACE database, not SQL >> Server. >> >> So none of your advice is applicable at all. > > I assumed the @@IDENTITY meant the user was against SQL Server and rolled > from there. If he is not using SQL Server, @@IDENTITY will NEVER work. But > if you goal was to publicly state "you're wrong", I am not sure it was > really worth the time, unless you are actually going to help with a > solution. Just my two cents. Your mileage may vary. > > -- > Peace and Grace, > Greg > > Twitter: @gbworld > Blog: http://gregorybeamer.spaces.live.com > > ************************************************ > | Think outside the box! | > ************************************************ |