How to SELECT single value in gridview

Question

User-218090889 posted

Hi,

I have a gridview control as below

<asp:GridView ID="GridView1" runat="server" EnableModelValidation="True" AutoGenerateColumns="False" 
            OnRowDataBound="GridView1_RowDataBound"
           OnRowCommand="GridView1_RowCommand"
            CellPadding="4" ForeColor="#333333" GridLines="None" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
            <AlternatingRowStyle BackColor="White" />
            <Columns>
                <asp:TemplateField>
                    <ItemTemplate>
                        <span class="auto-style14"></span>
                        <span><b><span style="color: #2B547E"><span class="auto-style2">
                        <span class="auto-style1" style="color: #000066">User Name:</span><asp:Label ID="UserName" 
                    runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"UserName") %>' CssClass="auto-style1"></asp:Label>
                        <span class="auto-style1">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span>
                        </span></span></b>
                        <br />
                        </span><span class="auto-style2">
                        &nbsp;<asp:Button ID="btBuyLot" runat="server" BackColor="#CC0000" CommandName="TakeEarn" Font-Bold="True" ForeColor="White" Height="53px" OnClick="btBuyLot_Click" Text="Click to Claim Earning" Width="186px" CausesValidation="True" />
                        <span class="auto-style7"><span class="auto-style8">[<asp:Label ID="lblUserID" runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"UserID") %>' style="font-size: x-small"></asp:Label>
                        ]</span></span><br />
                        
                        <br />
                        </span>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
           
        </asp:GridView>

My C# for gridview databind is as below

protected void Page_Load(object sender, EventArgs e)
        {
            bind1();
        }

        public void bind1()  // Take Referrer earning
        {

            if (!IsPostBack)

                if (!object.Equals(Session["UserId"], null))
                {

                    //**Normal sql connection not of DataBase class**
                    string UserID = Session["UserId"].ToString();
                    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConn"].ToString());

                    using (SqlCommand sqlCmd = new SqlCommand("SELECT  UserID, UserName  FROM Table1 WHERE ID = UserID", con))
                    {
                        con.Open();

                        DataTable dt = new DataTable();
                        dt.Load(sqlCmd.ExecuteReader());

                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                        con.Close();
                    }
                }

        }

what I want to archive from this code is to SELECT a single value WHERE UserId = UserID, but my problem is that when I run the code, it returns all the date in the table including unwanted UserIDs.

Please how else can I code this to be able to SELECT the needed Value by theUserID.

Answer 1

User-943250815 posted

Try

using (SqlCommand sqlCmd = new SqlCommand("SELECT  UserID, UserName  FROM Table1 WHERE ID = " + UserID , con))

Answer 2

User-158764254 posted

Try

using (SqlCommand sqlCmd = new SqlCommand("SELECT  UserID, UserName  FROM Table1 WHERE ID = " + UserID , con))

That may solve the issue but it exposes you to sql injection attacks and i'd really encourage you to use parameters

https://visualstudiomagazine.com/articles/2017/07/01/parameterized-queries.aspx

Also, seems the table includes a UserID column and an ID column... Should the session UserID possibly need to be compared to the tables UserID column instead of the ID?

Answer 3

User-218090889 posted

Try

using (SqlCommand sqlCmd = new SqlCommand("SELECT  UserID, UserName  FROM Table1 WHERE ID = " + UserID , con))

This could not fix the problem

Answer 4

User-943250815 posted

@mbanavige agree

@Enzyme
The only way your original query works is if you have fields ID and UserID, so you comparing values stored on table.
But if you want to find a record base on a supplied value you have to change it to compare with a value like ID = 'the ID I want'

For a test only you can use like my sample, but as mbanavige recommended on final version better to use as parameter

Answer 5

User-218090889 posted

Also, seems the table includes a UserID column and an ID column... Should the session UserID possibly need to be compared to the tables UserID column instead of the ID?

I modified my code as below but still get the following error message

Procedure or function 'bindID' expects parameter '@Id', which was not supplied.

if (!object.Equals(Session["UserId"], null))
            {

                //**Normal sql connection not of DataBase class**

                string Id = Session["UserId"].ToString();
;
                string UserName = "Name";
                
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConn"].ToString());
                    

                    using (SqlCommand sqlCmd = new SqlCommand("[dbo].[bindID]", con))
                        
                    {
                        con.Open();

                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@Id", SqlDbType.NVarChar).Value = Id;
                        cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = UserName;
                        

                        DataTable dt = new DataTable();
                        dt.Load(sqlCmd.ExecuteReader());

                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                        con.Close();
                    }
            }

And this is my S_Proc

CREATE PROCEDURE [dbo].[bindID]  (
    @Id nvarchar(128) ,
	@UserName nvarchar(20)  
	)  
AS   
BEGIN
    SET NOCOUNT ON; 
	 
    SELECT  Id, UserName  FROM Table1 WHERE Id = @Id AND UserName  = @UserName   ;  
      
END; 

Please can someone help me check what is the issue.

Answer 6

User-158764254 posted

You are adding your parameters to something named: cmd

But you are executing something named: sqlCmd