Solved

Internal Messaging System ASP.NET

Posted on 2009-04-13
10
808 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
ID: 24130293
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
ID: 24136397
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
ID: 24136504
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Neste
ID: 24136603
I am using MS Access...

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

0
 

Author Comment

by:Neste
ID: 24136618
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
 
LVL 15

Expert Comment

by:oobayly
ID: 24136641
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
ID: 24136655
yeah it works now thx m8

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

Author Comment

by:Neste
ID: 24136714
Any quick ides for an Insert command for this ?
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24136753
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
ID: 24136814
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Error handling in asp.net site 5 26
Question about JQuery and asp.net 3 25
VB.NET 2008 - SQL Timeout 9 24
MVC DDL Json Not Binding to Model 2 13
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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