Retrieve Row ID (rowid) (SCOPE_IDENTITY();) after Detailsview Insert

Posted by admin on August 6, 2009 under 4-Code snippets | 2 Comments to Read

There are a number of examples on the web of how to retrieve a row ID after a SQL insert. But what if the insert is part of a declarative SQL statement defined for a SQLDataSource? How do you expose the identity? it turns out it is fairly simple. If you retrieve the scope_identity as part of the Insert statement, you can retrieve it in the item_inserted event. However, I found that you need to create the item_inserted event manually with different parameters than those generated automatically by Visual Studio.

Let’s say you are saving a payment record. Your detailsview is called dtlPayment. In the SQL Insert statement, you will have something like this:

<asp:SqlDataSource       …

 InsertCommand  = “INSERT INTO [tblPayments] ([BillingName], [BillingAmount]) VALUES (@BillingName, @BillingAmount);  Select @Payment_rowid=SCOPE_IDENTITY();”
 OnInserted=”dtlPayment_ItemInserted”
          …
</asp:SqlDataSource>

In the Insert parameters section, define the parameter as an output parameter

    <asp:parameter direction=”Output” name=”Payment_rowid” type=”Int32″ />

Now set up the following event:

Protected Sub dtlPayment_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
       Dim thisRowID As Integer
       thisRowID = e.Command.Parameters(”@Payment_rowid”).Value
End Sub

A Simple way to span column headers in an asp.net Gridview

Posted by admin on June 19, 2009 under 4-Code snippets | Read the First Comment

Let’s say you have an asp.net gridview and you want to group or span several columns under the same heading. For example, you are displaying quarterly sales data:

But what you really want to show is:

If your Gridview is called grdSales, there is an easy way to do this in the code behind. Create an event for row_created, then put this in:

Protected Sub grdSales_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles grdSales.RowCreated

If e.Row.RowType = DataControlRowType.Header Then

Dim oGridView As GridView = DirectCast(sender, GridView)

Dim oGridViewRow As New GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert)

Dim oTableCell As New TableCell()

oTableCell.Text = “”

oTableCell.ColumnSpan = 2

oTableCell.BackColor = Drawing.Color.White

oGridViewRow.Cells.Add(oTableCell)

oTableCell = New TableCell()

oTableCell.Text = “Sales”

oTableCell.HorizontalAlign = HorizontalAlign.Center

oTableCell.ColumnSpan = 4

oGridViewRow.Cells.Add(oTableCell)

 oGridView.Controls(0).Controls.AddAt(0, oGridViewRow)�
End If

 End Sub

 If you found my post helpful and it saved you time or money, please contribute by making a small donation. Even a dollar or two adds up!

ASP.NET Gridview that shows all records if dropdown filter is not selected

Posted by admin on April 3, 2009 under 4-Code snippets | Be the First to Comment

If you have a grdiview that is tied to a dropdown selector box, it will not show any records when the page loads and the dropdown defaults to a “Select” state. Here is one simple way to have the gridview show all records in this case:

<asp:DropDownList ID=”ddlCustomers” runat=”server”

            AutoPostBack=”True” DataSourceID=”ds_ddlCustomers” AppendDataBoundItems=”true”

            DataTextField=”CustName” DataValueField=”CustID”>

            <asp:ListItem Text=”All” Value=”0″></asp:ListItem>

          </asp:DropDownList>

Gridview datasource Select:

                SelectCommand=”SELECT CustID, CustName, CustPhone, CustEmail FROM tblCustomers WHERE (CustID = @CustID) OR (0 = @CustID) ORDER BY CustName”

<SelectParameters>

                  <asp:ControlParameter ControlID=”ddlCustomers” Name=”CustID”

                    PropertyName=”SelectedValue” Type=”Int32″ />

 </SelectParameters>

Explanation: In the dropdownlist declaration, I’ve added the AppendDataBoundItems=”true” attribute. I then add a list item that displays “All” with a value of 0. In the datasource for the gridview, the SelectCommand has the additional OR clause 0 = @CustID. So when the page first loads, or when the user selects “All”, the dropdown has a value of 0, so 0=0 becomes true.

If you found my post helpful and it saved you time or money, please contribute by making a small donation. Even a dollar or two adds up!

Hiding an asp.net Gridview column while allowing update and Insert

Posted by Fred Blau on February 21, 2009 under 3-Tech Tips, 4-Code snippets | Be the First to Comment

Sometimes you want to put a value into a gridview column for updates and inserts, but you don’t want it shown. If you set the column to invisible, it will not be included in Updates or Inserts  defined in the SQLDataSource. The solution is to set it to Display:None through CSS. Here is how to do it.
First define the style, either in your stylesheet, or in your HTM header:

 <style type=”text/css”>

    .Hide

    {

      display: none;

    }

  </style>

The apply the style to the column in the Gridview:

                    <asp:BoundField DataField=”your_field” HeaderText=”your_field” SortExpression=”your_field”>

                      <ItemStyle CssClass=”Hide” />

                      <HeaderStyle CssClass=”Hide” />

                    </asp:BoundField>

 That’s all it takes!