vb recordset problem

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site
Reply

•
•
Join Date: May 2009
Posts: 5
Reputation: monurenjith is an unknown quantity at this point 
Solved Threads: 0
monurenjith monurenjith is offline Offline
Newbie Poster

vb recordset problem

 
0
  #1
May 15th, 2009
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>
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2009
Posts: 196
Reputation: jbisono is an unknown quantity at this point 
Solved Threads: 24
jbisono's Avatar
jbisono jbisono is offline Offline
Junior Poster

Re: vb recordset problem

 
0
  #2
May 15th, 2009
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.
If your already resolved your issue, flag it as solved.
José Bisonó
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2007
Posts: 2,149
Reputation: debasisdas will become famous soon enough debasisdas will become famous soon enough 
Solved Threads: 132
debasisdas's Avatar
debasisdas debasisdas is offline Offline
Postaholic

Re: vb recordset problem

 
0
  #3
May 15th, 2009
as suggested in the previous thread your sql statement is wrong.
Share your Knowledge.
Reply With Quote Quick reply to this message  
•
•
Join Date: Mar 2009
Posts: 913
Reputation: vb5prgrmr will become famous soon enough vb5prgrmr will become famous soon enough 
Solved Threads: 167
vb5prgrmr vb5prgrmr is offline Offline
Posting Shark

Re: vb recordset problem

 
0
  #4
May 16th, 2009
Should be something like...
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. 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.
If anyone has helped you solve your problem, please mark your thread as solved.

Thanks
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2009
Posts: 5
Reputation: monurenjith is an unknown quantity at this point 
Solved Threads: 0
monurenjith monurenjith is offline Offline
Newbie Poster

Re: vb recordset problem

 
0
  #5
May 18th, 2009
Thanks......................
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2007
Posts: 300
Reputation: jireh is an unknown quantity at this point 
Solved Threads: 43
jireh's Avatar
jireh jireh is offline Offline
Posting Whiz

Re: vb recordset problem

 
0
  #6
May 18th, 2009
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) 
  1. Dim sqlSelect As String
  2.  
  3. sqlSelect = " SELECT MP.name, MP.memberid "
  4. sqlSelect = sqlSelect & "FROM MemberPersonal MP "
  5. sqlSelect = sqlSelect & "INNER JOIN MembeRunion MR ON MP.memberid = MR.memberid "
  6. sqlSelect = sqlSelect & "WHERE MR.memberid = " & cmbUnion.Text  
  7.  
  8. myrs.Open sqlSelect , mydatabase
Last edited by jireh; May 18th, 2009 at 10:05 pm.
A conclusion is the place where you got tired of thinking.
http://www.martin2k.co.uk/forums/index.php?showforum=4
http://www.a1vbcode.com/a1vbcode/vbforums/Forum3-1.aspx
http://www.developerfusion.co.uk/for...orum&ForumID=4
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2009
Posts: 2
Reputation: Tassilo is an unknown quantity at this point 
Solved Threads: 0
Tassilo Tassilo is offline Offline
Newbie Poster

Re: vb recordset problem

 
0
  #7
Jun 18th, 2009
'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
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the Visual Basic 4 / 5 / 6 Forum


Views: 727 | Replies: 6
Thread Tools Search this Thread



Tag cloud for Visual Basic 4 / 5 / 6
* 6 429 2007 access activex age append application basic beginner birth bmp c++ calculator cd cells.find click client code college column component connection connectionproblemusingvb6usingoledb copy creat ctrl+f data database datareport date delete dissertations dissertationthesis dissertationtopic edit error excel excelmacro file filename form hardware header iamthwee image inboxinvb internetfiledownload keypress label listbox listview liveperson login looping machine microsoft movingranges number objectinsert open oracle prime program prompt range-objects readfile reading record refresh remotesqlserverdatabase report retrieve save search sendbyte sites sort sql sql2008 sqlserver struct subroutine table tags textbox time timer urldownloadtofile vb vb6 vb6.0 vba visual visualbasic visualbasic6 web window windows
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC