CODEWITHSUNDEEP
c#mysql
Bahaa Ashi
Bahaa Ashi

Reputation: 91

How to read and print out data from mysql in c#

My problem is that I can't print out all the data from the table in my mysql database, I got out just last row in the given table "teacher". is there anyone who can help me find the error?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace ReadDataFromMysql
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string sql = " SELECT * FROM teacher  ";
            MySqlConnection con = new MySqlConnection("host=localhost;user=root;password=859694;database=projekt;");
            MySqlCommand cmd = new MySqlCommand(sql, con);

            con.Open();

           MySqlDataReader  reader = cmd.ExecuteReader();

           while (reader.Read()) {
               data2txt.Text = reader.GetString("id");
              datatxt.Text = reader.GetString("userId");
           }

        }

        private void btnclose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}

Upvotes: 9

Views: 62234

Answers (5)

Miguel
Miguel

Reputation: 26

This code works.

private void getdata()
{
    MySqlConnection connect = new MySqlConnection("SERVER=localhost; user id=root; password=; database=databasename");
    MySqlCommand cmd = new MySqlCommand("SELECT ID, name FROM data WHERE ID='" + txtid.Text + "'");
    cmd.CommandType = CommandType.Text;
    cmd.Connection = connect;
    connect.Open();
    try
    {
        MySqlDataReader dr;
        dr = cmd.ExecuteReader();
        while(dr.Read())
        {
            txtID.Text = dr.GetString("ID");
            txtname.Text = dr.GetString("name");
        }
        dr.Close();
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        if(connect.State == ConnectionState.Open)
        {
            connect.Close();
        }
    }
}

Upvotes: 1

Mike Corcoran
Mike Corcoran

Reputation: 14565

Your problem is that you are overwriting data2txt.Text and datatxt.Text on each row of data. if you want to see all of the data in those fields, something like this should do what you need:

data2txt.Text = string.Empty;
datatxt.Text = string.Empty;

while (reader.Read())
{
    data2txt.Text += $"{reader.GetString("id")};";
    datatxt.Text += $"{reader.GetString("userId")};";
}

Upvotes: 9

David D.
David D.

Reputation: 367

You should output the data before again writing in it:

data2txt.Text = reader.GetString("id");
          datatxt.Text = reader.GetString("userId");

Or use a var to store all the data in with each 'read' and then output that var

varexample.Text += reader.GetString("id");

Upvotes: 0

Brian Warshaw
Brian Warshaw

Reputation: 22984

You're assigning the value of each field instead of the value of the existing control's text plus the new value. Add a breakpoint to make sure you're getting multiple rows, but as your code is written, you would only see the result of one row in your form because you're overwriting on each iteration through the loop.

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13486

Obviously your code shows the last row values of teacher table into your text fields on form.Because your are looping throught the datareader and assigning the values to textfiled.So each iteration it will overwright the previous values in textbox.

Upvotes: 0

Related Questions