Solved

Internal Messaging System ASP.NET

Posted on 2009-04-13
10
806 Views
Last Modified: 2012-05-06
Hi there i want to set up a very very simple internal messaging system...

Pages:
DefaultMessages.aspx --> Displays two links for, 1: Inbox, 2: Compose New Message
MessagesInbox.aspx --> Displays all the Messages that user received from other users
MessagesNew.aspx --> Make new messages to send

My Access DB is:
tblUsers --> UserID, Username, Password, Firstname, Lastname
tblPMMessages --> pmID, pmSubject, pmContent, MessageFrom, MessageTo

How do i setup this to work, do i need to have a relation between UserID --> MessageFrom and UserID --> MessageTo

I don't understand the logic here...Can anyone explain how i would need to setup this to make it work, if anyone just can push me in right direction...
0
Comment
Question by:Neste
  • 5
  • 5
10 Comments
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
If you're looking for the SQL query to to get all the messages sent to a particular user, this should do the job:
Of course, you'll want to select only the required fields rather than everything.
SELECT *

FROM tblPMMessages

INNER JOIN tblUsers ON tblPMMessages.fldMessageFrom = tblUsers.fldUserID

WHERE tblPMMessages.fldMessageTo = ?

Open in new window

0
 

Author Comment

by:Neste
Comment Utility
I have worked whit your example and i ended up with the code provided...

It works but how do i Display the Username instead for the ID...

When a user checks his/hers Inbox, i want them to know who the messages are from...

As it is now it only displays numbers/ids....
"SELECT * FROM [tblPMMessages], [tblUsers]" & _

"WHERE [tblPMMessages.MessageFromID] = [tblUsers.UserID]" & _

"AND [tblPMMessages.MessageToID] = @id"

Open in new window

0
 
LVL 15

Accepted Solution

by:
oobayly earned 125 total points
Comment Utility
What database server are you using? If you're using the query posted you should be receiving all fields from all tables specified in the query.
Also, you shouldn't really need the square brackets unless you have a table or field that shares a name with a keyword. ie Calling a field "Sum", or a table "View".
I'm also vaguely surprised that [tblPMMessages.MessageToID] works, as this implies that the field name is "tblPMMessages.MessageToID", not the field "MessageToID" in the table "tblPMMessages", of course this may depend on the database server.

You could of course specify the fields yourself.
SELECT

  [tblUsers].[Firstname], [tblUsers].[LastName],

  [tblPMMessages].[pmSubject], [tblPMMessages].[pmContent]

FROM [tblPMMessages], [tblUsers]

WHERE [tblPMMessages].[MessageFromID] = [tblUsers].[UserID]

AND [tblPMMessages].[MessageToID] = @id

Open in new window

0
 

Author Comment

by:Neste
Comment Utility
I am using MS Access...

Instead of MessageFromID i want to display the username from tblUsers...

0
 

Author Comment

by:Neste
Comment Utility
here is the aspx code...

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        DataKeyNames="pmID">
        <Columns>
            <asp:BoundField DataField="pmID" HeaderText="pmID" InsertVisible="False"
                ReadOnly="True" SortExpression="pmID" />
            <asp:BoundField DataField="pmSubject" HeaderText="pmSubject"
                SortExpression="pmSubject" />
            <asp:BoundField DataField="pmContent" HeaderText="pmContent"
                SortExpression="pmContent" />
            <asp:BoundField DataField="pmCreateDate" HeaderText="pmCreateDate"
                SortExpression="pmCreateDate" />
            <asp:BoundField DataField="MessageToID" HeaderText="MessageToID"
                SortExpression="MessageToID" />
            <asp:BoundField DataField="MessageFromID" HeaderText="MessageFromID"
                SortExpression="MessageFromID" />
        </Columns>
    </asp:GridView>
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 15

Expert Comment

by:oobayly
Comment Utility
Have you tried the query I posted, it should show the sender's name. All your should have to do is add a similar field for "Username"
0
 

Author Comment

by:Neste
Comment Utility
yeah it works now thx m8

just added:            
<asp:BoundField DataField="Username" HeaderText="Username" SortExpression="Username" />
0
 

Author Comment

by:Neste
Comment Utility
Any quick ides for an Insert command for this ?
0
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
Right, now I get you. Just change "MessageFromID" to "Username" for all the attributes in the BoundField tag
0
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
Should be as simple as this, assuming that pmID in an auto increment field
INSERT INTO tblPMMessages(pmSubject, pmContent, MessageFrom, MessageTo)

VALUES(@pmSubject, @pmContent, @MessageFrom, @MessageTo)

Open in new window

0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now