-
Aug 16th, 2010, 11:41 AM
#1
Thread Starter
Addicted Member
[RESOLVED] Primary Key and another field testing?
Hi all...
In my database I am using "Patient ID" as my primary key. When I add a new patient, the user must enter the patient id number (can't auto-generate this key), name, etc. So now I need a way to check that this id/name combination does NOT exist in the database before adding the new record. My confusion is how to build the sql to test for this?
I have this so far:
Code:
Public Sub search_dup_id()
strSQL = "SELECT * FROM Patient_Info"
strSQL = strSQL & " WHERE PATIENT_ID =" & Patient_Info5.patient_id.Text
If P_rs.State = adStateOpen Then
P_rs.Close 'close the recordset (required before reloading it)
End If
P_rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText 'load the new data
End Sub
but not sure how to put the other field into that same sql test
FCC Section 97.313(a) “At all times, an amateur station must use the minimum transmitter power necessary to carry out the desired communications.”
I'd rather run a "Killer-Watt" than a KiloWatt - QRP Rules!!!
-
Aug 16th, 2010, 11:48 AM
#2
Re: Primary Key and another field testing?
I think you just need to add:-
Code:
If p_rs.eof then
'There's no existing row so do the insert
else
'There's already a row with the selected id so warn the user
end if
Another method would be to put a unique constraint on the column in the database. Then you can just go ahead and do the insert but trap the unique constraint error that gets returned if the record already exists.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Aug 16th, 2010, 11:50 AM
#3
Re: Primary Key and another field testing?
The Where clause is like an If statement in VB, so simply add an extra condition using AND, eg:
Code:
strSQL = "SELECT * FROM Patient_Info"
strSQL = strSQL & " WHERE PATIENT_ID =" & Patient_Info5.patient_id.Text
strSQL = strSQL & " AND PATIENT_NAME ='" & Patient_Info5.patient_name.Text & "'"
-
Aug 16th, 2010, 01:05 PM
#4
Re: Primary Key and another field testing?
Reading Si's response I realised we've interpreted your question slightly differently. Do you want the ID and Name to be unique in combination or do both need to be unique individually? In other words, if there's already a record with a name of "Si The Geek" and an ID of 1234, is it acceptable to create a record for "Funky Dexter" with an ID of 1234? Is it acceptable to have two "Si the Geek"s with differect IDs?
PS. Of course, it's never acceptable to have two Si The Geeks. One's enough for any forum.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Aug 16th, 2010, 02:44 PM
#5
Thread Starter
Addicted Member
Re: Primary Key and another field testing?
Originally Posted by FunkyDexter
Reading Si's response I realised we've interpreted your question slightly differently. Do you want the ID and Name to be unique in combination or do both need to be unique individually? In other words, if there's already a record with a name of "Si The Geek" and an ID of 1234, is it acceptable to create a record for "Funky Dexter" with an ID of 1234? Is it acceptable to have two "Si the Geek"s with differect IDs?
PS. Of course, it's never acceptable to have two Si The Geeks. One's enough for any forum.
Actually as I thought about it, your correct. The ID is unique. I then need to test the Patient Name after I find the id to see if the names match. If so...save/update the record....if not don't save and warn user to change ID
FCC Section 97.313(a) “At all times, an amateur station must use the minimum transmitter power necessary to carry out the desired communications.”
I'd rather run a "Killer-Watt" than a KiloWatt - QRP Rules!!!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|