CODEWITHSUNDEEP
c#sql-servergridview
Oz Cohen
Oz Cohen

Reputation: 35

Error in gridview trying to show data of only the current user

The C# code:

string str = "Data Source=(LocalDB)\\MSSQLLocalDB;";
str += "AttachDbFilename=|DataDirectory|DinoData.mdf;";
str += "Integrated Security= True";

SqlConnection c;
c = new SqlConnection(str);

if (Show.Text == "all" || Show.Text == "All" || Show.Text == "all table" || Show.Text == "All table" || Show.Text == "All Table" || string.IsNullOrWhiteSpace(Show.Text))
{
    DataTable dt = new DataTable();

    String req;
    req = "SELECT * FROM [User] Where Username = "+Session["CurentUserid"];

    SqlDataAdapter da = new SqlDataAdapter(req, c);
    da.Fill(dt);

    datagrid.DataSource = dt;
    datagrid.DataBind();
}
else
{
    if (!string.IsNullOrWhiteSpace(Show.Text))
    {
        DataTable dt = new DataTable();

        String req;
        req = Show.Text+ " Where Username = " + Session["CurentUserid"];

        SqlDataAdapter da = new SqlDataAdapter(req, c);
        da.Fill(dt);

        datagrid.DataSource = dt;
        datagrid.DataBind();
    }
}

The error:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Invalid column name 'Niss'.

Please help, Niss is the id of one of my users

Upvotes: 0

Views: 35

Answers (1)

Steve
Steve

Reputation: 216283

This is a classical error caused by not using a parameterized query. You have forgotten to put the username value between single quotes so your Niss user has been confused as a column name

req = "SELECT * FROM [User] Where Username = @user";
SqlDataAdapter da = new SqlDataAdapter(req, c);
da.SelectCommand.Parameters.Add("@user", SqlDbType.NVarChar).Value = Session["CurentUserid"];
da.Fill(dt);

More in depth: UserName is a text column and thus if you want to search a value on this column you need to put the value between single quotes.
However fixing the problem adding a couple of quotes 

Where Username = '"+Session["CurentUserid"] + "'"

just changes the problem because if you have a user with a single quote in its user name you are facing again a syntax error. The only correct way is to use a parameterized query as I have shown.

I leave the discussion about Sql Injection and all the problems you could face in your code in the else part (where you use a whole textbox to build your query to this well known answer

Upvotes: 1

Related Questions