0

i have crm application. i have one difficulties that how i bind repeater control as message threading. like first thread as question and second thread as answer of that question. if user asked multiple question then first,second,.. threads as question and.as it is like message chatting...

for keeping data from database i use this stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ViewMessageThreads] (@inquiry_id varchar(50))
AS
BEGIN
    SET NOCOUNT ON;
    select
    i.body as master_body,
    h.body as history_body,
    q.body as question_body,
    q.Created_date as question_timestamp, 
    a.body as answer_body,
    a.Created_date as answer_timestamp,
    t.Type_name as user_type
from tbl_Inquiry_History i
left join tbl_Inquiry_master h on h.Inquiry_id=i.Inquiry_id
left join tbl_Question q on q.Inquiry_id=i.Inquiry_id
left join tbl_Answer a on a.Question_id=q.Inquiry_id
left join tbl_User_master u on u.Id=i.User_id
left join tbl_Login_master l on l.Id=u.User_id
left join tbl_Type t on t.Id = l.type_id
where (i.Inquiry_id=@inquiry_id)
END

and this gives me result as:

 master_body history_body question_body question_t..  answer_body answer_t.. user_type
__________________________________________________________________________________________
 question 1   NULL       question 1   2005-03-14...      NULL    NULL       User
 question 1   NULL       question 2   2005-03-14...      NULL    NULL       User

and i include this design source of repeater:

<asp:Repeater ID="Repeater_Inquiry_Messages" runat="server">
                            <ItemTemplate>
                             <table id="ctl00_ContentPlaceHolder1_dl_ticketmsg" cellspacing="0" border="0" style="width:100%;border-collapse:collapse;">
    <tbody><tr>
        <td style="background-color:#F5F5FF;">
                                    <table cellpadding="0" cellspacing="0" border="0">
                                        <tbody><tr>
                                            <td class="header">
                                                <span id="ctl00_ContentPlaceHolder1_dl_ticketmsg_ctl00_lbl_msg_no"><%#Container.ItemIndex+1 %></span></td>
                                            <td class="normaltext" valign="bottom">
                                                <span id="ctl00_ContentPlaceHolder1_dl_ticketmsg_ctl00_lbl_tagline">Message By <b><asp:Label ID="lbl_user_t" runat="server" Text='<%#Eval("user_type")%>'/></b> on <asp:Label ID="lbldatetime" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "question_timestamp","{0:ddd, dd MMMM yyyy}")%>'/></span></td>
                                        </tr>
                                        <tr>
                                            <td class="header">
                                                &nbsp;</td>
                                            <td class="normaltext" valign="bottom">
                                                <b>Message :</b><br>
                                                <span id="ctl00_ContentPlaceHolder1_dl_ticketmsg_ctl00_Label1"><asp:Label ID="lbl_inquiry_desc" runat="server" Text='<%#Eval("question_body")%>'/></span></td>
                                        </tr>
                                    </tbody></table>
                                </td>
    </tr>
</tbody></table>
                            </ItemTemplate>
                           <SeparatorTemplate>
                            <table>
                            <tr>
                            <td style="height:3px"></td>
                            </tr>
                            </table>
                            </SeparatorTemplate>
                            <ItemTemplate>
                            <table id="ctl00_ContentPlaceHolder1_dl_ticketmsg1" cellspacing="0" border="0" style="width:100%;border-collapse:collapse;">
    <tbody><tr>
        <td style="background-color:#F5F5FF;">
                                    <table cellpadding="0" cellspacing="0" border="0">
                                        <tbody><tr>
                                            <td class="header">
                                                <span id="ctl00_ContentPlaceHolder1_dl_ticketmsg1_ctl00_lbl_msg_no"><%#Container.ItemIndex+1 %></span></td>
                                            <td class="normaltext" valign="bottom">
                                                <span id="ctl00_ContentPlaceHolder1_dl_ticketmsg1_ctl00_lbl_tagline">Message By <b><asp:Label ID="Label1" runat="server" Text='<%#Eval("user_type")%>'/></b> on <asp:Label ID="Label2" runat="server"  Text='<%# DataBinder.Eval(Container.DataItem, "answer_timestamp","{0:ddd, dd MMMM yyyy}")%>'/></span></td>
                                        </tr>
                                        <tr>
                                            <td class="header">
                                                &nbsp;</td>
                                            <td class="normaltext" valign="bottom">
                                                <b>Message :</b><br>
                                                <span id="ctl00_ContentPlaceHolder1_dl_ticketmsg1_ctl00_Label1"><asp:Label ID="Label3" runat="server" Text='<%#Eval("answer_body")%>'/></span></td>
                                        </tr>
                                        <tr>
                                            <td class="header">
                                                &nbsp;</td>
                                            <td class="normaltext" valign="bottom">
                                                <b></b>
                                               </td>
                                      </tr>
                                    </tbody></table>
                                </td>
    </tr>
</tbody></table>
                            </ItemTemplate>
                            </asp:Repeater>

how ever this gives me only question thread while i commenting up this second message thread. ----------------------------------------Updated--------------------------------------- enter image description here please help me..

---------------------------------------Updated----------------------------------------

Server Error in '/OmInvestmentStockMarketing_new' Application.

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. 

Compiler Error Message: CS1026: ) expected

Source Error:


Line 162:                                                <span id="ctl00_ContentPlaceHolder1_dl_ticketmsg_ctl00_lbl_msg_no"><%#Container.ItemIndex+1 %></span></td>
Line 163:                                            <td class="normaltext" valign="bottom">
Line 164:                                                <span id="ctl00_ContentPlaceHolder1_dl_ticketmsg_ctl00_lbl_tagline">Message By <b><asp:Label ID="lbl_user_t" runat="server" Text='<%# If(Eval("cargo2").ToString() Is "Admin", "You", Eval("cargo2"))%>'/></b> 
Line 165:                                                on <asp:Label ID="lbldatetime" runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"cargo1","{0:ddd, dd MMMM yyyy}")%>'/></span></td>
Line 166:                                        </tr>

Source File: c:\Documents and Settings\Vishal\My Documents\Visual Studio 2005\WebSites\OmInvestmentStockMarketing_new\Admin\OWM_Inquiry.aspx    Line: 164 


Show Detailed Compiler Output:

Show Complete Compilation Source:


Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053

---------------------------Updated-------------------------------------------

<asp:Repeater ID="Repeater_Inquiry_Messages" runat="server">
                            <ItemTemplate>
                             <div style='display: <%# Container.ItemIndex == 0 ? "none" : "block"  %>'>
                             <table id="ctl00_ContentPlaceHolder1_dl_ticketmsg" cellspacing="0" border="0" style="width:100%;border-collapse:collapse;">
                             <tbody><tr>
        <td style="background-color:#F5F5FF;">
           <table cellpadding="0" cellspacing="0" border="0">
                                        <tbody><tr>
                                            <td class="header">
                                                <span id="ctl00_ContentPlaceHolder1_dl_ticketmsg_ctl00_lbl_msg_no"><%--<%#Container.ItemIndex+1 %>--%><%# Container.ItemIndex.ToString() == "0" ? int.Parse("1") : Container.ItemIndex %></span></td>
                                            <td class="normaltext" valign="bottom">
                                                <span id="ctl00_ContentPlaceHolder1_dl_ticketmsg_ctl00_lbl_tagline">Message By <b><asp:Label ID="lbl_user_t" runat="server" Text='<%# Eval("cargo2").ToString()=="Admin" ? "You" : Eval("cargo2") %>'></asp:Label></b> 
                                                on <asp:Label ID="lbldatetime" runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"cargo1","{0:ddd, dd MMMM yyyy}")%>'/></span></td>
                                        </tr>
                                        <tr>
                                            <td class="header">
                                                &nbsp;</td>
                                            <td class="normaltext" valign="bottom">
                                                <b>Message :</b><br>
                                                <span id="ctl00_ContentPlaceHolder1_dl_ticketmsg_ctl00_Label1"><asp:Label ID="lbl_inquiry_desc" runat="server" Text='<%#Eval("body")%>'/></span></td>
                                        </tr>
                                         <tr>
                                            <td class="header">
                                                &nbsp;</td>
                                            <td class="normaltext" valign="bottom">
                                               </td>

                                               </tr>
                                               </tbody></table>
        </td>
    </tr>
