Dynamically populating a combo box .?

I am trying to populate a combo box on a form based on the contents of a field within SQL.

i.e.

I read the data from the SQL field. In this case I return the string "Select * From Transactions"
I populate my dataset and set the dataset as the datasource of my combo box. This works fine.

However what I want to do is to use a where clause in my select statement such as "Select * From Transactions Where AccNo = '" & strAccNo & "'", where strAccNo is a variable within my VB app.
Now not surprisingly my combo box is empty as the the datasource is exactly as my select statement reads and it is trying to return records where AccNo matches strAccNo and not as I had hoped which is to match AccNo to the contents of strAccNo (X5235982 for example).

What I have:
Select * From Transactions Where AccNo = 'strAccNo'

What I want:
Select * From Transactions Where AccNo = 'X5235982 '

Any ideas appreciated.

Answer this question

Dynamically populating a combo box .?

Peter De Decker
I suggest also that you use Data Binding, as none of the other code samples seem to...
Stefan Wenig
Gretsky99,
If you have the variable name enclosed within quotes it will be treated as a string literal....otherwise if you have declared that variable it will be treated as one...

Dim Criteria as string = "'MyString'" apostrophees
Dim FieldName as String = "MyField"
Dim SqlString as string = "Select * FROM Transactions "
Dim WhereString as String ="Where " & FieldName & "=" & Criteria
SqlString = SqlString & WhereString

In the Above Senerio SqlString= Select * FROM Transactions Where MyField = 'MyString'

If that does not help...please show me an example.
PhilMan99
Thanks DMan1

but not sure that is quite what I'm looking for.

Because I do not always want to filter my select statement on the the same field (fields) I need to also store the variable name in the SQL table.

So
"Select * From Transactions Where AccNo = '" & strAccNo & "'"
could just as easily be
"Select * From Transactions Where Surname = '" & strSurname & "'"
or even
"Select * From Transactions Where AccNo = '" & strAccNo & "' and Surname = '" & strSurname & "'"

In the last example therefore I would save the following text within my SQL field
"Select * From Transactions Where AccNo = '" & strAccNo & "' and Surname = '" & strSurname & "'"
but when grabbed by my VB app, to work as
"Select * From Transactions Where AccNo = '" & X1234567 & "' and Surname = '" & Smith & "'"

Code example below:

Private Sub SetUpCombo(ByVal cbComboBox As ComboBox, ByVal strSelect As String, ByVal strField As String)

Dim oleConn As New System.Data.OleDb.OleDbConnection(strConnADO)

Dim oleAdapter As New System.Data.OleDb.OleDbDataAdapter()

Dim myDataset As DataSet

Dim intCounter As Integer

myDataset = New DataSet()

oleAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand(strSelect, oleConn)

oleAdapter.Fill(myDataset)

For intCounter = 0 To myDataset.Tables(0).Rows.Count - 1

cbComboBox.Items.Add(myDataset.Tables(0).Rows(intCounter).Item(strField))

Next

End Sub

In this instance strSelect is being passed in as
"Select * From Transactions Where AccNo = '" & strAccNo & "'"
and so obviously my combo box is not being populated.

Cheers

lost_in_ssl

Dim MyAccOuntNum as String = "'X5235982'"
  Dim strSQL As String
      strSQL = "Select * From Transactions Where AccNo = "
strsql = strsql & MyAccountNum

  Dim objCommand As New SqlCommand(strSQL,objConnection)
  objConnection.Open()
    Dim MyReader as DataReader = objCommand.ExecuteReader

While MyReader.Read

Me.Combobox1.Items.Add(MyReader("AccNo").ToString)

End While

objConnection.Close()

If Me.combobox1.Items.Count > 0 Then

Me.combobox1.SelectedIndex = 0

End If

Rick Heiges
If I were to create a select statement within my VB app I would use:
strSelect = "Select * From Transactions Where AccNo = '" & strAccNo & "' and Surname = '" & strSurname & "'"
In the immediate window I issue strSelect and get back:
"Select * From Transactions Where AccNo = X1234567 and Surname = Smith"
Correct.!

I am trying to populate strSelect however with the contents of a SQL table field which contains the following:
Select * From Transactions Where AccNo = '" & strAccNo & "' and Surname = '" & strSurname & "'

However when assigning this to strSelect by using:
Sub Populate_StrSelect()
...create ADO recordset
strSelect = .Fields("Arg1cbSelect").Value
End Sub

and issuing strSelect in the immediate window I get:
"Select * From Transactions Where AccNo = '" & strAccNo & "' and Surname = '" & strSurname & "'"

It seems that whatever syntax I use (quotes/no quotes) within my SQL table field, strSelect never includes the contents of my 2 VB variables (strAccNo & strSurname), but only the literal string.

Cheers
Galen Parker
Yes but when I pull in from a SQL field the variable name that I am using within VB, it is treated as a literal string and not as a variable name.
OoberBoober
Thanks.

I had also come to the conclusion that a parser was needed, but hoped there may be another way.

Gretzky
Zurdo
If you're using a SQL database why don't you create stored procedures to execute your query. You can pass in the field name and query parameter as a variables. I have done exactly this for update commands. The only downside is you will have to create stored procedures for each datatype relating to the field you are querying on, but this is still simple. Its also apparently faster and follows best practice rules. example below

procedure [dbo].[UpdatePrintfieldTypeChar]
@PrintFieldKey int,
@Column varchar(50),
@value varchar(50)
as
begin
    Update [PrintFields]
    Set @column = @value
    where PrintfieldKey = @PrintFieldKey
end

Kev
Joachim Roppert
Yes i understand now...you are storing the entire sql string in a table field...and that string contains your variable names....

Your issue is when you read the string from the field the entire field is being translated as a single string....no way around that...

your fix would be one of a couple of solutions...

Parse your string for the variable names and replace with the variable...or

build your string "on-the-fly"...

What you are trying to accomplish will not work...

you could also store the portions of your SQL statement in different fields to make the parsing task easier...in either case you will still have to translate the string variable name to the variable...

One last thing here....you need to look into using stored procedures and passing parameters....
Kiran Kirdat
Principle is the same for all select statements with a where clause...

Where FieldName = Criteria

and you can dynamically/progmatically set FieldName and criteria...

The issue is to be aware of the datatype of the criteria

Criteria as string = "'MyString'" apostrophees
Criteria as Integer = 10
Criteria as Date = #8/8/08#

Dim SqlString as string = "Select * FROM Transactions "
Dim WhereString as String ="Where " & FieldName & Operator & Criteria
SqlString = SqlString & WhereString..

Hope that helps...
Dynamically populating a combo box .?

Answer this question