locked
i want to use table id to insert record into comment table instead of using username RRS feed

  • Question

  • User-2074858223 posted

     please what i want to achieve is this, i want to use table id to insert record into comment table instead of using username, thats all i want to achieve, i use username to display the record in page load then use userid to insert record into column comment

    So i simply want to pass the username procedure and fetch the UserId within the procedure

    this is the procedure UserPost table were i want to fetch the id and and use it to post data in table usercomment

    procedure for usercomment

    ALTER PROCEDURE [dbo].[GetUSERcomment]
    	@UserName NVARCHAR(100),
    	@FriendUserName NVARCHAR(100),
    @id int AS BEGIN SELECT u.Name,u.ImageName,u.UserName, s.UserName,s.FriendUserName,s.MyComments,s.ImageName1,s.SendDate FROM [User3] as u, UserCommentPost as s WHERE u.UserName=s.UserName AND s.FriendUserName=@UserName ORDER BY SendDate DESC END

    procedure for userpost

    ALTER PROCEDURE [dbo].[GetUSERPost]
    	@UserName NVARCHAR(100),
    	@FriendUserName NVARCHAR(100),
    @id int AS BEGIN SELECT u.Name,u.ImageName,u.UserName, s.UserName,s.FriendUserName,s.ContentPost,s.ImageName1,s.SendDate FROM [User3] as u, USERPost as s WHERE u.UserName=s.UserName AND s.FriendUserName=@UserName ORDER BY SendDate DESC END

    Friday, July 1, 2016 6:11 PM

Answers

  • User1559292362 posted

    Hi micah2012,

    So i just want users to make comment on posts and the comments will be inserted into usercomment table using table Id of Userpost Table.

    According to your description, it seems that you want to insert comment message to usercomment with related postId, If so, we could retrieve related postId via control(such as label hidden) value, please refer to the following code.

    ASPX.

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DataListWithComment.aspx.cs" Inherits="ADONETDEMO.DataList.DataListWithComment" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:DataList ID="DataList1" runat="server" DataKeyField="Id" DataSourceID="SqlDataSource1" OnItemCommand="DataList1_ItemCommand">
                <ItemTemplate>
                    Id:
                    <asp:Label ID="IdLabel" runat="server" Text='<%# Eval("Id") %>' />
                    <br />
                    UserName:
                    <asp:Label ID="UserNameLabel" runat="server" Text='<%# Eval("UserName") %>' />
                    <br />
                    FriendUserName:
                    <asp:Label ID="FriendUserNameLabel" runat="server" Text='<%# Eval("FriendUserName") %>' />
                    <br />
                    Post:
                    <asp:Label ID="PostLabel" runat="server" Text='<%# Eval("Post") %>' />
                    <br />
    <br />
                    <asp:TextBox ID="TextBox1" runat="server" TextMode="MultiLine"></asp:TextBox>
                    <br />
                    <asp:Button ID="Button1" runat="server" Text="Button" CommandName="InsertComment" />
                </ItemTemplate>
            </asp:DataList>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AspDotNetDemoConnectionString %>" SelectCommand="SELECT * FROM [Userpost]"></asp:SqlDataSource>
        </div>
        </form>
    </body>
    </html>
    

    #Code Behind

    using System;
    using System.Data.SqlClient;
    using System.Web.UI.WebControls;
    
    namespace ADONETDEMO.DataList
    {
        public partial class DataListWithComment : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
            {
                if (e.CommandName == "InsertComment")
                {
                    string comments = ((TextBox)e.Item.FindControl("TextBox1")).Text;
                    string postId = ((Label)e.Item.FindControl("IdLabel")).Text;
                    string connstr = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=AspDotNetDemo;Integrated Security=True";
                    string sqlStrig = @"insert into [dbo].[UserComent] (CommentId,Comments) values (@CommentId,@Comments)";
                    using (SqlConnection conn = new SqlConnection(connstr))
                    {
                        try
                        {
                            SqlCommand cmd = new SqlCommand(sqlStrig, conn);
                            cmd.Parameters.AddWithValue("CommentId", postId);
                            cmd.Parameters.AddWithValue("Comments", comments);
                            conn.Open();
                            cmd.ExecuteNonQuery();
                            conn.Close();
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                    }
    
    
                }
    
            }
        }
    }

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 17, 2016 3:08 AM