</tbody></table>
                            </div>
                            </ItemTemplate>
                           <SeparatorTemplate>
                           <table>
                            <tr>
                                <td style="background-color:White;width:5px;">
                                    <span id="Label2"></span>
                                </td></tr>
                                </table>
                            </SeparatorTemplate>
                            </asp:Repeater>
Shalin Gajjar
  • 123
  • 4
  • 18
  • Pretty sure you will need to deal with everything in the same ItemTemplate (by working out what is in each row of the result set and displaying the content for each - that's what I meant in the earlier question when I said "by suppressing repeated text") – dav1dsm1th Oct 25 '13 at 16:48
  • then what we do to binds up question to answer association repeater. – Shalin Gajjar Oct 25 '13 at 17:10
  • It may be easier to COALESCE the inquiry, history, question and answers to make your front end code simpler. But, because you are dealing with a hierarchy, the code has to be complicated somewhere. This is difficult to explain without having your data available. – dav1dsm1th Oct 25 '13 at 17:17
  • for which kind of data u want.... – Shalin Gajjar Oct 25 '13 at 17:18
  • give me a few minutes and I'll try to put together an answer that makes sense. – dav1dsm1th Oct 25 '13 at 17:23
  • 1
    have you swapped around tbl_Inquiry_History and tbl_Inquiry_master in you SP? the ALIASs appear to be transposed - as you are pulling from history and calling it master_body and pulling from master and calling it history_body. – dav1dsm1th Oct 25 '13 at 17:47
  • no, i think i will use tbl_Inquiry_History and tbl_Question same time to inserting new inquiry that was raised by user. and store appropriate answers that was giving by staff/admin row created in tbl_Inquiry_master and tbl_Answer. – Shalin Gajjar Oct 25 '13 at 18:06
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/40031/discussion-between-shalin-gajjar-and-dav1dsm1th) – Shalin Gajjar Oct 26 '13 at 08:56

1 Answers1

1

