Problem in displaying data in datagridview from MySQL in Vb 2010

Question

0.00/5 (No votes)

See more:

I cannot figure out whats the problem. Data is just not displaying in datagridview on form load.
It shows "connected" msgbox, after clicking OK, it dispplays form1 with datagrid but nothing on it.
here is my code:

C#

Imports MySql.Data.MySqlClient

Public Class Form1
    Dim dataAdap As MySqlDataAdapter
    Dim cmdBuild As MySqlCommandBuilder
    Public data As DataTable
    Dim conn As New MySqlConnection
    Dim connected As Boolean = False
    Sub connect()
        Dim name As String = "test"
        Dim server As String = "localhost"
        Dim user As String = "root"
        Dim pwd As String = "mysql"

        If Not conn Is Nothing Then conn.Close()
        conn.ConnectionString = String.Format("server={0}; userid={1};password={2};database={3};pooling=false", server, user, pwd, name)
        Try
            conn.Open()
            MsgBox("connected")

        Catch ex As Exception
            MsgBox("Cannot connect")
        End Try
    End Sub

    Sub search(ByVal str As String)

        Try
            If connected Then
                data = New DataTable
                dataAdap = New MySqlDataAdapter("SELECT * from testtable", conn)

                cmdBuild = New MySqlCommandBuilder(dataAdap)
                dataAdap.Fill(data)
                Me.DataGridView1.DataSource = data
            End If
        Catch ex As Exception

        End Try
    End Sub

    Sub view()

        If Me.connected Then
            Try
                data = New DataTable
                dataAdap = New MySqlDataAdapter("SELECT * from testtable", conn)
                cmdBuild = New MySqlCommandBuilder(dataAdap)
                dataAdap.Fill(data)
            Catch ex As Exception
                MsgBox("Error connecting to database")
            End Try

            Me.DataGridView1.DataSource = data


        End If
    End Sub


    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Dim connector As New Form1
        connector.connect()
        DataGridView1.DataSource = connector.data
        connector.view()
    End Sub
    
End Class

Please help asap.

Posted 21-Jul-13 20:22pm

Sagar Chaudhary

Updated 21-Jul-13 20:44pm

Thanks7872

v3

Add a Solution

Comments

[no name] 22-Jul-13 16:44pm

Why are you creating a new instance of Form1 inside Form1_Load?

idenizeni 22-Jul-13 19:38pm

In your Form1_Load event try replacing what you have with the following...

connect()
DataGridView1.DataSource = data
view()

Winston Madiano 23-Jul-13 0:34am

you didn't set the connected variable to true when you successfully opened the connection.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aydin Homay · Answer 1 · 2013-07-22T18:09:00

VB

Imports MySql.Data.MySqlClient
 
Public Class Form1
    Dim dataAdap As MySqlDataAdapter
    Dim cmdBuild As MySqlCommandBuilder
    Public data As DataTable
    Dim conn As New MySqlConnection
    Dim connected As Boolean = False
    Sub connect()
        Dim name As String = "test"
        Dim server As String = "localhost"
        Dim user As String = "root"
        Dim pwd As String = "mysql"
         conn.ConnectionString = String.Format("server={0}; userid={1};password={2};database={3};pooling=false", server, user, pwd, name)
        If conn.status = System.Data.ConnectionState.Open Then conn.Close()
        Try
            conn.Open()
            MsgBox("connected")
            connected = True 
        Catch ex As Exception
            MsgBox("Cannot connect")
        End Try
    End Sub
 
    Sub search(ByVal str As String)
 
        Try
            If connected Then
                data = New DataTable
                dataAdap = New MySqlDataAdapter("SELECT * from testtable", conn)
 
                'cmdBuild = New MySqlCommandBuilder(dataAdap) you don`t need that
                dataAdap.Fill(data)
                Me.DataGridView1.DataSource = data
            End If
        Catch ex As Exception
 
        End Try
    End Sub
 
    Sub view()
 
        If Me.connected Then
            Try
                data = New DataTable
                dataAdap = New MySqlDataAdapter("SELECT * from testtable", conn)
                'cmdBuild = New MySqlCommandBuilder(dataAdap) you don`t need that
                dataAdap.Fill(data)
            Catch ex As Exception
                MsgBox("Error connecting to database")
            End Try
 
            Me.DataGridView1.DataSource = data
        End If
    End Sub
 

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
 
        Dim connector As New Form1
        connector.connect()
        DataGridView1.DataSource = connector.data
        connector.view()
    End Sub
    
End Class

However when you work with DataAdapter you don`t need to the control connection statue because this object managed your connection status. and I suggest to you used

VB

conn.status = System.Data.ConnectionState.Open

to connected Boolean variable it is better than.

Best Regards.