When I run a console application it does not return data from a stored procedure

newjeep19
newjeep19 used Ask the Experts™
on
I have a console application that when ran gets the zip code from a database table returns it to the application then with the zip code stored in the application it calls a second stored procedure that uses the zip code as a parameter for that stored procedure which builds an email. The email is sent out to people that are with in a 20 mile radius of the zip code.  Below is my code and stored procedures.
Console app code
namespace AutoEmail
{
    class Program
    {
        #region Globals
        string _zip = string.Empty;
        string _miles = string.Empty;
        string _aFName = string.Empty;
        string _aLName = string.Empty;
        string _aAddress = string.Empty;
        string _aCity = string.Empty;
        string _aState = string.Empty;
        string _aZip = string.Empty;
        string _aNumber = string.Empty;
        string _aEmail = string.Empty;
        string _cFName = string.Empty;
        string _cLName = string.Empty;
        string _cAddress = string.Empty;
        string _cCity = string.Empty;
        string _cState = string.Empty;
        string _cZip = string.Empty;
        string _cHomePhone = string.Empty;
        string _cAltPhone = string.Empty;
        string _cEmail = string.Empty;
        string _cPrefContact = string.Empty;
        string _cTravelTime = string.Empty;
        string _cTravelDist = string.Empty;
        string _cAddedDate = string.Empty;
        string _cZipCode = string.Empty;
        string _aNumber1 = string.Empty;
        #endregion

        static void Main(string[] args)
        {
            GetZip(); 
            SendEmail();
        }

        public static void GetZip()
        {
            #region Connection
            var lspAutoZip = new SqlConnection("Data Source=DataseSourceHere\\DatabaseSource; Initial Catalog=Database; User ID=DatabaseIDHere; Password=DatabasePasswordHere");
            var command = new SqlCommand("sp_Auto_ZipCode", lspAutoZip);
            #endregion

            #region Globals
            string _zipCode = string.Empty;
            #endregion

            try
            {
                command.CommandType = CommandType.StoredProcedure;

                var dt = new DataTable();
                lspAutoAgentZip.Open();

                using (var sda = new SqlDataAdapter(command))
                    sda.Fill(dt);

                DataSet dataSet = new DataSet();

                // Create the dictionary to hold the email data for each individual email. This allows us 
                // to group all of the books due for an individual together.  We will use the email address
                // as the key for the dictionary instead of CustomerID in case the user has given us two
                // different email addresses.

                Dictionary<string, List<DataRow>> List = new Dictionary<string, List<DataRow>>();

                // Iterate over the dataset and populate the dictionary
                foreach (DataRow row in dt.Rows)
                {
                    _zipCode = Convert.ToString(row["A_ZIP_CD"]);

                    if (!List.ContainsKey(_zipCode))
                    {
                        List.Add(_zipCode, new List<DataRow>());
                    }
                    //add the data row for the email
                    List[_zipCode].Add(row);
                }
            }
            catch { }
        }

