Returning Datatable

Returning Datatable

Postby mattmate » Mon 12 Dec 2005 04:43

Hey all,

I have a class called page. Inside this class I have a function as shown below:

Code: Select all
  Function pagecontent(ByVal pageid As String)
        Dim mysqlDataTable As New MySqlDataTable
        Dim mySqlSelectCommand As New MySqlCommand
        Dim mySqlConnection As New MySqlConnection

        mySqlConnection.ConnectionString = "User ID=xxxxxx;" & _
                                 "Password=xxxxxx" & _
                                 "Host=xxxxxx;" & _
                                 "Port=xxxxx;" & _
                                 "Database=xxxxxx;" & _
                                 "Direct=true;" & _
                                 "Protocol=TCP;" & _
                                 "Compress=false;" & _
                                 "Pooling=true;" & _
                                 "Min Pool Size=0;" & _
                                 "Max Pool Size=100;" & _
                                 "Connection Lifetime=0"

        mySqlConnection.Name = "MySqlConnection"

        mySqlSelectCommand.CommandText = "SELECT * FROM tblcontent"
        mySqlSelectCommand.Connection = mySqlConnection
        mySqlSelectCommand.Name = "mySqlSelectCommand"

        mysqlDataTable.SelectCommand = mySqlSelectCommand
        mysqlDataTable.TableName = "MySqlDataTable"

        Return mysqlDataTable

    End Function

I then call this class in like this :

Code: Select all
        Dim page As New page

        DataGrid1.DataSource = page.pagecontent(1)

    End Sub

The problems I have are :
1) Autogenerate coloumns does not seem to work
2) It does not return any results even though in the database there is data

Would anyone be able to point my in the right direction? I am using Visual Studio 2003, with mysql 4. I am programming in VB.NET

Also, as one additional request if anyone has found something or knows of how to make one, I am used to using a Data access layer for MSSQL server. It is a simple access layer that I found in a book, that I use in most of my projects to allow simply access to the database. It basically consisted of passing the parameters for a stored procedure and it in turn would connect to the db and return a dataset of the results.

I have since decided to move to MYSQL for a few reasons, I need to use Version 4 of MYSQL, that does not support Sprocs, my question is does anyone have or know of a good data access layer for MYSQL 4? Even something for me to get ideas from?

Posts: 1
Joined: Mon 12 Dec 2005 04:38

Postby Serious » Mon 12 Dec 2005 09:30

You do a lot of useless work in your code.
For example, component's Name property is required only in design-time and has no practical value in runtime.
Also, there is no reason to specify connection parameters whose value equals to default (Compress, Protocol and others).

As for your question. You have to set MySqlDataTable.Active property to True as described in corresponding documentation to retrieve data from database.

Return to dotConnect for MySQL