Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

fill listbox from db (translate code from vb6)

Posted on 2006-10-23
10
Medium Priority
?
236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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 1000 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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

650 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