        public static void SendEmail()
        {
            #region Connection
            var lspAutoEmail = new SqlConnection("Data Source=DatabaseSourceHere\\DatabaseSource; Initial Catalog=Database; User ID=DatabaseIDHere; Password=DatabasePasswordHere");          
            var command = new SqlCommand("sp_Auto_ZipCode", lspAutoEmail);
            var commandsql = new SqlCommand("sp_Auto_Email", lspAutoEmail);
            #endregion

            #region Globals
            string _aFName = string.Empty;
            string _aLName = string.Empty;
            string _aAddress = string.Empty;
            string _aCity = string.Empty;
            string _aState = string.Empty;
            string _aZip = string.Empty;
            string _aNumber = string.Empty;
            string _aEmail = string.Empty;
            string _cFName = string.Empty;
            string _cLName = string.Empty;
            string _cAddress = string.Empty;
            string _cCity = string.Empty;
            string _cState = string.Empty;
            string _cZip = string.Empty;
            string _cHomePhone = string.Empty;
            string _cAltPhone = string.Empty;
            string _cEmail = string.Empty;
            string _cPrefContact = string.Empty;
            string _cTravelTime = string.Empty;
            string _cTravelDist = string.Empty;
            //DateTime _canidateAddedDate;
            //DateTime _canidateAddedDate1;
            string _canidateAddedDate = string.Empty;
            string _zipCode = string.Empty;
            string _agentNumber1 = string.Empty;
            #endregion

            //GetZip();
            commandsql.CommandType = CommandType.StoredProcedure;
            //commandsql.Parameters.AddWithValue("@ZipCode", _zipCode);

            var dt = new DataTable();
            lspAutoAgentEmail.Open();

          [b]  using (var sda = new SqlDataAdapter(command))
                sda.Fill(dt);[/b]

            DataSet dataSet = new DataSet();

            // Create the dictionary to hold the email data for each individual email. This allows us 
            // to group all of the books due for an individual together.  We will use the email address
            // as the key for the dictionary instead of CustomerID in case the user has given us two
            // different email addresses.

            Dictionary<string, List<DataRow>> List = new Dictionary<string, List<DataRow>>();

            // Iterate over the dataset and populate the dictionary
            foreach (DataRow row in dt.Rows)
            {
                _zipCode = Convert.ToString(row["A_ZIP_CD"]);

                if (!List.ContainsKey(_zipCode))
                {
                    List.Add(_zipCode, new List<DataRow>());
                }
                //add the data row for the email
                List[_zipCode].Add(row);
            }

            foreach (string zip in List.Keys)
            {

                  #region Connection
            var lspAutoEmail = new SqlConnection("Data Source=DatabaseSourceHere\\DatabaseSource; Initial Catalog=Database; User ID=DatabaseIDHere; Password=DatabasePasswordHere");          
            var command = new SqlCommand("sp_Auto_ZipCode", lspAutoEmail);
            var commandsql = new SqlCommand("sp_Auto_Email", lspAutoEmail);
            #endregion

                commandsql.CommandType = CommandType.StoredProcedure;
                commandsql.Parameters.AddWithValue("@ZipCode", _zipCode);

                var table = new DataTable();

                using (var sda = new SqlDataAdapter(commandsql))<---------- this is where the error occurs -----
                    sda.Fill(table); < -------This is where the error message occurs ---------

                DataSet ds = new DataSet();

                Dictionary<string, List<DataRow>> emailList = new Dictionary<string, List<DataRow>>();

                // Iterate over the dataset and populate the dictionary
                foreach (DataRow row in table.Rows)
                {
                    _aFName = Convert.ToString(row["A_FIRST_NM"]);
                    _aLName = Convert.ToString(row["A_LAST_NM"]);
                    _aAddress = Convert.ToString(row["A_ADDRESS_CD"]);
                    _aCity = Convert.ToString(row["A_CITY_CD"]);
                    _aState = Convert.ToString(row["A_STATE_CD"]);
                    _aZip = Convert.ToString(row["A_ZIP_CD"]);
                    _aNumber = Convert.ToString(row["A_AGENT_NBR"]);
                    _aEmail = Convert.ToString(row["A_EMAIL_NBR"]);
                    _cFName = Convert.ToString(row["FIRST_NM"]);
                    _cLName = Convert.ToString(row["LAST_NM"]);
                    _cAddress = Convert.ToString(row["ADDRESS_TXT"]);
                    _cCity = Convert.ToString(row["CITY"]);
                    _cState = Convert.ToString(row["STATE_CD"]);
                    _cZip = Convert.ToString(row["ZIP"]);
                    _cHomePhone = Convert.ToString(row["HOME_PHONE_NBR"]);
                    _cAltPhone = Convert.ToString(row["ALT_PHONE_NBR"]);
                    _cEmail = Convert.ToString(row["EMAIL"]);
                    _cPrefContact = Convert.ToString(row["PREF_CONTACT_METHOD_CD"]);
                    _cTravelTime = Convert.ToString(row["TRAVEL_TIME_MINS_NBR"]);
                    _cTravelDist = Convert.ToString(row["TRAVEL_DISTANCE_MLS_NBR"]);
                    _cAddedDate = Convert.ToString(row["ADDED_DT"]);

                    if (!emailList.ContainsKey(_agentNumber))
                    {
                        emailList.Add(_agentNumber, new List<DataRow>());
                    }
                    //add the data row for the email
                    emailList[_agentNumber].Add(row);
                }

                _agentNumber1 = _agentNumber;

                foreach (string agentNum in emailList.Keys)
                {
                    try
                    {
                        using (var sda = new SqlDataAdapter(commandsql))
                            sda.Fill(table);

                        var App = string.Format("http://toapplication");
                        var recepientEmail = _agentEmail;

                        // create a string builder to build up the body of the email
                        StringBuilder body = new StringBuilder();
                        body.Append("<html>");
                        body.Append("<body>");
                        body.AppendFormat("<b><i>West Central Recruitment</i></b><hr/><br/>");
                        // assume the first/last name will be the same for each row, so just get the
                        // name information from the first row to build the opening line of the email
                        DataRow firstRow = emailList[agentNum][0];
                        body.AppendFormat("<p>Hello {0} {1}: </p>", firstRow["A_FIRST_NM"].ToString(), firstRow["A_LAST_NM"].ToString());
                        body.AppendFormat("</p>"); ;
                        body.Append("<table>");
                        body.AppendFormat("<p>This email contains important information regarding new candidate that has been entered  with in the last 30 days to the Database.</p></td></tr>");
                        body.AppendFormat("<tr><td>New C - was added on  <b>" + _cAddedDate + "</b> New C Name - <b>  " + " " + _cFName + "  " + _cLName + "</b></td></tr>"); //+  <br/> was added on  _cAddedDate 
                        body.AppendFormat("<tr><td><br/>The c information is listed below:<br/><br/>New c address : <b>" + _cAddress + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c city : <b>" + _cCity + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c state : <b> " + _cState + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c zipcode : <b> " + _cZip + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c home telephone number : <b> " + _cHomePhone + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c alt telephone number : <b> " + _cAltPhone + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c email : <b>" + _cEmail + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c prefered contact method : <b> " + _cPrefContact + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c willing travel time : <b>" + _cTravelTime + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c wiling travel distance : <b>" + _cTravelDist + "</b></p></td></tr>");
                        body.AppendFormat("<br/><tr><td> If you are interested click on the link to learn more: <b>" + App + "</b></td></tr>");
                        body.AppendFormat("<br/><tr><td> Thank you! </td></tr></table>");
                        body.Append("</body>");
                        body.Append("</html>");

                        foreach (DataRow rows in emailList[agentNum])
                        {
                            _agentFName = Convert.ToString(rows["A_FIRST_NM"]);
                            _agentLName = Convert.ToString(rows["A_LAST_NM"]);
                            _agentNumber = Convert.ToString(rows["A_AGENT_NBR"]);
                        }

                        // if (_agentFName != "")
                        {
                            //if (_agentLName != "")
                            {
                                //if (_agentLName != "")
                                {
                                    SendHtmlFormattedEmail(agentNum, body.ToString());
                                }
                            }
                        }

                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("ERROR MESSAGE: " + ex.Message);
                        lspAutoAgentEmail.Close();
                    }
                }
            }
        }

        public static void SendHtmlFormattedEmail(string recepientEmail, string body)
        {
            using (MailMessage mail = new MailMessage())
            {
                //initialize the entry for email
                mail.From = new MailAddress("CompanyA@companyA.com");
                mail.Subject = "****ATTENTION**** A new c has been added!****ATTENTION****";
                mail.IsBodyHtml = true;
                mail.To.Add(new MailAddress(recepientEmail));

                mail.BodyEncoding = System.Text.Encoding.UTF32;
                mail.Body = body;
                new SmtpClient();
                new MailMessage();
                var smtp = new SmtpClient { Host = "HostServiceHere", Port = PortNumberHere };
                smtp.Send(mail);
            }
        }
    }
}

Open in new window

SQL Stored Proc Zip Code - gets the zip code
ALTER PROCEDURE [dbo].[sp_Auto_ZipCode]
	
AS
BEGIN
	
