469,138 Members | 1,394 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

Date as primary key?

103 100+
Within a single form I have used id and date as primary key, so while we are saving it onto a recordset, how can we check the duplicate entry?

Expand|Select|Wrap|Line Numbers
  1. If rs.EOF Then
  2.     conn.Execute "insert into saldetail(id, date, othours, otamt, taxrate, netsal, province) values ('" & store & "','" & dtpay & "','" & txtoh & "','" & txtotamt & "','" & txttr & "','" & txtnsal & "','" & cbostate & "')"
  3.     MsgBox "INSERTED NEW RECORDS", vbOKOnly, "ADDING NEW RECORDS"
  4. Else
  5.     'ID Found In DB (Duplicate)
  6.     MsgBox "Duplicate Entry"
  7. End If
  8. rs.Close
  9.  
I have to insert into this code???
Please guide me through.
Jan 2 '08 #1
43 2105
lotus18
866 512MB
Hi

If i were you, i would not use date as primary key. You can also use it if you want : ). For your problem, try to use Count.

Have a nice day!

Rey Sean
Jan 2 '08 #2
debasisdas
8,127 Expert 4TB
Check for existence of the record using COUNT.

Insert only if the record does not exist.
Jan 2 '08 #3
Vbbeginner07
103 100+
Hi

If i were you, i would not use date as primary key. You can also use it if you want : ). For your problem, try to use Count.

Have a nice day!

Rey Sean
that im using it not for me but,to calculate the overtime work of a particular employee for amonth
eg:
MII/101 has worked 12 hours on 31
10 hours on 30
8 hours on 29
As 8 is the normal time,
such that if u click MII/101 in alist view
the id +the date shoule be shown in another list view
Jan 2 '08 #4
debasisdas
8,127 Expert 4TB
which date you you want to display . all the date or only the dates when the employee overworked ?
Jan 2 '08 #5
lotus18
866 512MB
Hi

As Debasisdas said, count checks the for the existence of the record. If the count is greater than 0, then it should prompt duplicate values. You can also use RecordCount. Good Luck : )

Rey Sean
Jan 2 '08 #6
Vbbeginner07
103 100+
which date you you want to display . all the date or only the dates when the employee overworked ?
all the dates corresponding to the particular id.....MII/101
Jan 2 '08 #7
debasisdas
8,127 Expert 4TB
all the dates corresponding to the particular id.....MII/101
you can simply do that by using SQL statment and by passing id in the where clause.
Jan 2 '08 #8
Vbbeginner07
103 100+
you can simply do that by using SQL statment and by passing id in the where clause.
But have to check the dates being inserted while we are saving it...
now it checks the id,but have to check the date also....
Jan 2 '08 #9
debasisdas
8,127 Expert 4TB
But have to check the dates being inserted while we are saving it...
now it checks the id,but have to check the date also....
you need to check both date and id using count before inserting.
Jan 2 '08 #10
Killer42
8,435 Expert 8TB
Another possible approach is just to go ahead and do the insert, and use error-handling to deal with the situation where it already exists.
Jan 2 '08 #11
QVeen72
1,445 Expert 1GB
Hi,

What SQL you are using to check for duplication (RS?)

Use this :

Expand|Select|Wrap|Line Numbers
  1. dim sSQL As String
  2. sSQL = "Select * From saldetail Where id='" & store & "' And date = #" & dtpay & "#"
  3. Open RS with this select statement..
Dont use "Date" as field name. It is a keyword.
So what is the FieldType of Date?
By looking into your Insert statement, you must have declared as Text type?

Regards
Veena
Jan 2 '08 #12
Within a single form ...
You can modify your code as follows to overcome the problem:
Expand|Select|Wrap|Line Numbers
  1. On error goto errHndlr
  2. conn.Execute "insert into saldetail(id, date, othours, otamt, taxrate, netsal, province) values ('" & store & "','" & dtpay & "','" & txtoh & "','" & txtotamt & "','" & txttr & "','" & txtnsal & "','" & cbostate & "')"
  3. MsgBox "INSERTED NEW RECORDS", vbOKOnly, "ADDING NEW RECORDS"
  4. rs.Close
  5.  
  6. Exit Sub
  7. errHndlr:
  8.     If Err.Number = -2147217873 Then
  9.        'ID Found In DB (Duplicate)
  10.        MsgBox "Duplicate Entry"
  11.        Resume Next
  12.    End If
  13. End Sub
  14.  
In Oracle, error number -2147217873 is returned when a unique constraint is violated. Here we have trapped this error number and simply resumed next if such an error occurs.
Jan 2 '08 #13
Vbbeginner07
103 100+
You can modify your code as follows to overcome the problem:
Expand|Select|Wrap|Line Numbers
  1. On error goto errHndlr
  2. conn.Execute "insert into saldetail(id, date, othours, otamt, taxrate, netsal, province) values ('" & store & "','" & dtpay & "','" & txtoh & "','" & txtotamt & "','" & txttr & "','" & txtnsal & "','" & cbostate & "')"
  3. MsgBox "INSERTED NEW RECORDS", vbOKOnly, "ADDING NEW RECORDS"
  4. rs.Close
  5.  
  6. Exit Sub
  7. errHndlr:
  8.     If Err.Number = -2147217873 Then
  9.        'ID Found In DB (Duplicate)
  10.        MsgBox "Duplicate Entry"
  11.        Resume Next
  12.    End If
  13. End Sub
  14.  
In Oracle, error number -2147217873 is returned when a unique constraint is violated. Here we have trapped this error number and simply resumed next if such an error occurs.
that too isnt working out...........
Jan 3 '08 #14
debasisdas
8,127 Expert 4TB
that too isnt working out...........
have you tried using count() ?
Jan 3 '08 #15
Vbbeginner07
103 100+
have you tried using count() ?
yes,but have some errors while debugging........
Jan 3 '08 #16
Vbbeginner07
103 100+
have you tried using count() ?
But have to check the dates being inserted while we are saving it...
now it checks the id,but have to check the date also....
Jan 3 '08 #17
What's the problem with error trapping method?

I mean, what message are you getting when using this? I don't find any problem using this method. If you are not using Oracle, then you can have error number for the db (you are using) that is thrown when a unique constraint is violated.

This has always worked for me without any fault.

Tell me in detail so I can help you out.
Jan 3 '08 #18
Vbbeginner07
103 100+
What's the problem with error trapping method?

I mean, what message are you getting when using this? I don't find any problem using this method. If you are not using Oracle, then you can have error number for the db (you are using) that is thrown when a unique constraint is violated.

This has always worked for me without any fault.

Tell me in detail so I can help you out.
but my questn. is that once an id is saved with one particular date,how can we add another date with the same id???
I am confused...each time it needs to check id and date,but when id is there,n other date can be saved,rite???t.,
Jan 3 '08 #19
debasisdas
8,127 Expert 4TB
but my questn. is that once an id is saved with one particular date,how can we add another date with the same id???
I am confused...each time it needs to check id and date,but when id is there,n other date can be saved,rite???t.,
how can the same employee have the same date entered twice ?

check using

Expand|Select|Wrap|Line Numbers
  1. select count(*) from table_name where user_id='id' and user_date = 'date'
before inserting values

insert only if this returns 0

also takecare not to use DATE as field name in any table.
Jan 3 '08 #20
Vbbeginner07
103 100+
how can the same employee have the same date entered twice ?
check using
Expand|Select|Wrap|Line Numbers
  1. select count(*) from table_name where id='id' and date = 'date'
before inserting values
insert only if this returns 0
see to that,one can work for different hours for different dates right???
As in above replies:
MII/101 added to database as 02/01/08 with 10 hours
the same emp.id should be enetered with 8 hours in 03/01/08
Did u understand???Oer else explanation needed?
Jan 3 '08 #21
debasisdas
8,127 Expert 4TB
see to that,one can work for different hours for different dates right???
As in above replies:
MII/101 added to database as 02/01/08 with 10 hours
the same emp.id should be enetered with 8 hours in 03/01/08
Did u understand???Oer else explanation needed?
so what , your date is different or not

and you are having composit key on id and date , right ?
Jan 3 '08 #22
Vbbeginner07
103 100+
so what , your date is different or not
and you are having composit key on id and date , right ?
yes,the date is different
Jan 3 '08 #23
debasisdas
8,127 Expert 4TB
yes,the date is different
i hope you got that right now .
Jan 3 '08 #24
Vbbeginner07
103 100+
i hope you got that right now .
yes,but not getting it in prper way.......ny way thanks
Jan 3 '08 #25
debasisdas
8,127 Expert 4TB
yes,but not getting it in prper way.......ny way thanks
what is the problem now ?
Jan 3 '08 #26
Killer42
8,435 Expert 8TB
If the primary key is made up of the ID and date fields, then any attempt to store a record which duplicates both of these fields must produce some sort of error (unless your database allows duplicate primary keys, which seems rather odd). Why can't you trap that error and deal with it accordingly?
Jan 4 '08 #27
Vbbeginner07
103 100+
If the primary key is made up of the ID and date fields, then any attempt to store a record which duplicates both of these fields must produce some sort of error (unless your database allows duplicate primary keys, which seems rather odd). Why can't you trap that error and deal with it accordingly?
killer,now im keeeping only date as a primary key...would it help??? sugeest if its not a good idea?
but the code count isnt working out,because im selecting date from a datepicker
and store date as 12/18/2007
anyone please guide me through?
Jan 4 '08 #28
debasisdas
8,127 Expert 4TB
Kindly post how you are trying to use count that it is not working.
Jan 4 '08 #29
Killer42
8,435 Expert 8TB
killer,now im keeeping only date as a primary key...would it help??? sugeest if its not a good idea?
I don't know enough about database design to tell you whether it's a good or a bad idea. I was merely making the point that if your primary key was made up of both fields, then you couldn't create duplicate records even if you tried - the database software would not allow it.

