ASP.NET bound hyperlink as SQL Server stored procedure parameter?-CodePudding

I've currently got a gridview that is populated with summary data. I've created a hyperlink from a bound field labeled ticket_num. What I'm wanting to accomplish is to click the hyperlink and have that call a stored procedure. I need to pass that text of that hyperlink into the stored procedure in SQL Server. So the flow is something like this...

User clicks link
The text of that hyperlink is passed into a parameter for the SQL Server stored procedure to use
Call the stored procedure and display results on new page

Any ideas? The stored procedure is created, connection into the server via ASP.NET is created. Everything works thus far but I can't figure this piece of it out.

CodePudding user response：

Well, since you want some code to execute, then LITTLE reason exists to use a hyper-link, since that not what you need nor want.

You don't mention if you prefer to "hide" the URL and hyper link anyway? (often for security, this is not all such a bad idea).

so, if you need to hide/not show/don't want the value in the grid, then our simple button click can get the row id of the grid (also hidden), and then get the value, and then pass to sql server.

Or, you can add to the button the URL or value to the button - just use command argument. That way you don't have to hit the database again based say on row PK id.

So, here is a simple grid. I have the URL as a row on the grid, but lets also shove/put it into the button for you to see how this works.

So, simple grid:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
    DataKeyNames="ID" CssClass="table" >
    <Columns>
        <asp:BoundField DataField="Fighter" HeaderText="Fighter"  />
        <asp:BoundField DataField="Engine" HeaderText="Engine"  />
        <asp:BoundField DataField="Thrust" HeaderText="Thrust"  />
        <asp:BoundField DataField="Description" HeaderText="Description" />

        <asp:TemplateField HeaderText="Preview">
            <ItemTemplate>
                <asp:Image ID="Image2" runat="server" ImageUrl = '<%# Eval("ImagePath") %>' Width="140" />
            </ItemTemplate>
        </asp:TemplateField>

        <asp:TemplateField HeaderText="View">
            <ItemTemplate>
                <asp:Button ID="cmdView" runat="server" Text="View" CssClass="btn" 
                    CommandArgument = '<%# Eval("ImagePath") %>'  OnClick="cmdView_Click" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

And our code to load up the above:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadGridF()
    End If

End Sub

Sub LoadGridF()

    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand("SELECT * FROM Fighters", conn)
            conn.Open()
            Dim rstData = New DataTable
            rstData.Load(cmdSQL.ExecuteReader)
            GridView1.DataSource = rstData
            GridView1.DataBind()
        End Using
    End Using

End Sub

And now we have this:

Note how we have a PLAIN JANE button - after all, it not really a hyper link we need, is it?

So, for our button click event, we have this:

Protected Sub cmdView_Click(sender As Object, e As EventArgs)

    Dim btn As Button = sender
    Dim gRow As GridViewRow = btn.NamingContainer

    Debug.Print("Row index click = " & gRow.RowIndex)

    ' get database row PK id

    Dim iPK As Integer = GridView1.DataKeys(gRow.RowIndex).Item("ID")

    ' now we have database row PK - walk the dog, do payroll processing
    ' do ANYTHING we want like get the database row etc.
    Debug.Print("Data base row PK id = " & iPK)

    Debug.Print("Command button arugment = " & btn.CommandArgument)

End Sub

And output is then:

So, you can see, we did not need (or want a hyper link), but a simple button, and that click event can:

get any value from the given grid row
get the database PK id (hidden - never exposed to client side (good security).
get the row click index
get parameters passed to button

And we can use gRows.Cells() to get the other row values (for data fields)

And of course gRow.FindControl("ctrl name") for templated columns.