如何在SQL数据视图中将SQL查询中的数据显示为超链接 [英] How to show data from SQL query as hyperlink in datagridview

问题描述

我正在从我的数据库填充我的datagridview，如下所示

I am populating my datagridview from my database as following

con.Open();
            SqlCommand cmd = new SqlCommand("select consumerData01.consumerSubDivision as 'Sub-Division',COUNT (*) as TOTAL,"
    + "SUM(case when Office_AppStatus= 'Demand Notice Issued' then 1 else 0 end) as 'Demand Notice Issued',"
    + "SUM(case when Office_AppStatus= 'Survey Report' then 1 else 0 end)as 'Survey',"
    + "SUM(case when Office_AppStatus= 'Rejected' then 1 else 0 end)as 'Rejected',"
    + "SUM(case when Office_AppStatus= 'Submitted' then 1 else 0 end)as 'Submitted',"
    + "SUM(case when Office_AppStatus= 'Meter Installed' then 1 else 0 end)as 'Meter Installed'"
    + "from OfficeDat01 "
    + "inner join consumerData01 on consumerAppRegNo = OfficeDat01.Office_AutoCode "
    + "where consumerData01.consumerRegDate between '" + d1 + "' and '" + d2 + "'"
    + " and consumerSubDivision like '" + s + "%'"
    + "group by consumerSubDivision ", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            con.Close();
            if (ds.Tables[0].Rows.Count > 0)
            {
              
                GridView1.DataSource = ds;
                GridView1.DataBind();


            }
            else {

                 GridView1.DataSource = "";
                 GridView1.DataBind();
                 lblMsg.Font.Bold = true;
                 lblMsg.ForeColor = System.Drawing.Color.Red;
                 lblMsg.Text = "No Record Found";

这部分工作正常但现在我想在数据网格视图中显示值作为超链接而不是文本，这样当我点击特定链接时它应该导航到页面显示该记录的详细信息。



我的尝试：



我我试过以下但它只适用于第二列我想对所有具有数值的列做同样的事情。

this part is working fine but now I want to show the values as hyper link in datagrid view instead text so that when I click on specific link it should navigate to page showing details of that record.

What I have tried:

I have tried following but it only works for 2nd column only I want to do the same with all columns having numeric value.

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if(e.Row.RowType == DataControlRowType.DataRow)
    {
      // Get the value in the hyperlink column.
      string HyperLinkValue = e.Row.Cells[1].Text;
      
      HyperLink myLink = new HyperLink();
      myLink.NavigateUrl = HyperLinkValue;
      myLink.Text = HyperLinkValue;
      e.Row.Cells[1].Controls.Add(myLink);
    }
    }

推荐答案

首先修复 SQL注入 [ ^ ]漏洞：

Start by fixing the SQL Injection[^] vulnerability in your code:

SqlCommand cmd = new SqlCommand("select consumerData01.consumerSubDivision as 'Sub-Division',COUNT (*) as TOTAL,"
    + "SUM(case when Office_AppStatus= 'Demand Notice Issued' then 1 else 0 end) as 'Demand Notice Issued',"
    + "SUM(case when Office_AppStatus= 'Survey Report' then 1 else 0 end)as 'Survey',"
    + "SUM(case when Office_AppStatus= 'Rejected' then 1 else 0 end)as 'Rejected',"
    + "SUM(case when Office_AppStatus= 'Submitted' then 1 else 0 end)as 'Submitted',"
    + "SUM(case when Office_AppStatus= 'Meter Installed' then 1 else 0 end)as 'Meter Installed'"
    + "from OfficeDat01 "
    + "inner join consumerData01 on consumerAppRegNo = OfficeDat01.Office_AutoCode "
    + "where consumerData01.consumerRegDate between @d1 and @d2"
    + " and consumerSubDivision like @s + '%' "
    + "group by consumerSubDivision ", con);

cmd.Parameters.AddWithValue("@d1", d1);
cmd.Parameters.AddWithValue("@d2", d2);
cmd.Parameters.AddWithValue("@s", s);

然后，将所需列更改为超链接。如果你正在使用 GridView 控制 [ ^ ]，使用 a HyperLinkField [ ^ ]。如果你正在使用 DataGrid 控制 [ ^ ]，使用 a HyperLinkColumn [ ^ ]。



注意： ASP.NET中没有DataGridView控件，所以从您的问题中不清楚您正在使用哪种控件。 />


例如：

Then, change the required columns to hyperlinks. If you're using the GridView control[^], use a HyperLinkField[^]. If you're using the DataGrid control[^], use a HyperLinkColumn[^].

NB: There is no "DataGridView" control in ASP.NET, so it's not clear from your question which control you're using.

Eg:

<asp:HyperLinkField
    HeaderText="Demand Notice Issued"
    DataTextField="Demand Notice Issued"
    DataNavigateUrlFields="Sub-Division"
    DataNavigateUrlFormatString="~/ViewSubDivision.aspx?id={0}"
/>

这将生成一个超链接，其文本设置为发布的需求通知的值字段，并将URL设置为〜/ ViewSubDivision.aspx？id = ... ，将sub-Division字段的值放在id =之后。

---

你想知道关于SQL注入的一切（但不敢问）|特洛伊亨特 [ ^ ]

如何在没有技术术语的情况下解释SQL注入？ |信息安全堆栈交换 [ ^ ]

查询参数化备忘单| OWASP [ ^ ]

This will generate a hyperlink with the text set to the value of the "Demand Notice Issued" field, and the URL set to ~/ViewSubDivision.aspx?id=..., putting the value of the "Sub-Division" field after the "id=".

---

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

这篇关于如何在SQL数据视图中将SQL查询中的数据显示为超链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！