Um... in this case, using the date alone as the PK is probably not a good idea. Since the PK must be unique, it means you can only store one record per date. And I don't think this is what you want.
Jan 4 '08 #30
debasisdas
8,127 Expert 4TB
Um... in this case, using the date alone as the PK is probably not a good idea. Since the PK must be unique, it means you can only store one record per date. And I don't think this is what you want.
To avoid this both id and date must me marked as composit primary key.

so that id can be duplicate ,date can be duplicate but the combination of both can't be.
Jan 4 '08 #31
Vbbeginner07
103 100+
To avoid this both id and date must me marked as composit primary key.
so that id can be duplicate ,date can be duplicate but the combination of both can't be.
im rite now using sql server where i kept both as a primary key....making errors?
Jan 4 '08 #32
lotus18
866 512MB
To avoid this both id and date must me marked as composit primary key.

so that id can be duplicate ,date can be duplicate but the combination of both can't be.
I have a little knowledge in database, but can you explain to me what does the composite primary key mean? I think it has been discussed to us by our professor but I think I wasn't listening at that time.
Jan 4 '08 #33
debasisdas
8,127 Expert 4TB
Composite primary key means more than one field combination works as the primary key. In Oracle a maximum of 32 key combination can be used as composite key.
Jan 4 '08 #34
Vbbeginner07
103 100+
Composite primary key means more than one field combination works as the primary key. In Oracle a maximum of 32 key combination can be used as composite key.
how about sql server???
Jan 4 '08 #35
Killer42
8,435 Expert 8TB
Keep in mind, it's not some special type of field called a "composite primary key". The word "composite" refers to any key (or "index") which is made up of more than one field.

If you want the database software to automatically prevent record duplication, you need to define a "unique key". I think the meaning of this one is fairly obvious - it only allows unique values in that key. So a record which would produce a duplicate key value cannot be stored.

Any key may be defined as unique, regardless of whether it is the primary one, and regardless of how many fields are used to build it.

But I'm pretty sure that by definition, the primary key is always unique.
Jan 4 '08 #36
QVeen72
1,445 Expert 1GB
Hi Lotus,

"Composite Primary Key", means in a Table, Primary Key is composed of more than one Column (all together )..
Say in the above Example ,
Date + ID is Composite PK, Like Same Date Can Have Many ID's and Same ID can have Many Dates, but when combined together, there is only one Unique Record:

Date ID
01-01-2008 001
01-01-2008 002
01-01-2008 003
01-01-2008 004

02-01-2008 001
03-01-2008 001
04-01-2008 001
05-01-2008 001


Regards
Veena
Jan 4 '08 #37
Vbbeginner07
103 100+
Hi Lotus,

"Composite Primary Key", means in a Table, Primary Key is composed of more than one Column (all together )..
Say in the above Example ,
Date + ID is Composite PK, Like Same Date Can Have Many ID's and Same ID can have Many Dates, but when combined together, there is only one Unique Record:

Date ID
01-01-2008 001
01-01-2008 002
01-01-2008 003
01-01-2008 004

02-01-2008 001
03-01-2008 001
04-01-2008 001
05-01-2008 001


Regards
Veena
any meansd for sql server?
Jan 4 '08 #38
QVeen72
1,445 Expert 1GB
any meansd for sql server?
Hi,

You can create Composite PK in SQL Server also.
Create table syntax is :

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE  MYTABLE (
  2.   TDATE DATE  NOT NULL,
  3.   ID INTEGER NOT NULL,
  4.   ENAME VARCHAR(50), 
  5.   PRIMARY KEY (TDATE, ID)
  6. )
  7.  
  8.  
Regards
Veena
Jan 4 '08 #39
lotus18
866 512MB
Hi Lotus,

"Composite Primary Key", means in a Table, Primary Key is composed of more than one Column (all together )..
Say in the above Example ,
Date + ID is Composite PK, Like Same Date Can Have Many ID's and Same ID can have Many Dates, but when combined together, there is only one Unique Record:

