Help in sql statement


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Help in sql statement

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    Help in sql statement

    [Originally posted by Cristy]

    I am stuck in this project.˙ My combo box has LastName, FirstName and I needed to display in the labelbox the PatientID but it doesn't work, how will I do this:
    Here's some of my code:


    Private Sub LoadPatientList()

    If Not EmptyTable(rsPatients) Then
    ˙ ˙ ˙ ˙ With rsPatients
    ˙ ˙ ˙ ˙ ˙ ˙ 'Make sure we're on the first record
    ˙ ˙ ˙ ˙ ˙ ˙ .MoveFirst
    ˙ ˙ ˙ ˙ ˙ ˙ lblPatientID.Caption = !PatientID
    ˙ ˙ ˙ ˙ ˙ ˙
    ˙ ˙ ˙ ˙ ˙ ˙ 'Load the combo drop down list
    ˙ ˙ ˙ ˙ ˙ ˙ Do While Not .EOF
    ˙ ˙ ˙ ˙ ˙ ˙ ˙ ˙ cboPatient.AddItem !LastName˙ ", " & !FirstName
    ˙ ˙ ˙ ˙ ˙ ˙ ˙ ˙ .MoveNext
    ˙ ˙ ˙ ˙ ˙ ˙ Loop
    ˙ ˙ ˙ ˙ ˙ ˙
    ˙ ˙ ˙ ˙  End With
    ˙ ˙
    ˙ ˙ ˙ Exit Sub
    LoadPatientErr:
    ˙  MsgBox "Load Models Table failed: " & Err.Description
    End Sub

    '******************************************
    Private Sub cboPatient_Click()
    ˙ ˙ 'Find the Patients to the corresponding Patients Table
    ˙ ˙ Dim strPatientQry As String
    ˙ ˙
    ˙ ˙ 'Putting the query string in a variable helps with debugging
    ˙ ˙ strPatientQry = conFindPatients & cboPatient.List(cboPatient.ListIndex) & "'"
    ˙ ˙
    ˙ ˙ On Error GoTo FindPatientErr:
    ˙ ˙ rsFindPatients.Open strPatientQry, cnConnection, adOpenStatic, adLockReadOnly, adCmdText
    ˙ ˙ ˙ ˙
    ˙ ˙ If Not EmptyTable(rsFindPatients) Then
    ˙ ˙ ˙ ˙ 'Update the label control
    ˙ ˙ ˙ ˙  lblPatientID.Caption = rsFindPatients!PatientID
    ˙ ˙ ˙ ˙
    ˙ ˙ End If

    '******************************************
    Const conPatientQry As String = "SELECT * From Patients order by LastName"
    Const conFindPatients As String = "SELECT * FROM Patients WHERE LastName = '"


  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Help in sql statement

    [Originally posted by Larry Asher]

    Can you be more specific as to what you want to happen and what is and is not working?

    If a user selects from the drop down list are you mapping that slection to the patient id and displaying that as well in the label caption?

  3. #3
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Help in sql statement

    [Originally posted by Cristy]

    Since my combo box is concatenated by LastName and FirstName (LastName,FirstName), now my patientID will not show up because I do not know how to address the concatenation to sql.˙ Now my sql is only asking the LastName from the combo box.˙ I hope you understand.

    Thanks

  4. #4
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Help in sql statement

    [Originally posted by Larry Asher]

    Here is some code you can use to parse the text property from the combo box to get the last name.

    What I would probably do, just to make the code cleaner is create a string variable called lastname and then place the following code in your procedure just prior to your query. Replace cboComboBox.text with the name of your combo box.

    Dim strLastName as String

    strLastName = Trim(left(cboComboBox.Text,instr(1,cboComboBox.Text,",",vbTextCompare)-1))

    Now simply pass strLastName as a parameter in your query.

    If you want to see if the code really works you can always run it in the Immediate Window, under the view menu in VB. If you do you will have to put a string value in place of the cboComboBox.text ie. "LastName, FirstName"

    Hope this helps.

  5. #5
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Re:Help in sql statement

    [Originally posted by Cristy]

    Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center