How to set HyperLinkField NavigateURL to SQL Column Values C# ASP.Net Web Forms

Question

I'm trying to build out a gridview and a few of the columns need to use a HyperLinkField to display the text from one field while linking to the text from another. In this case the text that I'm trying to use for the NavigateURL is the name of a pdf. The method I'm currently using to get the text for the HyperLinkField's NavigateURL is only grabbing the first value from the field in the query though, so the correct text will display, but the NavigateURL will be incorrect for everything except the first value.

Here is the gridview in the aspx page.

<asp:GridView ID="PaperProp" RowStyle-BackColor="#f4f4f4" HeaderStyle-BackColor="#CFDBE5" runat="server" AutoGenerateColumns="False" >
                        <columns>
                        </columns>
                        </asp:GridView> 

And here is the code from the code behind that I'm using to build out the gridview

string PaperPropQuery = "SELECT bidd_number, bidd_name, bidd_desc, prebid_req, open_dt, buyer, bidd_status, Addendum FROM vPaper_Proposals ORDER BY CONVERT(DateTime, open_dt,101) DESC";
        SqlCommand cmd2 = new SqlCommand(PaperPropQuery, new SqlConnection(ConStr));

        cmd2.Connection.Open();
        var cmdr2 = cmd2.ExecuteReader();

            if (cmdr2.Read())
            {
                LinkField = new HyperLinkField();

                LinkField.HeaderText = "RFP No.";
                LinkField.DataTextField = "bidd_number";
                LinkField.HeaderStyle.CssClass = "GVpadding";
                LinkField.ItemStyle.CssClass = "GVpadding";
                LinkField.NavigateUrl = "./bids/" + cmdr2["bidd_name"].ToString();
                LinkField.Target = "_blank";

                PaperProp.Columns.Add(LinkField);

                TextField = new BoundField();

                TextField.HeaderText = "Description";
                TextField.DataField = "bidd_desc";
                TextField.HeaderStyle.CssClass = "GVpadding";
                TextField.ItemStyle.CssClass = "GVpadding";

                PaperProp.Columns.Add(TextField);

                LinkField = new HyperLinkField();

                LinkField.HeaderText = "PreBid";
                LinkField.DataTextField = "prebid_req";
                LinkField.HeaderStyle.CssClass = "GVpadding";
                LinkField.ItemStyle.CssClass = "GVpadding";

                PaperProp.Columns.Add(LinkField);

                TextField = new BoundField();

                TextField.HeaderText = "Open Date";
                TextField.DataField = "open_dt";
                TextField.HeaderStyle.CssClass = "GVpadding";
                TextField.ItemStyle.CssClass = "GVpadding";

                PaperProp.Columns.Add(TextField);

                LinkField = new HyperLinkField();

                LinkField.HeaderText = "Buyer";
                LinkField.DataTextField = "buyer";
                LinkField.HeaderStyle.CssClass = "GVpadding";
                LinkField.ItemStyle.CssClass = "GVpadding";
                LinkField.NavigateUrl = "";

                PaperProp.Columns.Add(LinkField);

                TextField = new BoundField();

                TextField.HeaderText = "Status";
                TextField.DataField = "bidd_status";
                TextField.HeaderStyle.CssClass = "GVpadding";
                TextField.ItemStyle.CssClass = "GVpadding";

                PaperProp.Columns.Add(TextField);

                LinkField = new HyperLinkField();

                LinkField.HeaderText = "Addendum";
                LinkField.DataTextField = "Addendum";
                LinkField.HeaderStyle.CssClass = "GVpadding";
                LinkField.ItemStyle.CssClass = "GVpadding";

                PaperProp.Columns.Add(LinkField);

                cmdr2.Close();

                PaperProp.DataSource = cmd2.ExecuteReader();
                PaperProp.DataBind();

                cmd2.Connection.Close();
                cmd2.Connection.Dispose();
            }
            else
            {
                PaperProplbl.Text = "None at this time.";
            }

I don't code much and I'm pretty new to C#, so I'm aware that this is probably a terrible way to go about querying a DB and building a gridview, because of that I'm open to any recommendations on how to improve the code, but my main question is how can I get

LinkField.NavigateUrl = "./bids/" + cmdr2["bidd_name"].ToString();

to contain the bidd_name of each row in the field instead of just the first one?

Answer 1

Found my own answer. You can use DataNavigateUrlFields and DataNavigateUrlFormatString to create urls that represent your query's data.

        string PaperInfoQuery = @"SELECT pi.bidd_number, pi.bidd_name, pi.bidd_desc, pi.prebid_req, pi.open_dt, pi.buyer, 
                                pi.bidd_status, pi.Addendum, ct.code_description AS Email FROM vPaperInformation pi LEFT OUTER JOIN 
                                Code_Table ct ON ct.code_table_cd = pi.buyer + ' EM' ORDER BY CONVERT(DateTime, open_dt,101) DESC";
        string ConStr = "server=sqltest;database=OnlineBidders;UID=xxxx;password=xxxx";
        SqlCommand cmd = new SqlCommand(PaperInfoQuery, new SqlConnection(ConStr));

        cmd.Connection.Open();

        var cmdr = cmd.ExecuteReader();

        HyperLinkField LinkField = new HyperLinkField();
        BoundField TextField = new BoundField();

        if (cmdr.Read())
        {
            string[] UrlFields = { "bidd_name" };

            LinkField.HeaderText = "RFI No.";
            LinkField.DataTextField = "bidd_number";
            LinkField.HeaderStyle.CssClass = "GVpadding";
            LinkField.ItemStyle.CssClass = "GVpadding";
            LinkField.DataNavigateUrlFields = UrlFields;
            LinkField.DataNavigateUrlFormatString = "./bids/{0}";
            LinkField.Target = "_blank";