How do I send email to each user listed in the gridview individually by clicking on the sanction button?

Question

I created a gridview on my approval.aspx page. I am using VS 2014 and SQL Server 2014.

<asp:GridView ID="gv_pending_requests" runat="server" AutoGenerateColumns="False" Width="95%" CellPadding="1" CellSpacing="2">
    <Columns>
        <asp:BoundField HeaderText="Requested By" DataField="username" />                                
        <asp:BoundField HeaderText="No. of Days" DataField="total_days" />
        <asp:BoundField HeaderText="Type of Leave" DataField="leave_type" />
        <asp:BoundField HeaderText="Reason" DataField="reason" />
        <asp:BoundField HeaderText="Starting" DataField="start_date"/>
        <asp:BoundField HeaderText="Ending" DataField="end_date" />

        <asp:TemplateField HeaderText="Sanction">
            <ItemTemplate>
                <asp:Button ID="btn_apprv" Text="Sanction" runat="server" OnClick="btn_apprv" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Deny">
            <ItemTemplate>
                <asp:Button ID="btn_deny" Text="Deny" runat="server" OnClick="btn_deny"/>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

//Now, the code from .cs file:

lbl_uid.Text = Convert.ToString(Session["Username"]);
string connectionstring = @"Data Source=server;Integrated Security=true   Initial Catalog=E_M_S"; 

SqlDataReader rdr;
DataTable dt = new DataTable();
dt.Columns.Add("Requested By");
dt.Columns.Add("Type of Leave");
dt.Columns.Add("Reason");
dt.Columns.Add("Starting");
dt.Columns.Add("Ending");
dt.Columns.Add("No. of Days");

DataRow dr;
SqlConnection conn = new SqlConnection(connectionstring);

SqlCommand cmd = new SqlCommand("select leave_type,reason,start_date,end_date from Leave where status='" + lbl_status.Text + "'", conn);

cmd.CommandType = CommandType.Text;
using (conn)
{
    conn.Open();
    rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        dr = dt.NewRow();
        dr["Requested By"] = rdr["username"].ToString();
        dr["Type of Leave"] = rdr["leave_type"].ToString();
        dr["Reason"] = rdr["reason"].ToString();
        dr["Starting"] = rdr["start_date"].ToString();
        dr["Ending"] = rdr["end_date"].ToString();
        dr["No. of Days"] = rdr["total_days"].ToString();

        dt.Rows.Add(dr);
        dt.AcceptChanges();
        gv_pending_requests.DataSource = dt;
        gv_pending_requests.DataBind();
        conn.Close();
    }
} 

//Button for sending the E-Mail
protected void btn_apprv(object sender, EventArgs e)
{
    Response.Write("<script type='javascript'>alert('The leave has been sanctioned, and mail has been sent to inform the employee of the same.');</script>");
}

Problem1 : It shows below error pointing at the line :- gv_pending_requests.DataSource = dt;

"a field or property with the name 'username' was not found on the selected data source"

Problem 2: I want that a mail be sent to each user according to the button pressed for that row. How do i define an on click event with that mail code?

Please forgive me for missing details,if any. Please help me with this error.

score 1 · Accepted Answer · edited May 23 '17 at 12:05

To your Problem 1:

In your SQL-Select-Statement you don't select for the username:

SqlCommand cmd = new SqlCommand("select leave_type,reason,start_date,end_date from Leave where status='" + lbl_status.Text + "'", conn);

Instead this code should be:

SqlCommand cmd = new SqlCommand("select username,leave_type,reason,start_date,end_date from Leave where status='" + lbl_status.Text + "'", conn);

Otherwise - as stated by the errormessage - the DataReader you use cannot find the column username in the result from the database. You can test this if you go to your database and fire the SQL-statement there directly, you will see that you do not get the usernames back.

Also, you defined your DataTable with the column Requested by.

dt.Columns.Add("Requested By");

But if you look to your BoundField now, you will see that the DataField is defined as "username":

<asp:BoundField HeaderText="Requested By" DataField="username" />

Either you change the column-name in your DataTable to "username" or you change the DataField in the BoundField to "Requested By".

To your second Problem:

Please read the How-To-Ask-FAQ:

Include just enough code to allow others to reproduce the problem. For help with this, read How to create a Minimal, Complete, Valid Example.

You don't provide any research or a minimal example which shows us that you at least tried to find a solution.

So I will only give you hints for your "problem", if you got any question then regarding a not working code, please post a new question.

Get Cell Value in C# Gridview when Button clicked on row

How to send email in ASP.NET C#

Use the first link to get the user where the E-Mail should be sent to, and the second link provides the logic to send a E-Mail. Good luck!

Thank you for replying. Regarding including username, I did rerun the query with it and ran it. Still the same error on the databind statement. — avneesh, Oct 29 '14 at 08:29
i corrected what you suggested, and for all datafields. now the error is "{"Invalid attempt to call Read when reader is closed."}" on the line while (rdr.Read()) — avneesh, Oct 29 '14 at 08:56
nevermind. just solved it! it was a silly mistake. had to move the databind and close statements out of the while loop. Thank you so much for the help! — avneesh, Oct 29 '14 at 08:58

How do I send email to each user listed in the gridview individually by clicking on the sanction button?

1 Answers1