	SET NOCOUNT ON;

    SELECT A_ZIP_CD
	FROM ASRD_AUTO_EMAIL 
END

Open in new window


SQL Stored Proc to get data for the email - builds the email
ALTER PROCEDURE [dbo].[sp_Auto_Email]
		(
			@ZipCode NUMERIC(5, 0) = Null
		)
AS
BEGIN
		 DECLARE @Latitude FLOAT(10)
		 DECLARE @Longitude FLOAT(10)
		 

	SET NOCOUNT ON;
	---GET LAT/LONG FROM ZIP CODE table
	SELECT @Latitude = S.Latitude, @Longitude = S.Longitude FROM wcr_ci.dbo.[State]S WHERE S.Zip = @ZipCode

	SELECT	S.Zip, 
			--S.Zip_Name,
			3443.917*ACOS(COS(RADIANS(90-(@Latitude)))*COS(RADIANS(90-(Latitude)))+SIN(RADIANS(90-(@Latitude)))*SIN(RADIANS(90-(Latitude)))
			*COS(RADIANS((@Longitude-Longitude))))  AS Miles,
			AAE.A_FIRST_NM,
			AAE.A_LAST_NM,
			AAE.A_ADDRESS_CD,
			AAE.A_CITY_CD,
			AAE.A_STATE_CD,
			AAE.A_ZIP_CD,
			AAE.A_AGENT_NBR,
			AAE.A_EMAIL_NBR,
			AC.FIRST_NM,
			AC.LAST_NM,
			AC.ADDRESS_TXT,
			AC.CITY,
			AC.STATE_CD,
			AC.ZIP,					
			STUFF(STUFF(STUFF(AC.HOME_PHONE_NBR,1,0,' ('),6,0,') '),11,0,'-') AS 'HOME_PHONE_NBR',	
			STUFF(STUFF(STUFF(AC.ALT_PHONE_NBR,1,0,' ('),6,0,') '),11,0,'-') AS 'ALT_PHONE_NBR',
			AC.EMAIL,
			AC.PREF_CONTACT_METHOD_CD,
			AC.TRAVEL_TIME_MINS_NBR,
			AC.TRAVEL_DISTANCE_MLS_NBR,
			AC.ADDED_DT 
	FROM wcr_ci.dbo.[State] S
			 JOIN ASRD_AUTO_EMAIL AAE ON S.Zip = AAE.A_ZIP_CD
			 JOIN ASRD_C AC ON S.Zip = AC.ZIP 
	WHERE 3443.917*ACOS(COS(RADIANS(90-(@Latitude)))*COS(RADIANS(90-(Latitude)))+SIN(RADIANS(90-(@Latitude)))*SIN(RADIANS(90-(Latitude)))
			*COS(RADIANS((@Longitude-Longitude))))  <= '20'
		  AND AC.ADDED_DT >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-180,0) 
		  AND AAE.A_ZIP_CD = @ZipCode
	ORDER BY miles