All replies

  • User-698989805 posted

    Hello micah2012! I assume, you want to store the UserId into the table and retrieve it as Username. If that so, it is always a good practice to save the UserId in a table if there is a relationship between tables I mean with foreign key. You just have to do a inner join to show the name. So the query would become like this:

    SELECT u.Name, u.ImageName, u.UserName, s.UserName,s.FriendUserName,s.MyComments,s.ImageName1,s.SendDate FROM User3 u 
    INNER JOIN UserCommentPost s ON s.UserId = u.Id WHERE s.FriendUserName = @UserName ORDER BY SendDate DESC

    Saturday, July 2, 2016 1:16 PM
  • User-2074858223 posted
    I want to select id which will be a foreign key then use it to insert record
    Saturday, July 2, 2016 6:21 PM
  • User-698989805 posted
    Hello micah2012! Then better you use DropDownList to do so. What it does is: It will display Username as DataTextField and UserId as DataValueField. That means it will show name and saves it as Id.
    Saturday, July 2, 2016 7:07 PM
  • User-2074858223 posted
    No the ideology is outside what am looking for, I have a datalist that has a textbox inside it, I want users to make comment with the textbox and it will be insert using the table id
    Saturday, July 2, 2016 7:21 PM
  • User475983607 posted

    The procedure would be similar to the following.  This is just a guess as I have no idea how you data is structured.

    CREATE PROCEDURE InsertComment(
    	@MyComments VARCHAR(MAX),
    	@Username	VARCHAR(128)
    )
    AS
    BEGIN
    	DECLARE @UserId INT;
    	SELECT @UserId = u.UserId FROM User3 AS u WHERE u.UserName = @Username
    
    	INSERT INTO UserCommentPost (MyComments, UserId )
    	VALUES (@MyComments, @UserId)
    END

    Saturday, July 2, 2016 8:08 PM
  • User-2074858223 posted

    from your procedure example i modified it to what i want but now the code behind is asking me parameter Id, i tried to add that but the variable still showing error

    Server Error in '/' Application.
    Procedure or function 'InsertComment' expects parameter '@Id', which was not supplied.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
    
    Exception Details: System.Data.SqlClient.SqlException: Procedure or function 'InsertComment' expects parameter '@Id', which was not supplied.
    
    Source Error:
    
    
    Line 96:         catch (Exception ex)
    Line 97:         {
    Line 98:             throw ex;
    Line 99: 
    Line 100:        }

    code behind

     protected void GetMergedAll_ItemCommand(object source, DataListCommandEventArgs e)
        {
            try
            {
                if (this.Page.User.Identity.IsAuthenticated)
            {
                   //  string username = this.Page.User.Identity.Name;
                     //Int32 id;
            
               // string username6;
                if (e.CommandName == "Insert")
                {
                    TextBox txtComments = (TextBox)e.Item.FindControl("txtComments");
                  //  TextBox txtaddress = (TextBox)e.Item.FindControl("txtsaddress");
                   // TextBox txtrollno = (TextBox)e.Item.FindControl("txtsrollno");
                    SqlCommand cmd = new SqlCommand("InsertComment", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@MyComments", txtComments.Text);
                   // cmd.Parameters.AddWithValue("@UserName", username);
                    //cmd.Parameters.AddWithValue("@FriendUserName", username);
                   cmd.Parameters.AddWithValue("@Id", id);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    cmd.Dispose();

    modified procedure to use table id to insert into usercomment table

    ALTER PROCEDURE [dbo].[InsertComment](
    	@MyComments VARCHAR(MAX),
    	@Id int,
    	@FriendUserName VARCHAR(128)
    		
    )
    AS
    BEGIN
    	DECLARE @postId INT;
    	SELECT @Id = u.Id FROM USERPost AS u WHERE u.Id = @Id 
    
    	INSERT INTO UserCommentPost (MyComments, postId )
    	VALUES (@MyComments, @postId)
    END

    please any correction?, i will appreciate, thanks for helping

    Sunday, July 3, 2016 10:03 AM
  • User475983607 posted

    Id is not defined and therefore the code does not compile.  Like your other post, the error message does not match the code.

    In the procedure you defined a variable @postId but never assign @postId a value.

    DECLARE @postId INT;
    SELECT @Id = u.Id FROM USERPost AS u WHERE u.Id = @Id 

    Moreover, if your intention is this...

    DECLARE @postId INT;
    SELECT @postId  = u.Id FROM USERPost AS u WHERE u.Id = @Id 

    then @postId equals @Id so so there is no reason to query the database. You have the Id already.  What is the purpose of this code?  Why did you change it?

    Your original requirement...

    micah2012

    So i simply want to pass the username procedure and fetch the UserId within the procedure

    I provided a sample that meets this requirement by assigning the @UserId variable using a query filtered by Username.   It seems the requirement changed and you're passing ID? I'm confused why you made this change.

    micah2012

    please any correction?, i will appreciate, thanks for helping

    I'm not sure how to help you.  As developers we use code to convey ideas and intent.  What's cool about code, C# and T-SQL in this case, is the code transcends culture and the spoken word.  However, if you can't express your ideas using code then I can't help you because I don't understand the intent.  My best advice is you need to slow down and do a much better job debugging and proofing the code.

    Sunday, July 3, 2016 10:44 AM
  • User-2074858223 posted

    Thank you for your quick response, i believe i didnt express my problem enough for you and am sorry on that

    let me put it step by step

    i have two tables, Table Userpost and Table UserComments, and the primary key is Id

    Now i have a datalist on page which shows posts made by users and connected users like friends. the datalist has a textbox for comment, a user makes a comment on that post

    and the comment will by inserted into Usercomment table using the Id of Userpost. So your code was using username to select userid tp post comments which is not what i want and i know the fault was from me because i gave you wrong description.

    So i just want users to make comment on posts and the comments will be inserted into usercomment table using table Id of Userpost Table.

    this is Userpost table

    Id     |    UserName,  |  FriendUserName,  |  Post

    --------------------------------------------------------------

    13         mic                        steve                 hello world

    ======================================

    UserComent Table

    Id         |         CommentId         |        Comments

    ----------------------------------------------------------------------------

    1                  13                                i love this post

    this is just all that am looking for to achieve, i hop my diagram explained it well, thanks for helping

    Sunday, July 3, 2016 11:04 AM
  • User475983607 posted

    Let me take the liberty of rephrasing your question to "How do I get the unique record ID from an ASP.NET DataList control?"

    Go to MSDN and look up the DataList control.  Then read the reference documentation.  

    https://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.datalist(v=vs.110).aspx

    I copied the source code found in the reference docs above and modified it slightly by adding a command button.  I assigned the unique Id to the command button's command property.  Then I created a command event handler to retrieve the ID. 

    Markup

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DatalistDemo3.aspx.cs" Inherits="TestingWeb.DatalistDemo3" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
        <h3>DataList Example</h3>
    
          <asp:DataList id="ItemsList"
               BorderColor="black"
               CellPadding="5"
               CellSpacing="5"
               RepeatDirection="Vertical"
               RepeatLayout="Table"
               RepeatColumns="3"
               runat="server">
    
             <HeaderStyle BackColor="#aaaadd">
             </HeaderStyle>
    
             <AlternatingItemStyle BackColor="Gainsboro">
             </AlternatingItemStyle>
    
             <HeaderTemplate>
    
                List of items
    
             </HeaderTemplate>
    
             <ItemTemplate>
    
                Description: <br />
                <%# DataBinder.Eval(Container.DataItem, "StringValue") %>
    
                <br />
    
                Price: <%# DataBinder.Eval(Container.DataItem, "CurrencyValue", "{0:c}") %>
    
                 <br />
                 <br />
                 <asp:Button ID="Submit" runat="server" CommandArgument='<%# DataBinder.Eval(Container.DataItem, "IntegerValue", "{0}") %>' CommandName="id" Text="Button" OnCommand="Submit_Command" />
    
             </ItemTemplate>
    
          </asp:DataList>
    
        </div>
        </form>
    </body>
    </html>
    

    Code behind

    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    namespace TestingWeb
    {
        public partial class DatalistDemo3 : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    ItemsList.DataSource = CreateDataSource();
                    ItemsList.DataBind();
                }
            }
    
    
            public ICollection CreateDataSource()
            {
    
                // Create sample data for the DataList control.
                DataTable dt = new DataTable();
                DataRow dr;
    
                // Define the columns of the table.
                dt.Columns.Add(new DataColumn("IntegerValue", typeof(Int32)));
                dt.Columns.Add(new DataColumn("StringValue", typeof(String)));
                dt.Columns.Add(new DataColumn("CurrencyValue", typeof(double)));
                dt.Columns.Add(new DataColumn("ImageValue", typeof(String)));
    
                // Populate the table with sample values.
                for (int i = 0; i < 9; i++)
                {
                    dr = dt.NewRow();
    
                    dr[0] = i;
                    dr[1] = "Description for item " + i.ToString();
                    dr[2] = 1.23 * (i + 1);
                    dr[3] = "Image" + i.ToString() + ".jpg";
    
                    dt.Rows.Add(dr);
                }
    
                DataView dv = new DataView(dt);
                return dv;
            }
    
            protected void Submit_Command(object sender, CommandEventArgs e)
            {
                string id = e.CommandArgument.ToString();
            }
        }
    
    }

    I want to point out that you have a large disconnect between what you say you want, what the code infers, and what you actually want.  The best advice I can give you is you need to take a little more time reading the reference documents and planning your code.  You should map out what you're truing to do on a piece of paper before writing any code.  then use your map to structure the code.  This will align your deign with your code.

    Sunday, July 3, 2016 11:55 AM
  • User325386820 posted

    By using "DatakeyNames" you can achieve this.. I am trying to give you some idea to achieve this..

    <asp:DataList id="dlPosts" runat="server"  DataKeyNames="UserPost_ID" OnItemCommand="dlPosts_ItemCommand">
    ----Do stuff--
    <ItemTemplate>
    <asp:Button ID="btnPost" runat="server" Text="Post Comment" CommandName="PostComment" CommandArgument='<%# Eval("UserPost_ID") %>' />
    </ItemTemplate>
    In the page load you have bind data to data list with the fields(UserPost_Id,User_Name,-----what you want)
    protected void dlPosts_ItemCommand(object source, DataListCommandEventArgs e)
        {
            if (e.CommandName.Equals("PostComment"))
            {
               int userPostId = e.CommandArgument;
               //Dostuff();
            }
        }

    You can pass "userPostID" to your stored procedure to insert in to "UserCommentTable"

    Sunday, July 3, 2016 1:37 PM
  • User1559292362 posted

    Hi micah2012,

    So i just want users to make comment on posts and the comments will be inserted into usercomment table using table Id of Userpost Table.

    According to your description, it seems that you want to insert comment message to usercomment with related postId, If so, we could retrieve related postId via control(such as label hidden) value, please refer to the following code.

    ASPX.

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DataListWithComment.aspx.cs" Inherits="ADONETDEMO.DataList.DataListWithComment" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:DataList ID="DataList1" runat="server" DataKeyField="Id" DataSourceID="SqlDataSource1" OnItemCommand="DataList1_ItemCommand">
                <ItemTemplate>
                    Id:
                    <asp:Label ID="IdLabel" runat="server" Text='<%# Eval("Id") %>' />
                    <br />
                    UserName:
                    <asp:Label ID="UserNameLabel" runat="server" Text='<%# Eval("UserName") %>' />
                    <br />
                    FriendUserName:
                    <asp:Label ID="FriendUserNameLabel" runat="server" Text='<%# Eval("FriendUserName") %>' />
                    <br />
                    Post:
                    <asp:Label ID="PostLabel" runat="server" Text='<%# Eval("Post") %>' />
                    <br />
    <br />
                    <asp:TextBox ID="TextBox1" runat="server" TextMode="MultiLine"></asp:TextBox>
                    <br />
                    <asp:Button ID="Button1" runat="server" Text="Button" CommandName="InsertComment" />
                </ItemTemplate>
            </asp:DataList>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AspDotNetDemoConnectionString %>" SelectCommand="SELECT * FROM [Userpost]"></asp:SqlDataSource>
        </div>
        </form>
    </body>
    </html>
    

    #Code Behind

    using System;
    using System.Data.SqlClient;
    using System.Web.UI.WebControls;
    
    namespace ADONETDEMO.DataList
    {
        public partial class DataListWithComment : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
            {
                if (e.CommandName == "InsertComment")
                {
                    string comments = ((TextBox)e.Item.FindControl("TextBox1")).Text;
                    string postId = ((Label)e.Item.FindControl("IdLabel")).Text;
                    string connstr = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=AspDotNetDemo;Integrated Security=True";
                    string sqlStrig = @"insert into [dbo].[UserComent] (CommentId,Comments) values (@CommentId,@Comments)";
                    using (SqlConnection conn = new SqlConnection(connstr))
                    {
                        try
                        {
                            SqlCommand cmd = new SqlCommand(sqlStrig, conn);
                            cmd.Parameters.AddWithValue("CommentId", postId);
                            cmd.Parameters.AddWithValue("Comments", comments);
                            conn.Open();
                            cmd.ExecuteNonQuery();
                            conn.Close();
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                    }
    
    
                }
    
            }
        }
    }

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 17, 2016 3:08 AM