|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to count existing items??I want to check and see if an item exists within one of my tables
before I go and replicate. let's say that "John Doe" already exists in a names database and the user tries to add him again, i want to notify the user that this name already exists. also, i want to do this programatically(sp?) in C#. any help? Put a unique constraint on the identifying columns. When you try to
insert (using a stored procedure), SQL Server will throw an error (2627). Handle the error in your C# app. JLuv wrote: Show quote > I want to check and see if an item exists within one of my tables > before I go and replicate. let's say that "John Doe" already exists in > a names database and the user tries to add him again, i want to notify > the user that this name already exists. also, i want to do this > programatically(sp?) in C#. > any help? i've never tried that before. is this method called a "unique
constraint"? what should i search for to find information on this? Stu wrote: Show quote > Put a unique constraint on the identifying columns. When you try to > insert (using a stored procedure), SQL Server will throw an error > (2627). Handle the error in your C# app. > > > JLuv wrote: > > I want to check and see if an item exists within one of my tables > > before I go and replicate. let's say that "John Doe" already exists in > > a names database and the user tries to add him again, i want to notify > > the user that this name already exists. also, i want to do this > > programatically(sp?) in C#. > > any help? You could also use the 'Primary Key' -it is by design a 'unique constraint'.
-- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "JLuv" <JLuv***@gmail.com> wrote in message news:1151694549.094788.110810@h44g2000cwa.googlegroups.com... > i've never tried that before. is this method called a "unique > constraint"? what should i search for to find information on this? > > > Stu wrote: >> Put a unique constraint on the identifying columns. When you try to >> insert (using a stored procedure), SQL Server will throw an error >> (2627). Handle the error in your C# app. >> >> >> JLuv wrote: >> > I want to check and see if an item exists within one of my tables >> > before I go and replicate. let's say that "John Doe" already exists in >> > a names database and the user tries to add him again, i want to notify >> > the user that this name already exists. also, i want to do this >> > programatically(sp?) in C#. >> > any help? > You could do this in this manner
USE Northwind GO IF NOT EXISTS ( SELECT (LastName) FROM Employees WHERE ( LastName = 'Fuller' AND FirstName = 'Andrea' ) ) INSERT INTO Employees ( LastName , FirstName ) VALUES ( 'Fuller' , 'Andrea' ) Your application can check the RowCount (RowsAffected) to determine if more 0 (zero) rows were inserted. Then message the user appropriately. -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "JLuv" <JLuv***@gmail.com> wrote in message news:1151692692.332535.294860@75g2000cwc.googlegroups.com... >I want to check and see if an item exists within one of my tables > before I go and replicate. let's say that "John Doe" already exists in > a names database and the user tries to add him again, i want to notify > the user that this name already exists. also, i want to do this > programatically(sp?) in C#. > any help? > i did something like that. i went and updated the database with the
exact same information it already holds. it returns the correct # of columns affected using ExecuteNonQuery(). Arnie Rowland wrote: Show quote > You could do this in this manner > > USE Northwind > GO > > IF NOT EXISTS > ( SELECT (LastName) > FROM Employees > WHERE ( LastName = 'Fuller' > AND FirstName = 'Andrea' > ) > ) INSERT INTO Employees > ( LastName > , FirstName > ) > VALUES > ( 'Fuller' > , 'Andrea' > ) > > Your application can check the RowCount (RowsAffected) to determine if more 0 (zero) rows were inserted. Then message the user appropriately. > > > -- > Arnie Rowland, YACE* > "To be successful, your heart must accompany your knowledge." > > *Yet Another Certification Exam > > > "JLuv" <JLuv***@gmail.com> wrote in message news:1151692692.332535.294860@75g2000cwc.googlegroups.com... > >I want to check and see if an item exists within one of my tables > > before I go and replicate. let's say that "John Doe" already exists in > > a names database and the user tries to add him again, i want to notify > > the user that this name already exists. also, i want to do this > > programatically(sp?) in C#. > > any help? > > Not columns affected -BUT Rows Affected. Notice the use of IF NOT EXISTS.
That prevents adding a row that meets the WHERE clause criteria. -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "JLuv" <JLuv***@gmail.com> wrote in message news:1151694709.234122.133440@75g2000cwc.googlegroups.com... >i did something like that. i went and updated the database with the > exact same information it already holds. it returns the correct # of > columns affected using ExecuteNonQuery(). > > > Arnie Rowland wrote: >> You could do this in this manner >> >> USE Northwind >> GO >> >> IF NOT EXISTS >> ( SELECT (LastName) >> FROM Employees >> WHERE ( LastName = 'Fuller' >> AND FirstName = 'Andrea' >> ) >> ) INSERT INTO Employees >> ( LastName >> , FirstName >> ) >> VALUES >> ( 'Fuller' >> , 'Andrea' >> ) >> >> Your application can check the RowCount (RowsAffected) to determine if >> more 0 (zero) rows were inserted. Then message the user appropriately. >> >> >> -- >> Arnie Rowland, YACE* >> "To be successful, your heart must accompany your knowledge." >> >> *Yet Another Certification Exam >> >> >> "JLuv" <JLuv***@gmail.com> wrote in message >> news:1151692692.332535.294860@75g2000cwc.googlegroups.com... >> >I want to check and see if an item exists within one of my tables >> > before I go and replicate. let's say that "John Doe" already exists in >> > a names database and the user tries to add him again, i want to notify >> > the user that this name already exists. also, i want to do this >> > programatically(sp?) in C#. >> > any help? >> > > yea, i mean row, not column. and i'll try out IF NOT EXIST
Arnie Rowland wrote: Show quote > Not columns affected -BUT Rows Affected. Notice the use of IF NOT EXISTS. > That prevents adding a row that meets the WHERE clause criteria. > > -- > Arnie Rowland, YACE* > "To be successful, your heart must accompany your knowledge." > > *Yet Another Certification Exam > > > "JLuv" <JLuv***@gmail.com> wrote in message > news:1151694709.234122.133440@75g2000cwc.googlegroups.com... > >i did something like that. i went and updated the database with the > > exact same information it already holds. it returns the correct # of > > columns affected using ExecuteNonQuery(). > > > > > > Arnie Rowland wrote: > >> You could do this in this manner > >> > >> USE Northwind > >> GO > >> > >> IF NOT EXISTS > >> ( SELECT (LastName) > >> FROM Employees > >> WHERE ( LastName = 'Fuller' > >> AND FirstName = 'Andrea' > >> ) > >> ) INSERT INTO Employees > >> ( LastName > >> , FirstName > >> ) > >> VALUES > >> ( 'Fuller' > >> , 'Andrea' > >> ) > >> > >> Your application can check the RowCount (RowsAffected) to determine if > >> more 0 (zero) rows were inserted. Then message the user appropriately. > >> > >> > >> -- > >> Arnie Rowland, YACE* > >> "To be successful, your heart must accompany your knowledge." > >> > >> *Yet Another Certification Exam > >> > >> > >> "JLuv" <JLuv***@gmail.com> wrote in message > >> news:1151692692.332535.294860@75g2000cwc.googlegroups.com... > >> >I want to check and see if an item exists within one of my tables > >> > before I go and replicate. let's say that "John Doe" already exists in > >> > a names database and the user tries to add him again, i want to notify > >> > the user that this name already exists. also, i want to do this > >> > programatically(sp?) in C#. > >> > any help? > >> > > > The only real reason to use this 'IF NOT EXISTS' form is if you wish to have
an ELSE -such as update an existing record. Otherwise, just insert the data and let the unique constraint or Primary key force an error and your application can capture the error and react accordingly. -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "JLuv" <JLuv***@gmail.com> wrote in message news:1151695591.304350.187130@i40g2000cwc.googlegroups.com... > yea, i mean row, not column. and i'll try out IF NOT EXIST > > > Arnie Rowland wrote: >> Not columns affected -BUT Rows Affected. Notice the use of IF NOT EXISTS. >> That prevents adding a row that meets the WHERE clause criteria. >> >> -- >> Arnie Rowland, YACE* >> "To be successful, your heart must accompany your knowledge." >> >> *Yet Another Certification Exam >> >> >> "JLuv" <JLuv***@gmail.com> wrote in message >> news:1151694709.234122.133440@75g2000cwc.googlegroups.com... >> >i did something like that. i went and updated the database with the >> > exact same information it already holds. it returns the correct # of >> > columns affected using ExecuteNonQuery(). >> > >> > >> > Arnie Rowland wrote: >> >> You could do this in this manner >> >> >> >> USE Northwind >> >> GO >> >> >> >> IF NOT EXISTS >> >> ( SELECT (LastName) >> >> FROM Employees >> >> WHERE ( LastName = 'Fuller' >> >> AND FirstName = 'Andrea' >> >> ) >> >> ) INSERT INTO Employees >> >> ( LastName >> >> , FirstName >> >> ) >> >> VALUES >> >> ( 'Fuller' >> >> , 'Andrea' >> >> ) >> >> >> >> Your application can check the RowCount (RowsAffected) to determine if >> >> more 0 (zero) rows were inserted. Then message the user appropriately. >> >> >> >> >> >> -- >> >> Arnie Rowland, YACE* >> >> "To be successful, your heart must accompany your knowledge." >> >> >> >> *Yet Another Certification Exam >> >> >> >> >> >> "JLuv" <JLuv***@gmail.com> wrote in message >> >> news:1151692692.332535.294860@75g2000cwc.googlegroups.com... >> >> >I want to check and see if an item exists within one of my tables >> >> > before I go and replicate. let's say that "John Doe" already exists >> >> > in >> >> > a names database and the user tries to add him again, i want to >> >> > notify >> >> > the user that this name already exists. also, i want to do this >> >> > programatically(sp?) in C#. >> >> > any help? >> >> > >> > >  |
|||||||||||||||||||||||
Other interesting topics