locked
Follow Up posting to Digest emails by developer RRS feed

  • Question

  • User-718146471 posted

    Hello again folks, I'm back at it again using C#.net code behind to first grab the developer's email address based on it being checked off in the gridview. That part works but I want to only return one email address even if I have three matrices to send out. I think it may be my code logic is whacked. I'll post my code here and help me if you can. Here is the pseudo-code logic:

    Query DB for developer email address among checked items

    Developer email is selected once no matter the items it applies to

    Query DB for all matrix records that match the developer email address

    Send email as combined digest to developer of records

    Here is the code:

                        foreach (GridViewRow row in gvUnsent.Rows)
                        {
                            Label lblCheckRow = (row.Cells[0].FindControl("lblSendTo") as Label);
                            CheckBox chkRow = (row.Cells[0].FindControl("cbSendTo") as CheckBox);
                            if (chkRow.Checked)
                            {
                                string sql = "SELECT DISTINCT DevEmail FROM EvalSent WHERE RecID = @RecID ORDER BY DevEmail";
                                recID = Convert.ToInt32(lblCheckRow.Text.ToString());
                                {
                                    // Query by email address
                                    SqlCommand cmd = new SqlCommand(sql, conn);
                                    try
                                    {
                                        conn.Open();
                                        cmd.Parameters.AddWithValue("@RecID", recID);
                                        SqlDataReader rdr = cmd.ExecuteReader();
                                        while (rdr.Read())
                                        {
                                            DevEmails.Add(rdr.GetValue(0).ToString() + "; ");
                                        }
                                        rdr = null;
                                    }
                                    catch (Exception ex)
                                    {
                                        Response.Write("Error getting value to send email to. Error: " + ex.ToString());
                                    }
                                    finally
                                    {
                                        conn.Close();
                                    }
                                }
                            }
                        }
                        foreach (var item in MatrixIDs)
                        {
                            int recId = 0;
                            // Start with saluation
                            string sendmailMessage = "Hello, I have audited the applications you have submitted for review. Please see the 
    attached matrixes below for more information."; // fill in text for email here string sendmailSubject = "Audit results of submitted applications"; string AppName = string.Empty, AuditedIssues = string.Empty, AuditorComments = string.Empty,
    AuditorName = string.Empty, CCEmail = string.Empty, matrixCombined = string.Empty; string SqlIDs = "SELECT RecID, AppName, AuditedIssues, AuditorComments, AuditorName, CCEMail from EvalSent
    WHERE DevEmail = @devEmail"; SqlCommand cmd2 = new SqlCommand(SqlIDs, conn); try { conn.Open(); cmd2.Parameters.AddWithValue("@devEmail", item.ToString()); SqlDataReader rdr2 = cmd2.ExecuteReader(); while (rdr2.Read()) { recId = Convert.ToInt32(rdr2.GetValue(0).ToString()); // RecID AppName = rdr2.GetValue(1).ToString(); // AppName AuditedIssues = rdr2.GetValue(2).ToString(); // AuditedIssues AuditorComments = rdr2.GetValue(3).ToString(); // AuditorComments AuditorName = rdr2.GetValue(4).ToString(); // AuditorName CCEmail = rdr2.GetValue(5).ToString(); matrixCombined = matrixCombined + "<hr><br />Application Name: " + AppName.ToString() + "<br />Audited By:
    " + AuditorName + "Findings:<br /> " + AuditedIssues.ToString() + "<br><br />Auditor Comments:<br />" +
    AuditorComments.ToString(); } } catch (Exception ex) { } finally { string sendmailFrom = "***********************************"; string sendmailTo = item.ToString(); // Use sendmail helper here } } } #endregion BindData();

    Monday, December 19, 2016 7:16 PM

Answers

  • User-1838255255 posted

    Hi bbcompent1,

    According to your description and code, I know is when foreach GridView rows, then get selected data though the ID of the checked row, the second paragraph of code, it is foreach MatrixIDs, then get a datalist  from database.

    bbcompent1

    That part works but I want to only return one email address even if I have three matrices to send out.

    My thought is you want t o combine both of the DataList, if Address ‘A’ appear there times, you want to filter it and only sent out one time instead of three times.  

    if  I misunderstand your idea, please give me more detail of the how to get email addresses and you wanted really.

    Best Regards,

    Eric Du

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 20, 2016 7:24 AM
  • User-1838255255 posted

    Hi bbcompent1,

    According to your description and error message. I think this issue is caused by parameter is a string format, you need use split and convert Toint32 method convert it to multiple int numbers, the pass them to sqlcommand, here is the sample code:

    string a = "1,2,3";
                string[] list = a.Split(',');
                int[] array = new int[3];
                array[0] = Convert.ToInt32(list[0]);
                array[1] = Convert.ToInt32(list[0]);
                array[2] = Convert.ToInt32(list[0]);
                int b = array[0];
                int c = array[1];
                int d = array[2];

    Best Regards,

    Eric Du

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 21, 2016 2:56 AM
  • User-1838255255 posted

    Hi bbcompent1,

    Oh, I think you misunderstand my meaning, you need handle your array use split method, split it to three int parameters, then pass them to SqlCommand! I make a sample for  your reference!

    protected void Button1_Click(object sender, EventArgs e)
            {
                getdata();
            }
            public void getdata()
            {
                GridView2.DataSource = null;
                GridView2.DataBind();
                string a = TextBox1.Text;
                string[] list = a.Split(',');
                int[] array = new int[3];
                for (int i = 0; i < list.Length; i++)
                {
                    array[i] = Convert.ToInt32(list[i]);
                }
                int b = array[0];
                int c = array[1];
                int d = array[2];
    
                string constr = ConfigurationManager.ConnectionStrings["ProductConnectionString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand("select * from Product where ID in(@b,@c,@d)"))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.AddWithValue("@b", b);
                        cmd.Parameters.AddWithValue("@c", c);
                        cmd.Parameters.AddWithValue("@d", d);
                        cmd.Connection = con;
                        con.Open();
                        DataTable dt = new DataTable();
                        SqlDataAdapter ada = new SqlDataAdapter();
                        ada.SelectCommand = cmd;
                        ada.Fill(dt);
                        GridView2.DataSource = dt;
                        GridView2.DataBind();
                        con.Close();
                    }
                }
            }

    Best Regards,

    Eric Du

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 23, 2016 9:48 AM

