Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Unsolved
Views: 916

May 15th, 2009

vb recordset problem

Expand Post »

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
<code>
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........
</code>

Similar Threads

Recordset problem in Visual Basic 4 / 5 / 6
Reading excel sheet into a recordset in Visual Basic 4 / 5 / 6
recordset problem in ASP

monurenjith

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since May 2009

Permalink

May 15th, 2009

Re: vb recordset problem

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.

jbisono

Reputation Points: 56

Solved Threads: 56

Posting Pro in Training

Offline

432 posts
since May 2009

Permalink

May 15th, 2009

Re: vb recordset problem

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

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

May 16th, 2009

Re: vb recordset problem

Should be something like...

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
myrs.Open " SELECT memberpersonal.name,memberpersonal.memberid, FROM memberpersonal, INNER JOIN memberunion ON memberpersonal.memberid = memberunion.memberid where memberunion.memberid = " & cmbUnion.Text , mydatabase
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

Last edited by vb5prgrmr; May 16th, 2009 at 2:41 am.

vb5prgrmr

Reputation Points: 156

Solved Threads: 296

Posting Virtuoso

Offline

1,670 posts
since Mar 2009

Permalink

May 18th, 2009

Re: vb recordset problem

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

monurenjith

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since May 2009

Permalink

May 18th, 2009

Re: vb recordset problem

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...)

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
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
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

Last edited by jireh; May 18th, 2009 at 10:05 pm.

jireh

Reputation Points: 11

Solved Threads: 49

Posting Whiz

Offline

316 posts
since Jul 2007

Permalink

Jun 18th, 2009

Re: vb recordset problem

'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

Tassilo

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Jun 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: VB4 Help Complier Help

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: how can i get Internet history

DaniWeb Message

Cancel Changes

User Name
Password