Fetch outllook data to add to mysql

Question

1.00/5 (1 vote)

See more:

I am stuck with retrieving email data from this code
My mysql table looks like this:

TicketID	varchar(15)	True
Caller	        char(30)	True
Priority	varchar(15)	True
Business_service	char(15)	True
Short_description	varchar(30)	True

My email looks like this:

Quote:
SLA warning for INC33333333

Ticket Details:
Short description: ship-to 8766545 cannot be found in 27.20.2.2 customer ship-to modify
Caller: Octavian
Priority: 3 - Moderate
Business service: TCSIT

emailbody has all the mails

My problem: When I run this I am getting an empty table without any values, can you please tell me how to fetch these values(short description, priority, caller etc from the mail) and add to the table.

I tried using Contains and startswith but still ain't getting it.

Thank you.

What I have tried:

My code looks like this in c#

C#

MySqlCommand com = new MySqlCommand("insert into " + tableName + " (" + colName + ") values (" + paramName + ")", connection);
MySqlDataReader read = null;

string emailBody = mail.EmailBody; // Extract Short description, Caller, Priority, Business service from the 

string Short_description = ""; // Need to get the shortDescription from emailBody
string Caller = "";
string Priority = "";
string Business_service = "";
string TicketID = "";

// Number of parameters should match with DB table except auto increment ID in DB.
com.Parameters.Clear();
com.Parameters.Add("@TicketID", MySqlDbType.VarChar).Value = TicketID;
com.Parameters.Add("@Caller", MySqlDbType.VarChar).Value = Caller;
com.Parameters.Add("@Priority", MySqlDbType.VarChar).Value = Priority;
com.Parameters.Add("@Business_service", MySqlDbType.VarChar).Value = Business_service;
com.Parameters.Add("@Short_description", MySqlDbType.VarChar).Value = Short_description;

read = com.ExecuteReader();
read.Close();

Posted 28-Oct-20 22:56pm

cdax

Updated 29-Oct-20 2:41am

Richard Deeming

v3

Add a Solution

Comments

Richard Deeming 29-Oct-20 5:14am

You haven't shown any of your attempts to extract the details from the message body.

You're also using ExecuteReader for an INSERT command, which doesn't return any rows. Use ExecuteNonQuery instead.

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)

CHill60 · Answer 1 · 2020-10-29T02:42:00

You are missing a fair bit of code after

C#

string emailBody = mail.EmailBody; // Extract Short description, Caller, Priority, Business service from the

as in the extraction of those values from the email body!

One approach that is possibly the simplest to follow is
1. Split the email body into the individual lines - lots of examples here[^]

2. For each of the lines of email body look for the key words/phrases that you are interested in - you could use String.IndexOf Method [^] for that

3. Grab the actual value for each key word/phrase - you could use the String.Substring Method [^] for that, or you could split each line using the ":" character

4. You can then set up your parameters for your SQL statement as you already have - I'm hoping that your variable paramName is properly constructed, I don't really see the point of using variables for tableName, colName or paramName here as you know what parameters will be needed.

5. As @Richard-Deeming has pointed out - use ExecuteNonQuery to run the insert and get rid of the pointless

C#

MySqlDataReader read = null;

You are probably going to hit issues with the values returned - the most common error with beginners is not allowing for the position of the actual ":" character when using IndexOf for example. Use the debugger to help yourself understand how to change the code - see the CP article Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]

Give it a go and write the code, then if you are still stuck come back with that code and we will try to help you further. But we won't write it for you