CODEWITHSUNDEEP
c#mysql
Alex
Alex

Reputation: 49

Unknown column 'count' in 'field list'

I try to make a login form in C# with MySQL database but i get this error: Unknown column 'count' in 'field list'. What can I do?

Image with error:

enter image description here

Here is my code:

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 ScoalaIT
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void pictureBox1_Click(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            string myConnection = "server=localhost;database=test;uid=root;password=123456";
            MySqlConnection connection = new MySqlConnection(myConnection);


            //string StrQuery1 = "Select count from users where user='" + txtUser.Text + "' and parola='" + txtParola.Text + "'";
            MySqlCommand myCommand = new MySqlCommand("Select count from users where user='" + txtUser.Text + "' and parola='" + txtParola.Text + "'", connection);
            //MySqlDataAdapter MyAdapter = new MySqlDataAdapter(myCommand);
            MySqlDataReader myReader;
            connection.Open();
            myReader = myCommand.ExecuteReader();
            int count = 0;
            while (myReader.Read())
            {
                count++;
            }
            if (count == 1)
            {
                MessageBox.Show("Ok");
            }
            else
            {
                MessageBox.Show("Nu ok");
            }
            connection.Close();
        }
    }
}

Thanks!!!

Upvotes: 0

Views: 1934

Answers (3)

Sudhakar Tillapudi
Sudhakar Tillapudi

Reputation: 26209

Problem : count is reserved word

Solution : you could wrap up the resereved words inside the back ticks.

Try This:

MySqlCommand myCommand = new MySqlCommand("Select `count` from users where 
 user='" + txtUser.Text + "' and parola='" + txtParola.Text + "'", connection);

Suggestion: your query is open to sql injection attacks i'd suggest to use parameterised queries to avoid them.

Note: if you want to get the total count of matching users then you should use count(*) instead of count

Try This:

MySqlCommand myCommand = new MySqlCommand("Select count(*) from users where 
   user='" + txtUser.Text + "' and parola='" + txtParola.Text + "'", connection);

Using Parameterised queries: Complete Solution

MySqlCommand myCommand = new MySqlCommand("Select count(*) from users where 
   user=@username and parola=@password", connection);
myCommand.Parameters.AddWithValue("@username",txtUser.Text);
myCommand.Parameters.AddWithValue("@pasword",txtParola.Text);
int totalCount = Convert.ToInt32(myCommand.ExecuteScalar());

        if (totalCount > 0)
        {
            MessageBox.Show("Ok");
        }
        else
        {
            MessageBox.Show("Nu ok");
        }

Upvotes: 1

n8wrl
n8wrl

Reputation: 19765

Change this...

"Select count from

to this...

"Select count(*) from

and then read this: http://en.wikipedia.org/wiki/SQL_injection becuase you're sure to suffer from it.

Upvotes: 4

John Conde
John Conde

Reputation: 219884

count is a MySQL function. This means you shouldn't name a column that. But if you do, you must wrap it in ticks:

MySqlCommand myCommand = new MySqlCommand("Select `count` from users where user='" + txtUser.Text + "' and parola='" + txtParola.Text + "'", connection);

If you're trying to get the number of rows that your WHERE caluse matches, you need to count something for this to work:

MySqlCommand myCommand = new MySqlCommand("Select count(*) from users where user='" + txtUser.Text + "' and parola='" + txtParola.Text + "'", connection);

Upvotes: 0

Related Questions