Re: How To Add/Update Record In ADO.NET

That's okay--we get all skill levels here and people doing everything from simple to complex stuff. When it comes time to choose it's probably best to use the free stuff first--especially when you're on a budget. Access is an entirely separate and distinct way of handling data. It's just fine for home and (very) small business use. It has a number of limitations that don't make it particularly suitable where data has to be secure or shared beyond a few casual users.

Keep asking questions--just tell them the whole story when you do.
As to the "test to see if it's already there" issue, put a unique primary key on the Identity column. Next, when it's time to add a row, it does not make any difference if it's there or not--it's always added. If you want to make sure there aren't duplicate rows (for example, by Name or StoreName, put a unique index on those columns which make up the unique key of the table. Then JET (the database engine) prevents duplicates from being added. In this case, just do your INSERT. If the row is already there (there is already someone with the same name in the database), the INSERT will fail. If it does, turn around and execute the UPDATE.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Jeff Gaines" <whitedragon@xxxxxxxxxxxxxxxxx> wrote in message news:xn0fayq3qzkwrr001@xxxxxxxxxxxxxxxxxxxxxxx
On 08/09/2007 in message <OVFJEAk8HHA.4612@xxxxxxxxxxxxxxxxxxxx> William Vaughn wrote:

Thanks Cor.

Actually, Jeff, since you're re-writing, I might consider another DBMS engine--one that's not being phased out by Microsoft. SQL Server Compact Edition or Express Edition come to mind. This gives you the flexibility to write more sophisticated SQL statements that include logic to execute the appropriate command in a single round-trip.
Consider that if JET says the row is not present, you might still get an concurrency collision when another application is sharing the database and inserts a row after your SELECT but before your INSERT. I might try to run this in a transaction or simply try the INSERT first. In this case you'll have to consider that your instance of the application is overlaying someone else's data. Is your data right or is theirs?

If this is a single-user application, then SQLCe is likely ideal for your needs. It also makes the logic simpler. In this case you can create an ADO classic-like Recordset object that can be updated in place.

hth

Hi William.

I have just paid a small fortune for Access 2007 so I don't want to give up on it yet!
I write small apps as a hobby, all single user, the dog can't use the computer - he doesn't know my password!

As I said in my reply to Cor, I am looking for a simple way of knowing if a record exists or not so I can decide whether to update it or add a new record.

--
Jeff Gaines

.

Relevant Pages

  • Re: Which Method to Create a Database Do I Use?
    ... Okay, once the database is built, you have to get rights to access it. ... SSPI and "SQL Server" ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Which Method to Create a Database Do I Use?
    ... >> SQL Server Configuration Manager ... This mode assumes that the Windows User or IIS has rights to access the SQL Server itself and the database mentioned as the initial catalog in the Connection String. ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... "Jonathan Wood" wrote in message ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How do you clear a datatable?
    ... When i look at the database in Server Explorer, ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: OleDbParameter vs. manually SQL Statement to Insert mass date from host
    ... But I think a bulkcopy is a "external" Database tool, ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... conn = new OleDbConnection; ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Advice On How To Keep Data Please
    ... XML, JET database, SQL Server database or some other source? ... Do you plan to pass the stored data around? ... Hitchhiker's Guide to Visual Studio and SQL Server ... Jeff Gaines ...
    (microsoft.public.data.ado)
Loading