locked
Using SelectParameters in an AccessDataSource RRS feed

  • Question

  • User1840655121 posted

    Hello, new to VisualWebDesigner, long-time VB programmer.

    I have a GridView on my page, and can successfully fill a Label with the value of a cell (OrderNumber).

    How can I pass this as a variable to an AccessDataSource SELECT query??

     Here's my code, warts and all:

     

    <%@ Page Language="VB" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <script runat="server">

     

    Dim szSO As String

    Protected Sub gvOrders_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs)

    ' Retrieve the row index stored in the CommandArgument property.

    Dim index As Integer = Convert.ToInt32(e.CommandArgument)

    ' Retrieve the row that contains the button from the Rows collection.

    Dim row As GridViewRow = gvOrders.Rows(index)

    ' Set up a placeholder for the new Tasks gridview

    Dim gvNewTasks As New GridView

     

    With row

    szSO = .Cells.Item(1).Text

    Label1.Text =
    "You just selected SO# " & szSO

    End With

    gvNewTasks.ID = "NewTasksGridView"

    gvNewTasks.DataSourceID = "SpecificTasks"

    gvNewTasks.AutoGenerateColumns = True

    ' Add the GridView object to the Controls collection

    ' of the PlaceHolder control.

    PlaceHolder1.Controls.Add(gvNewTasks)

     

    ' Add a spacer in the form of an HTML <br /> element

    Dim spacer As LiteralControl = New LiteralControl("<br />")

    PlaceHolder1.Controls.Add(spacer)

    End Sub

     

    </script>

    <html xmlns="http://www.w3.org/1999/xhtml">

    <head runat="server">

    <title></title>

    <style type="text/css">

    .style1

    {

    font-family: Haettenschweiler;

    font-style: italic;

    font-size: xx-large;text-align: center;

    }

    </style>

    </head>

    <body>

    <p class="style1">

    Precision System Tasks</p>

    <form id="form1" runat="server">

    <div>

     

    <asp:AccessDataSource ID="tblTasks" runat="server"

    DataFile="~/App_Data/PSITaskLists.mdb"

    SelectCommand="SELECT tblTasks.fldSalesOrder, tblTasks.fldRevision, tblTasks.fldAssignmentDate, tblTasks.fldEmployee, tblTasks.fldTask, tblTasks.fldInitials, tblTasks.fldCompletionDate FROM (tblSalesOrders INNER JOIN tblTasks ON tblSalesOrders.fldSalesOrder = tblTasks.fldSalesOrder AND tblSalesOrders.fldRevision = tblTasks.fldRevision AND tblSalesOrders.fldRevision = tblTasks.fldRevision) WHERE (tblTasks.fldRevision = 'A') ORDER BY tblTasks.fldAssignmentDate, tblTasks.fldEmployee">

    </asp:AccessDataSource>

     

    <asp:AccessDataSource ID="SpecificTasks" runat="server"

    DataFile="~/App_Data/PSITaskLists.mdb"

    SelectCommand="SELECT fldSalesOrder AS Order, fldRevision AS Rev, fldAssignmentDate AS [Assignment Date], fldEmployee AS Employee, fldTask AS Task, fldInitials AS Initials, fldCompletionDate AS [Completion Date] FROM tblTasks WHERE (fldSalesOrder = @szSO)">

    <SelectParameters>

    <asp:Parameter Name=szSO Type=Int32 />

    </SelectParameters>

    </asp:AccessDataSource>

     

    <asp:AccessDataSource ID="tblSalesOrder" runat="server"

    DataFile="~/App_Data/PSITaskLists.mdb"

    SelectCommand="SELECT tblSalesOrders.fldSalesOrder, Max(tblSalesOrders.fldRevision) AS Revision, First(tblSalesOrders.fldPSICustomerID) AS Customer, Max(tblSalesOrders.fldDueDate) AS DueDate, First(tblSalesOrders.fldDescription) AS Description FROM tblSalesOrders GROUP BY tblSalesOrders.fldSalesOrder">

    </asp:AccessDataSource>

    <asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="False"

    DataKeyNames="fldSalesOrder,Revision" DataSourceID="tblSalesOrder"

    OnRowCommand="gvOrders_RowCommand" BorderStyle="Double" BorderWidth="4px"

    Caption="Current System Orders" CaptionAlign="Top" CellPadding="10"

    HorizontalAlign="Center">

    <Columns>

    <asp:CommandField ButtonType="Button" HeaderText="Click To Select"

    ShowHeader="True" ShowSelectButton="True">

    <ItemStyle HorizontalAlign="Center" />

    </asp:CommandField>

    <asp:BoundField DataField="fldSalesOrder" HeaderText="Order #" ReadOnly="True"

    SortExpression="fldSalesOrder" >

    <ItemStyle HorizontalAlign="Center" />

    </asp:BoundField>

    <asp:BoundField DataField="Revision" HeaderText="Revision" ReadOnly="True"

    SortExpression="Revision">

    <ItemStyle HorizontalAlign="Center" />

    </asp:BoundField>

    <asp:BoundField DataField="Customer" HeaderText="Customer" ReadOnly="True"

    SortExpression="Customer">

    <ItemStyle HorizontalAlign="Center" />

    </asp:BoundField>

    <asp:BoundField DataField="Description" HeaderText="Description"

    ReadOnly="True" SortExpression="Description">

    <ItemStyle HorizontalAlign="Center" />

    </asp:BoundField>

    <asp:BoundField DataField="DueDate" HeaderText="DueDate" ReadOnly="True"

    SortExpression="DueDate">

    <ItemStyle HorizontalAlign="Center" />

    </asp:BoundField>

    </Columns>

    </asp:GridView>

    <br />

    <br />

    <br />

    <asp:Label ForeColor="Crimson" ID="Label1" runat="server"></asp:Label>

    <br />

    <br />

    <asp:PlaceHolder ID="PlaceHolder1" runat="server"></asp:PlaceHolder>

    <br />

    <br />

    <asp:GridView ID="gvTasks" runat="server" AutoGenerateColumns="False"

    DataSourceID="tblTasks">

    <Columns>

    <asp:BoundField DataField="fldSalesOrder" HeaderText="Sales Order#"

    SortExpression="fldSalesOrder" />

    <asp:BoundField DataField="fldRevision" HeaderText="Rev"

    SortExpression="fldRevision" />

    <asp:BoundField DataField="fldAssignmentDate"

    HeaderText="Assignment Date" SortExpression="fldAssignmentDate" />

    <asp:BoundField DataField="fldEmployee" HeaderText="Employee"

    SortExpression="fldEmployee" />

    <asp:BoundField DataField="fldTask" HeaderText="Task"

    SortExpression="fldTask" />

    <asp:BoundField DataField="fldInitials" HeaderText="Initials"

    SortExpression="fldInitials" />

    <asp:BoundField DataField="fldCompletionDate"

    HeaderText="CompletionDate" SortExpression="fldCompletionDate" />

    </Columns>

    </asp:GridView>

    <br />

    <br />

     

    </div>

    </form>

    </body>

    </html>

     

     

     

    Help!!

     

    -karl

     

    Tuesday, November 18, 2008 4:33 PM

Answers

  • User-821857111 posted

    Use an <asp:ControlParameter> and reference the TextBox control:

    <SelectParameters>

    <asp:ControlParameter Name=szSO ControlID="Label1" PropertyName="Text"  />

    </SelectParameters>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 19, 2008 2:27 AM

All replies

  • User-821857111 posted

    Use an <asp:ControlParameter> and reference the TextBox control:

    <SelectParameters>

    <asp:ControlParameter Name=szSO ControlID="Label1" PropertyName="Text"  />

    </SelectParameters>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 19, 2008 2:27 AM
  • User1840655121 posted

    Great, Mark, that's just what I needed.

     BTW, I *did* need to enclose "szSO" in quotes in the ControlParameter Name=szSO, but I'm pretty sure that was just a typo on your part.

     

    Thanks

     

    -karl

     

    Wednesday, November 19, 2008 9:47 AM
  • User-821857111 posted

     BTW, I *did* need to enclose "szSO" in quotes in the ControlParameter Name=szSO, but I'm pretty sure that was just a typo on your part.
     

    I blame you.  All I did was copy and paste your initial code. [;)]

     

    Wednesday, November 19, 2008 2:19 PM