Open in new window


Please help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Robert SchuttSoftware Engineer

Commented:
That code could really use some cleaning up. Please indicate if you want help with that or maybe this is part of a larger program making it look like there's all kinds of unused stuff.

For the actual question: I suspect the reason for not getting any data is that you're not using the correct variable on line 163, please try:
commandsql.Parameters.AddWithValue("@ZipCode", zip);

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
If you run locally in SSMS it works?

Author

Commented:
Thanks for your response. I did try changing line 163 to your suggestion, Same results as before no data returned.  I am not sure what would need to be cleaned up. However, I am always looking on how to improve my code and coding skills. So, I welcome suggestions.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
It does work in SSMS. The data is not returned in the application.

Author

Commented:
I get the following error when I changed line 163 to zip from _ZipCode. Error message: Error converting data type varchar to float. Not sure which variable is causing the error. I have commented out various parts of the stored procedure queries and still got the same error. Please help.
Robert SchuttSoftware Engineer

Commented:
So what did you change exactly? Because I suggested changing _ZipCode to zip and both are string variables so in that sense should not cause a conversion error unless maybe the first stored procedure returns a null value or empty string in the first row? (So like Vitor suggested, double check your output in Management Studio or at least debug the value of the zip variable in the loop).

There are 2 ways to do this:
1) get it to work first (hopefully the above is a step in right direction) and then clean up;
2) clean it up first and see if this helps getting to the bottom of the problem quicker.

To clean the code up I would need to know what you actually want to do with the lists of data rows because as it is this takes up a lot of the code and it seems to be hardly used because when you assign a variable in a loop, only the last one is used (mainly talking about the loop around line 250). Also the altered copy of the block of global variables is not needed (not both). This is why I asked if this is part of a larger piece of code. If not, then I can post a 'compressed' version that should do the same.
Software Engineer
Commented:
I had another look at the code and changed a couple of things. If this is going in the right direction we can take out a lot of the commented code and maybe some more stuff. Note that I could do with some example data because at the moment I'm working with a number of assumptions which lead me in the direction of thinking this code can not work correct at all...

This mainly has to do with variables not being defined or being used before being set or not being set at all, 'number' variable used as email address etc.

But also the logic of sending the emails to all agents inside a certain radius from a loop of all zip codes gives me a feeling that there will be duplicates?

