Click here to Skip to main content
14,930,169 members
Please Sign up or sign in to vote.
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
Updated 29-Oct-20 2:41am
v3
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

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
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900