using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace BugHistoryInPlaceDataListEdit
{
   public class WebForm1 : System.Web.UI.Page
   {
      protected System.Web.UI.WebControls.DataList DataList1;
      protected System.Web.UI.WebControls.ListBox lbReportedby;
      public System.Data.SqlClient.SqlDataReader personReader;
      
      public WebForm1(  )
      {
         Page.Init += new System.EventHandler(Page_Init);
      }

      private void Page_Load(object sender, System.EventArgs e)
      {
         if (! Page.IsPostBack)
         {
            BindGrid(  );
         }
      }

      // extract the bug records and bind to the datagrid
      private void BindGrid(  )
      {
         // connect to the Bugs database
         string connectionString = 
            "server=YourServer; uid=sa; pwd=YourPW; database=ProgASPDotNetBugs";

         System.Data.SqlClient.SqlConnection connection = 
            new System.Data.SqlClient.SqlConnection(connectionString);
         connection.Open(  );

         // get records from the Bugs table
         string commandString = 
            "Select b.BugID, b.Version, b.Description, ";
         commandString += "p.ProductDescription, peo.FullName from Bugs b ";
         commandString += "join lkProduct p on b.Product = p.ProductID ";
         commandString += "join People peo on b.Reporter = peo.PersonID ";

            
         System.Data.SqlClient.SqlCommand command = 
            new System.Data.SqlClient.SqlCommand(  );
         command.CommandText = commandString;
         command.Connection = connection;

         // Create the Reader adn bind it to the datagrid
         SqlDataReader reader = 
            command.ExecuteReader(CommandBehavior.CloseConnection);
         DataList1.DataSource=reader;
         DataList1.DataBind(  );
      }

      // Given the name of a table, return a DataReader for
      // all values from that table
      public System.Data.SqlClient.SqlDataReader GetValues(string tableName)
      {
         // connect to the Bugs database
         string connectionString = 
            "server=YourServer; uid=sa; pwd=YourPW; database=ProgASPDotNetBugs";

         // create and open the connection object
         System.Data.SqlClient.SqlConnection connection = 
            new System.Data.SqlClient.SqlConnection(connectionString);
         connection.Open(  );

         // get records from the Bugs table
         string commandString = "Select * from " + tableName;

         // create the command object and set its
         // command string and connection
         System.Data.SqlClient.SqlCommand command = 
            new System.Data.SqlClient.SqlCommand(  );
         command.CommandText = commandString;
         command.Connection = connection;

         // create the DataReader and return it
         return command.ExecuteReader(CommandBehavior.CloseConnection);

      }

      // Handle the Edit event - set the EditItemIndex of the
      // selected row
      public void OnEdit(Object source, DataListCommandEventArgs e)
      {
         DataList1.EditItemIndex = e.Item.ItemIndex;
         BindGrid(  );
      }     

      private int ExecuteQuery(string sqlCmd)
      {
         // connect to the Bugs database
         string connectionString = 
            "server=YourServer; uid=sa; pwd=YourPW; database=ProgASPDotNetBugs";

         System.Data.SqlClient.SqlConnection connection = 
            new System.Data.SqlClient.SqlConnection(connectionString);
         connection.Open(  );

         // call the update and rebind the datagrid
         System.Data.SqlClient.SqlCommand command = 
            new System.Data.SqlClient.SqlCommand(  );
         command.CommandText = sqlCmd;
         command.Connection = connection;
         return command.ExecuteNonQuery(  );
      }

      public void OnDelete(Object source, DataListCommandEventArgs e)
      {
         
         string cmd = "Delete from Bugs where BugID = " + 
            DataList1.DataKeys[e.Item.ItemIndex];
         int rowsDeleted = ExecuteQuery(cmd);

         DataList1.EditItemIndex = -1;
         BindGrid(  );
      }     

      // Handle the cancel event - set the EditItemIndex to -1
      public void OnCancel(Object source, DataListCommandEventArgs e)
      {
         DataList1.EditItemIndex = -1;
         BindGrid(  );

      }

      // Handle the Update event
      // Extract the new values
      // Update the database and rebind the datagrid
      public void OnUpdate(Object source, DataListCommandEventArgs e)
      {
         
         string PersonID =  
            ((DropDownList)(e.Item.FindControl("editReporter"))).
            SelectedItem.Value;

         string newDescription = 
            ((TextBox)(e.Item.FindControl("txtDescription"))).Text;

         string ProductID = 
            ((DropDownList)(e.Item.FindControl("editProduct"))).
            SelectedItem.Value;

         // form the update statement
         string cmd = "Update Bugs set Product = " + ProductID + 
            ", Description = '" + newDescription + 
            " ', Reporter = " + PersonID +
            " where BugID = " + DataList1.DataKeys[e.Item.ItemIndex];

         ExecuteQuery(cmd);
         DataList1.EditItemIndex = -1;
         BindGrid(  );
 
      }

      private void Page_Init(object sender, EventArgs e)
      {
         //
         // CODEGEN: This call is required by the ASP.NET Web Form Designer.
         //
         InitializeComponent(  );
      }

        #region Web Form Designer generated code
      /// <summary>
      /// Required method for Designer support - do not modify
      /// the contents of this method with the code editor.
      /// </summary>
      private void InitializeComponent(  )
      {    
         this.Load += new System.EventHandler(this.Page_Load);

      }
        #endregion
   }
}

