9 Replies - 4583 Views - Last Post: 13 April 2009 - 03:16 PM Rate Topic: -----

#1 AccursedAce  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 07-April 09

VB6 - Using SQL and Drop Down Menu's

Post icon  Posted 09 April 2009 - 02:34 PM

This is going to be a two question on one post that are quite related

Firstly how do I put SQL commands into my code on vb, I'm trying to get it so that in my drop down menu when the form loads it only loads distinct entries for example if you had a gender field, you wouldn't want it to keep loading male, male, female, male, female etc. which happens if you use this code.

cboCuGender.AddItem rs.Fields("Gender") & ""


you would need an SQL statement something like

SELECT DISTINCT (Gender)
FROM tblCustomer;


Also I'm using ADO to connect to my database. I was wondering how the following scenario would be possible.

I have one drop down menu with categories when I select one of the categories, a different drop down menu is enabled where I can then select only items in that category.

If this isn't clear enough I'll try to clarify, just post below.

Thank you all for your help, I really appreciate it

Ross

Is This A Good Question/Topic? 0
  • +

Replies To: VB6 - Using SQL and Drop Down Menu's

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: VB6 - Using SQL and Drop Down Menu's

Posted 09 April 2009 - 02:58 PM

Place SQL strings within "":
[do some action like recordset.Open] "SELECT DISTINCT gender FROM tblCustomer;" [if action is to open recordset may need parameters like: , CurrentProject.Connection, adOpenStatic, adLockPessimistic]


By 'drop down menu' do you mean a list or combo box? If so, might use double click event on the first box to run code that will modify the enabled property of the second box from No to Yes. Try coding and then come back here.

This post has been edited by June7: 09 April 2009 - 03:03 PM

Was This Post Helpful? 0
  • +
  • -

#3 AccursedAce  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 07-April 09

Re: VB6 - Using SQL and Drop Down Menu's

Posted 09 April 2009 - 03:12 PM

View PostJune7, on 9 Apr, 2009 - 01:58 PM, said:

Place SQL strings within "":
[do some action like recordset.Open] "SELECT DISTINCT gender FROM tblCustomer;" [if action is to open recordset may need parameters like: , CurrentProject.Connection, adOpenStatic, adLockPessimistic]


By 'drop down menu' do you mean a list or combo box? If so, might use double click event on the first box to run code that will modify the enabled property of the second box from No to Yes. Try coding and then come back here.



Couldn't quite figure out how to get it to work by your guide, this is the code for the sub that i'm working on.

Private Sub Form_Load()
	'turn MousePointer to HourGlass to show that we are busy processing, useful for when the database gets bigger or when its opened on an old machine
	Me.MousePointer = vbHourglass
	
	'instantiate the connection object
	Set cn = New ADODB.Connection
	'specify the connectionstring
	cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
						  "Data Source=" & App.Path & "\kungfumaindb.mdb"
	'open the connection
	cn.Open
	
	'instantiate the recordset object
	Set rs = New ADODB.Recordset
	'open the recordset
	With rs
		.Open "tblCustomers", cn, adOpenKeyset, adLockPessimistic, adCmdTable
		   
		'loop through the records until reaching the end or last record
		Do While Not .EOF
			Dim SQL As String
			

			cboCuGender.AddItem rs.Fields("Gender") & "" 'Adds items to the combo box, plus an empty string, stops null error
			cboCuMemberType.AddItem rs.Fields("MemberType") & "" 'as above
			
		   rs.MoveNext 'moves next record
		Loop
		
		If Not (.EOF And .BOF) Then
			rs.MoveFirst	'go to the first record if there are existing records
			FillFields	  'to reflect the current record in the controls
		End If
		
	End With
	
	Me.MousePointer = vbNormal 'sets the mouse pointer to the normal arrow
	
Protect True
End Sub



These are the two offending lines!

cboCuGender.AddItem rs.Fields("Gender") & "" 'Adds items to the combo box, plus an empty string, stops null error
			cboCuMemberType.AddItem rs.Fields("MemberType") & "" 'as above

This post has been edited by AccursedAce: 09 April 2009 - 03:12 PM

Was This Post Helpful? 0
  • +
  • -

#4 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: VB6 - Using SQL and Drop Down Menu's

Posted 09 April 2009 - 08:38 PM

