I have a procedure below that joins the User table accounts with userposts table, but now i want to include another table to it.
Here is the example, User3 table Holds User profiles, while USERPost table holds user posts. Now there is another table called
Dim_favorite Table that holds users favorites. So how it works is this, on the Default page, all the books are deplayed in datalist,
the image, the name and a button add favorite. So when the user clicks add favorite button it inserts the UserName of the user and the
Username of the book into Dim favorite table then the add button will change to added.
But on the GetUserPosts procedure that merges the tables the Dem favorite table will be join to the procedure so that on the Default
Page deplay the Added button will appear along side of the Book image.
I got this example from this page but now i want to expand it futher.
procedure
ALTER PROC [dbo].[GetUserPOSTS]
@UserName VARCHAR (200)
AS
BEGIN
SELECT u.Name,u.ImageName,u.UserName,
up.Id,up.UserName,up.BookUserName,up.fName,up.Content,up.ImageName1,up.SendDate,up.TotalCount,
up.Path
FROM USERPost up
INNER JOIN User3 u ON u.UserName = up.USERNAME
WHERE up.USERNAME IN (
SELECT uf.UserName FROM Status uf
//Status table here
)
ORDER BY up.Id DESC
END
http://www.aspforums.net/Threads/851264/Checking-Added-favrite-books-on-item-page-not-showing-Added-button/
Default page html
<form id="form1" runat="server">
<div>
<asp:DataList ID="dlBooks" runat="server" OnItemDataBound="dlBooks_ItemDataBound">
<HeaderTemplate>
<table class="table" border="1">
<tr>
<td>
<asp:Label Text="Book Pics" runat="server" />
</td>
<td>
<asp:Label ID="Label1" Text="BookName" runat="server" />
</td>
<td>
<asp:Label ID="Label2" Text="Favorite Book" runat="server" />
</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td>
<asp:Label ID="lblId" Text='<%#Eval("Id") %>' Visible="false" runat="server" />
<asp:Image ID="Imagebooks" ImageUrl='<%# "Images/" + Eval("Name") %>' Width="80px"
Height="50px" runat="server" />
</td>
<td>
<asp:Label ID="lblbookName" Text='<%#Eval("Name")%>' runat="server" />
</td>
<td>
<asp:Button ID="btnAdd" Text="Add" OnClick="OnfavoriteBook" runat="server" />
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:DataList>
</div>
<br />
<br />
<asp:LinkButton Text="Logout" OnClick="OnLogOut" runat="server" />
</form>
===========================================
Code
--------------------------------
private string constring = ConfigurationManager.ConnectionStrings["constring"].ToString();
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
if (!this.IsPostBack)
{
username = this.Page.User.Identity.Name;
// DataTable dt = GetProfile();
GetMergedAll.DataSource = dt;
ViewState["DataTable"] = dt;
GetMergedAll.DataSource = dt;
GetMergedAll.DataBind();
// if (dt.Rows.Count > 0)
{
}
}
}
this.GetData();
}
private void GetData()
{
string str = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
string GetData = "GetUserPOSTS";
using (SqlConnection con = new SqlConnection(str))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(GetData, con))
{
cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@Name", Request.QueryString["Id"].ToString());
cmd.Parameters.AddWithValue("@UserName", Session["userName"]);
// cmd.Parameters.AddWithValue("@Id", Id);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
ViewState["DataTable"] = dt;
GetMergedAll.DataSource = dt;
GetMergedAll.DataBind();
}
}
}
//So here i want to add the code that will enable users add favorite which will be selected by GetUserPOSTS procedure after merging
protected void OnfavoriteBook(object sender, EventArgs e)
{
DataListItem item = (sender as Button).NamingContainer as DataListItem;
int bookuserid = Convert.ToInt32((item.FindControl("lblId") as Label).Text);
using (SqlConnection con = new SqlConnection(constring))
{
if ((item.FindControl("btnAdd") as Button).Text.ToUpper() == "ADD")
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Dim_favorite values(@userName,@BookuserName)", con))
{
cmd.Parameters.AddWithValue("@UserId", Session["userName"]);
cmd.Parameters.AddWithValue("@BookuserName", bookuserid);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
this.Populatebooks();
}
