so how to use the like Text39lt Bind34Phone34 gt39 statement in the pivot queryhow to send sms to selected students if write the following format in another pages it is sending the smsltdiv class34tableresponsive34gtltaspGridView ID34GridView134 runat34server34 AllowPaging34false34 Class34table tablestriped tablebordered tablehover34 RowStyleWrap34false34 HeaderStyleWrap34false34gtltColumnsgtltaspTempl

Hi smileIn your query return result add row number which will help you to get checked row and if you did not want to show that row number in gridview make it visible false OnRowDataBound and after that make a loop in send button click event and find your checkbox control and get datarow In datarow select datatable record according to row number and then get your selected value and use it in your send codePlease refer below exampleHTMLltaspGridView ID34GridView134 runat34server34 OnRowDataBound34GridView1RowDataBound34gt ltColumnsgt ltaspTemplateFieldgt ltHeaderTemplategt ltaspCheckBox ID34checkAll34 runat34server34 onclick34checkAllthis34 gt ltHeaderTemplategt ltItemTemplategt ltaspCheckBox ID34chkSelect34 runat34server34 gt ltItemTemplategt ltaspTemplateFieldgt ltColumnsgtl

Bind dynamically generated columns in GridView for sending sms in ASP.Net

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on Oct 15, 2018 11:44 PM
Forum: Data Controls
Answer: 2
Views: 2428
Sample Code: Download

so how to use the like

Text='<%# Bind("Phone") %>' statement in the pivot query?

fhjsdhjfhdsf

how to send sms to selected students. if write the following format in another pages it is sending the sms

<div class="table-responsive">
<asp:GridView ID="GridView1" runat="server" AllowPaging="false" Class="table table-striped table-bordered table-hover" RowStyle-Wrap="false" HeaderStyle-Wrap="false">
<Columns>

<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="checkAll" runat="server" onclick = "checkAll(this);" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server" onclick = "Check_Click(this)" />
</ItemTemplate>
</asp:TemplateField>

</Columns>
</asp:GridView>

</div>

con = new SqlDbConnect();
string myqry = @"DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME([SubjectName])
FROM (SELECT DISTINCT SubjectName FROM tblTestSystem as sm inner join tblAssignSubjects as si on si.ASID = sm.SubjectID INNER JOIN tblDefSubject AS dsu ON si.SubjectID=dsu.SubjectID) AS Course

