Solved

fill listbox from db (translate code from vb6)

Posted on 2006-10-23
10
209 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:Dany Balian
  • 6
  • 4
10 Comments
 
LVL 18

Expert Comment

by:vbturbo
ID: 17794183
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
 
LVL 11

Author Comment

by:Dany Balian
ID: 17794789
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
 
LVL 18

Expert Comment

by:vbturbo
ID: 17795668
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
 
LVL 11

Author Comment

by:Dany Balian
ID: 17809703
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
 
LVL 11

Author Comment

by:Dany Balian
ID: 17809724
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Expert Comment

by:vbturbo
ID: 17810244
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
 
LVL 18

Accepted Solution

by:
vbturbo earned 250 total points
ID: 17810263
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
 
LVL 18

Expert Comment

by:vbturbo
ID: 17810275

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
 
LVL 11

Author Comment

by:Dany Balian
ID: 17812768
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
 
LVL 18

Expert Comment

by:vbturbo
ID: 17813067
You should be able to Sub Main in the combo box

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

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Zoom web page in asp.net 2 37
Form design in vb.net 7 21
Get String split 5 33
Setting runtime form location 4 19
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now