Outlook mail is not correct when saved to SQL server database

Question

0.00/5 (No votes)

See more:

Hi. I have a grave problem that needs to be immediately resolved due to a looming deadline. Whenever I save outlook mail body into my sql server database, the contents message are somehow mixed up. Here's the an illustration of my problem:

ropbox - Outlook.jpg[^]

As you can see, I need to preserve the original message in order to properly parse the message data it contains as it directly inserts records to my database. But since it contains erroneous data when saved, my application fails everytime I invoke insert sql statement because of data mismatch.

I have done no special formatting in my code. It somehow looks like this :

C#

'x marks the index number of the current mail item being processed
Dim oMsg As Outlook.MailItem = msgItm.Item(x) 
QRY = "Insert into mytable( ..., msgbody) values (...,oMsg.Body)

then perform executenonquery and there were no errors. But when I look up the values saved the msgbody column data is already mixedup. I'm really dumbfounded as to how to properly troubleshoot this matter. I would really appreciate any hints or help from anyone on how to solve this. Many thanks in advance and looking forward to your immediate feedbacks :)

What I have tried:

tried to remove new line characters and still it didnt work. tried replacing spaces with underscore but got the same result. :(

Posted 11-Dec-16 18:27pm

Boy Balantoy

Updated 6-Jan-17 7:21am

Add a Solution

Comments

Richard MacCutchan 12-Dec-16 3:59am

There is no way to answer this. You need to use your debugger to examine exactly what data is being extracted from the message.

Boy Balantoy 12-Dec-16 4:18am

Hi Richard. I already tried that and the data I expected to see is indeed being shown by the debugger, I even put up breakpoints for every single row/column inserts to try and capture the error to no avail. That's why I am dumbstruck as to what's wrong with the system I am developing...

Richard MacCutchan 12-Dec-16 5:00am

Well I am afraid there is no way anyone here can guess what is happening. You need to do more debugging to follow that data all the way into your database and back out again. It is obviously either getting corrupted somewhere in your code, or the display is somehow not interpreting it correctly.

1 solution

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)

NightWizzard · Accepted Answer · 2017-01-06T07:21:00

Think the solution is simple but first of all: I hope the example code above contains the syntax error only because you simplified the statement? Otherwise the statemt should look like this:

QRY = "Insert into mytable( ..., msgbody) values (...,'" & oMsg.Body & "')"

Second: If you're using a string column (varchar or nvarchar) and the message body contains chars that have special meaning in SQL language this will lead into a syntax error when executing QRY. You either have to search and replace or escape all chars that may cause a syntax error or (better) use the SQLCommand class with Parameter to insert the value correctly (this will also prevent from SQL injection if message contains an SQL statement).