fill listbox from db (translate code from vb6)

dear experts,

i'm switching to vb.net and i would like to know how to write this peace of code in dot net.

please give me the optimal code for databases with lots of records (avg: 2000 products).
and what references do i have to include?

i appreciate your help,

Dan


VB6 code:
----------------

private sub LoadProducts()

on error resume next
dim db as new adodb.connection
db.connectionstring="dsn=test;uid=admin;pwd=pass"
db.open
if err.number<>0 then
msgbox "cannot connect to db"
exit sub
end if

dim rs as new adodb.recordset
rs.open "select * from products",db,adopenforwardonly,adlockreadonly
list1.clear
if not rs.eof then
rs.movefirst
while not rs.eof
 list1.additem rs("productname")
 list1.itemdatat(list1.newindex)=rs("productID")
 rs.movenext
wend
end if
if list1.listcount>0 then list1.listindex=0

end sub
LVL 11
Dany BalianCTOAsked:
Who is Participating?
 
vbturboConnect With a Mentor Commented:
Sorry

Imports System.Windows.Forms


Module MainModule

Public objDs As New DataSet
Public rs As DataRow
Dim ConnString = "dsn=pos;uid=dba;pwd=pass"
Public m_cn As New OdbcConnection(ConnString)
Public sSql As String

'___________________________________________________________________
'******************************** is there any way i can make this default in my dot net?? i want all new combos that i add to have this behavior?
'here you make the scope of the class public
Public g_myListItems As New ListItem()
'____________________________________________________________________

'this would populate the public dataset at startup

    Public Sub Main()

        sSql = "select p.prodnum, p.descript,r.descript as rep from products p, reportcategory r where p.reportcat=r.id and p.isactive=1"

        If m_cn.State <> ConnectionState.Open Then m_cn.Open()
        Try
            Dim objDa As New OdbcDataAdapter(sSql, m_cn)
            objDa.Fill(objDs, "d")
            m_cn.Close()
            MsgBox("Dataset is populated : " &  g_myListItems.MsgCaption)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
            m_cn.Close()
        End Try

            '// Start the application.
            System.Windows.Forms.Application.Run(New frm form1 ())
    End Sub

End Module


'and the list item class

Public Class ListItem
    Private mID As Integer
    Private mName As String

    Public ReadOnly Property MsgCaption() As String
        Get
            Return " Returned with succes"
        End Get
    End Property

#Region "Constructor(s)"
    Public Sub New(ByVal ItemData As Long, ByVal Caption As String)
        mID = ItemData
        mName = Caption
    End Sub
    Public Sub New()
        'Empty default constructor
    End Sub
#End Region

#Region "Properties"
    Public Property ItemData() As Integer
        Get
            Return mID
        End Get
        Set(ByVal Value As Integer)
            mID = Value
        End Set
    End Property

    Public Property Caption() As String
        Get
            Return mName
        End Get
        Set(ByVal Value As String)
            mName = Value
        End Set
    End Property

    Public Overrides Function ToString() As String
        'Required so that the listbox will display the correct
        'label
        Return mName
    End Function
#End Region
End Class

0
 
vbturboCommented:
Imports System.Data.OleDb  

Private Sub test_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
   
   Dim  objDs as new dataset
   Dim  objDataRow As DataRow
   Dim ConnString ="dsn=test;uid=admin;pwd=pass"
   Dim m_cn As New OleDbConnection(ConnString)
   Din  sSql As String

        sSql = "select * from products"

        If m_cn.State <> ConnectionState.Open Then m_cn.Open()
        Try
            Dim objDa As New OleDbDataAdapter(sSql, m_cn)
            objDa.Fill(objDs, "products")
            m_cn.Close()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
            m_cn.Close()
        End Try


        list1.Items.Clear()

        For Each objDataRow In objDs.Tables("products").Rows

            list1.Items.Add(objDataRow("productID").ToString())

        Next

    End Sub
0
 
Dany BalianCTOAuthor Commented:
before i test this... i have two comments:

1. you have not used the itemdata property which basically associates an id to every product.
cos i want later on when the user selects the combo to fill other data on the form by having the combo's itemdata which is the product id.
what you have done (i guess) is just showing the product ids...

2. objDa.Fill(objDs, "products")
what does this line do??
what is "products"; is it the name of the table??
what if i have a complexe sql, with joins?? and i have to take the multiple data and load them into the listbox??

for instance:
select cp.catid,c.catname,count(cp.id) from categories c, categoryproducts cp
where c.id*=cp.catid
group by cp.catid, c.catname

this will give me a list of categories with the count of items in each category.

my list would be:

Stationary (5 items)     << with an itemdata of 1 (not visible)
Snacks (15 items)       << with an itemdata of 2
Drinks (3 items)          << with an itemdata of 3
Electronic Devices (0 items)   << with an itemdata of 4

I appreciate your help,

Cheers,

Dan

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
vbturboCommented:
Hi

Sorry i did'nt this with itemdata

i suppose that you could do it like this

        For Each objDataRow In objDs.Tables("products").Rows
            list1.Items.Add(objDataRow("productID").ToString())'           and make this    visible = false
            'and when ever something is selected in list2 or your combo ,there index would be the same index as in list1.listindex
            ' that way you can retrive the primarykey list1.("productID") value
            list2.Items.Add(objDataRow("productname").ToString())
           'combo1.Items.Add(objDataRow("productname").ToString())
        Next

2. objDa.Fill(objDs, "products")
what does this line do??
what is "products"; is it the name of the table??
what if i have a complexe sql, with joins?? and i have to take the multiple data and load them into the listbox??


