-
Nov 11th, 2018, 11:06 PM
#1
Thread Starter
New Member
SQL query generation with combobox
For a particular purpose, I need to dynamically generate SELECT SQL queries and preserve them for later use by the user.
For easiness and error free operation, I give user to visually design their queries. For this, I use listboxes, comboboxes, textboxes, ect.
For an instance, consider the generation of the following query:
SELECT SelectField1, SelectField2, SelectField3, SelectField4, SelectField5 FROM SelectTable
In the above example, I want user to decide their selection of fields out of the field names given for the query. As you can see, users may have different selection.
What I need is to give user to choose their selection from a combobox in some situations, where more than one item from a comboboxes need to be selected. Since normal combobox can not be used for this, how can I accomplish this since I can not use list boxes all the time. And this is not only for selecting field names, but for selecting different tables and other inputs.
Thank you.
-
Nov 12th, 2018, 09:25 AM
#2
Re: SQL query generation with combobox
I've recently posted a "Multi-Select-Combo" into the Codebank here:
http://www.vbforums.com/showthread.p...x-(OwnerDrawn)
HTH
Olaf
-
Nov 12th, 2018, 12:11 PM
#3
Thread Starter
New Member
Re: SQL query generation with combobox
Thank you very much.
But, it's complex for me. How to insert/add simple text to the combobox?
I want to use it for adding some field names to the combobox.
I only found TextOut method in it and use it as follows for testing, but nothing inserted.
ucVirtualCombo1.TextOut 1, 1, "SelectField1"
ucVirtualCombo1.TextOut 2, 2, "SelectField2"
ucVirtualCombo1.TextOut 3, 3, "SelectField3"
ucVirtualCombo1.TextOut 4, 4, "SelectField4"
What am I doing wrong?
-
Nov 12th, 2018, 12:34 PM
#4
Thread Starter
New Member
Re: SQL query generation with combobox
I put it in ucVirtualCombo1_OwnerDraw as follows, it only display the "SelectField1" in the edit area of the combobox, nothing inserted.
Private Sub ucVirtualCombo1_OwnerDraw(ByVal Index As Long, ByVal IsSelected As Boolean, ByVal IsComboItem As Boolean, Canvas As PictureBox, ByVal dx As Long, ByVal dy As Long)
ucVirtualCombo1.TextOut 1, 1, "SelectField1"
ucVirtualCombo1.TextOut 1, 20, "SelectField2"
ucVirtualCombo1.TextOut 1, 30, "SelectField3"
ucVirtualCombo1.TextOut 1, 40, "SelectField4"
End Sub
What is x, y parameters in TextOut and how to decide the values for x, y?
What am I doing wrong?
-
Nov 12th, 2018, 12:44 PM
#5
Thread Starter
New Member
Re: SQL query generation with combobox
Another question
How to clear or delete items so that I can dynamically add another set of field names to the combobox?
Thanks
-
Nov 12th, 2018, 12:49 PM
#6
Re: SQL query generation with combobox
Another potentially easier solution to this problem is to have a ComboBox for the Table, and then a ComboBox for the field names, with Add and Remove buttons that add or remove the field name selected to a simple text box that shows the Field1, Field2, Fieldxx fields that have been selected for reporting.
-
Nov 12th, 2018, 01:21 PM
#7
Thread Starter
New Member
Re: SQL query generation with combobox
jdc2000,
Thank you for your idea, but I prefer to have a multi-select approach, which can reduce the number of mouse-clicks, like the query designer in MS SQL server.
-
Nov 12th, 2018, 01:38 PM
#8
Re: SQL query generation with combobox
You know? I've been asked many times to do something similar to this in my main application. I do have one form that allows users to set certain date ranges, certain filters, and certain sort orders. It queries a set of tables and then shows them the results in a ListView. And then, if they so choose, they can dump it to an Excel file.
However, it's never enough. They come back and say, "well, on this particular field, can you give me the ability to get this AND this, but EXCLUDE these. And then, the next person wants additional fields linked in that aren't in the query. The requested list of "wants" is endless.
I frequently tell them that they're building a SQL query, and that Microsoft Access has an absolutely excellent Visual SQL Query Builder (i.e., their Queries tab). And they say, "ooohhhh, that's too complicated". And I explain that that level of complexity is necessary to give them what they want, and that simple Select queries aren't that complicated. Eventually, in my user-base, there are now enough people proficient at it that they help each other to get it done. (All of my users are in a relatively tight-knit community.)
Piyantha, it looks to me like you're in a similar situation. Users want complex things, but they're not willing to learn the level of complexity necessary to get those things. It's a catch-22.
Good Luck,
Elroy
EDIT1: Just as a note, I've taught several people the basic fundamentals of queries in MS-Access. If I can just get the ideas of logical ANDs and ORs across, and also the use of the LIKE statement, they seem to start flying on their own. Particularly, when they learn how to use the LIKE statement, I can often see them start buzzing with excitement of thoughts of things they may do.
I guess I should also add that they must learn how to make JOINs, and how to specify those joins as LEFT, RIGHT, or INNER with the little join-line context menu. Beyond that, they seem to do okay.
Last edited by Elroy; Nov 12th, 2018 at 01:46 PM.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Nov 12th, 2018, 02:13 PM
#9
Re: SQL query generation with combobox
Originally Posted by piyantha
Thank you very much.
But, it's complex for me. How to insert/add simple text to the combobox?
I want to use it for adding some field names to the combobox.
The OwnerDraw-Event is basically called "once for each row in the external DataSource"
(though the Combo is only informed about the Count of the external DataSource, which can be anything).
To address "which TextContent to draw" in the OwnerDraw-Event, it provides an incoming Index-param,
which is zerobased, and its value can be used for "lookups within your external container"
(in the example below, the different FieldNames are looked up via Rs.Fields(Index).Name)
So, here is some replacement-code for the Demos Test-Form, which you could try...
It does use a Recordset (it's Fields-List) as the external Container for the available Fields,
and a FldSel-Collection, to gather click-selected Fields from the Combo (you needs to add an ADO-Reference to the project then).
An ADO-Recordset which contains "only Field-Descriptions and no Records" can be easily selected using:
"Select * From [SomeTableName] Where 1=0"
To reset the current Rs to new content, you will have to perform a new Select on it -
and to reset the FldSel-Collection you will have to do a simple: Set FldSel = Nothing
After that (to make the Combo aware of the possibly changed Field-Count) you do a:
cboV.ListCount = Rs.Fields.Count
But as said, the new "Count of the external DataSource" is the only thing the ComboBox needs to know,
it will then fire back its ownerdraw-events at you (for one Row-Index at a time - and only for those which are currently visible in the ScrollBox).
Code:
Option Explicit
Private Rs As New ADODB.Recordset, FldSel As New Collection
Private Sub Form_Load()
Rs.Fields.Append "ID", vbLong
Rs.Fields.Append "Name", vbString
Rs.Fields.Append "Descr", vbString
Rs.Fields.Append "Date", vbDate
Rs.Open 'open a Fields-only Rs (without Records - when working against a DB, this would be: "Select * From SomeTable Where 1=0")
'now setup the "MultiSelect-Combo"
cboV.ListCount = Rs.Fields.Count
cboV.ItemHeight = 22
cboV.MinVisibleItems = 10
cboV.MultiSelect = True
' cboV.HorizontalExtent = 400
End Sub
'***** 4 Event-Handlers for the "Multi-Fields-Combo"-Scenario
Private Sub cboV_KeyPress(KeyAscii As Integer, sKey As String) 'a simple search-routine on cboV's Field-Names
Dim i As Long
For i = 0 To Rs.Fields.Count - 1
If StrComp(Left$(Rs.Fields(i).Name, 1), sKey, 1) = 0 Then cboV.ListIndex = i: Exit For
Next
End Sub
Private Sub cboV_ListMultiClick()
With Rs.Fields(cboV.ListIndex)
If KeyExists(FldSel, .Name) Then FldSel.Remove .Name Else FldSel.Add .Name, .Name
End With
End Sub
Private Sub cboV_OwnerDraw(ByVal Index As Long, ByVal IsSelected As Boolean, ByVal IsComboItem As Boolean, Canvas As PictureBox, ByVal dx As Long, ByVal dy As Long)
Canvas.FontName = "Arial": Canvas.FontSize = 10
Canvas.Line (0, 0)-(dx, dy), IIf(IsSelected, RGB(212, 232, 255), Canvas.BackColor), BF
If Index = -1 Or IsComboItem Then 'here we choose, to draw the "checked accumulation" when Index= -1 comes in
cboV.TextOut 3, 3, IIf(Len(GetSelectedFields), GetSelectedFields, "<Select multiple Fields>")
Else
cboV.TextOut 23, 3, Rs.Fields(Index).Name 'Print the name of the Rs-Field
Canvas.Line (4, 4)-(dy - 5, dy - 5), vbBlack, B 'draw an empty Rectangle for the CheckBox
If KeyExists(FldSel, Rs.Fields(Index).Name) Then 'now Print the Checked-State into the just drawn Rectangle
Canvas.FontName = "WebDings": Canvas.FontSize = 13: cboV.TextOut 3, 0, "a"
End If
End If
End Sub
Private Function GetSelectedFields() As String
Dim i As Long
For i = 1 To FldSel.Count
GetSelectedFields = GetSelectedFields & IIf(i = 1, "", ", ") & FldSel(i)
Next
End Function
Function KeyExists(Col As Collection, Key As String) As Boolean
On Error GoTo ReturnFalse
Col Key: KeyExists = True
ReturnFalse:
End Function
If you wrap the above Form-Code in your own (Parent-) UserControl (for that Rs-Fields-Scenario, just passing a new Rs into it, whenever you want other Contents),
you can significantly lower the Code within your "real Form" (your real App) of course...
HTH
Olaf
-
Nov 12th, 2018, 11:03 PM
#10
Thread Starter
New Member
Re: SQL query generation with combobox
Olaf,
Thank you for your code.
But, I am not that interested in extra/additional complexity in my code.
I just want to do it simple.
-
Nov 13th, 2018, 02:58 AM
#11
Re: SQL query generation with combobox
Hi Piy,
what Olaf posted is probably as simple yet effective as it is going to get, sounds like Elroy and jdc2000 have allready done such a Task and gave you some good advice.
so good luck
Last edited by ChrisE; Nov 13th, 2018 at 03:01 AM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Nov 14th, 2018, 01:10 AM
#12
Addicted Member
Re: SQL query generation with combobox
Originally Posted by piyantha
I just want to do it simple.
Regarding your exact point, which is multi-select ComboBox approach, what has been suggested is a great solution.
However, If you need to do it more simple, you may try my CheckBoxCombo here, which is designed in general-purpose perspective.
Following is a sample code that you can do what you need more simple.
Note: Just put a CheckBoxCombo control from the Toolbox and name it as cbxCombo, with 2 Command buttons.
Then, try the code with a new form.
Code:
Option Explicit
Private Sub Command1_Click()
'This is just a matter of one line of code, which will clear your existing list and fill it with your new fields names.
Call cbxCombo.FillListByArray(Array("Field1", "Field2", "Field3"), True)
End Sub
Private Sub Command2_Click()
'If your need to use a recordset for filling your field info, following is the way, with a small helper function.
Dim rstData As Object
Set rstData = CreateObject("ADODB.Recordset")
With rstData.Fields
.Append "Field4", vbString
.Append "Field5", vbString
.Append "Field6", vbString
End With
Call SetFieldsInfo(cbxCombo, rstData)
End Sub
Private Sub SetFieldsInfo(cbc As CheckBoxCombo, rst As Object)
cbc.Clear
With rst
.Open
Dim i As Long
For i = 0 To .Fields.Count - 1
cbc.AddItem (.Fields(i).Name)
Next
.Close
End With
End Sub
-
Nov 14th, 2018, 10:58 AM
#13
Thread Starter
New Member
Re: SQL query generation with combobox
Thank you VERY much PGBSoft.
I just tested and it works like a charm. I really feel it like a familiar control with familiar syntax.
Never thought, it is this easy. Vbforum is a great forum.
Thank you, all for the valuable advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|