5

I am trying to access all the records from database on a data grid view depending upon the type of user logging in through a form having 2 text-boxes for user_name and password respectively and on submit button records are displayed. But the code I have written is giving me the following error: The data types text and varchar are incompatible in the equal to operator. Please suggest changes.

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 System.Configuration;
using System.Data.SqlClient;


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

        private void Form1_Load(object sender, EventArgs e)
        {



        }

        private void button1_Click(object sender, EventArgs e)
        {

            string str = ConfigurationSettings.AppSettings["constring"].ToString();
            SqlConnection sqlcon = new SqlConnection(str);


            try
            {
                sqlcon.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }


            SqlCommand sqlcmd = new SqlCommand("select user_type from employee where user_name='" + textBox1.Text + "'and pwd= '" + textBox2.Text + "' ;", sqlcon);


            SqlDataReader myReader = sqlcmd.ExecuteReader();

            string user_type=string.Empty;

            while(myReader.Read())
            {
            user_type= myReader["user_type"].ToString();
            }

            sqlcon.Close();



            sqlcon.Open();
            SqlCommand sqlcmd2 = new SqlCommand("select * from employee where user_type= '" +user_type + "'", sqlcon);
            SqlDataReader myReader2 = sqlcmd2.ExecuteReader();

          /*  SqlDataAdapter sqladapter = new SqlDataAdapter(sqlcmd2);
            DataSet ds = new DataSet();
            sqladapter.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];*/

            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(string));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Email_ID", typeof(string));
            dt.Columns.Add("Contact", typeof(string));
            dt.Columns.Add("Address", typeof(string));

            while (myReader2.Read())
            {
                DataRow dr = dt.NewRow();
                dr["ID"] = myReader2["ID"];
                dr["Name"] = myReader2["user_name"];
                dr["Email_ID"] = myReader2["Email_ID"];
                dr["Contact"] = myReader2["Contact"];
                dr["Address"] = myReader2["Address"];
                dt.Rows.Add(dr);
            }


            dataGridView1.DataSource = dt;


            sqlcon.Close();
        }

    }
}
ankita alung
  • 327
  • 3
  • 6
  • 20

1 Answers1

11

This is a simple Database Problem.

in the Database generation script change:

columnname text NULL,

to: 

columnname varchar(number of chars) NULL,

in your case columnname would be "user_name" or "user_type"

this problem occurs, because SQL-Type text is !not compatible for comparison with strings!

as soon as you change the type from text to varchar() the operation should work

Vogel612
  • 5,620
  • 5
  • 48
  • 73
  • 1
    And of course `VARCHAR(MAX)` is available for those situations where you want to store arbitrarily large text values. – Corey Feb 28 '13 at 10:39
  • Is there a way to make linq work with such a column? Since I can't change the table right now. – MosheG Apr 14 '16 at 10:26
  • 1
    @MosheG not to my knowledge, no. The problem is that the SQL type is not actually "compatible" for use as String. As such the only fix is changing the SQL-Column's data-type – Vogel612 Apr 14 '16 at 10:31
  • It's really great, that over four years later this is still a problem with EF. I love it when the tool that is meant to abstract away knowledge of the database fails so utterly and completely at what should be a simple task. – A.R. Sep 14 '17 at 15:06