VB2010 and Mysql does not display all data from table fields

Question

0.00/5 (No votes)

See more:

I have a MySQL Database (ixdatabase) where I have several tables one of them is "masterlist". I have several textboxes which can display the data from my table BUT not all. It can only display SURNAME,GIVEN,MID, MIDDLE but for the other fields, it can't. I have double checked my database and I'm positive that I am linking my code to the correct one, also there are no whitespaces or special characters. I am quite a newbie on this, I wonder what is wrong with what I am doing?

VB

Imports MySql.Data.MySqlClient

Public Class frm_masterlist
    Dim ServerString As String = "Server=localhost;User Id=root;Password=aaaaaa;Database=ixdatabase"
    Dim SQLConnection As MySqlConnection = New MySqlConnection
    Dim cmd As MySqlCommand = New MySqlCommand
    Dim dadapter As New MySqlDataAdapter
    Dim datardr As MySqlDataReader
    Dim strsql As String

    Private Sub frm_masterlist_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        SQLConnection.ConnectionString = ServerString

        If SQLConnection.State = ConnectionState.Closed Then
            SQLConnection.Open()
            MsgBox("Connection to Database is Established")
        Else
            MsgBox("Cannot establish connection to Database")
        End If

        strsql = "SELECT * FROM ixdatabase.masterlist ORDER BY SURNAME"

        cmd.CommandText = strSql
        cmd.Connection = SQLConnection
        dadapter.SelectCommand = cmd

        datardr = cmd.ExecuteReader

        If datardr.HasRows Then
            datardr.Read()
            tb_lname.Text = datardr("SURNAME")
            tb_fname.Text = datardr("GIVEN")
            tb_mname.Text = datardr("MID")
            tb_mi.Text = datardr("MIDDLE")
            tb_app.Text = datardr("APPELLATION")
            tb_prefix.Text = datardr("PREFIX")
            tb_sex.Text = datardr("SEX")
            tb_status.Text = datardr("STATUS")
        End If

    End Sub

    Private Sub cmd_close_Click(sender As System.Object, e As System.EventArgs) Handles cmd_close.Click
        Me.Close()
        SQLConnection.Close()
    End Sub
End Class

Or perharps, you can help me improve my code.. Please help. Thanks!

Posted 29-Jun-12 20:12pm

ixthe3rd

Add a Solution

2 solutions

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)

Sandeep Mewara · Answer 1 · 2012-06-30T00:34:00

2 things:
1. If you are fetching more than one row of data, how are you planning to display it in textboxes? Current logic does not fit in for it.
2. If you are fetching more than one row of data, change your data reading part to:

VB

If reader.HasRows Then
            Do While reader.Read()
                //Console.WriteLine(reader.GetInt32(0) & vbTab & reader.GetString(1))
            Loop
        Else
            Console.WriteLine("No rows found.")
        End If

Refer:
Retrieving Data Using a DataReader (ADO.NET)[^]
DataReader Class[^]

Lastly, since you are doing a 'Select * from Table', you need to display the data in a list or datagrid kind of control.

ixthe3rd · Answer 2 · 2012-06-30T02:03:00

VB

If datardr.HasRows Then
    Do While datardr.Read()
        tb_lname.Text = datardr("SURNAME")
        tb_fname.Text = datardr("GIVEN")
        tb_mname.Text = datardr("MID")
        tb_mi.Text = datardr("MIDDLE")
        tb_app.Text = datardr("APPELLATION")
        tb_prefix.Text = datardr("PREFIX")
        tb_sex.Text = datardr("SEX")
        tb_status.Text = datardr("STATUS")
    Loop
Else
    Console.WriteLine("No rows found.")
End If

This is what I did, how would I be able to navigate records if I have FIRST,PREV,NEXT and LAST buttons? BTW, there there are still some data that are not displayed.

VB2010 and Mysql does not display all data from table fields

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0