Any way, please have a look:
        public static void SendEmail()
        {
            #region Connection
            SqlConnection lspAutoEmail = new SqlConnection("...");
            SqlCommand command = new SqlCommand("sp_Auto_ZipCode", lspAutoEmail);
            SqlCommand commandsql = new SqlCommand("sp_Auto_Email", lspAutoEmail);
            #endregion

            #region Globals
            string _aFName = string.Empty;
            string _aLName = string.Empty;
            string _aAddress = string.Empty;
            string _aCity = string.Empty;
            string _aState = string.Empty;
            string _aZip = string.Empty;
            string _aNumber = string.Empty;
            string _aEmail = string.Empty;
            string _cFName = string.Empty;
            string _cLName = string.Empty;
            string _cAddress = string.Empty;
            string _cCity = string.Empty;
            string _cState = string.Empty;
            string _cZip = string.Empty;
            string _cHomePhone = string.Empty;
            string _cAltPhone = string.Empty;
            string _cEmail = string.Empty;
            string _cPrefContact = string.Empty;
            string _cTravelTime = string.Empty;
            string _cTravelDist = string.Empty;
            string _cAddedDate = string.Empty;
            //DateTime _canidateAddedDate;
            //DateTime _canidateAddedDate1;
            string _canidateAddedDate = string.Empty;
            string _zipCode = string.Empty;
            //string _agentNumber1 = string.Empty;
            #endregion

            // not used (list is filled below)
            //GetZip();
            command.CommandType = CommandType.StoredProcedure;
            commandsql.CommandType = CommandType.StoredProcedure;
            //commandsql.Parameters.AddWithValue("@ZipCode", _zipCode);

            DataTable dt = new DataTable();
            //lspAutoAgentEmail.Open();

            using (SqlDataAdapter sda = new SqlDataAdapter(command))
                sda.Fill(dt);

            // not used
            //DataSet dataSet = new DataSet();

            // Create the dictionary to hold the email data for each individual email. This allows us 
            // to group all of the books due for an individual together.  We will use the email address
            // as the key for the dictionary instead of CustomerID in case the user has given us two
            // different email addresses.

            //Dictionary<string, List<DataRow>> List = new Dictionary<string, List<DataRow>>();
            // changed to simple list since the data rows are not used and zip codes should be unique anyway?
            List<string> List = new List<string>();

            // Iterate over the dataset and populate the dictionary
            foreach (DataRow row in dt.Rows)
            {
                _zipCode = Convert.ToString(row["A_ZIP_CD"]);

                if (!List.Contains(_zipCode)) // was ContainsKey
                {
                    List.Add(_zipCode);
                }
                //add the data row for the email
                //List[_zipCode].Add(row);
            }

            // add parameter only once
            commandsql.Parameters.Add("@ZipCode", SqlDbType.Decimal, 5);

            foreach (string zip in List) // was List.Keys
            {

                // use existing connection and command objects outside loop
                //#region Connection
                //var lspAutoEmail = new SqlConnection("...");          
                //var command = new SqlCommand("sp_Auto_ZipCode", lspAutoEmail);
                //var commandsql = new SqlCommand("sp_Auto_Email", lspAutoEmail);
                //#endregion

                //commandsql.CommandType = CommandType.StoredProcedure;

                // set value of parameter instead of adding it each time
                commandsql.Parameters["@ZipCode"].Value = zip;

                DataTable table = new DataTable();

                using (SqlDataAdapter sda = new SqlDataAdapter(commandsql))
                    sda.Fill(table);

                // not used
                //DataSet ds = new DataSet();

                // simplified list
                //Dictionary<string, List<DataRow>> emailList = new Dictionary<string, List<DataRow>>();
                List<string> emailList = new List<string>();

                // Iterate over the dataset and populate the dictionary
                foreach (DataRow row in table.Rows)
                {
                    _aAddress = Convert.ToString(row["A_ADDRESS_CD"]);
                    _aCity = Convert.ToString(row["A_CITY_CD"]);
                    _aState = Convert.ToString(row["A_STATE_CD"]);
                    _aZip = Convert.ToString(row["A_ZIP_CD"]);
                    _aNumber = Convert.ToString(row["A_AGENT_NBR"]);
                    _aEmail = Convert.ToString(row["A_EMAIL_NBR"]);
                    _cFName = Convert.ToString(row["FIRST_NM"]);
                    _cLName = Convert.ToString(row["LAST_NM"]);
                    _cAddress = Convert.ToString(row["ADDRESS_TXT"]);
                    _cCity = Convert.ToString(row["CITY"]);
                    _cState = Convert.ToString(row["STATE_CD"]);
                    _cZip = Convert.ToString(row["ZIP"]);
                    _cHomePhone = Convert.ToString(row["HOME_PHONE_NBR"]);
                    _cAltPhone = Convert.ToString(row["ALT_PHONE_NBR"]);
                    _cEmail = Convert.ToString(row["EMAIL"]);
                    _cPrefContact = Convert.ToString(row["PREF_CONTACT_METHOD_CD"]);
                    _cTravelTime = Convert.ToString(row["TRAVEL_TIME_MINS_NBR"]);
                    _cTravelDist = Convert.ToString(row["TRAVEL_DISTANCE_MLS_NBR"]);
                    _cAddedDate = Convert.ToString(row["ADDED_DT"]);

                    if (!emailList.Contains(_aEmail)) // _agentNumber
                    {
                        emailList.Add(_aEmail);

                        _aFName = Convert.ToString(row["A_FIRST_NM"]);
                        _aLName = Convert.ToString(row["A_LAST_NM"]);
                    }
                    //add the data row for the email
                    //emailList[_agentNumber].Add(row);
                }

                //_agentNumber1 = _agentNumber;

                foreach (string agentEmail in emailList)
                {
                    try
                    {
                        //using (var sda = new SqlDataAdapter(commandsql))
                        //    sda.Fill(table);

                        string App = string.Format("http://toapplication");
                        //var recepientEmail = _agentEmail;

                        // create a string builder to build up the body of the email
                        StringBuilder body = new StringBuilder();
                        body.Append("<html>");
                        body.Append("<body>");
                        body.AppendFormat("<b><i>West Central Recruitment</i></b><hr/><br/>");
                        // assume the first/last name will be the same for each row, so just get the
                        // name information from the first row to build the opening line of the email
                        //DataRow firstRow = emailList[agentNum][0];
                        //body.AppendFormat("<p>Hello {0} {1}: </p>", firstRow["A_FIRST_NM"].ToString(), firstRow["A_LAST_NM"].ToString());
                        body.AppendFormat("<p>Hello {0} {1}: </p>", _aFName, _aLName);
                        body.AppendFormat("</p>"); ;
                        body.Append("<table>");
                        body.AppendFormat("<p>This email contains important information regarding new candidate that has been entered  with in the last 30 days to the Database.</p></td></tr>");
                        body.AppendFormat("<tr><td>New C - was added on  <b>" + _cAddedDate + "</b> New C Name - <b>  " + " " + _cFName + "  " + _cLName + "</b></td></tr>"); //+  <br/> was added on  _cAddedDate 
                        body.AppendFormat("<tr><td><br/>The c information is listed below:<br/><br/>New c address : <b>" + _cAddress + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c city : <b>" + _cCity + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c state : <b> " + _cState + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c zipcode : <b> " + _cZip + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c home telephone number : <b> " + _cHomePhone + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c alt telephone number : <b> " + _cAltPhone + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c email : <b>" + _cEmail + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c prefered contact method : <b> " + _cPrefContact + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c willing travel time : <b>" + _cTravelTime + " </b></p></td></tr>");
                        body.AppendFormat("<tr><td><br/>New c wiling travel distance : <b>" + _cTravelDist + "</b></p></td></tr>");
                        body.AppendFormat("<br/><tr><td> If you are interested click on the link to learn more: <b>" + App + "</b></td></tr>");
                        body.AppendFormat("<br/><tr><td> Thank you! </td></tr></table>");
                        body.Append("</body>");
                        body.Append("</html>");

                        //foreach (DataRow rows in emailList[agentNum])
                        //{
                        //    _agentFName = Convert.ToString(rows["A_FIRST_NM"]);
                        //    _agentLName = Convert.ToString(rows["A_LAST_NM"]);
                        //    _agentNumber = Convert.ToString(rows["A_AGENT_NBR"]);
                        //}

                        // if (_agentFName != "")
                        {
                            //if (_agentLName != "")
                            {
                                //if (_agentLName != "")
                                {
                                    SendHtmlFormattedEmail(agentEmail, body.ToString());
                                }
                            }
                        }

                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("ERROR MESSAGE: " + ex.Message);
                        //lspAutoAgentEmail.Close();
                    }
                }
            }
        }

