Dynamic Table Selection

djobes31770 · November 19th, 2007, 09:43 AM

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.

gbianchi · November 19th, 2007, 10:02 AM

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

djobes31770 · November 19th, 2007, 10:34 AM

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.

gbianchi · November 19th, 2007, 11:18 AM

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

djobes31770 · November 19th, 2007, 11:38 AM

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

gbianchi · November 19th, 2007, 11:53 AM

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

djobes31770 · November 19th, 2007, 12:32 PM

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.

djobes31770 · November 19th, 2007, 12:41 PM

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.