All replies

  • User-1838255255 posted

    Hi bbcompent1,

    According to your description and code, I know is when foreach GridView rows, then get selected data though the ID of the checked row, the second paragraph of code, it is foreach MatrixIDs, then get a datalist  from database.

    bbcompent1

    That part works but I want to only return one email address even if I have three matrices to send out.

    My thought is you want t o combine both of the DataList, if Address ‘A’ appear there times, you want to filter it and only sent out one time instead of three times.  

    if  I misunderstand your idea, please give me more detail of the how to get email addresses and you wanted really.

    Best Regards,

    Eric Du

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 20, 2016 7:24 AM
  • User-718146471 posted

    if Address ‘A’ appear there times, you want to filter it and only sent out one time instead of three times.  

    You understood what I am trying to do exactly. So I will wait for your response :)

    Tuesday, December 20, 2016 1:53 PM
  • User-718146471 posted

    Ok, I'm closing in on what my problem is now. I got an error in the ex error catch: " {"Conversion failed when converting the nvarchar value '1060,1061,1062' to data type int."} "

    How can I get this to convert to the correct format?

    "string SqlIDs = "SELECT AppName, AuditedIssues, AuditorComments, AuditorName, CCEMail from EvalSent WHERE RecID in (CAST(@matrixID AS NVARCHAR(50)))";"

    Tuesday, December 20, 2016 9:12 PM
  • User-1838255255 posted

    Hi bbcompent1,

    According to your description and error message. I think this issue is caused by parameter is a string format, you need use split and convert Toint32 method convert it to multiple int numbers, the pass them to sqlcommand, here is the sample code:

    string a = "1,2,3";
                string[] list = a.Split(',');
                int[] array = new int[3];
                array[0] = Convert.ToInt32(list[0]);
                array[1] = Convert.ToInt32(list[0]);
                array[2] = Convert.ToInt32(list[0]);
                int b = array[0];
                int c = array[1];
                int d = array[2];

    Best Regards,

    Eric Du

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 21, 2016 2:56 AM
  • User-718146471 posted

    Ok, let me give that a go. I had a feeling that is what it was but could not be sure. Thanks!

    Wednesday, December 21, 2016 11:54 AM
  • User-718146471 posted

    Eric, I think I am stuck. How can I pass the array to the SQL query? I've tried, but it always takes the first element and stops there, even though I use While (rdr.Read()) { }

    Thursday, December 22, 2016 7:23 PM
  • User-1838255255 posted

    Hi bbcompent1,

    Oh, I think you misunderstand my meaning, you need handle your array use split method, split it to three int parameters, then pass them to SqlCommand! I make a sample for  your reference!

    protected void Button1_Click(object sender, EventArgs e)
            {
                getdata();
            }
            public void getdata()
            {
                GridView2.DataSource = null;
                GridView2.DataBind();
                string a = TextBox1.Text;
                string[] list = a.Split(',');
                int[] array = new int[3];
                for (int i = 0; i < list.Length; i++)
                {
                    array[i] = Convert.ToInt32(list[i]);
                }
                int b = array[0];
                int c = array[1];
                int d = array[2];
    
                string constr = ConfigurationManager.ConnectionStrings["ProductConnectionString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand("select * from Product where ID in(@b,@c,@d)"))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.AddWithValue("@b", b);
                        cmd.Parameters.AddWithValue("@c", c);
                        cmd.Parameters.AddWithValue("@d", d);
                        cmd.Connection = con;
                        con.Open();
                        DataTable dt = new DataTable();
                        SqlDataAdapter ada = new SqlDataAdapter();
                        ada.SelectCommand = cmd;
                        ada.Fill(dt);
                        GridView2.DataSource = dt;
                        GridView2.DataBind();
                        con.Close();
                    }
                }
            }

    Best Regards,

    Eric Du

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 23, 2016 9:48 AM
  • User-718146471 posted

    OK, that makes sense. Now, what if I need to do the listing of IDs dynamically? Say one email address there are four IDs, the next email address has two, and so on? There must be some way. I'll try experimenting unless you know right off Eric. Thank you!

    Friday, December 23, 2016 12:26 PM
  • User-718146471 posted

    I think I just found an answer:

    string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
    string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";
    
    string[] paramNames = tags.Select(
        (s, i) => "@tag" + i.ToString()
    ).ToArray();
    
    string inClause = string.Join(",", paramNames);
    using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
        for(int i = 0; i < paramNames.Length; i++) {
           cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
        }
    }

    I'll give this a go and let you know. Thanks for all your help thus far. :)

    Friday, December 23, 2016 12:31 PM
  • User-718146471 posted

    Oh I am so close I can taste it. I'm trying to assign the values from the array to tags[].

    string[] tags = new string[] { MatrixIDs.ToString() };

    The array MatrixIDs does indeed have elements in it. Should I use MatrixIDs instead of the call out to tags?

    Friday, December 23, 2016 2:04 PM
  • User-718146471 posted

    Folks, I'm going to split the last reply off into its own thread since the basics of the email sorting is done. I'll mark the answers to my question now.

    Friday, December 23, 2016 7:42 PM