SqlDataReader Read method not working

Question

I am trying to retrieve column values from database here is my code

protected void LoadProfile()
{
        conn = new SqlConnection(connString);

        cmdProfile = new SqlCommand("SELECT Name, Father, Gender, UserName, City, Country, Department, Year, Degree, JobTittle, Organization, JobCity, JobCountry, JobTittle FROM UserProfile WHERE UserName=@UserName", conn);

        cmdProfile.Parameters.AddWithValue("@UserName", userName);

        conn.Open();

        reader = cmdProfile.ExecuteReader();

        if (reader.Read())
        {
            labelName.Text = reader["Name"].ToString();
            txtBoxFather.Text = reader["Father"].ToString();
            TextBoxGender.Text = reader["Gender"].ToString();
            TextBoxAge.Text = "";
            TextBoxCity.Text = reader["City"].ToString();
            TextBoxCountry.Text = reader["Country"].ToString();
            TextBoxDepartment.Text = reader["Department"].ToString();
            TextBoxDegree.Text = reader["Degree"].ToString();
            TextBoxYear.Text = reader["Year"].ToString();
            TextBoxJobTittle.Text = reader["JobTittle"].ToString();
            TextBoxJobCity.Text = reader["JobCity"].ToString();
            TextBoxJobCountry.Text = reader["JobCountry"].ToString();
            TextBoxOrganization.Text = reader["Organization"].ToString();
        }

        conn.Close();
    }

But it is not retrieving anything from database.

Actually I am taking userName parameter as query string from different page using this line

 userName = Request.QueryString["Name"].ToString();

When I put break points the control is not going forward after this line

if (reader.Read())

This is the page from where I am taking query string. .

<asp:GridView ID="GridAllAlumni" runat="server" 
        onitemcommand="GridAllAlumni_ItemCommand">
   <Columns>
      <asp:TemplateField>
         <ItemTemplate>
            <asp:LinkButton ID="lnkname" runat="server"
                            Text='<%#Eval("Name") %>'
                            PostBackUrl='<%#"~/Profile/Profile.aspx?Name="+Eval("Name") %>'/>
         </ItemTemplate>
      </asp:TemplateField>
   </Columns>
</asp:GridView>

Where am I getting wrong?

Your help will be appreciated. Thanx

Does the query work if you execute it directly at the DB server? is this possibly simply because the query returns zero rows? perhaps due to case sensitivity on the `UserName=@UserName` check? Is `userName` what you *expect* it to be? — Marc Gravell, Jun 24 '13 at 19:38
btw, it won't *stop it working*, but it would be a really good idea to a: separate the UI and data access, and b: use `using` aggressively (all of `conn`, `cmdProfile` and `reader` are `IDisposable`, and should make use of `using` blocks) — Marc Gravell, Jun 24 '13 at 19:39
@Jonesy: I checked in sql server there are rows with that userName but it's not showing — user2517610, Jun 24 '13 at 19:39
@user2517610 have you checked that `userName` is what you *expected* it to be, and that it matches the data in the DB? — Marc Gravell, Jun 24 '13 at 19:40
well, are you sure the userName variable is the same as the one you supplied in sql server when it gets added? — Jonesopolis, Jun 24 '13 at 19:40
Actually i am trying to retrieve the profile of user from gridview on diffrent page after clicking on the link in gridview. . .it works properly for 1 record but not working for other records in the same gridview — user2517610, Jun 24 '13 at 19:42
@MarcGravell: Yes it matches the data in the DB. . it works fine for only one record but for others it shows nothing. . same gridview same query but not the same result :( — user2517610, Jun 24 '13 at 19:46
are you updating the right textboxes? Try adding an ORDER BY in the query then seeing what the one textbox that is working displays. does it display the last record? — Jonesopolis, Jun 24 '13 at 19:47
@Jonesy: Just trying to retrive fields in textbox from database — user2517610, Jun 24 '13 at 19:49

score 1 · Answer 1 · answered Jun 24 '13 at 19:45

1

It is while(reader.Read()) It is not looping.

answered Jun 24 '13 at 19:45

iefpw

6,816
15
55
79

score 0 · Answer 2 · answered Jul 02 '13 at 19:49

Try this. I basically changed @UserName to :UserName in the query and removed the @ in the cmdProfile.Parameters.AddWithValue("@UserName", userName);. It worked for me. But, I may be wrong. So, feel free to correct me.

protected void LoadProfile()
{
        conn = new SqlConnection(connString);

        cmdProfile = new SqlCommand("SELECT Name, Father, Gender, UserName, City, Country, Department, Year, Degree, JobTittle, Organization, JobCity, JobCountry, JobTittle FROM UserProfile WHERE UserName=:UserName", conn);

        cmdProfile.Parameters.AddWithValue("UserName", userName);

        conn.Open();

        reader = cmdProfile.ExecuteReader();

        if (reader.Read())
        {
            labelName.Text = reader["Name"].ToString();
            txtBoxFather.Text = reader["Father"].ToString();
            TextBoxGender.Text = reader["Gender"].ToString();
            TextBoxAge.Text = "";
            TextBoxCity.Text = reader["City"].ToString();
            TextBoxCountry.Text = reader["Country"].ToString();
            TextBoxDepartment.Text = reader["Department"].ToString();
            TextBoxDegree.Text = reader["Degree"].ToString();
            TextBoxYear.Text = reader["Year"].ToString();
            TextBoxJobTittle.Text = reader["JobTittle"].ToString();
            TextBoxJobCity.Text = reader["JobCity"].ToString();
            TextBoxJobCountry.Text = reader["JobCountry"].ToString();
            TextBoxOrganization.Text = reader["Organization"].ToString();
        }

        conn.Close();
    }

SqlDataReader Read method not working

2 Answers2