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.
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?
@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
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.
Member
46 Points
174 Posts
How to SELECT single value in gridview
Feb 16, 2019 11:18 AM|Enzyme|LINK
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"> </span> </span></span></b> <br /> </span><span class="auto-style2"> <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.
Member
749 Points
522 Posts
Re: How to SELECT single value in gridview
Feb 16, 2019 11:35 AM|jzero|LINK
Try
using (SqlCommand sqlCmd = new SqlCommand("SELECT UserID, UserName FROM Table1 WHERE ID = " + UserID , con))All-Star
158423 Points
13166 Posts
ASPInsiders
Moderator
Re: How to SELECT single value in gridview
Feb 16, 2019 01:42 PM|mbanavige|LINK
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?
Member
46 Points
174 Posts
Re: How to SELECT single value in gridview
Feb 16, 2019 02:12 PM|Enzyme|LINK
This could not fix the problem
Member
749 Points
522 Posts
Re: How to SELECT single value in gridview
Feb 16, 2019 02:24 PM|jzero|LINK
@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
Member
46 Points
174 Posts
Re: How to SELECT single value in gridview
Feb 17, 2019 11:24 PM|Enzyme|LINK
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.
All-Star
158423 Points
13166 Posts
ASPInsiders
Moderator
Re: How to SELECT single value in gridview
Feb 17, 2019 11:44 PM|mbanavige|LINK
You are adding your parameters to something named: cmd
But you are executing something named: sqlCmd