locked
How to SELECT single value in gridview RRS feed

  • 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.

    Saturday, February 16, 2019 11:18 AM

Answers

  • User-158764254 posted

    You are adding your parameters to something named: cmd

    But you are executing something named: sqlCmd

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 17, 2019 11:44 PM

All replies

  • User-943250815 posted

    Try

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

    Saturday, February 16, 2019 11:35 AM
  • 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?

    Saturday, February 16, 2019 1:42 PM
  • User-218090889 posted

    Try

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

    This could not fix the problem

    Saturday, February 16, 2019 2:12 PM
  • 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

    Saturday, February 16, 2019 2:24 PM
  • 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.

    Sunday, February 17, 2019 11:24 PM
  • User-158764254 posted

    You are adding your parameters to something named: cmd

    But you are executing something named: sqlCmd

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 17, 2019 11:44 PM