I'm going to go back to my schema (related question) - purely because it's quicker for me than trying to reproduce yours with data, etc. but hopefully this will help (You'll just have to map it back onto your structures). This answer may get heavily down voted - as it does not answer the question posed - but suggests a way to avoid the complex binding that would be required (having now seen how the output of the stored procedure is going to be used).

If your schema was like this:-

create table tbl_Inquiry_master (
    id int,
    body varchar(1024)
);
create table tbl_Inquiry_history (
    id int,
    inquiry_id int,
    body varchar(1024)
);
create table tbl_question (
    id int,
    inquiry_id int,
    body varchar(1024)
);
create table tbl_answer (
    id int,
    question_id int,
    body varchar(1024)
);
insert into tbl_Inquiry_master values (1, 'inquiry one');
insert into tbl_Inquiry_master values (2, 'inquiry two');
insert into tbl_Inquiry_master values (3, 'inquiry three');
insert into tbl_Inquiry_master values (4, 'inquiry four');
insert into tbl_Inquiry_history values (1, 1,'history 1 (relates to inquiry 1)');
insert into tbl_Inquiry_history values (2, 3,'history 2 (relates to inquiry 3)');
insert into tbl_question values (1,1,'inquiry one question one')
insert into tbl_question values (2,1,'inquiry one question two')
insert into tbl_question values (3,2,'inquiry two question one')
insert into tbl_question values (4,4,'inquiry four question one')
insert into tbl_answer values (1,1,'answer 1 to question 1')
insert into tbl_answer values (2,1,'answer 2 to question 1')
insert into tbl_answer values (3,2,'answer 1 to question 2')
insert into tbl_answer values (4,4,'answer 1 to question 4')
insert into tbl_answer values (5,4,'answer 2 to question 4')
insert into tbl_answer values (6,4,'answer 3 to question 4')

You could have a stored procedure like this:-

create procedure [dbo].[ViewMessageThreads] (
    @inquiry_id int)
AS
BEGIN

    create table #return (
        FromTable varchar(10),
        sort1 int,
        sort2 int,
        sort3 int,
        body varchar(1024),
        cargo1 datetime,
        cargo2 varchar(50)
    )

    insert into #return
    select 'master', 1, 0, 0, m.body , null, null
    from tbl_Inquiry_master m
    where m.id=@inquiry_id

    insert into #return
    select 'history', 2, 0, 0, h.body , null, null
    from tbl_Inquiry_history h
    where h.inquiry_id=@inquiry_id

    insert into #return
    select 'question', 3, q.id, 0, q.body , GETDATE(), 'user'
    from tbl_question q
    where q.inquiry_id=@inquiry_id

    insert into #return
    select 'answer', 3, q.id, a.id, a.body , GETDATE(), 'staff'
    from tbl_answer a
    join tbl_question q on q.id=a.question_id
    where q.inquiry_id=@inquiry_id

    /*
    -- uncomment this block to update the type in the way you appear
    -- to want it set,  I'm leaving it commented in my answer as I
    -- cannot confirm the logic works - as I don't have your schema/data
    -- and the code is, therefore, untested.

    -- this logic could be moved to the top of the SP and the variable could be 
    -- substituted into each SELECT, so that the UPDATE would then not be required

    declare @Type_name = @varchar(50) -- I don't know the dimensions of this type

    select top 1 @Type_name=t.Type_name 
    from tbl_Type t 
    join tbl_Login_master l on l.Id=t.Id 
    join tbl_User_master u on u.Id=l.Id 
    join tbl_Inquiry_History h on h.User_id=u.Id 
    where (h.Inquiry_id=@inquiry_id))     

    update #return set cargo2=@Type_name

    */

    select *
    from #return
    order by sort1, sort2, sort3

end

and calling it like:-

exec [dbo].[ViewMessageThreads] 1
exec [dbo].[ViewMessageThreads] 2
exec [dbo].[ViewMessageThreads] 3
exec [dbo].[ViewMessageThreads] 4

which would produce:-

out from call to SP which would produce output that should be easier for you to bind onto, rather than that output by the SP in your question.

You can use the values in the fromtable and sort columns to make decisions in your repeater about how to format the other columns.

You'll have to play around with populating the cargo slots (you may need more) and the sort order - as I'm not sure where/whether you want any output from the History/Master tables. Hopefully this will head you in the right direction.

Community
  • 1
  • 1
