?
Solved

VB.Net: Connect Existing SQL DB to Existing Programmatically Created DataGridView

Posted on 2009-12-16
20
Medium Priority
?
511 Views
Last Modified: 2012-05-08
Experts, please help me with code to connect an existing SQL Table to an already EXISTING, programmatically created datagridview.  The major challenge for me is how to write line of code for the datagridview when its name is based on the variable "moduletableCB.text"??

For example if you hardcode a datagridview named "dg" you can write the line like this:
dg.datasource = table    (this would work if it were my situation)

But in this case the datagridviews were programmatically created indexing off of a variable and the user is selecting the datagridview to populate based on the their selection in the combobox "moduletableCB" so I tried this line of code but it doesn't work because my syntax is out of wack:
moduletableCB.datasource = table    (this doesn't work...help!)


Details:
SQL table: name comes from variable "moduletableCB.text"
DataGridView: name comes from variable "moduletableCB.text"

The code in the snippet below is my failed attempt...it creates a NEW datagridview on the form instead of what I need which is connecting an EXISTING datagridview to the SQL Table

Dim dbadp As New SqlDataAdapter("USE " & DBName & ";SELECT * FROM " & "dbo." & moduletableCB.Text, Conn2)
        Dim dTable As New DataTable
        dbadp.Fill(dTable)
        dbadp.Dispose() 
        Dim dgv As New DataGridView
        dgv.Name = Me.moduletableCB.Text
        dgv.Location = New Point(20, 20) 
        Me.Controls.Add(dgv) 
        dgv.DataSource = dTable
        Conn2.Close()

Open in new window

0
Comment
Question by:adamchicago
  • 12
  • 8
20 Comments
 
LVL 12

Expert Comment

by:GMGenius
ID: 26065589
Hi,

have you tried

dg.datasource = moduletableCB.text  ?
0
 
LVL 1

Author Comment

by:adamchicago
ID: 26066777
GM thanks for your response...yep, I gave that a try and it just ran with no result.  I think by re-using the "dg" variable, which is dim'd "Dim as New DataGridView" it is trying to create a new datagridview rather than just repopulating the current one that is open on the screen.

Any other ideas?


Thanks again!!!
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 26067302
Can you paste here the code section please
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 1

Author Comment

by:adamchicago
ID: 26067426
Here is the latest code attempt at populating the datagridview:

FYI this is almost identical to the code snippet at the top.


Public dg as New DataGridView

'CONNECT THE DATA MODULE TABPAGE-DATAGRIDVIEWS TO THE SQL DATA MODULE TABLES        
Dim dbadp As New SqlDataAdapter("USE " & DBName & ";SELECT * FROM " & "dbo." & moduletableCB.Text, Conn2)
        Dim dTable As New DataTable
        dbadp.Fill(dTable)
        dbadp.Dispose()
        dg.Name = Me.moduletableCB.Text
        dg.Location = New Point(20, 20)
        dg.DataSource = dTable

        'CLOSE THE SQL CONNECTION
        If Conn.State = ConnectionState.Open Then
            Conn.Close()
            If Conn2.State = ConnectionState.Open Then
                Conn2.Close()
            End If
        End If

Open in new window

0
 
LVL 1

Author Comment

by:adamchicago
ID: 26067449
This is the code that is used to initially-programmatically create and populate the datagridviews.

They are generated by reading the SQL Table names from a SQL Table that houses the names in one column.

FYI I've confirmed that the SQL connection strings (not shown) for Conn and Conn2 are correct.

Also, the other variables are populating correct as well (Me.moduletableCB.text), DBName)
Dim cmdString As String = "USE " & DBName & "; SELECT NAME FROM MODULE_LIBRARY"
        Dim cmd As New SqlCommand(cmdString, Conn)
        Dim reader As SqlDataReader = cmd.ExecuteReader()
        While reader.Read()
            'CREATE TABPAGES
            Dim tb As New TabPage
            With tb
                .Name += reader("NAME").ToString()
                .Text += reader("NAME").ToString()
                .Dock = DockStyle.Fill
                .Location = New Point(0, 24)

            End With
            'CREATE DATAGRIDVIEWS
            Dim dg As New DataGridView
            With dg
                .Name += reader("NAME").ToString()
                .Location = New Point(2, 2)
                .Size = New Size(800, 500)
                .Dock = DockStyle.Fill
                .BringToFront()
                '.ColumnCount = 250
                '.RowCount = 1000
                'Me.mainTabcontrol.TabPages(0).Controls.Add(dg)
            End With

            ' ADD THE CONTROLS TO THE FORM
            Me.mainTabcontrol.Controls.Add(tb)
            tb.Controls.Add(dg)

            'CONNECT THE DATA MODULE TABPAGE-DATAGRIDVIEWS TO THE SQL DATA MODULE TABLES
            Dim dbadp As New SqlDataAdapter("USE " & DBName & ";SELECT * FROM " & reader("NAME").ToString(), Conn2)
            Dim dTable As New DataTable
            dbadp.Fill(dTable)
            dbadp.Dispose()
            dg.DataSource = dTable
        End While

        'CLOSE THE SQL CONNECTION
        If Conn.State = ConnectionState.Open Then
            Conn.Close()
            If Conn2.State = ConnectionState.Open Then
                Conn2.Close()
            End If
        End If
    End Sub

