Merging 2 sql columns into one textbox

Question

0.00/5 (No votes)

See more:

VS2012

I'm creating a messenger using a database and I'm stuck on trying to retrieve messages. This is what I have so far.

C#

strQuery = "SELECT message FROM chat WHERE chat.from ='" & TextBox5.Text & "' AND chat.to ='" & TextBox3.Text & "'"

What the query does is retrieve messages from the database with textbox5 being the sender and textbox3 being the retriever. Currently it only displays messages sent to you by the other party and I have it working fine. But I want it to display both sent messages and your messages. For example:

Tom J.: Hey man, what's up?
Me: Nothing much, how about yourself?

It's currently only showing sent messages. The database structure is of the following:
ID (Message ID) Message (Message area) From (ID of sender) To (ID of retriever)

I want to be able to retrieve both, FROM and TO and display them in order using a richtextbox. Is this possible? If so how would I go about doing this?

Posted 25-Sep-14 8:37am

CodexHawk

Updated 25-Sep-14 15:24pm

George Jonsson

v2

Add a Solution

Comments

Kschuler 25-Sep-14 15:05pm

If it were me...I'd give every conversation an Message ID, and use THAT to pull the data. That way you'd get both the FROM and the TOs.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

George Jonsson · Answer 1 · 2014-09-25T15:39:00

Solution 2

You can concatenate the result from your query like this

SQL

SELECT concat(message_from, ' ', message_to) AS message FROM chat;

This is MySQL syntax, but the syntax should be similar in other flavors.

I agree with Mehdi, though, that you should consider to change your design.

Posted 25-Sep-14 15:39pm

George Jonsson

Mehdi Gholam · Answer 2 · 2014-09-25T09:22:00

Solution 1

You need a new model for this, like "Conversation" and "Conversation Details" where the "Conversation" has a list of participants (you can have more than 2 people in the chat) and an ID and the "Conversation Details" has a datetime, UserId, Message, ConversationId etc.

Posted 25-Sep-14 9:22am

Mehdi Gholam

Comments

CodexHawk 25-Sep-14 20:12pm

Is there not a way to pull both sql columns and list them as 1 result?

george4986 · Answer 3 · 2014-09-25T18:32:00

Solution 3

try this

SQL

SELECT userName +' : '+ [Message] as message FROM chat
INNER JOIN userTbl ON userTbl.userId=chat.[from]

if u have a date field then use order by datefield desc

good luck ;-)

Posted 25-Sep-14 18:32pm

george4986

Comments

CodexHawk 28-Sep-14 15:32pm

I'm a bit confused on how to set this query up.. Can you set it up using the information I've provided above?

george4986 28-Sep-14 23:32pm

as per ur given info the chat table structure is like below right?
ID (Message ID) Message (Message area) From (ID of sender) To (ID of retriever).
can u provide the user table structure?

CodexHawk · Answer 4 · 2014-09-28T09:56:00

Solution 4

Solved this by easily using UNION.

SQL

SELECT * FROM Table1
UNION
SELECT * FROM Table2

Posted 28-Sep-14 9:56am

CodexHawk