using 2 database for Listview function

[xiaofeiii]

if i need to link two database to one listview, i had written the following but it doesn't seems to work. can someone help me out with this??
The subrecordset doesn't seems to able to extract data from database




Dim sqlstring As String
Dim sqlstring2 As String


sqlstring = "SELECT * FROM APPOINTMENT_T WHERE APPOINTMENT_DATE = #" & Apptdate2 & "# ORDER BY APPOINTMENT_DATE, START_TIME"


mainRecordset.Open sqlstring, mainConnection, adOpenKeyset, _
adLockOptimistic, adCmdText


If mainRecordset.RecordCount <> 0 Then
mainRecordset.MoveFirst

IC.Text = mainRecordset.Fields("PATIENT_ID").Value
sqlstring2 = "SELECT * FROM PATIENT_RECORD_T WHERE NRIC = '" + IC.Text + "' "
subrecordset.Open sqlstring, mainConnection, adOpenKeyset, _
adLockOptimistic, adCmdText

Do While Not mainRecordset.EOF

If mainRecordset.RecordCount <> 0 And subrecordset.RecordCount <> 0 Then
subrecordset.MoveFirst
With ListView

With ListView.ListItems.Add(, , Format(mainRecordset.Fields("PATIENT_ID").Value, "dd-mmm-yy"))
.SubItems(1) = subrecordset.Fields("FIRSTNAME").Value
.SubItems(2) = subrecordset.Fields("MIDDLENAME").Value
.SubItems(3) = subrecordset.Fields("LASTNAME").Value
.SubItems(4) = Format(mainRecordset.Fields("START_TIME").Value, "hh:mm AMPM")
.SubItems(5) = mainRecordset.Fields("PURPOSE").Value
End With

mainRecordset.MoveNext
End With


End If
Loop

[si_the_geek]

Do you actually mean two databases, or is it two tables in the same database? (it is a very big difference).

Next question, what data type is the NRIC field?

And finally.. is there a particular reason for the listview, as opposed to an MSHFlexGrid? (which can show child recordsets nicely).

[xiaofeiii]

i think i shall rephrase my question
coz it seems a bit blur. so sorry


imagine i have 2 table, A and B, if there is a similar field C
how can i place a SELECT statement which ensure the field C in A and B are equal??

[si_the_geek]

Do you want to return data from both tables in the same recordset?

If not, then basically what you had before (but you need to at least answer my second question above to be sure this is right).

If you do want it all in the same RS, use SQL like this:

SELECT TableA.Field1, TableB.Field2, ...
FROM TableA INNER JOIN TableB ON (TableA.FieldC = TableB.FieldC)
ORDER BY ....

[xiaofeiii]

i tried the below

sqlString = "SELECT * FROM USER_LOGIN_T INNER JOIN PATIENT_RECORD_T WHERE (PATIENT_RECORD_T.NRIC = USER_LOGIN_T.NRIC AND FIRSTNAME LIKE '%" + TextFirstname.Text _
+ "%' AND MIDDLENAME LIKE '%" + TextMiddlename.Text _
+ "%' AND LASTNAME LIKE '%" + TextLastname.Text _
+ "%' AND NRIC LIKE '%" + TextNRIC.Text + "%' )"


but it said error in FROM clause

[si_the_geek]

The Join information goes into an "ON" clause, not the Where clause. Try this:

sqlString = "SELECT * " _
          & "FROM USER_LOGIN_T " _ 
          & "INNER JOIN PATIENT_RECORD_T ON (PATIENT_RECORD_T.NRIC = USER_LOGIN_T.NRIC) " _
          & "WHERE FIRSTNAME LIKE '%" & TextFirstname.Text _
          & "%' AND MIDDLENAME LIKE '%" & TextMiddlename.Text _
          & "%' AND LASTNAME LIKE '%" & TextLastname.Text _
          & "%' AND NRIC LIKE '%" & TextNRIC.Text & "%' )"

Oh, and you should always use & for joining strings in VB - it's quicker, and removes the chances of accidental arithmetic.

[xiaofeiii]

thanks for ur help but it states the NRIC could refer to more than 1 table from ur sql statements

[Hack]

thanks for ur help but it states the NRIC could refer to more than 1 table from ur sql statements

It probably doesn't like this

& "%' AND NRIC LIKE '%" & TextNRIC.Text & "%' )"

Which table are you referring to here, RECORD_T or LOGIN_T?

[si_the_geek]

As that field is the one being joined on, it doesn't actually matter. This would fix it:

& "%' AND PATIENT_RECORD_T.NRIC LIKE '%" & TextNRIC.Text & "%' )"