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
SQL Stored Proc to get data for the email - builds the email
Please help!
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);
}
}
}
}
ALTER PROCEDURE [dbo].[sp_Auto_ZipCode]
AS
BEGIN
SET NOCOUNT ON;
SELECT A_ZIP_CD
FROM ASRD_AUTO_EMAIL
END
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
Please help!
C#Microsoft SQL Server 2008Microsoft SQL Server
Last Comment
newjeep19
If you run locally in SSMS it works?
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.
ASKER
It does work in SSMS. The data is not returned in the application.
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.
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.
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.
ASKER CERTIFIED SOLUTION
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.
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?
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?
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?
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?
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.
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.
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.
Can you try something like:
select * from ASRD_C where isnull(zip,'') not like '[0-9][0-9][0-9][0-9][0-9]'
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(
*COS(RADIANS((@Longitude-L
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]
Added to the Where clause:
WHERE 3443.917*ACOS(COS(RADIANS(
*COS(RADIANS((@Longitude-L
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]
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?
I would hope so yes and then we can get to the real issues ;-)
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.
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.
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.
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.
ASKER
The except was a huge help!
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:
Open in new window