Results 1 to 13 of 13

Thread: SQL query generation with combobox

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Posts
    8

    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.

  2. #2
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    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

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Posts
    8

    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?

  4. #4

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Posts
    8

    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?

  5. #5

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Posts
    8

    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

  6. #6
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    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.

  7. #7

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Posts
    8

    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.

  8. #8
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    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.

  9. #9
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL query generation with combobox

    Quote Originally Posted by piyantha View Post
    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

  10. #10

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Posts
    8

    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.

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    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.

  12. #12
    Addicted Member
    Join Date
    Jun 2018
    Posts
    189

    Re: SQL query generation with combobox

    Quote Originally Posted by piyantha View Post
    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

  13. #13

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Posts
    8

    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
  •  



Click Here to Expand Forum to Full Width