Read from Recordset in VB6

[GerardMcL]

Hi,

I have been using VB6 up until recently and have switched to VB.Net. What Im trying to do is populate a combo box from a recordset but I cant get the same to work in VB.Net.

E.G.
strConn = "Microsoft.Jet.OLEDB.4.0;DSN=CorlinDSN;"
'Wanted to use an ODBC Connection & Access

dbConn.Open strConn
rs = "SELECT * FROM Tools"

While Not rs.EOF
cmbBox1.Items.Add(rs(ColA) & rs(ColB))
rs.MoveNext
Wend


Are the structures completely changed now or do I just need to do some fine tuning?

Help as always is greatly appreciated.

 

[PGoRule]

If you want to populate combo in VB.NET, then you must go for using DataTable or DataSet...The concept of Recordset will not work here.
The concept is same, but you need to go with ADO.NET
Open a connection, Fire SQL query , take the result in OleDbDataReader, use this output as a DataSourse for your combo and bind that data to combo by calling .Bind method. There is no need to iterate through the recordset as done previously.

Sharing the best from side...

--Prashant--

 

[TipGiver]

Hi, you can try something like the below. Please note that:
1. I have not tested it
2. The is no error handling

        Dim cnStr As String = "Your connection string"
        Dim cn As New Odbc.OdbcConnection(cnStr)

        If Not cn.State = ConnectionState.Open Then
            cn.Open()
        End If

        Dim qyStr As String = "SELECT * FROM Tools"
        Dim da As New Odbc.OdbcDataAdapter(qyStr, cnStr)
        Dim dt As DataTable
        da.Fill(dt)

        For Each dr As DataRow In dt.Rows
            Me.ComboBox1.Items.Add(dr("colA_Name") & dr("colB_Name"))
        Next

        cn.Close()
        da.Dispose()

        cn = Nothing
        da = Nothing

?

 

[ThatRickGuy]

You can do it with ADO from vb6, but this would be a great exersize to learn more about ADO.Net like the above two posts suggest.

The direct bind vs iterate loop approach is often debated. For combo boxes, I have been known to use ComboBox1.datasource = MyDataTable for lookup fields. But I usually avoid tieing the GUI directly to the database.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[IT4EVR]

What I do to populate combo boxes is to try to use the items.addrange method as much as possible. Basically it allows you to add items to the combo box in one chunk, instead of iterating through each row one by one. Loading combo boxes can be very resource intensive.

The example below uses an array to populate the combo in one fell swoop.

Private Sub btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn.Click
        Dim states As String() = {"MD", "VA", "PA", "DE"}

        Me.cbo.Items.AddRange(states)
    End Sub

I've noticed that this is incredibly faster than iterating through items.add() for each row.

You can use the datareader.getvalues to return an array, then populate combos, without having to create a data table or open a data adapter.

Just another way of doing things...