How to read data from the MySQL database of an application in C #?

Question

How to read data from the MySQL database of an application in C #?

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (1 votes)
#4 by (0 votes)
#5 by (0 votes)

8

I have a table with 2 columns in phpmyadmin, I can connect and read one column but the other does not, it gives the following error

An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
Additional information: Unknown column 'in' where clause '

 MySqlConnection conectar = new MySqlConnection(conex);
            MySqlCommand command = conectar.CreateCommand();
            command.CommandText = "SELECT id from hackers where Nick= "+textBox1.Text;
        try{    
        conectar.Open();
            MessageBox.Show("Conexão estabelecida!!");
        }
        catch (Exception ex)
        { MessageBox.Show(ex.Message); }
        MySqlDataReader reader = command.ExecuteReader();
        while(reader.Read())
        { label1.Text = reader["nick"].ToString(); }
        conectar.Close();
    }

When I use SELECT NICK FROM HACKERS WHERE id = x works but the opposite does not

mysql c#

asked by anonymous 07.12.2015 / 02:30

5 answers

Best solution for three types of users How to put the scrollbar inside the body?

score 2 · Answer 1

Two things.

First, the error occurs by syntax. If you go looking for strings, they have to be surrounded by single quotes.

This is wrong:

... WHERE nick = x

Because the bank considers that you want to compare the columns nick and x (and x does not exist).

That's right:

... WHERE nick = 'x'

The most serious, though, is that you assemble your SQL command through concatenation. If it's just an app to learn, it will not hurt you right now. But if you free an internet system that searches that way, it will be vulnerable to attack. Then I ask that after resolving the problem in the command syntax, do a search (it can be here in the same Stack Overflow) about SQL injection . I leave it to your discretion to research and study this in your own time.

score 1 · Answer 2

See the example below ...

public DataTable PesquisarPorNome(string NomePesquisado)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = myConnString;
        try
        {
            var SQL = string.Format("SELECT * FROM tbEspecialidades WHERE NomeEspecialidade  LIKE @NomePesquisado");

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = SQL;
            cmd.Parameters.Add("@NomePesquisado", SqlDbType.NVarChar).Value = "%" + NomePesquisado + "%";
            SqlDataAdapter sqlA = new SqlDataAdapter();
            DataTable tabela = new DataTable();

            sqlA.SelectCommand = cmd;

            conn.Open();
            sqlA.Fill(tabela);

            return tabela;
        }
        finally
        {
            conn.Close();
        }
    }

Obs: The connection made here is for sql server , just change to mysql .

score 1 · Answer 3

Switch:

command.CommandText = "SELECT id from hackers where Nick= "+textBox1.Text;

by

command.CommandText = "SELECT id from hackers where Nick like "+textBox1.Text;

Although I recommend using Entity Framework, it greatly facilitates access to the database because it converts your tables / records into classes / objects, thus facilitating access and manipulation.

score 0 · Answer 4

If you want to search for more than one information the sql statement should be

sqlCommand selectTable = new SqlCommand("SELECT * FROM nomeTabela WHERE nick = '@nick';", conexao);

and to load the data use the loop as follows.

while(reader.Read())
    { 
       label1.Text = reader["nick"].ToString();
       label2.Text = reader["2ªColuna"].ToString(); 
    }

score 0 · Answer 5

0

Try:

 command.CommandText = string.Format("SELECT id from hackers where Nick='{0}'",textBox1.Text);

05.07.2017 / 19:23