dav1dsm1th
  • 1,687
  • 2
  • 20
  • 24
  • for type is which type of user here i have to use three tables first tbl_User_master that relates Id to user_id(as unique ref_no) in tbl_Inquiry_History, second tbl_Login_master that relates Id to user_id(as unique username) in tbl_User_master, and last tbl_Type that relates Id to type_id(user type: EndUser/Staff/Admin). – Shalin Gajjar Oct 25 '13 at 19:19
  • so update the stored procedure to populate the cargo slot with the value that you lookup using whatever logic you chose. – dav1dsm1th Oct 25 '13 at 20:16
  • i returns data with some duplicates. and without proper type_name as 'User' or 'Admin'. – Shalin Gajjar Oct 25 '13 at 20:21
  • your query is using inner joins which are not going to work unless all inquiries have questions that ALL have answers. you need to use left joins for where a question has no answer and where an inquiry has no question (maybe that doesn't happen) – dav1dsm1th Oct 25 '13 at 21:33
  • can u update your stored procedure as for this senario please. – Shalin Gajjar Oct 26 '13 at 02:51
  • i don't idea how i make this stored procedure it's complicated. can u assist how i take type from tbl_Login_master from tbl_User_master. – Shalin Gajjar Oct 26 '13 at 06:15
  • i modify stored procedure which u suggested but still i'm not getting right User type. it gives me all user type as User and i have to mention that proper type which indicates question to user and answer to user association. – Shalin Gajjar Oct 26 '13 at 09:52
  • update #return set cargo2 =(select t.Type_name as 'type' from tbl_Type t join tbl_Login_master l on l.Id=t.Id join tbl_User_master u on u.Id=l.Id join tbl_Inquiry_History h on h.User_id=u.Id where (h.Inquiry_id=@inquiry_id)) – Shalin Gajjar Oct 26 '13 at 12:28
  • it's give me all cargo2 to user. while i want this desired output: body - question1 then cargo2 - (definitely User), body - answer1 then crago2 - (definitely Staff)..but this update all rows to user.i update your ans. – Shalin Gajjar Oct 28 '13 at 08:23
  • here i examine that i supplied User_id of tbl_Inquiry_History as username 'shalin'(which is one of End User) and User_id of tbl_Inquiry_Master as username 'samir'(which is one of staff). In update query we use tbl_Inquiry_History which have only EndUser ids. so it's returns all cargo2 to resulted 'User'. – Shalin Gajjar Oct 28 '13 at 09:29
  • What text do you want in cargo2 for a question? is it 'user' or the users actual name? which table holds user names? what text do you want in cargo2 for an answer? is it 'staff' or staffs actual name? which table holds staff names? – dav1dsm1th Oct 28 '13 at 18:52
  • i mean i want only type(user or staff) not an name. the question is that about update query we select data from tbl_Inquiry_History this stores only user_id(as unique_no) who asked question. and tbl_Inquiry_master this stores answers definitely stores user_id of staff. now focus query on update we select type_name from references to tbl_Inquiry_History so we get only type name as User. so this append this type to all cargo2 items. for example asnwer1 we get 'User' but we want 'staff'. so there is problem in update query can we get type name from combination of two tables. – Shalin Gajjar Oct 29 '13 at 15:54
  • I've update my answer to set the cargo slot to 'user' on all questions and 'staff' on all answers. This removes the need for the update statement to be executed. – dav1dsm1th Oct 29 '13 at 20:10
  • how to avoid to bind row to repeater if they have cargo1 and cargo2 null. – Shalin Gajjar Oct 30 '13 at 13:13
  • Keep it bound - just test the value for null and format your output accordingly. – dav1dsm1th Oct 30 '13 at 13:17
  • in repeater:- <%#(String.IsNullOrEmpty(Eval("Data").ToString()) ? "0" : Eval("Data"))%> or use if():- <%# If(Eval("item").ToString() Is DBNull.Value, "0 value", Eval("item")) %> see http://stackoverflow.com/questions/1979806/using-evalitem-handling-null-value-and-showing-0-against – dav1dsm1th Oct 30 '13 at 13:28
  • put "" in the first code block where it says "0" - or where it says "0 value" in the second. – dav1dsm1th Oct 30 '13 at 13:49
  • or go back to the sql and put '' in the insert statements wherever I put null - that way you don't need to do anything in your repeater. – dav1dsm1th Oct 30 '13 at 13:55
  • ya i use this but it's still binds null row to repeater. see ur updated answer. – Shalin Gajjar Oct 30 '13 at 14:25
  • the row isn't null - the column is. I assume you still want to use other values from the row. so either update the insert statement to insert empty strings - or update the code in the repeater to handle nulls and display nothing... or some other meaningful value. The nulls should be irrelevant - as i'd expect you to be testing the value of FromTable and not be displaying the cargo columns on rows from the master and history tables. – dav1dsm1th Oct 30 '13 at 16:59
  • i place '' where null in sql. but still u know what i mean. – Shalin Gajjar Oct 30 '13 at 21:51
  • or can we hide only first item of repeater with this link http://stackoverflow.com/questions/9363715/how-to-hide-first-element-in-a-asprepeater – Shalin Gajjar Oct 30 '13 at 22:04
  • update your question so I have an idea what you have in your repeater code (and the output it produces). – dav1dsm1th Oct 31 '13 at 00:27
  • ya can u just look my recently created [question](http://stackoverflow.com/questions/19847503/how-to-get-count-new-replies-from-all-inquiries-as-per-user) – Shalin Gajjar Nov 08 '13 at 10:20