Avatar of newjeep19
newjeep19
Flag for United States of America asked on

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

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!
C#Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
newjeep19

8/22/2022 - Mon
Robert Schutt

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ão

If you run locally in SSMS it works?
newjeep19

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
newjeep19

ASKER
It does work in SSMS. The data is not returned in the application.
newjeep19

ASKER
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 Schutt

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Robert Schutt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
newjeep19

ASKER
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 Schutt

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 Schutt

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
newjeep19

ASKER
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 Schutt

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

newjeep19

ASKER
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]'
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
newjeep19

ASKER
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 Schutt

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

ASKER
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Robert Schutt

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.
newjeep19

ASKER
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.
newjeep19

ASKER
The except was a huge help!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.