So you want to update a single row in your table and then return the row identifier that was affected, here’s one way of going about it. Remember SCOPE_IDENTITY() is about inserting so as we are updating here we need a little something different.

Meh, that previous post was kind of too simple etc, so thought I’d wrap up the logic in a nice class here you go.

It ccouldn’t be easier to call one example to write exceptions is

[codesyntax lang="csharp"]

EventLogger.Write(ex.Message, ”error”);

[/codesyntax]

The name of the assembly is captured by reflection to identify it from other applications in the event log.

[codesyntax lang="csharp"]

using System.Diagnostics;
using System.Reflection;

namespace utilities
{
  public static class EventLogger
  {
    /// <summary>
    /// Write entry to application event log
    /// </summary>
    /// <param name="message">Text to write to log</param>
    /// <param name="type">"information" / "error" / "success" defaults to information (optional)</param>

    public static void Write(string message, string type = "information")
    {
      var applicationName = Assembly.GetExecutingAssembly().GetName().Name;
      var sType = EventLogEntryType.Information;

      switch (type)
      {
        case "error":
          sType = EventLogEntryType.Error;
          break;
        case "success":
          sType = EventLogEntryType.SuccessAudit;
          break;
      }

      if (!EventLog.SourceExists(applicationName))
        EventLog.CreateEventSource(applicationName, "Application");

      EventLog.WriteEntry(applicationName, message,
      sType, 0);
    }
  }
}

If you want to add records to your database using a gridview (the gridview should already have at least one record with this method), you can use the GridView FooterRow as a container to hold the controls for the user to enter new data.

You will then use the InsertParameters collection and InsertCommand of the SqlDataSource, which you populate with FindControl methods in your codebehind in the SqlDataSource Inserting event which is initally fired by something like a LinkButton command.

Summary over, here’s the meat and potatoes..

Code for a GridView and an SqlDataSource

[codesyntax lang="csharp"]

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
  DataKeyNames="Code" DataSourceID="SqlDataSource1" EnableModelValidation="True"
    ShowFooter="True" AllowPaging="True" AllowSorting="True"
    onrowcommand="GridView1_RowCommand" >
  <Columns>
    <asp:TemplateField ShowHeader="False">
        <ItemTemplate>
            <asp:LinkButton ID="lbEdit" runat="server" CausesValidation="False" CommandName="Edit"
            Text="Edit" />
        </ItemTemplate>

        <EditItemTemplate>
            <asp:LinkButton ID="lbUpdate" runat="server" CausesValidation="True" CommandName="Update" Text="Update" />
        </EditItemTemplate>

        <FooterTemplate>
            <asp:LinkButton ID="lbInsert" runat="server" CommandName="Insert">Insert</asp:LinkButton>
        </FooterTemplate>
    </asp:TemplateField>

    <asp:BoundField DataField="Code" HeaderText="Code" InsertVisible="False"
      ReadOnly="True" SortExpression="Code" />
    <asp:TemplateField HeaderText="Description" SortExpression="Description">
      <EditItemTemplate>
        <asp:TextBox ID="txtDescripton" runat="server" Text='<%# Bind("Description") %>'></asp:TextBox>
      </EditItemTemplate>
      <ItemTemplate>
        <asp:Label ID="lblDescription" runat="server" Text='<%# Bind("Description") %>'></asp:Label>
      </ItemTemplate>
        <FooterTemplate>
          <asp:TextBox ID="txtDescription" runat="server" />
        </FooterTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Active" SortExpression="Active">
      <EditItemTemplate>
        <asp:CheckBox ID="chkActive" runat="server" Checked='<%# Bind("Active") %>' />
      </EditItemTemplate>
      <ItemTemplate>
        <asp:CheckBox ID="chkActive" runat="server" Checked='<%# Convert.ToBoolean(Eval("Active")) %>'
          Enabled="false" />
      </ItemTemplate>
      <FooterTemplate>

      </FooterTemplate>
    </asp:TemplateField>
  </Columns>
</asp:GridView>

You then need your SqlDataSource control

[codesyntax lang="csharp"]

<asp:SqlDataSource ID="SqlDataSource1" runat="server" OnInserting="onInserting"
  ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
  InsertCommand="INSERT INTO [TABLENAME] ([Description]) VALUES (?)"
  ProviderName="<%$ ConnectionStrings:AdminConnectionString.ProviderName %>"
  SelectCommand="SELECT * FROM [TABLENAME]"
  UpdateCommand="UPDATE [TABLENAME] SET [Description] = ?, [Active] = ? WHERE [Code] = ?">

  <InsertParameters>
    <asp:Parameter Name="Description" Type="String" />
  </InsertParameters>
  <UpdateParameters>
    <asp:Parameter Name="Description" Type="String" />
    <asp:Parameter Name="Active" Type="Boolean" />
    <asp:Parameter Name="Code" Type="Int32" />
  </UpdateParameters>
</asp:SqlDataSource>

And finally the codebehind should be something like this where you use the events within the page lifecycle to set parameter values and do the insert.

[codesyntax lang="csharp"]

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Admin : System.Web.UI.Page
{
    protected void onInserting(object sender, SqlDataSourceCommandEventArgs e)
    {
      string _Description = ((TextBox)(GridView1.FooterRow.FindControl("txtDescription"))).Text;

      if (_Description != null)
      {
        e.Command.Parameters["Description"].Value = _Description;
      }
    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
      if (e.CommandName.Equals("Insert"))
      {    
        SqlDataSource1.Insert();
      }
    }
}

You have a DetailsView control with edit / add and all the other goodness enabled, but when adding a new record you want a non-visible field to get it’s value from another control like the SelectedValue of a GridView or a DropDownList in the InsertParameters collection of your SqlDataSource just use a ControlParameter instead

[codesyntax lang="csharp"]

<InsertParameters>
    <asp:ControlParameter ControlID="ddlYourList" Name="ParameterName"
        PropertyName="SelectedValue" Type="Int32" />
    <asp:Parameter Name="SomeDetailsViewParameter" Type="String" />
</InsertParameters>

Need to bind a web control to an OleDb data source in your C# web application, here’s the magic. In this case I am binding a ListBox control with an ID of ListBox1 to the ProductName field in the Products table of our old friend the Northwind database.

These are the using statements that you need to apply including the ConfigurationManager to get your web.config connection string conveniently

[codesyntax lang="csharp"]

using System.Data;
using System.Data.OleDb;
using System.Configuration;

Here is our class that does all the work

[codesyntax lang="csharp"]

protected void DataSetFromOleDbDemo()
    {
      string strSQL = "SELECT * FROM Products WHERE CategoryID = 1";

      var conString = ConfigurationManager.ConnectionStrings["Northwind"];
      string Conn = conString.ConnectionString;

      try
      {
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(strSQL, Conn))
        {
          DataSet ds = new DataSet();
          adapter.Fill(ds, "ProductInfo");

          foreach (DataRow dr in ds.Tables["ProductInfo"].Rows)
          {
            ListBox1.Items.Add(dr["ProductName"].ToString());
          }
        }
      }
      catch (Exception)
      {

        throw;
      }
    }

And your OleDb connection string to put in the connectionStrings section of your web.config is as follows

[codesyntax lang="xml"]

<connectionStrings>
    <add name="Northwind" connectionString="Provider=SQLOLEDB;server=YOURSERVER;database=Northwind;uid=USERNAME;password=PASSWORD;" />
</connectionStrings>