First of all, there is only two possible genders, right? Unless you want to include hermaprodites. So don't bother with the recordset, just do two .AddItem's for Male and Female outside the loop.
As for the MemberType, modify your recordset to get just the MemberType distinct values that aren't null and in ascending alphabetical order:
rs.Open "SELECT DISTINCT MemberType FROM tblCustomers WHERE Not IsNull(MemberType) ORDER BY MemberType;", cn, adOpenKeyset, adLockPessimistic, adCmdTable


Also, refer to the following:

http://visualbasic.f...esson8/p26.html

"You can use the numeric index of the field in the Fields collection (Fields is zero-based, so the first field is element 0 in the collection):

rs.Fields(2).Value

This technique is flexible (you could use a numeric variable as the index for the Fields collection), but it's not quite as useful as the following technique, because unless you know the position of fields in the data, you will have a hard time getting the right index.

Because the Fields collection also supports index key strings, you can use the field's name in a string literal or variable to refer to the field in the Fields collection:

rs.Fields("LastName").Value
rs("LastName").Value

Notice the second alternative form of this example, which bypasses an explicit reference to the Fields collection.

This technique is perhaps the most useful from a programming standpoint, but it also requires the most runtime overhead and so is the slowest of the three techniques.

You can also refer to the field as a temporary property of the Recordset, using the bang (!) syntax: rs!LastName"

This post has been edited by June7: 09 April 2009 - 09:03 PM

Was This Post Helpful? 1
  • +
  • -

#5 AccursedAce  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 07-April 09

Re: VB6 - Using SQL and Drop Down Menu's

Posted 10 April 2009 - 04:25 AM

Thanks June7, i'm still running into problems.

I placed your code exactly where my current combo box code is (replacing it),


However when I run the program I get the following error,

Operation is not allowed when the object is open


now I'm pretty sure this error was called by the recordset already being open, so I tried puting "rs.close" in the line before the rs.open with all the sql and got a different error.

Runtime Error - (lots of numbers)

Syntax error in FROM clause


and then when I click debug it throws me back to the line of code you gave me.

Thanks again for taking your time out to help me, really looking forward to getting this fixed.
Was This Post Helpful? 0
  • +
  • -

#6 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: VB6 - Using SQL and Drop Down Menu's

Posted 10 April 2009 - 11:56 AM

Sorry, I had the syntax for the Null criteria messed up. Try:
rs.Open "SELECT DISTINCT MemberType FROM tblCustomers WHERE MemberType Is Not Null ORDER BY MemberType;", cn, adOpenKeyset, adLockPessimistic, adCmdTable

Here is a URL for help on SQL:
http://www.w3schools...null_values.asp
Was This Post Helpful? 1
  • +
  • -

#7 AccursedAce  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 07-April 09

Re: VB6 - Using SQL and Drop Down Menu's

Posted 10 April 2009 - 03:22 PM

Still running into the same errors with your new code. Thank you again for trying.

Have a little look at my form (as attached to this post). If you get some time see if you can get it to work.

If you do I'd most apreciate information on what you changed, this has really stumped me.

Attached File(s)


This post has been edited by AccursedAce: 10 April 2009 - 03:23 PM

Was This Post Helpful? 0
  • +
  • -

#8 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: VB6 - Using SQL and Drop Down Menu's

Posted 10 April 2009 - 03:46 PM

Tried to download and open your zip but get error that it is corrupt. Maybe you could make a new zip and send to me as attachment to an email - use DIC msging.


Edit: I did get your msg but deleted before thought to look for attachment. If there was one, try again if you want. Your personal message box is full.

This post has been edited by June7: 10 April 2009 - 08:02 PM

Was This Post Helpful? 0
  • +
  • -

#9 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: VB6 - Using SQL and Drop Down Menu's

Posted 11 April 2009 - 03:58 PM

Not sure what you mean by 'I placed your code exactly where my current combo box code is (replacing it)' My suggestion was to replace the line that opened the recordset, does nothing to a combo box.
Was This Post Helpful? 0
  • +
  • -

#10 AccursedAce  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 07-April 09

Re: VB6 - Using SQL and Drop Down Menu's

Posted 13 April 2009 - 03:16 PM

Sorry for the slow reply, i did manage to fix this by following a tutorial on another site, was pretty good, got a module set up now so I only need a little code to fill up all the combo boxes around my project.

Thanks again for try June7, also the reason you might have got a corrupt error on that file was because I created it using WinRAR, might be some compatibility issues if you open it up on another decompressing program.

Hope you had a good Easter and thank you again for trying I do appreciate it.

This post has been edited by AccursedAce: 13 April 2009 - 03:17 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1