0

trying to fill a combobox using a generic routine in a Module in vb.net 2010. I am passing to the sub the variable formname.comboboxname and splitting it into 2, running a query and this is where I can't go further. I need to do this because I have quite a few forms and comboboxes to fill, thought a generic method in one module would be great.

I may break down and do a dataset for each combobox but this certainly should work and would be my preferred choice.

in the form code I call it with --- loadComboBoxOrHelp("FrmArchive.cmbSrcType") and this is in my module, some code is commented just to show what I have tried so far. And of course the line above the CTYPE works if I uncomment it but then its not generic. As you can guess from the name of the function, i want to fill a combobox OR a help text field but I have not done that code yet, I assume once the combobox part works I can guess out the text box part. The query will be the same, just a text box instead of combobox.

The CTYPE is my error, I am trying to do this from a module so its very generic, if I put it in the forms code of course it works. ** I took out the SQL as it was confusing the issue. I am now calling it this with loadComboBoxOrHelp(Me, cmbSrcType)

Public Sub loadComboBoxOrHelp(ByVal formName, ByVal boxName)
    Try
        CType(formName.Controls(boxName), ComboBox).Items.Add("ABC") 'myReader.GetString("lookupValue").ToString)
    Catch ex As Exception
        MessageBox.Show("Error while retrieving records on table Lookups..." & ex.Message, "Lookups Table")
    Finally
        If conn.State = ConnectionState.Open Then conn.Close()
    End Try
End Sub

End Module

Mike
  • 47
  • 7
  • Why don't you pass directly the combobox control instead of a string with its name embedded? – Steve Dec 07 '13 at 00:47

1 Answers1

2

Well, I think you could pass directly the ComboBox to the function, but if you really want to use a string then you need to find a way to retrieve the Form and combo instance from the name passed in

Public Sub loadComboBoxOrHelp(ByVal boxName)
    Dim words = boxName.Split(".") 
    Dim formName = Application.OpenForms(words(0))  
    Dim cmbBox = formName.Controls(words(1))
    ......

As a side note. Use a parameterized query to build sql commands, not string concatenation. Also if you are in full control of what is passed to the sql command it is better to avoid the quoting/unquoting of values 

    conn.Open()
    myCommand.Connection = conn
    Dim query = "SELECT name, lookupValue " _
                & "FROM lookups " _
                & "WHERE name = @bxname"
    myCommand.CommandText = query
    myCommand.Parameters.AddWithValue("@bxname", boxName)
    myReader = myCommand.ExecuteReader()
Steve
  • 213,761
  • 22
  • 232
  • 286
  • I swear I tried to pass the form and combo name separately and it did not work but its working now, so THANK YOU. However, I could not get your code to work as is, it does not give a error but also does not put the values in. Because of the way the DB is built, its easier if I can pass in the one value but I will experiment more with your code, I would prefer spliting the value. I am not parameterizing the SQL because in this case only 4 managers have access to change the lookup values so I didnt care about sql injection however I see your point about it being easier and will do it anyway. – Mike Dec 07 '13 at 01:57
  • OK, I spoke too soon, still not working. From home I got it to work but only on a very small test app. On my real app still not working at work that I did not have access to when i marked this solved, I gave up. It can work as Steve coded but my app with lots of forms its not for some reason. For the record, I changed the sub to a function and return a array with the data and load it that way, a little less generic but it works. – Mike Dec 09 '13 at 19:23
  • This line, `Dim cmbBox = formName.Controls(words(1))`, was exactly what I needed in this exact situation. I already had the form name as a global, just needed to know how to address the combobox. – pixelmeow Apr 22 '16 at 19:09