Open in new window

Author

Commented:
OK I ran the first store procedure (sp.Auto_ZipCode) to get the list of zip codes for those who signed up to get the emails in SSMS. The list returned w/those zip codes. Next in SSMS I executed the stored procedure with several of the zip codes (one at a time) and I get the following error message in SSMS. Error: Error converting data type varchar to float. So I went thru the second stored procedure and commented out certain columns to see why I would get the above error. No luck. I looked at all the tables in the query and they only thing that I found that could be causing the error is here:
FROM wcr_ci.dbo.[State] S
                   JOIN ASRD_AUTO_EMAIL AAE ON S.Zip = AAE.A_ZIP_CD
                   JOIN ASRD_C AC ON S.Zip = AC.ZIP


in the state table ASRD_AUTO_EMAIL table the column S.Zip and  AAE.A_ZIP_CD are floats  and in the ASRD_C AC  table the AC.ZIP is a varchar(5).  I don't know if this would cause the issue or not. Thoughts?
Robert SchuttSoftware Engineer

Commented:
Yes that would be a logical place to suspect, do you get the same error if you run a simple "select *" with that join? Is it possible to change the float definition or could that affect other people/programs using these tables?

You could also try to use cast or convert to make the join happen.

To be able to recreate:
Can you post the table creation SQL please? as stated before, some data would also help.
Has this ever worked before or is this the first time you're using it?
Robert SchuttSoftware Engineer

