Sep 9, 2007
I am a SQL newbie trying to create a registration page. The registration data is stored in "tblUsers," and the username is stored in the field named "UID" -- which is the primary key. If the user enters a username that already exists in the database, I want to return the "That username already exists" message and keep them on the registration page. If the username doesn't exist, I want to write all their registration info to the database.
I tried the code below using both an existing username and then using a username that didn't exist. Both times I got the "that username already exists" error message on the top of the confirmation page, and the record was not written to the database.
I also tried changing the "If objRec.RecordCount <> 0" to "If objRec.RecordCount >0" I got it to write to the database if the username didn't exist, but if the username did exist, I got the microsoft error '80040e14' (The changes you requested to the table were not successful because they would create duplicate values...)
Thanks for any help.
<%
SQLCmd = "SELECT UID From tblUsers WHERE UID = " & "'" & UID & "'"
Set objRec = objConn.Execute(SQLCmd)
If objRec.RecordCount <> 0 Then
Response.write "That username already exists"
Else
SQLCmd = "INSERT INTO tblUsers"
SQLCmd = SQLCmd & " (FIRSTNAME, LASTNAME, UID, PWD, STREET, CITY, STATE, ZIP, EMAIL, PHONE, FAX, PHYSICIAN, SPECIALTY, OTHER) "
SQLCmd = SQLCmd & " VALUES ('" & firstname & "',"
SQLCmd = SQLCmd & "'" & lastname & "',"
SQLCmd = SQLCmd & "'" & UID & "',"
SQLCmd = SQLCmd & "'" & PWD & "',"
SQLCmd = SQLCmd & "'" & street & "',"
SQLCmd = SQLCmd & "'" & city & "',"
SQLCmd = SQLCmd & "'" & state & "',"
SQLCmd = SQLCmd & "'" & zip & "',"
SQLCmd = SQLCmd & "'" & email & "',"
SQLCmd = SQLCmd & "'" & phone & "',"
SQLCmd = SQLCmd & "'" & fax & "',"
SQLCmd = SQLCmd & "'" & physician & "',"
SQLCmd = SQLCmd & "'" & specialty & "',"
SQLCmd = SQLCmd & "'" & other & "')"
Set objRec = objConn.Execute(SQLCmd)
End If
%>
View 14 Replies
View Related
Jul 23, 2005
I need to insert records into the table parSalesDetailModifier fromOLDparSalesDetailModifier where (1) those records DO NOT exit inparSalesDetailModifier and (2) those records have a parent record inparSalesDetail.When I run the below query I get the error message that I am violatingthe Primary Key Constraint for parSalesDetailModifier. In other words,it's trying to insert a record that does exist.Also posted below are create and insert startements for thte tables.If someone would be kind enough to show me what I am doing wrong, I'dreally appreciate it.Thanks,Jennifer-------------------------------- STORED PROCEDURE-------------------------------CREATE Proc LoadModifier2@S datetime,@E datetimeASINSERT INTO ParSalesDetailModifier(parSalesDetailModifierID,parSalesHdrID,parSalesDetailID,ModifierType,POSModifier,Condiment,CondimentPrice,UnitNumber,BusinessDay)SELECTOLD.parSalesDetailModifierID,OLD.parSalesHdrID,OLD.parSalesDetailID,OLD.ModifierType,OLD.POSModifier,OLD.Condiment,OLD.CondimentPrice,OLD.UnitNumber,OLD.BusinessDayFROM OldParSalesDetailModifier OLDWHEREEXISTS( SELECT DET.parSalesHdrID,DET.parSalesDetailID,DET.UnitNumber,DET.BusinessDayFROM ParSalesDetail DETWHERE OLD.parSalesHdrID = DET.parSalesHdrIDANDOLD.parSalesDetailID = DET.parSalesDetailIDANDOLD.UnitNumber = DET.UnitNumberANDOLD.BusinessDay = DET.BusinessDay)ANDNOT EXISTS( SELECT NEW.parSalesHdrID,NEW.parSalesDetailID,NEW.parSalesDetailModifierID,NEW.UnitNumber,NEW.BusinessDayFROM ParSalesDetailModifier NEWWHERE OLD.parSalesHdrID = NEW.parSalesHdrIDANDOLD.parSalesDetailID = NEW.parSalesDetailIDANDOLD.parSalesDetailModifierID = NEW.parSalesDetailModifierIDANDOLD.UnitNumber = NEW.UnitNumberANDOLD.BusinessDay = NEW.BusinessDay)AND OLD.BusinessDay between @S and @E-------------------------------- END STORED PROCEDURE--------------------------------------------------------------- CREATE TABLES-------------------------------CREATE TABLE [parSalesDetailModifier] ([ParSalesDetailModifierID] [int] NOT NULL ,[parSalesHdrID] [int] NOT NULL ,[parSalesDetailID] [int] NOT NULL ,[ModifierTYPE] [int] NULL ,[POSModifier] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Condiment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CondimentPrice] [money] NOT NULL ,[UnitNumber] [int] NOT NULL ,[BusinessDay] [datetime] NOT NULL ,CONSTRAINT [PK_parSalesDetailModifier] PRIMARY KEY CLUSTERED([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID],[ParSalesDetailModifierID]) WITH FILLFACTOR = 70 ON [PRIMARY] ,CONSTRAINT [FK_parSalesDetailModifier_parSalesDetail] FOREIGN KEY([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID]) REFERENCES [parSalesDetail] ([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID])) ON [PRIMARY]GOCREATE TABLE [OLDparSalesDetailModifier] ([ParSalesDetailModifierID] [int] NOT NULL ,[parSalesHdrID] [int] NOT NULL ,[parSalesDetailID] [int] NOT NULL ,[ModifierTYPE] [int] NULL ,[POSModifier] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Condiment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CondimentPrice] [money] NOT NULL ,[UnitNumber] [int] NOT NULL ,[BusinessDay] [datetime] NULL) ON [PRIMARY]GOCREATE TABLE [parSalesDetail] ([parSalesHdrID] [int] NOT NULL ,[parSalesDetailID] [int] NOT NULL ,[Before] [int] NOT NULL ,[Quantity] [int] NOT NULL ,[After] [int] NOT NULL ,[Promo] [money] NOT NULL ,[PromoBefore] [money] NOT NULL ,[ItemPrice] [money] NOT NULL ,[PromoAfter] [money] NOT NULL ,[POSItem] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[UnitNumber] [int] NOT NULL ,[Depleted] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[AmountTenderTime] [datetime] NULL ,[BusinessDay] [datetime] NOT NULL ,CONSTRAINT [PK_parSalesDetail] PRIMARY KEY CLUSTERED([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID]) WITH FILLFACTOR = 70 ON [PRIMARY] ,CONSTRAINT [FK_parSalesDetail_parSalesHdr] FOREIGN KEY([BusinessDay],[UnitNumber],[parSalesHdrID]) REFERENCES [parSalesHdr] ([BusinessDay],[UnitNumber],[parSalesHdrID])) ON [PRIMARY]GO-------------------------------- END CREATE TABLES--------------------------------------------------------------- INSERT INTO TABLES-------------------------------insert into parSalesDetailmodifier values (1,2298561,10917332,2,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (1,2298561,10917332,2,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917332,2,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(3,2298561,10917332,2,'TBBS','TBBS',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (1,2298561,10917340,2,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917340,2,'NOONIN','NO ONIN',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(3,2298561,10917340,2,'TBBS','TBBS',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(4,2298561,10917340,2,'WELL','WELL',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (1,2298561,10917341,2,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917341,2,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(3,2298561,10917341,2,'TBBS','TBBS',.0000,2,'2003-12-01')insert into parSalesDetailvalues(2298561,10917332,0,1,0,.0000,.0000,3.4900,. 0000,'DM',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10917340,0,1,0,.0000,.0000,.2500,.0 000,'JALA',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10917341,0,1,0,.0000,.0000,1.3400,.0000,'MD-DP',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928910,0,1,0,.0000,.0000,.9900,.0000,'2PIE99',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928911,0,1,0,.0000,.0000,.5900,.0000,'DECAF',2,'N','2003-12-0110:09:44.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928912,0,1,0,.0000,.0000,1.6900,.0000,'BOB-BAC',2,'N','2003-12-0110:09:44.000','2003-12-01')insert into parSalesDetailvalues(2298561,10929376,0,1,0,.0000,.0000,.5900,.0000,'COFFEE',2,'N','2003-12-0110:00:44.000','2003-12-01')-------------------------------- END INSERT INTO TABLES-------------------------------
View 7 Replies
View Related