Trying to get database entries limited to current UserName

Question

I have the database updating with the UserName of the person who uploaded a file and am trying to retrieve only the files the current user uploaded, to display in the gridview.

The page displays the current user name and when that person uploads a file everything is fine. Though when that user hits the search button, all records show up and I get the error:

Error:Invalid column name 'test'

  protected void ButtonSearch_Click(object sender, EventArgs e)
{
    GridView1.Visible = true;

    try
    {
        string UN = Session["New"].ToString();
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);


        SqlDataReader reader;
        SqlCommand command = new SqlCommand();
        command.CommandText = "SELECT * FROM UserUpload WHERE UserName = @un";
        command.Parameters.Add(new SqlParameter("@un", UN));
        command.Connection = conn;

        conn.Open();

            reader = command.ExecuteReader();

                    GridView1.DataSource = reader;
                    GridView1.DataBind();

        conn.Close();

        }

    catch (Exception ex)
    {

        LabelMessage.Text = ("Error:" + ex.Message);

    }
}

Matas Vaitkevicius · Accepted Answer · 2014-05-23T16:46:28.720

2

Change this line

string UserSearch = "SELECT * FROM UserUpload WHERE UserName =" + UN;

to

string UserSearch = string.Format("SELECT * FROM UserUpload WHERE UserName ='{0}'",UN);

you want to match to username as string strings are being wrapped in '' in SQL
If you would be matching by number it would work fine as numbers do not have this requirement.

UPDATE to UPDATE: Change to something like this (untested)

SqlCommand com = new SqlCommand(UserSearch, conn);

    {   DataSet ds = com.ExecuteReader();
        if (ds.Tables.Count > 0)
        {
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
            conn.Close();
    }

You would benefit from reading this

edited May 23 '14 at 16:46

answered May 23 '14 at 15:07

Matas Vaitkevicius

58,075
31
238
265

Thank you, that corrects the error, though the gridview is still showing all records, regardless of username – JLM May 23 '14 at 15:09
I changed the code to what you suggested, though the gridview still shows all entries – JLM May 23 '14 at 15:16
you need to databind data you read from database. http://stackoverflow.com/questions/6358799/asp-net-programatically-bind-dataset-to-gridview – Matas Vaitkevicius May 23 '14 at 15:20
Updated the original post, no errors, but gridview isn't showing up – JLM May 23 '14 at 16:36

score 2 · Answer 2 · answered May 23 '14 at 15:11

Use Parameters instead of assinging the Value to the query string

    protected void ButtonSearch_Click(object sender, EventArgs e)
    {
        GridView1.Visible = true;
        try
        {
            string UN = Session["New"].ToString(); ;
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            conn.Open();
            string UserSearch = "SELECT * FROM UserUpload WHERE UserName = @un";
            SqlCommand com = new SqlCommand(UserSearch, conn);
            com.Parameters.Add(new SqlParameter("@un", UN));

            com.ExecuteNonQuery();
            conn.Close();
        }
        catch (Exception ex)
        {

            LabelMessage.Text = ("Error:" + ex.Message);

        }
    }

After changing my code to this, the gridview still shows all entries. — JLM, May 23 '14 at 15:19
Then try this Statement on with a database tool e.g. SQL Management Studio and have a look at the results. Maybe something is wrong with your query. — NCC-1701-M, May 23 '14 at 15:37

Trying to get database entries limited to current UserName

2 Answers2