Date ID
01-01-2008 001
01-01-2008 002
01-01-2008 003
01-01-2008 004

02-01-2008 001
03-01-2008 001
04-01-2008 001
05-01-2008 001


Regards
Veena
OK. I got it. Very well explained Veena. To Debasisdas and Killer42, thank you also. From now on, I am going to pay attention to our class.
Jan 4 '08 #40
Vbbeginner07
103 100+
OK. I got it. Very well explained Veena. To Debasisdas and Killer42, thank you also. From now on, I am going to pay attention to our class.
code( vb)
If Val(Form2.txttamt.Text) = 0 Then
MsgBox "Sorry,Ur Not Allowed To Do So!!!", vbOKOnly, "ERROR"
Exit Sub
Else
strName = Trim(ListView1.SelectedItem)
strId = ListView1.SelectedItem.SubItems(1)
rs.Open "select * from empdetail Where name = '" & strName & "'", conn, adOpenStatic, adLockOptimistic
store = rs!id
rs.Close
Set rs = Nothing
rs1.Open "select * from saldetail where id = '" & store & "'", conn, adOpenStatic, adLockOptimistic
storDate = rs1!Date
storId = rs1!id
If rs1.EOF Then
If Form2.dtpay.Value = "'" & storDate & "'" And strId = "'" & store & "'" Then
MsgBox "duplicate entry"
Else
conn.Execute "insert into saldetail(id,date,othours,otamt,taxrate,netsal,pro vince)values ('" & store & "','" & dtpay & "','" & txtoh & "','" & txtotamt & "','" & txttr & "','" & txtnsal & "','" & cbostate & "')"
MsgBox "INSERTED NEW RECORDS", vbOKOnly, "ADDING NEW RECORDS"
rs1.Close
Set rs1 = Nothing
End If
End If
End If


code using composit key??? i am not gettng it........used with sql server and vb???
Jan 5 '08 #41
QVeen72
1,445 Expert 1GB
Hi,

First Create Composite PK (ID + Date), and change last part of your coding ...:

Expand|Select|Wrap|Line Numbers
  1. rs1.Open "select * from saldetail where id = '" & store & "' and Date = '" & dtPay & "'", conn, adOpenStatic, adLockOptimistic
  2. If Not rs1.EOF Then
  3.     MsgBox "duplicate entry"
  4. Else
  5.     conn.Execute "insert into saldetail(id,date,othours,otamt,taxrate,netsal,pro vince)values ('" & store & "','" & dtpay & "','" & txtoh & "','" & txtotamt & "','" & txttr & "','" & txtnsal & "','" & cbostate & "')"
  6. MsgBox "INSERTED NEW RECORDS", vbOKOnly, "ADDING NEW RECORDS"
  7. rs1.Close
  8. Set rs1 = Nothing
  9. End IF
  10.  
Regards
Veena
Jan 5 '08 #42
Vbbeginner07
103 100+
Hi,

First Create Composite PK (ID + Date), and change last part of your coding ...:

Expand|Select|Wrap|Line Numbers
  1. rs1.Open "select * from saldetail where id = '" & store & "' and Date = '" & dtPay & "'", conn, adOpenStatic, adLockOptimistic
  2. If Not rs1.EOF Then
  3.     MsgBox "duplicate entry"
  4. Else
  5.     conn.Execute "insert into saldetail(id,date,othours,otamt,taxrate,netsal,pro vince)values ('" & store & "','" & dtpay & "','" & txtoh & "','" & txtotamt & "','" & txttr & "','" & txtnsal & "','" & cbostate & "')"
  6. MsgBox "INSERTED NEW RECORDS", vbOKOnly, "ADDING NEW RECORDS"
  7. rs1.Close
  8. Set rs1 = Nothing
  9. End IF
  10.  
Regards
Veena
pk can be set as if in ms-access in sql server,so kept both as primary...but im not getting it,the way i meant to be...
Jan 5 '08 #43
9815402440
180 100+
hi
i think every possible way has been suggested by the experts. if still there is any problam then you can try following logic.

Expand|Select|Wrap|Line Numbers
  1. rs.Open "select id, date, id & format(date,'dd/mm/yyyy') as key from table"
  2.  
  3. rs.Movefirst
  4. rs.Find "key = '" & txtid.text & format(txtdate.text,"dd/mm/yyyy") & "'"
  5. If Not rs.Eof Then 
  6.    MsgBox "duplicate entry"
  7. Else
  8.    'insert new values
  9. End If
regards
manpreet singh dhillon hoshiarpur
Jan 5 '08 #44

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

17 posts views Thread by Philip Yale | last post: by
7 posts views Thread by Thomas R. Hummel | last post: by
7 posts views Thread by rAinDeEr | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.