Results 1 to 5 of 5

Thread: [RESOLVED] Primary Key and another field testing?

  1. #1

    Thread Starter
    Addicted Member aa9gg's Avatar
    Join Date
    Apr 2009
    Location
    Chicagoland
    Posts
    184

    Resolved [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!!!

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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 & "'"

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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

  5. #5

    Thread Starter
    Addicted Member aa9gg's Avatar
    Join Date
    Apr 2009
    Location
    Chicagoland
    Posts
    184

    Re: Primary Key and another field testing?

    Quote Originally Posted by FunkyDexter View Post
    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
  •  



Click Here to Expand Forum to Full Width