Open in new window

0
 
LVL 1

Author Comment

by:adamchicago
ID: 26067459
The 2nd code snippet runs prior to the 1st code snippet...sorry for the mis-ordering.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 26067591
Hi,

What is your error, I can see that youo are selecting all data from a specific table and you as expecting to show this in the data grid. the code looks ok to me

If you can tell me the error you get it might make more sense to me.

0
 
LVL 1

Author Comment

by:adamchicago
ID: 26067925
There is no error, it's just not populating the datagridview...it remains blank after the user clicks the update button.

Basically the user runs the 1st set of code which names and programmatically creates SQL tables and corresponding datagridviews based on the 2nd set code. (this works perfect)

After that the datagridviews are then visible on screen the user is now ready to import data.(works correctly)

The user points to where the import file is, then selects the SQL table name from the combobox that the data is to be imported into....the data successfully imports to the SQL Table (works correctly)  ....then...

...the part you've been helping me with is to update the datagridview (that they selected from the combobox) based on the data that was successfully import into the SQL Table.

Thanks again for all your help!
0
 
LVL 1

Author Comment

by:adamchicago
ID: 26067937
Sorry I got 1st and 2nd set of codes mixed up in the last post.

The 2nd set of code creates the SQL tables and datagridviews

The 1st set of code attempts to populate the datagridview from the SQL Table...the part you're helping me with.
0
 
LVL 1

Author Comment

by:adamchicago
ID: 26067947
This whole thing boils down to how to code this line:

dg.DataSource = dTable

dg is not a name of datagridview, it is a variable that was used to create the original datagridviews and now dg needs to equal the name of the datagridview and sql table that is selected in the combobox variable "me.moduletableCB.text"
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 26070448
Hi,

Your problem is referencing the correct control in code

The solution is as follows

The DataGridView is the only control on each tab so you need to reference this control when you need to update it.

So the new code will be



        Dim dbadp As New SqlClient.SqlDataAdapter("USE " & DBName & ";SELECT * FROM " & "dbo." & moduletableCB.Text, Conn2)
        Dim dTable As New DataTable
        Dim tempdg As DataGridView
        dbadp.Fill(dTable)
        dbadp.Dispose()

        tempdg = Me.mainTabcontrol.Controls.Item(Me.moduletableCB.Text).Controls.Item(0)
        tempdg.DataSource = dTable

Open in new window

0
 
LVL 12

Expert Comment

by:GMGenius
ID: 26070459
Or infact you can change the

tempdg = Me.mainTabcontrol.Controls.Item(Me.moduletableCB.Text).Controls.Item(0)

to

tempdg = Me.mainTabcontrol.TabPages.Item(Me.moduletableCB.Text).Controls.Item(0)
0
 
LVL 1

Author Comment

by:adamchicago
ID: 26072866
GMGenius, thank you so much...I think we're on the right track.

I plugged in your code and made the adjustment to the "tempdg = me.maintabcontrol..." line.

I had to add: Public maintabcontrol as tabcontrol to the code in a separate class of shared variables

I get this error at runtime: NullReferenceException was unhandled...Object reference not set to an instance of an object."

Thanks again for all of your help!!!!!!!!!!!!!!!!!!
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 26073029
Obviously i dont know all your code so i built a test application and it works for me

I dont understand why you added the declaration Public maintabcontrol as tabcontrol as your code snip showed me.mainTabcontrol.Controls.Add(tb)

so maintabcontrol is an actuall control on your form. adding the declaration has cause dhte error you are now getting because its set to NULL by the declaration

Have you changed the tab control name?
0
 
LVL 1

Author Comment

by:adamchicago
ID: 26073318
The set of code that has the original maintabcontrol is on a different form from the code that attempts to link the SQL table to the datagridview...that comes from a button click event on a separate form.

The user names and creates the original SQL Tables, tabpages and datagridviews on a large "main" form (this is where the maintabcontrol control exists).  Then the user goes goes to the "import" menu option which opens an "import" form, then in the import form is a "continue" button (and combobox moduletableCB) and that is where the code you're helping me with exists...try to connect the SQL table to the datagridviews that actually reside on "main" form.

0
 
LVL 1

Author Comment

by:adamchicago
ID: 26073322
Sorry for the confusion...you've been very patient and helpful!
0
 
LVL 12

Accepted Solution

by:
GMGenius earned 2000 total points
ID: 26073381
In that case

change the me.mainTabcontrol... to    mainformname.mainTabcontrol

where mainformname is the name of the form the mainTabcontrol resides on

That should do the trick
0
 
LVL 1

Author Comment

by:adamchicago
ID: 26073494
oh my lord...it worked!!! hallelujah!!!!! from a VB beginners perspective, this one almost killed me...I had such a hard time clearly conveying to experts what was going on, so one after another they gave up on me, but you actually came through!!  Thank you very, very, much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
0
 
LVL 1

Author Closing Comment

by:adamchicago
ID: 31666784
GMGenius, you really are a genius!  Thanks again a million times over for sticking it out with my garbled explanations and finally resolving the problem!!!
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 26073599
Your welcome.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 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