Date as primary key?

[Vbbeginner07]

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?

[code=vb]
If rs.EOF Then
conn.Execute "insert into saldetail(id, date, othours, otamt, taxrate, netsal, province) values ('" & store & "','" & dtpay & "','" & txtoh & "','" & txtotamt & "','" & txttr & "','" & txtnsal & "','" & cbostate & "')"
MsgBox "INSERTED NEW RECORDS", vbOKOnly, "ADDING NEW RECORDS"
Else
'ID Found In DB (Duplicate)
MsgBox "Duplicate Entry"
End If
rs.Close
[/code]
I have to insert into this code???
Please guide me through.

[lotus18]

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

[debasisdas]

Check for existence of the record using COUNT.

Insert only if the record does not exist.

[Vbbeginner07]

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

[debasisdas]

which date you you want to display . all the date or only the dates when the employee overworked ?

[lotus18]

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

[Vbbeginner07]

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

[debasisdas]

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.

[Vbbeginner07]

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....

[debasisdas]

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.

[Killer42]

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.

[QVeen72]

Hi,

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

Use this :

[CODE=vb]dim sSQL As String
sSQL = "Select * From saldetail Where id='" & store & "' And date = #" & dtpay & "#"
Open RS with this select statement..[/CODE]

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

[vikas000000a]

Within a single form ...

You can modify your code as follows to overcome the problem:
[code=vb]
On error goto errHndlr
conn.Execute "insert into saldetail(id, date, othours, otamt, taxrate, netsal, province) values ('" & store & "','" & dtpay & "','" & txtoh & "','" & txtotamt & "','" & txttr & "','" & txtnsal & "','" & cbostate & "')"
MsgBox "INSERTED NEW RECORDS", vbOKOnly, "ADDING NEW RECORDS"
rs.Close

Exit Sub
errHndlr:
If Err.Number = -2147217873 Then
'ID Found In DB (Duplicate)
MsgBox "Duplicate Entry"
Resume Next
End If
End Sub
[/code]

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.

[Vbbeginner07]

You can modify your code as follows to overcome the problem:
[code=vb]
On error goto errHndlr
conn.Execute "insert into saldetail(id, date, othours, otamt, taxrate, netsal, province) values ('" & store & "','" & dtpay & "','" & txtoh & "','" & txtotamt & "','" & txttr & "','" & txtnsal & "','" & cbostate & "')"
MsgBox "INSERTED NEW RECORDS", vbOKOnly, "ADDING NEW RECORDS"
rs.Close

Exit Sub
errHndlr:
If Err.Number = -2147217873 Then
'ID Found In DB (Duplicate)
MsgBox "Duplicate Entry"
Resume Next
End If
End Sub
[/code]

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...........

[debasisdas]

that too isnt working out...........

have you tried using count() ?