DECLARE @qry NVARCHAR(4000)
SET @qry =
N'
;WITH cteStudentMarksDetails
AS ( SELECT TA.AdmissionNo
,(SELECT SName FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as SName
,(SELECT FName FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as FName
,(SELECT SPhone FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as Phone
,SUM(Marks) ObtainedMarks
,SUM([MaxMarks]) TotalMarks
,Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) as Percentage
,(CASE WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 90 THEN ''A+''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 80 THEN ''A''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 70 THEN ''B''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 60 THEN ''C''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 50 THEN ''D''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 40 THEN ''E''
ELSE ''FAIL'' END ) AS Grade
,(CASE WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 90 THEN ''OutStanding''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 80 THEN ''Excellent''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 70 THEN ''Very Good''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 60 THEN ''Good''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 50 THEN ''Satisfactory''
WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 40 THEN ''Work Hard''
ELSE ''FAIL'' END ) AS Remarks
FROM tblTestSystem TA
GROUP BY AdmissionNo
)

SELECT distinct AdmissionNo
,SName
,FName
,Phone
, ' + @cols + '
,ObtainedMarks
,TotalMarks
,Percentage
,Grade
,Remarks
FROM (SELECT AdmissionNo
,SubjectName
, Marks
,(SELECT ObtainedMarks FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as ObtainedMarks
,(SELECT Percentage FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Percentage
,(SELECT TotalMarks FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as TotalMarks
,(SELECT Grade FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Grade
,(SELECT Remarks FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Remarks
,(SELECT SName FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as SName
,(SELECT FName FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as FName
,(SELECT Phone FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Phone
FROM tblTestSystem as sm

inner join tblAssignSubjects as si on si.ASID = sm.SubjectID
INNER JOIN tblDefSubject AS dsu ON si.SubjectID=dsu.SubjectID


Where sm.ClassID=('+@CId+') and sm.SectionID=('+@SId+')
) p
PIVOT (MAX(Marks) FOR SubjectName IN ('+@cols+')) AS Pvt'
EXEC sp_executesql @qry;";

con.SqlQuery(myqry);

con.Cmd.Parameters.Add(new SqlParameter("@CId", this.ddlClass.SelectedValue.ToString()));
con.Cmd.Parameters.Add(new SqlParameter("@SId", this.ddlSection.SelectedValue.ToString()));
adapt.SelectCommand = con.Cmd;
adapt.Fill(sTable);
if (sTable.Rows.Count > 0)
{
GridView1.Visible = true;
GridView1.DataSource = sTable;
GridView1.DataBind();
lblTotal.Text = GridView1.Rows.Count.ToString();
}
else
{
GridView1.Visible = false;
lblTotal.Text = "0";
ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('No Record Found');", true);
return;
}
con.conClose();

                <asp:TemplateField HeaderText="Applicant Name">
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("StdName") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Left" />
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Mobile No.">
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("Phone") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                </asp:TemplateField>

Download FREE API for Word, Excel and PDF in ASP.Net

pandeyis...

0 2

Reply
ANSWER
User: pandeyism
Replied: on Oct 16, 2018 03:31 AM
Report

Hi smile,

In your query return result add row number which will help you to get checked row and if you did not want to show that row number in gridview make it visible false OnRowDataBound and after that make a loop in send button click event and find your checkbox control and get datarow. In datarow select datatable record according to row number and then get your selected value and use it in your send code.

Please refer below example.

HTML

<asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound">
    <Columns>
        <asp:TemplateField>
            <HeaderTemplate>
                <asp:CheckBox ID="checkAll" runat="server" onclick="checkAll(this);" />
            </HeaderTemplate>
            <ItemTemplate>
                <asp:CheckBox ID="chkSelect" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<asp:Button Text="Send" runat="server" OnClick="Send" />

Namespaces

using System.Data.SqlClient;
using System.Data;
using System.Configuration;

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable sTable = GetPivotedDataTable();
        if (sTable.Rows.Count > 0)
        {
            GridView1.Visible = true;
            GridView1.DataSource = sTable;
            GridView1.DataBind();
            lblTotal.Text = GridView1.Rows.Count.ToString();
        }
        else
        {
            GridView1.Visible = false;
            lblTotal.Text = "0";
            ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('No Record Found');", true);
            return;
        }
    }
}

private DataTable GetPivotedDataTable()
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Constring"].ConnectionString);
    string myqry = @"DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
                    DECLARE @ColumnName AS NVARCHAR(MAX)
                    SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(Date) FROM (SELECT DISTINCT [Date] FROM Pvt) AS [Date]
                    SET @DynamicPivotQuery = ';WITH CTE AS(SELECT [Date],Gender,Participation FROM Pvt)
                    SELECT ROW_NUMBER() OVER(ORDER BY(Gender))Row,Gender,'+@ColumnName+' FROM CTE
                    PIVOT (MAX(Participation) FOR [Date] IN('+@ColumnName+')) p
                    ORDER BY Gender DESC'
                    EXEC(@DynamicPivotQuery)";

    SqlCommand cmd = new SqlCommand(myqry, con);
    SqlDataAdapter adapt = new SqlDataAdapter();
    DataTable sTable = new DataTable();
    adapt.SelectCommand = cmd;
    adapt.Fill(sTable);
    con.Close();

    return sTable;
}

protected void Send(object sender, EventArgs e)
{
    DataTable dt = GetPivotedDataTable();
    foreach (GridViewRow row in GridView1.Rows)
    {
        CheckBox chk = (row.Cells[0].FindControl("chkSelect") as CheckBox);
        if (chk.Checked)
        {
            DataRow dr = dt.Select("Row=" + row.Cells[1].Text)[0];
            string gender = dr["Gender"].ToString();
            // Code for sending sms as per the DataRow.
        }
    }
}

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.Header)
    {
        e.Row.Cells[1].Visible = false;
    }
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        e.Row.Cells[1].Visible = false;
    }
}

dharmend...

0 4

Reply
ANSWER
User: dharmendr
Replied: on Oct 17, 2018 06:35 AM
Report

You need to add ROW_NUMBER() function in your query to return the RowNumber as provided in the sample example.

Rest your query will be same and modify the code as in the sample example.

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Bind dynamically generated columns in GridView for sending sms in ASP.Net

Company

Explore

Follow us

Contact Us