Commented:
PS: I just changed my table definitions and it still joins the tables without problem so I guess I would really need to see table structure and preferably some data and you may want to double check there are no null values or other anomalies in the 3 zip code fields used for joining the tables.

Author

Commented:
Yes,  when I do SELECT *  FROM the tables I get the same error so it appears to be the join between  JOIN ASRD_C AC ON S.Zip = AC.ZIP  
I can't change the data type on either column due to other applications using the tables.  
In test I was able to get the application to work with a test table for ASRD_C  and in the stored procedure I set the zip code to a zip code for the dummy data in the test table SET @ZipCode = 'Zipe code number here'.  Once I changed the table to production and did not set the zip code I started to have issues.
Robert SchuttSoftware Engineer

Commented:
Can you try something like:
select * from ASRD_C where isnull(zip,'') not like '[0-9][0-9][0-9][0-9][0-9]'

Open in new window

Author

Commented:
I tried the SELECT statement that you suggested and only one row returned. I then added the below to the WHERE clause and I still got the same error.

Added to the Where clause:

WHERE 3443.917*ACOS(COS(RADIANS(90-(@Latitude)))*COS(RADIANS(90-(Latitude)))+SIN(RADIANS(90-(@Latitude)))*SIN(RADIANS(90-(Latitude)))
                  *COS(RADIANS((@Longitude-Longitude))))  <= '20'
              AND AC.ADDED_DT >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-180,0)
              AND ISNULL(AC.ZIP,'') NOT LIKE '[0-9][0-9][0-9][0-9][0-9]'

Author

Commented:
Ahh... the zip code for the one record is  wrong ... ZIP for the one row ran is cufan and not a zip code number. Bad data in bad data out. So, if I fix the error then it should work?
Robert SchuttSoftware Engineer

Commented:
I would hope so yes and then we can get to the real issues ;-)

Author

Commented:
OK so I executed the stored procedure in SSMS after updating the record w/the zip code cufan to the correct zip code for that record. I then tested w/several emails from the list when the first stored procedure and no data is returned.
Robert SchuttSoftware Engineer

Commented:
Can you be more specific? No data for the first zip, or all zips? Could there be a difference between test data and production data?

Have you tried using breakpoints and watches to determine that intermediate and loop variables contain expected values?

The WHERE clauses (especially miles and dates) can of course have an empty result set with specific data.

So I wanna remind you of the request to make it possible to reproduce your exact situation by posting SQL for table structures and some data which results in output for the 2 procedures and also recommend for you to work in a test environment with only a few relevant records at first so you can more easily step through the code in the debugger without having to go through hundreds of zip codes that have no result from the second procedure for example.

Author

Commented:
OK so I was pointing to the wrong table. The [State] table was incorrect table. Once I pointed to the correct table then it returned all of the data in the second stored procedure.  I then tested the second stored procedure in  SSMS and then tested the application and it worked. It returned the data in the for loop which populated the body of the email as well as the Smtp credentials (To and From etc). Then the emails where sent. Thanks for your help. Much appreciated. I am the only developer here so it helped to have another set of eyes looking at the quires and application that has more experience.

Author

Commented:
The except was a huge help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial