Preventing Duplicate Database Entries
Jan 31, 2006
Hi all.. I've been scouring the forums for about 6 hours to no
avail. This is a really simple question. I'm trying to have
a registration page that lets the user input name, email, desired
username, and password. I want to check the username and email
fields to make sure ppl cannot sign up twice. So from what I've
gathered I have a couple of options:
1) i can set up a unique constraint on the database columns,
2) i can run a select statement before inserting,
3) i can store the whole database column in a variable then search through it.
My question is how to do option 2? All of my transactions are through a sqldatasource object in c#.
View 6 Replies
ADVERTISEMENT
Jun 25, 2004
Hello,
How can I delete duplicate entries from tables in my database using Query Analyzer, as there are many duplicate entries in my tables, I want to delete them.
Thanks in advance,
Uday.
View 4 Replies
View Related
Jan 10, 2006
My problem is that the 'INSERT INTO' query that sends the records to thetable is dynamically compiled in VBA and and the target table has a twocolumn primary key. I have made a number of attempts at getting 'WHERENOT EXISTS' to cure the problem but so far without success and previouspostings have resulted in advice to create an 'ignore duplicates' index.This solved the problem in asmuch as it allowed the SQL to insert the records that did not alreadyexist but resulted in the message appearing every time the user ran thethe query. Whilst this is not a major problem it is vaguely irritatingand I would like to find a way to stop it happening. I suspect that thesolution may involve using the @@ERROR command but I am not sure of thesyntax.RegardsColin*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Jul 23, 2005
I have a table using an identity column as its Primary Key and twocolumns (table reduced for simplicity) EmployeeNumber and ArrivalTime.CREATE TABLE [tblRecords] ([ID] [bigint] IDENTITY (1, 1) NOT NULL ,[EmployeeNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,[ArrivalTime] [datetime] NOT NULL ,CONSTRAINT [PK_tblRecords] PRIMARY KEY CLUSTERED([ID]) ON [PRIMARY]) ON [PRIMARY]GOI have an insert procedure that checks for duplicates before insertinga new record:IF (SELECT TOP 1 [ID] FROM tblRecords WHERE EmployeeNumber =@SocialSecurity) IS NULLBEGININSERT INTO tblRecords(EmployeeNumber,ArrivalTime)VALUES (@EmployeeNumber, @ArrivalTime)SELECT SCOPE_IDENTITY()ENDELSESELECT 0 AS DuplicateRecordIn 99.9% of the cases, this works well. However, in the event that theinsert attempts are literally "ticks" apart, the "SELECT TOP 1..."command completes on both attempts before the first attempt completes.So I end up with duplicate entries if the procedure is called multipletimes vey quickly. The system needs to prevent duplicateEmployeeNumbers within the past 45 days so setting the EmployeeNumberto UNIQUE would not work. I can check for older entries (45 days ornewer) very easily, but I do not know how to handle the times when theprocedure is called multiple times within milliseconds. Would aTRANSACTION with a duplicate check after the INSERT with a ROLLBACKwork in this case? Any help is greatly appreciated!-E
View 18 Replies
View Related
Oct 24, 2007
Hi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..
View 10 Replies
View Related
Oct 26, 2007
Hi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this article http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005
I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit....thanks ..
View 2 Replies
View Related
Mar 4, 2015
In my trivial example below I have a Nationality entity. The entity just has the code and Name attributes, with the code being an automatically generated Integer. I am inserting one record and then trying to prevent the same record being inserted, by setting the ImportType to 1:
truncate table [stg].[Nationality_1_Leaf]
insert into [stg].[Nationality_1_Leaf](importType, ImportStatus_id, BatchTag, Name)
values(1, 0, 'some batch' , 'American' )
select * From [stg].[Nationality_1_Leaf]
exec stg.udp_Nationality_1_Leaf 'VERSION_1', 1, 'some batch'
After this I truncate the staging table and repeat the process. Unfortunately a new record is entered into the Entity even though the name is identical to a record that already exists.
View 13 Replies
View Related
Mar 28, 2006
I'm extracting data from a log (log_history) of patients where nurses perform various actions on a call, such as assessing and reassessing, despatching etc. This is the script:
Select
L.URN,
LH.THE_TIMESTAMP,
LH.ACTION_TYPE,
LH.ACTION_BY,
LH.ACTION_REQD,
LH.NOTE,
em.position_type_ref
From
LOG L
Join Log_history LH on (L.URN = LH.LOG_URN)
left outer join employee em on (em.code = LH.action_by)
Where
(L.Taken_at >= :DateFrom and L.Taken_at <= :DateTo) and (LH.ACTION_TYPE = 'D') and (em.position_type_ref ='NU')
Order By
L.URN ASC, LH.THE_TIMESTAMP DESC
The result I get shows duplicate 'timestamp' entries and I only want to return unique timestamp entries. Does anyone have any ideas. I'm self taught and have hit a wall
View 7 Replies
View Related
Feb 7, 2007
I have an application that allows the user to enter data into a table. There are multiple users so I put in some code that, I thought, would keep 2 users from creating a new record at the same time. The IDs for the records are identical and this is causing a problem.
The IDs are in the format of ####-mmyy. at the start of each month the #### part goes back to 1.
We tried a test today where we had 2 users click on the New button at exactly the same time. The IDs that were created were identical. Is there anyway on the database that I can prevent this from happening?
Here is how I create the new record id:
I get the MAX(ID) from the table
I add 1 to the ID and then insert a new record with the new ID into the table.
Any help is appreciated.
Thanks,
enak
View 5 Replies
View Related
Sep 14, 2007
I have an issue where certain parts of data are repeated several times after i create my query. Without providing my SQL code for now could anyone suggest possibly the main reason(s) for data being duplicated?
Thanks
View 3 Replies
View Related
May 8, 2008
I am a newb at ms sql and was hoping someone could help me
eliminate duplicate PRODUCT.PRODUCT from this statement. I have tried using DISTINCT with the same results.The ProductImage table is causing this because
the duplicates are from the PRODUCT.PRODUCT that have more than 1 image.
If anyone could rewrite this statement so I can learn from this, it would
be most appreciated!
Thank you for your time
<asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"SelectCommand="SELECT Product.Product.productid,Product.Product.catid,Product.Product.name,Product.Product.smalltext,Product.Product.longtext,Product.Product.price,Product.ProductSpecial.saleprice, Product.ProductSpecial.feature,Product.ProductImage.imgId, Product.ProductImage.imgUrlFROM Product.ProductINNER JOIN Product.ProductSpecialON Product.ProductSpecial.productid = Product.Product.productidINNER JOIN Product.ProductImageON Product.Product.imgid = Product.ProductImage.imgId"></asp:SqlDataSource>
View 6 Replies
View Related
Feb 29, 2000
I have a field called RegId. RegId is of datatype NVARCHAR (20).
RegId
-----
12322
2122111
23423
etc
etc
I want to run a query to find out if there are duplicate entries in this field.
Any ideas on how I can achieve this?
Thanks in advance,
Anthony
View 1 Replies
View Related
Jan 2, 2008
Hi.
I have a table with Login and Logoff Time of users, but there could be duplicate Logtimes in the dataset, but for
different products. Because of this I cant do a distinct in the dataset. I need the Product and some other details in my Report.
I tried to make two datasets. One for the Select distinct and one for the other.
But the Problem is:
in my report, I need a table, where I make the Sum of the Logintime a day and in another column I calculate with data from the other dataset.(Logtime + data from dataset2). But this doesnt work, so I think, that is it not possible to join 2 dataset in one table.
datetime Login | datetime Logout | Product
11.12.2007 10:15 | 11.12.2007 12:15 | p1
11.12.2007 10:15 | 11.12.2007 12:15 | p2
11.12.2007 12:19 | 11.12.2007 15:15 | p2
Is there another option I can do this?
View 4 Replies
View Related
Sep 20, 2007
Hi! I am joining 3 tables in SQL , I am getting the results I want exept it's duplicated. So the resultinmg table fom my stored procedure has 3 rows that have the same bulletin. How do I filter the storedprocedure to output only the rows that don't have duplicate entries for the column 'Bulletin' Thanks.
Here is my stored procedure:PROCEDURE [dbo].[spGetCompBulletins]
@Userid uniqueidentifier OUTPUT,@DisplayName varchar(200)
AS
SELECT *
FROM dbo.UserProfile INNER JOIN
dbo.bulletins ON dbo.UserProfile.UserId = dbo.bulletins.Userid INNER JOINdbo.Associations ON dbo.Associations.BusinessID = dbo.bulletins.Userid WHERE UserProfile.DisplayName=@DisplayName
and Userprofile.Userid = @Userid ORDER BY Bulletins.Bulletin_Date
Return
View 7 Replies
View Related
Nov 11, 2003
I have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember.
The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID.
Here is a sample of some data:
User1 Group1
User1 Group2
User2 Group2
User3 Group1
User3 Group3
Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example:
User1 Group1
User2 Group2
User1 Group1
I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome.
Is there a simpler way to prevent duplicate entries in a table using sql?
Thanks a lot,
Chris
View 3 Replies
View Related
Oct 17, 2005
Obviously, I'm a complete n00b at SQL.
I have a table in Access 2003 with about 6,000 records and there are about 20 records that have duplicate data in the first field (CompID).
I'm trying to make the first field my primary key, so I need to fix these duplicate entry.
I could export to Excel and fix the problem that way, but in the interest of learning SQL I want to figure out how to do it properly.
Thanks in advance for what is hopefully a simple answer.
View 1 Replies
View Related
Jan 3, 2007
Hi All !
Below is a snippet of MS SQL inside ASP that retieves
Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get back green, red, blue, and yellow which is correct. On another similar search different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic Glass Sand. I want to remove the repeating elements returned in this field. I hope this makes sense.
PS I tried to use distinct but with no luck I want just one of each in the example below.
Thanks in Advance!
Scott
==============================
SQL = ""
SQL = "SELECT B.CIMS_MSDS_NUM," & _
"A.COMMODITY_NUMBER, " & _
"B.CIMS_TRADE_NME," & _
"B.CIMS_MFR_NME," & _
"B.CIMS_MSDS_PREP_DTE," & _
"B.APVL_CDE," & _
"COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _
"COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _
"A.MSDS_CMDTY_VERIF, " & _
"A.CATALOG_ID " & _
"FROM ( MATEQUIP.VMSDS_CMDTY A " & _
" RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _
" ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _
" LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _
" ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _
" LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _
" ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) "
SQL1 = ""
SQL1 = SQL
SQL = SQL & "WHERE " & Where & " "
==================================
Here is a piece of the problem field, note repeating colors etc.
CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS
View 2 Replies
View Related
Feb 8, 2007
Yet another simple query that is eluding me. I need to find records in a table that have the same first name and last name. Because the table has a primaty key, these people were entered twice or they share the same first and last name.
How could you query this:
ID fname lname
10001 Bill Jones
10002 Joe Smith
10003 Sue Jenkins
10004 John Sanders
10005 Joe Smith
10006 Harrold Simpson
10007 Sue Jenkins
10008 Sam Worden
and get a result set of this:
ID fname lname
10002 Joe Smith
10005 Joe Smith
10003 Sue Jenkins
10007 Sue Jenkins
View 2 Replies
View Related
Sep 10, 2007
Hi I am trying to insert entries in a table which has a composite primary key and i am inserting it on UID basis.
INSERT INTO TABLE_B (TABLE_B_UID,NUM_MIN, NUM_MAX,BIN, REGN_CD, PROD_CD, CARD)
(SELECT UID,LEFT(NUM_MIN,16),LEFT(NUM_MAX,16),BIN, REGN_CD, PROD_CD, CARD FROM TABLE_A WHERE UID NOT IN (SELECT TABLE_B_UID FROM TABLE B))
When i insert it tries to insert a duplicate entries and gives me an error. Since I am new to SQL SERVER 2000 i need some help. I tried IF NOT EXISTS, EXCEPT but i guess i am wrong at the syntax.
Can anybody help me out?
View 20 Replies
View Related
Sep 18, 2007
Hi,
I have a table with no primary key and i just want to see all the duplicate entries on the basis of two columns. Can anyone suggest me how should i go about it.
Can anyone provide me the syntax for the same?
I have only 1 table say ISSR_TBL and two columns using which i want to delete the duplicate ones. i.e. MIN and MAX.
Please help me out...
View 3 Replies
View Related
Mar 2, 2006
I have a database being populated by hits to a program on a server.The problem is each client connection may require a few hits in a 1-2second time frame. This is resulting in multiple database entries -all exactly the same, except the event_id field, which isauto-numbered.I need a way to query the record w/out duplicates. That is, anyrecords exactly the same except event_id should only return one record.Is this possible??Thank you,Barry
View 14 Replies
View Related
Jan 3, 2007
Hi All,
Below is a snippet of MS SQL inside some VB that retieves
Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense.
Below is the SQL and the results from the returned page.
PS I tried to use distinct but with no luck I want just one of each in the example below.
Thanks in Advance!
Scott
==============================
SQL = ""
SQL = "SELECT B.CIMS_MSDS_NUM," & _
"A.COMMODITY_NUMBER, " & _
"B.CIMS_TRADE_NME," & _
"B.CIMS_MFR_NME," & _
"B.CIMS_MSDS_PREP_DTE," & _
"B.APVL_CDE," & _
"COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _
"COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _
"A.MSDS_CMDTY_VERIF, " & _
"A.CATALOG_ID " & _
"FROM ( MATEQUIP.VMSDS_CMDTY A " & _
" RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _
" ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _
" LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _
" ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _
" LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _
" ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) "
SQL1 = ""
SQL1 = SQL
SQL = SQL & "WHERE " & Where & " "
==================================
Here is a piece of the problem field, note repeating colors etc.
CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11
View 1 Replies
View Related
Aug 22, 2007
Here is my situation. I have a table in my application that pairs users with cars they like. We'll call this table Favorites. A user can browse the site and they can designate as many cars they want as favorites. For example, a user can go to the Honda Accord page and add that as a favorite car and then go to the Toyota Camry page and add that as a favorite car. However, if he/she goes to that Honda Accord page and tries to click the "Add to Favorites" button again, at the present state of my application, it will just add another entry into the Favorites table with a duplicate pairing. So, if I were to datalist the table to generate a listing of all favorites belonging to a certain user, he/she may potentially be returned with superfluous duplicate entries. Not to mention, taking up valuable database space and not looking very professional.
In my Favorites table, the 3 fields are.....favoriteId (set as primary key)userIdcarId
I've been thinking about this for awhile and I've come up with 2 solutions. I'm a newbie to ASP.NET/programming so I don't have enough insight to make a decision or to even think up of other alternatives.
1) Check proactively by doing a.....SELECT favoriteID FROM Favorites WHERE userId = x and carId = y (where x and y are variables)If I get a null return, it means I can go ahead and let the user add the car as a favorite in the database. If I get a valid value, then it means there already exists the same pairing, so I exit out without updating the table.
2) Check reactively by forcing an exception whenever a user tries to enter a duplicate pairing. I'm not sure how to do this, but perhaps, instead of making "favoriteId" a primary key, perhaps, I can make a primary key pairing of "userId" and "carId". And by trying to do an insert with a primary key that already exists, we know it won't work since primary keys by definition are unique.
Now, I expect some concurrent users on my site, so I must take into consideration pros and cons of each and determine which is more efficient. Checking proactively will force a check even if the table does not contain a duplicate pairing of user and car. However, having a duplicate primary key may be more expensive from a database point of view and may slow down lookups, etc. Or maybe neither has significant benefits, in which case, I rather go with proactive, since I've already coded it and it works fine. Or maybe there is a third alternative, which I did not think. Which method do programmers usually take and which is a better practice?
TIA for your help.
View 5 Replies
View Related
Jun 12, 2014
I got error while updating a table ,duplicate entries and trigger got fired ..so how to debug the trigger to know duplicate record.
View 1 Replies
View Related
Jun 11, 2015
Someone ran an update statement multiple times so their are multiple entries in the table. What is the quickest way to track down the multiple entries? I would only want to see where timein and timeoff exist in the table multiple times for the same id. So this would be a duplicate
EntryID -- ID -- timein -- timeoff
1487 11 2015-05-05 16:33:23 2015-05-05 18:45:26
1623 11 2015-05-05 16:33:23 2015-05-05 18:45:26
View 7 Replies
View Related
Jul 20, 2005
What is the best way to compare two entries in a single table wherethe two fields are "almost" the same?For example, I would like to write a query that would compare thefirst two words in a "company" field. If they are the same, I wouldlike to output them.For example, "20th Century" and "20th Century Fox" in the companyfield would be the same.How do I do this? Do I need to use a cursor? Is it as simple as using"Like?"
View 2 Replies
View Related
Sep 3, 2007
I am using Sql Server 2000.
I have a customer table with fields - CustId, Name, Address, City, StdCode, Phone.
I used to insert entries in this table from an excel file.
One excel file will contain thousands of customer.
In this table combination of StdCode and Phone should not be repeated.
If I do it in my VB.Net coding.then application gets drastically slow.
So I want to write a procedure or trigger for this.
Here what I will do, I will send all records into database then this trigger or procedure will check for
any existing entry of combination of StdCode and phone. If entry exists then this will delete new entry
or will not allow this new entry.
Is this possible to do using Trigger or stored procedure?
View 5 Replies
View Related
Sep 11, 2014
i have a table like below
create table staff_attendance
(
attendance_id int,
attendace_date datetime,
staff_id int,
working_year int,
hours int
)
values like
1 2014-06-30 00:00:00.0ST10121
2 2014-06-30 00:00:00.0ST10122
3 2014-06-30 00:00:00.0ST10122 ----same entry like previous one
4 2014-07-01 00:00:00.0ST10121
5 2014-07-01 00:00:00.0ST10122
6 2014-07-02 00:00:00.0ST10121
7 2014-07-02 00:00:00.0ST10122
8 2014-06-30 00:00:00.0ST10221
9 2014-06-30 00:00:00.0ST10222
10 2014-07-01 00:00:00.0ST1022 1
11 2014-07-01 00:00:00.0ST102 22
12 2014-07-02 00:00:00.0ST102 21
13 2014-07-02 00:00:00.0ST102 22
I Need to find the duplicate rows like same entries which is having more than 1 rows.... how do i find?
View 3 Replies
View Related
Dec 31, 2007
This morning I found that during the night someone hacked into my remote hosted SQL server 2005 database. Every record in a description field in each of three tables had everything after the first sentence replaced with the same URL. The three pages that used these tables badly infected anyone who viewed them in IE.I had my host restore the 12/30 backup, and I changed all the database and ftp passwords, now I want to be sure my code isn't giving anyone access. I'm using strongly typed datasets. This is my code behind code.Dim sCategory = Request.QueryString("cat")Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapterDim dF As Data.DataTable = ResourceAdapter.GetDataByCategory(sCategory)Dim numRows = dF.Rows.CountLabelCat.Text = sCategoryRepeater1.DataSource = dFRepeater1.DataBind() Is there anything I can do to better secure this? Oh, my connect string is in the web.config file only, but it isn't encrypted. I will be encrypting it today.Diane
View 9 Replies
View Related
Feb 24, 2006
Hi
I have a problem. Every time I select the first Item of a dropdownlist, it prevents the Insert to the database. There are 8 droplists on the page but the insert is only effected by the First Item of lstTheme selcetion.
My Insert code is here: Any ideas??
Private Sub btnInsertChange_Click(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs) Handles btnInsertChange.Command
Me.SqlCommandThemeTest.Connection = Me.SqlConnection1
Dim Name As String
Dim values As String
Name = "Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText, ContentManager, date"
values = "lstTheme, lstGuideName, lstGuidePage, lstPageType, lstChangeCategory, lstChangeFrom, txtChangeFrom, lstChangeTo, txtChangeTo, DropDownList1, txtDate"
SqlCommandThemeTest.CommandText = "INSERT INTO dbo.ChangesReport (Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText, ContentManager, date) VALUES (@themeValue, @guideValue, @guidepageValue, @pagetypeValue, @changecategoryValue, @changefromValue, @changefromtextValue, @changetoValue, @changetotextValue, @contentmanagerValue, @dateValue)"
SqlCommandThemeTest.Parameters.Add("@themeValue", lstTheme.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@guideValue", lstGuideName.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@guidepageValue", lstGuidePage.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@pagetypeValue", lstPageType.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changecategoryValue", lstChangeCategory.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changefromValue", lstChangeFrom.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changefromtextValue", txtChangeFrom.Text)
SqlCommandThemeTest.Parameters.Add("@changetoValue", lstChangeTo.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@changetotextValue", txtChangeTo.Text)
SqlCommandThemeTest.Parameters.Add("@contentmanagerValue", DropDownList1.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@dateValue", txtDate.Text)
Try
Me.SqlConnection1.Open()
Me.SqlCommandThemeTest.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
Me.SqlConnection1.Close()
End Try
Response.Redirect("WebForm3.aspx")
End Sub
View 1 Replies
View Related
Jul 20, 2005
The database is configured for single publisher, many subscribers,merge replication. The maintenance plan started to fail a couple ofmonths ago and the database would not get backed up. After clearingall the locks, I am able to backup the database manually. The locksreturn again and I'm not able to backup the database with themaintenance plan. How can I get around the lock issue or solve it sothat I can backup the database again?Thanks,Chris
View 1 Replies
View Related
Oct 4, 2007
I have a user AD account SOPUSJSmith for example. I have given this account security admin at the SQL Server level and dbo at the database level. Is there any way to prevent them from using ODBC to pull data into an Access DB or an Excel spreadsheet?
Thanks in Advance - SOX is killing me!
View 4 Replies
View Related
Jan 27, 2007
I am trying to deploy a SQL Server Express database to a remote SQL Server 2005 host. I was planning to copy and paste the data from my local tables to the remote tables using Management Studio Express but my ISP (NTL) appears to be blocking port 1433 and preventing me from getting a connection to the remote database.
Will an upgrade to SQL Server 2005 Developer Edition help me? I understand that it has a Backup & Restore Wizard but how does this work? Does it rely on a remote connection and will it fall foul of the NTL port blocking? Or, can the file that the Backup stage produces be copied into the web-based MSSQL Manager, that my web host provides, to restore to the remote database?
Any help would be much appreciated.
View 11 Replies
View Related
