Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 19th, 2007, 09:43 AM
Authorized User
 
Join Date: Nov 2007
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to djobes31770 Send a message via Yahoo to djobes31770
Default Dynamic Table Selection

I am new to vb and I have been writing an app in Realbasic, but it has does not allow for me to do reports and several other key items needed. So i have started to port to VB Express. I have an access db that has several tables that i need to be able to select based on a drop-down list box.

Level 1 public, class, sens
Level 2
Level 3

There are a total of 9 different tables, each has differing information in them, i need to be able to select a table, and then populate a listbox or datagrid based on the selection. I have searched online and msdn and a few oriely cookbooks, no help. Is this possible and what would be the steps i would use, i have a this defined already



Code:
Private Sub LoadSys()
        Dim strMAC As String

        ' Define the tables for lookup based on the popup/ drop-down control
        Select Case cmbMAC.Text
            Case "Level 1 Public"
                strMAC = "M1PControls"
                displayControls(strMAC)
            Case "Level 1 Classified"
                strMAC = "M1CControls"
                displayControls(strMAC)
            Case "Level 1 Sensitive"
                strMAC = "M1SControls"
                displayControls(strMAC)
            Case "Level 2 Public"
                strMAC = "M2PControls"
                displayControls(strMAC)
            Case "Level 2 Classified"
                strMAC = "M2CControls"
                displayControls(strMAC)
            Case "Level 2 Classified"
                strMAC = "M2SControls"
                displayControls(strMAC)
            Case "Level 3 Public"
                strMAC = "M3PControls"
                displayControls(strMAC)
            Case "Level 3 Classified"
                strMAC = "M3CControls"
                displayControls(strMAC)
            Case "Level 3 Sensitive"
                strMAC = "M3CControls"
                displayControls(strMAC)
        End Select

    End Sub

    ' load the db grid based on selected controls table, by using dynamic query
    Public Sub displayControls(ByVal strMAC As String)
        Dim ds As New DataSet
        Dim strOption As String
        Using dsControls As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=iatrack.mdb")
            Dim list As DataSet = New DataSet()
            Dim dsadapter As OleDbCommand = New OleDbCommand("SELECT * from strMAC")
            DataGrid1.DataSource(dsadapter)
            'OleDbDataAdapter1.Fill(list, "Control Number")
        End Using



    End Sub
How far off base or is there another way to do this.

Reply With Quote
  #2 (permalink)  
Old November 19th, 2007, 10:02 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hi there..

In DisplayControls, if strMAC is the name of the table you want to select for, change this
Code:
Dim dsadapter As OleDbCommand = New OleDbCommand("SELECT * from strMAC")
into this
Code:
Dim dsadapter As OleDbCommand = New OleDbCommand("SELECT * from " & strMAC)
try this and tell us if you are closer of what you need....

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========
Reply With Quote
  #3 (permalink)  
Old November 19th, 2007, 10:34 AM
Authorized User
 
Join Date: Nov 2007
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to djobes31770 Send a message via Yahoo to djobes31770
Default

That worked, but now i am getting an error

in DisplayControls the following:

DataGrid1.DataSource(dsadapter) <- Property Access must assign to the property or use its value.

Reply With Quote
  #4 (permalink)  
Old November 19th, 2007, 11:18 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

yeah.. I didn't check it out .. it should be
Code:
DataGrid1.DataSource = dsadapter
I don't remember, but can you pass an adapter as a source?? or you need a table?? if you need a table use
dsadapter.tables(0)

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========
Reply With Quote
  #5 (permalink)  
Old November 19th, 2007, 11:38 AM
Authorized User
 
Join Date: Nov 2007
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to djobes31770 Send a message via Yahoo to djobes31770
Default

Thanks that got rid of the 2 errors, the problem i get now, is that is i try using the dsadapter.tables(0) it errors saying that tables is not a member. Here is the code i have so far

Code:
' load the db grid based on selected controls table, by using dynamic query

    Public Sub displayControls(ByVal strMAC As String)

        'Dim ds As New DataSet

        Dim list As DataSet = New DataSet()

        Dim cmd As New OleDbCommand("SELECT * from " & strMAC)

        Using dsControls As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=iatrack.mdb")

            dsControls.Open()

            Dim adapter As New OleDbDataAdapter(cmd)

            Dim dsadapter As OleDbCommand = New OleDbCommand("SELECT * from " & strMAC)

            DataGrid1.DataSource = dsadapter

        End Using
    End Sub
Reply With Quote
  #6 (permalink)  
Old November 19th, 2007, 11:53 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Ok.. here's a free tip.. investigate how adapter works ;)

you are doing to much job that the adapter does by itself.. you pass a conection, a query and then you fill a datatable with it, and then pass that datatable to the grid..

if should take you 4 lines of code, no more..

something like
Code:
        Dim da As New Data.OleDb.OleDbDataAdapter("SELECT * from " & strMAC, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=iatrack.mdb")
        Dim dt As DataTable
        da.Fill(dt)
        DataGrid1.DataSource = dt
        da.dispose
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========
Reply With Quote
  #7 (permalink)  
Old November 19th, 2007, 12:32 PM
Authorized User
 
Join Date: Nov 2007
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to djobes31770 Send a message via Yahoo to djobes31770
Default

Thank You, I see now, but i have one more question, in looking at the

dadapter.Fill(dtControls)

when i run it, with all the correct tables and varibles defined, i now see that i am generating a null referance, the datatable is the dynamic ref to 1 of the 9 choices in the combobox, should i move or copy the Select .... End Select to the cmbMAC_Change function. to get rid of the null ref, or is the null coming from a different section.



Reply With Quote
  #8 (permalink)  
Old November 19th, 2007, 12:41 PM
Authorized User
 
Join Date: Nov 2007
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to djobes31770 Send a message via Yahoo to djobes31770
Default

My bad, i should have called

Dim dtControls as New DataTable

this resolved problem, Thank you

now on to the others. lol, Many thanks again, this is far easier than
RealBasic.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic selection of next page based on list box s gurupot JSP Basics 1 December 24th, 2007 06:03 AM
help writing dynamic form data to dynamic table ublend Classic ASP Professional 1 June 1st, 2007 08:08 AM
Dynamic combo option selection deb1980 ASP.NET 2.0 Basics 3 April 23rd, 2007 01:26 PM
Dynamic ListBox Population n Selection nkrust ASP.NET 2.0 Basics 1 January 9th, 2007 05:05 AM
Dynamic Report By selection ... mostafa_h BOOK: Professional Crystal Reports for VS.NET 0 August 11th, 2006 07:02 AM



All times are GMT -4. The time now is 11:36 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.