DGV with multiple Tables

Raabi Anony

Hi everybody I had been trying to code a DatagridView with multiple tables, without much success, until I found the following code; which seems fulfilling my aspirations:

Private Function GetDataSource() As DataTable
Const sqlSelect As String = "SELECT a.Col1 AS aCol1, a.Col2 AS aCol2, b.Col1 AS bCol1, b.Col2 AS bCol2 " & _
"FROM dbo.TableA AS a
INNER JOIN dbo.TableB AS b ON a.IdCol = b.aIdCol " & _
"ORDER BY aCol1 ASC, bCol1 ASC"
Try
Dim table = New DataTable()
Using con = New MySqlConnection(My.Settings.MySqlConnectionString)
con.Open()
Using da = New MySqlDataAdapter(sqlSelect, con)
da.Fill(table)
Return table
End Using
End Using
Catch ex As Exception
' log message instead '
Throw ' don't use throw new Exception or throw ex '
End Try
End Function
me.DGV1.DataSource = GetDataSource()

But, unfortunately, it gives a couple of the following errors: MySqlConnection is not defined MySqlDataAdapter is not defined Looking forward for some explanation and remedy for these errors. Please help!

Mycroft Holmes

The code and your title have nothing to do with each other. Declaring a const within a method is just wrong, it should be a class level constant! Having declared it you do not use it instead you use My.Settings.MySqlConnectionString which does not exist. MySqlDataAdapter is also not declared in the method (it is probably declared somewhere else in the source you copied from). You are running across the fundamental problem with cut and paste learning. You are trying to use some code you found with no understanding what it does. You will now get piecemeal explanations which you do not understand (above is an example). Get a book, READ it and work through the examples, when you have a basic understanding come back and we can be of more use to you.

Never underestimate the power of human stupidity RAH

Richard Deeming

Mycroft Holmes wrote:

Declaring a const within a method is just wrong, it should be a class level constant!

There's nothing wrong with declaring a Const in a method if it's only used within that method. For SQL queries, it's even a good idea, because it prevents you from concatenating user input into the query, and forces you to use parameters. :)

Mycroft Holmes wrote:

Having declared it you do not use it instead you use My.Settings.MySqlConnectionString which does not exist.

You're looking at the wrong line - it's used on the line immediately below that:

Using da = New MySqlDataAdapter(sqlSelect, con)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Richard Deeming

What DBMS are you using? Your query looks like a Microsoft SQL Server query, but you're trying to use the MySQL classes to execute it. If your database is MS SQL Server, use the classes from the System.Data.SqlClient namespace:

Using con = New SqlConnection(My.Settings.SqlServerConnectionString)
Using da = New SqlDataAdapter(sqlSelect, con)
da.Fill(table)
Return table
End Using
End Using

If your database is MySQL, you'll need to install Connector/Net[^], add a reference to it from your project, and add Imports MySql.Data.MySqlClient at the top of your code file. NB: The *DataAdapter classes will open and close the connection for you. There's no need to call con.Open() before you call da.Fill(...).

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Mycroft Holmes

Constant - I learn something every now and then, makes it worth hanging around. I also did not know that about the dataadaptor, I explicitly open and close the connection but then I have not looked at the mechanics of DB comms for ages.

Never underestimate the power of human stupidity RAH

Raabi Anony

Thanks a lot for the responses and sorry for the late acknowledgment. Let me try your suggestions and may get back to you geeks for further help, if necessary. Actually, I am using Microsoft SQL Server. I have fair knowledge of programming in VB.NET, in general, but not in databases. Would anyone suggest the necessary correction, in view of using MS SQL Server, because this piece of code can serve my purpose, at the moment. Have a good time.

Raabi Anony

Sorry for another show up with hope. I am still stuck with the

Dim conxnString As String = My.MySettings.sqlConnectionString

Or

Dim conxnString As String = My.Settings.sqlConnectionString

VisualStudio 2015 says "sqlConnectionString is not a member of MySettings" Do I need to Import some Library, other than System.Data.SqlClient ? My whole code is as below:

Private Function cboCampuses_SelectedValueChanged(sender As Object, e As EventArgs) Handles cboCampuses.SelectedValueChanged
Dim selectedValue As String
selectedValue = cboCampuses.SelectedValue
Me.StaffEvaluationDGV.DataSource = GetDataSource(selectedValue)
End Function

Private Function GetDataSource(selectedValue) As DataTable
    Dim sqlSelect As String = "SELECT \* FROM tblEvaln " &
       "WHERE CampusName = " & selectedValue

    Dim SqlConnection As SqlConnection
    Dim conxnString As String = My.MySettings.sqlConnectionString
    Try
        Dim table = New DataTable()
        Using con = New System.Data.SqlClient.SqlConnection(conxnString)
            con.Open()
            Using da = New System.Data.SqlClient.SqlDataAdapter(sqlSelect, con)
                da.Fill(table)
                Return table
            End Using
        End Using
    Catch ex As Exception
        ' Error message here
    End Try
End Function

Would anybody help me please!

Richard Deeming

Use My.Settings.MySqlConnectionString, assuming the value is a valid SQL Server connection string[^]. I just changed the name to highlight the fact that it's not a MySQL connection string. :)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer