vb recordset problem

[monurenjith]

i was trying to join two tables using a adodb mysql5.1 driver connection .i tried to print the record count in the record set .
no result was shown and the program is not responding . when i started debugging it was shown that the record set object was closed .........pls help me................ my code is

Set myrs = New ADODB.Recordset
myrs.CursorLocation = adUseClient
myrs.CursorType = adOpenStatic
myrs.LockType = adLockReadOnly

myrs.Open " SELECT memberpersonal.name,memberpersonal.memberid, FROM memberpersonal,memberunion where(memberunion.memberid = memberpersonal.memberid and memberpersonal.memberid = '" & cmbUnion.Text & "')", mydatabase
MsgBox myrs.RecordCount
thanks in advance........


[jbisono]

Hi, I just take a quick view to your code and I notice that your select statement have a " , " extra

SELECT memberpersonal.name,memberpersonal.memberid, FROM

I make it bold, Take it out.

regards.

[debasisdas]

as suggested in the previous thread your sql statement is wrong.

[vb5prgrmr]

Should be something like...

myrs.Open " SELECT memberpersonal.name,memberpersonal.memberid, FROM memberpersonal, INNER JOIN memberunion ON memberpersonal.memberid = memberunion.memberid where memberunion.memberid = " & cmbUnion.Text , mydatabase

Good Luck

[monurenjith]

Thanks......................

[jireh]

best way of practice in sql is that if you have a joined tables then use the keyword join (INNER JOIN, LEFT JOIN, RIGHT JOIN ETC...)

Dim sqlSelect As String

sqlSelect = " SELECT MP.name, MP.memberid "
sqlSelect = sqlSelect & "FROM MemberPersonal MP "
sqlSelect = sqlSelect & "INNER JOIN MembeRunion MR ON MP.memberid = MR.memberid "
sqlSelect = sqlSelect & "WHERE MR.memberid = " & cmbUnion.Text  

myrs.Open sqlSelect , mydatabase

[Tassilo]

'You have the wrong propereties for the recordset

With myrs
If .state = adStateOpen Then .Close
'
' set properties of recordset
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic

.Open sqlSelect , mydata
End With