Imports System.Data.SqlClient

Public Class WebForm1
   Inherits System.Web.UI.Page

   Protected WithEvents DataList1 As System.Web.UI.WebControls.DataList
   Protected WithEvents lbReportedby As System.Web.UI.WebControls.ListBox
   Public personReader As System.Data.SqlClient.SqlDataReader

#Region " Web Form Designer Generated Code "

   'This call is required by the Web Form Designer.
   <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent(  )

   End Sub

   Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) 
Handles MyBase.Init
      'CODEGEN: This method call is required by the Web Form Designer
      'Do not modify it using the code editor.
      InitializeComponent(  )
   End Sub

#End Region

   Private Sub Page_Load(ByVal sender As System.Object, _
                         ByVal e As System.EventArgs) Handles MyBase.Load
      If Not Page.IsPostBack Then
         BindGrid(  )
      End If
   End Sub

   ' extract the bug records and bind to the datagrid
   Private Sub BindGrid(  )
      ' connect to the Bugs database
      Dim connectionString As String = _
          "server=YourServer; uid=sa; pwd=YourPW; " & _
          "database=ProgASPDotNetBugs"

      Dim connection As New SqlConnection(connectionString)
      connection.Open(  )

      ' get records from the Bugs table
      Dim commandString As String = _
          "Select b.BugID, b.Version, b.Description, " & _
          "p.ProductDescription, peo.FullName from Bugs b " & _
          "join lkProduct p on b.Product = p.ProductID " & _
          "join People peo on b.Reporter = peo.PersonID "

      Dim command As New SqlCommand(  )

      command.CommandText = commandString
      command.Connection = connection

      ' Create the Reader and bind it to the datagrid
      Dim reader As SqlDataReader = _
          command.ExecuteReader(CommandBehavior.CloseConnection)
      DataList1.DataSource = reader
      DataList1.DataBind(  )
   End Sub

   ' Given the name of a table, return a DataReader for
   ' all values from that table
   Public Function GetValues(ByVal tableName As String) As SqlDataReader
      ' connect to the Bugs database
      Dim connectionString As String = _
          "server=YourServer; uid=sa; " & _
          "pwd=YourPassword; database=ProgASPDotNetBugs"

      ' create and open the connection object
      Dim connection As New SqlConnection(connectionString)
      connection.Open(  )

      ' get records from the Bugs table
      Dim commandString As String = "Select * from " & tableName

      ' create the command object and set its
      ' command string and connection
      Dim command As New SqlCommand(  )

      command.CommandText = commandString
      command.Connection = connection

      ' create the DataReader and return it
      Return command.ExecuteReader(CommandBehavior.CloseConnection)
   End Function

   ' Handle the Edit event - set the EditItemIndex of the
   ' selected row
   Public Sub OnEdit(ByVal source As Object, ByVal e As DataListCommandEventArgs)
      DataList1.EditItemIndex = e.Item.ItemIndex
      BindGrid(  )
   End Sub

   ' Handle the cancel event - set the EditItemIndex to -1
   Public Sub OnCancel(ByVal source As Object, ByVal e As DataListCommandEventArgs)
      DataList1.EditItemIndex = -1
      BindGrid(  )
   End Sub

   ' Handle the delete event 
   Public Sub OnDelete(ByVal source As Object, ByVal e As DataListCommandEventArgs)
      Dim cmd As String = "Delete from Bugs where BugID = " & _
            DataList1.DataKeys(e.Item.ItemIndex)
      DataList1.EditItemIndex = -1
      BindGrid(  )
   End Sub

   ' Handle the Update event
   ' Extract the new values
   ' Update the database and rebind the datagrid
   Public Sub OnUpdate(ByVal source As Object, ByVal e As DataListCommandEventArgs)
      Dim PersonID As String = CType(e.Item.FindControl( _
                     "editReporter"), DropDownList).SelectedItem.Value

      Dim newDescription As String = CType(e.Item.FindControl("txtDescription"), _
                                     TextBox).Text

      Dim ProductID As String = CType(e.Item.FindControl( _
                      "editProduct"), DropDownList).SelectedItem.Value

      Dim newVersion As String = CType(e.Item.FindControl( _
                       "txtVersion"), TextBox).Text

      ' form the update statement
      Dim cmd As String = "Update Bugs set Product = " & ProductID & _
          ", Version = '" & newVersion & _
          "', Description = '" & newDescription & _
          " ', Reporter = " & PersonID & _
          " where BugID = " & DataList1.DataKeys(e.Item.ItemIndex)

      ExecuteQuery(cmd)
      DataList1.EditItemIndex = -1
      BindGrid(  )
   End Sub
   Private Function ExecuteQuery(ByVal sqlCmd As String) As Int32
      ' connect to the Bugs database
      Dim connectionString As String = _

      Dim myConnection As New System.Data.SqlClient.SqlConnection(connectionString)
      myConnection.Open(  )

      ' call the update and rebind the datagrid
      Dim myCommand As New System.Data.SqlClient.SqlCommand(  )
      myCommand.CommandText = sqlCmd
      myCommand.Connection = myConnection
      Return myCommand.ExecuteNonQuery(  )
   End Function
End Class