this dataset( objDs ) holds all the data from the table "products". Its a copy of the actual dabase table so to say.
You can join tables as you like this populate the dataset with your Query results as the above  sSql = "select * from products"
and take the result into a combo ,listbox as you like

vbturbo

0
 
Dany BalianCTOAuthor Commented:
ok vbturbo, your second answer was definitely not what i want...
i definitely dont want to add 2 listboxes for every combo or listbox that i want to add itemdata to...
but your code was definitely a good start to start working with dotnet...

after a long day of testing and searching...
i was able to do what i wanted, and below is the code:
as i understood, the items in the listboxes (in dotnet) are objects. so i created a class that contains an id and caption
and instead of adding captions to the listbox, i'm actually adding new instances of that class to it...

the trick that i learned by searching is that... i should overload the tostring function of the listbox so that it returns the caption of my class.

cheers,

dan

code follows:
**************************************

        Dim objDs As New DataSet
        Dim rs As DataRow
        Dim ConnString = "dsn=pos;uid=dba;pwd=pass"
        Dim m_cn As New OdbcConnection(ConnString)
        Dim sSql As String

        sSql = "select p.prodnum, p.descript,r.descript as rep from products p, reportcategory r where p.reportcat=r.id and p.isactive=1"

        If m_cn.State <> ConnectionState.Open Then m_cn.Open()
        Try
            Dim objDa As New OdbcDataAdapter(sSql, m_cn)
            objDa.Fill(objDs, "d")
            m_cn.Close()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
            m_cn.Close()
        End Try

        List1.Items.Clear()

        Try
            For Each rs In objDs.Tables("d").Rows

                List1.Items.Add(New ListItem(rs("prodnum"), rs("descript").ToString()))

            Next
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
        End Try

and here's my class:
*********************

Public Class ListItem
    Private mID As Integer
    Private mName As String

#Region "Constructor(s)"
    Public Sub New(ByVal ItemData As Long, ByVal Caption As String)
        mID = ItemData
        mName = Caption
    End Sub
    Public Sub New()
        'Empty default constructor
    End Sub
#End Region

#Region "Properties"
    Public Property ItemData() As Integer
        Get
            Return mID
        End Get
        Set(ByVal Value As Integer)
            mID = Value
        End Set
    End Property

    Public Property Caption() As String
        Get
            Return mName
        End Get
        Set(ByVal Value As String)
            mName = Value
        End Set
    End Property

    Public Overrides Function ToString() As String
        'Required so that the listbox will display the correct
        'label
        Return mName
    End Function
#End Region
End Class
0
 
Dany BalianCTOAuthor Commented:
the question that i need to ask is:
is there any way i can make this default in my dot net?? i want all new combos that i add to have this behavior?
i appreciate your help...

and one more question... usually i connect to the db in vb6 in my sub main() in a module

i searched the internet, and found out that i need to create a public module, and put a public sub main in it...
and it should appear in the project properties (startup form)

but it doesnt.. what's wrong?? i only have form1 in my startup forms

thanks again,,

dan
0
 
vbturboCommented:
Hi

'This is the way to start from a module

Imports System.Windows.Forms

Public objDs As New DataSet
Public rs As DataRow
Dim ConnString = "dsn=pos;uid=dba;pwd=pass"
Public m_cn As New OdbcConnection(ConnString)
Public sSql As String

'___________________________________________________________________
'******************************** is there any way i can make this default in my dot net?? i want all new combos that i add to have this behavior?
'here you make the scope of the class public
Public g_myListItems As New ListItem()
'____________________________________________________________________

Module MainModule

'this would populate the public dataset at startup
    Public Sub Main()

        sSql = "select p.prodnum, p.descript,r.descript as rep from products p, reportcategory r where p.reportcat=r.id and p.isactive=1"

        If m_cn.State <> ConnectionState.Open Then m_cn.Open()
        Try
            Dim objDa As New OdbcDataAdapter(sSql, m_cn)
            objDa.Fill(objDs, "d")
            m_cn.Close()
            MsgBox("Dataset is populated : " &  g_myListItems.MsgCaption)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
            m_cn.Close()
        End Try

            '// Start the application.
            System.Windows.Forms.Application.Run(New frm form1 ())
    End Sub

End Module




Public Class ListItem
    Private mID As Integer
    Private mName As String

    Public ReadOnly Property MsgCaption() As String
        Get
            Return " Returned with succes"
        End Get
    End Property

#Region "Constructor(s)"
    Public Sub New(ByVal ItemData As Long, ByVal Caption As String)
        mID = ItemData
        mName = Caption
    End Sub
    Public Sub New()
        'Empty default constructor
    End Sub
#End Region

#Region "Properties"
    Public Property ItemData() As Integer
        Get
            Return mID
        End Get
        Set(ByVal Value As Integer)
            mID = Value
        End Set
    End Property

    Public Property Caption() As String
        Get
            Return mName
        End Get
        Set(ByVal Value As String)
            mName = Value
        End Set
    End Property

    Public Overrides Function ToString() As String
        'Required so that the listbox will display the correct
        'label
        Return mName
    End Function
#End Region
End Class

hope this helps
0
 
vbturboCommented:

hmm...... im a bit to quick on the submit

alter this System.Windows.Forms.Application.Run(New frm form1 ())

to
'(your formname or form1)
System.Windows.Forms.Application.Run(New form1())
0
 
Dany BalianCTOAuthor Commented:
when i go to the project properties, and i see the startup form... there's only form1

i cannot see sub main in the combobox..

why?
0
 
vbturboCommented:
You should be able to Sub Main in the combo box

have you re build the solution after you pasted the above code ?

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.