VB.NET - Grid View - Asked By DGowda on 17-Nov-11 06:32 AM

Hi EveryOne,
 
How to make data from DB to be displayed in grid view and should set the options Update,Delete,Add in grid view.please help with the code.
Thanks & Regards
dipa ahuja replied to DGowda on 17-Nov-11 06:35 AM
protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    bindGrid();
  }
}
void bindGrid()
{
  SqlDataAdapter da = new SqlDataAdapter("select * from TableName", "Connection String");
  DataTable dt = new DataTable();   
  da.Fill(dt);
  GridView1.DataSource = dt;
  GridView1.DataBind();
}
 
Vikas replied to DGowda on 17-Nov-11 06:35 AM
This link contain all your problem solution .
For C#
http://geekswithblogs.net/dotNETvinz/archive/2009/02/22/gridview-insert-edit-update-and-delete--the-ado.net-way.aspx

For Vb.net

http://www.asp.net/data-access/tutorials/inserting-updating-and-deleting-data-with-the-sqldatasource-vb
dipa ahuja replied to DGowda on 17-Nov-11 06:36 AM
You can do so by:
1. Take Gridview on your webpage.
2. Right Click on it , and choose "Add new Column" from that dialogBox select commandField from the combobox,
which will allow you to add "select, edit/update and delete" buttons on your GridView.

 
3.Now implement following events of Gridview
onrowcancelingedit="GridView1_RowCancelingEdit"
onrowdeleting="GridView1_RowDeleting"
onrowediting="GridView1_RowEditing"
onrowupdating="GridView1_RowUpdating"
 
.CS Code
 
static string ConnString ="ConnectionString";
protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    //Code to bind gridview
  }
 
}
 
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
  GridView1.EditIndex = e.NewEditIndex;
    //Code to bind gridview
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
  int id = int.Parse(((Label)GridView1.Rows[e.RowIndex].FindControl("lblid")).Text);//ID
  string name = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtname")).Text;
 
  SqlConnection connect = new SqlConnection(ConnString);
  connect.Open();
 
  string q = "Update people set name=@name where id=@id";
 
  SqlCommand comm = new SqlCommand(q, connect);
  comm.Parameters.AddWithValue("name", name);
  comm.Parameters.AddWithValue("id", id);
  comm.ExecuteNonQuery();
  connect.Close();
 
  GridView1.EditIndex = -1;
 
    //Code to bind gridview
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
  GridView1.EditIndex = -1;
  BindGrid();
 
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
  int id = int.Parse(((Label)GridView1.Rows[e.RowIndex].FindContro("lblid")).Text);//ID
 
  SqlConnection connect = new SqlConnection(ConnString);
  connect.Open();
 
  string q = "Delete from table where id=@id";
 
  SqlCommand comm = new SqlCommand(q, connect);
  comm.Parameters.AddWithValue("id", id);
  comm.ExecuteNonQuery();
  connect.Close();
 
  GridView1.EditIndex = -1;
 
    //Code to bind gridview
} http://converter.telerik.com/
Reena Jain replied to DGowda on 17-Nov-11 06:39 AM
Hi,

here is simple insert, update, delete code for you

<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server"
        AutoGenerateColumns="False"
        DataKeyNames="ID"
        DataSourceID="SqlDataSource1"
        OnRowDeleted="GridView1_RowDeleted"
        OnRowUpdated="GridView1_RowUpdated"
        ShowFooter="true"
        OnRowCommand="GridView1_RowCommand">
<Columns>
  <asp:CommandField ShowDeleteButton="True"
            ShowEditButton="True" />
  <asp:TemplateField HeaderText="ID" SortExpression="ID">
  <ItemTemplate>
  <asp:Label ID="lblID" runat="server"
              Text='<%#Eval("ID") %>'>
  </asp:Label>
  </ItemTemplate>
  <FooterTemplate>
  <asp:Button ID="btnInsert" runat="server"
        Text="Insert" CommandName="Add" />
  </FooterTemplate>
  </asp:TemplateField>
   
  <asp:TemplateField HeaderText="FirstName"
             SortExpression="FirstName">
  <ItemTemplate>
  <asp:Label ID="lblFirstName" runat="server"
         Text='<%#Eval("FirstName") %>'>
  </asp:Label>
  </ItemTemplate>
  <EditItemTemplate>
  <asp:TextBox ID="txtFirstName" runat="server"
         Text='<%#Bind("FirstName") %>'>
  </asp:TextBox>
  </EditItemTemplate>
  <FooterTemplate>
  <asp:TextBox ID="txtFname" runat="server">
  </asp:TextBox>
  </FooterTemplate>
  </asp:TemplateField>
   
  <asp:TemplateField HeaderText="LastName"
             SortExpression="LastName">
  <ItemTemplate>
  <asp:Label ID="lblLastName" runat="server"
         Text='<%#Eval("LastName") %>'>
  </asp:Label>
  </ItemTemplate>
  <EditItemTemplate>
  <asp:TextBox ID="txtLastName" runat="server"
         Text='<%#Bind("LastName") %>'>
  </asp:TextBox>
  </EditItemTemplate>
  <FooterTemplate>
  <asp:TextBox ID="txtLname" runat="server">
  </asp:TextBox>
  </FooterTemplate>
  </asp:TemplateField>
   
  <asp:TemplateField HeaderText="Department"
             SortExpression="Department">
  <ItemTemplate>
  <asp:Label ID="lblDepartment" runat="server"
         Text='<%#Eval("Department") %>'>
  </asp:Label>
  </ItemTemplate>
  <EditItemTemplate>
  <asp:TextBox ID="txtDepartmentName" runat="server"
         Text='<%#Bind("Department") %>'>
  </asp:TextBox>
  </EditItemTemplate>
  <FooterTemplate>
  <asp:TextBox ID="txtDept" runat="server">
  </asp:TextBox>
  </FooterTemplate>
  </asp:TemplateField>
   
  <asp:TemplateField HeaderText="Location"
             SortExpression="Location">
  <ItemTemplate>
  <asp:Label ID="lblLocation" runat="server"
         Text='<%#Eval("Location") %>'>
  </asp:Label>
  </ItemTemplate>
  <EditItemTemplate>
  <asp:TextBox ID="txtLocation" runat="server"
         Text='<%#Bind("Location") %>'>
  </asp:TextBox>
  </EditItemTemplate>
  <FooterTemplate>
  <asp:TextBox ID="txtLoc" runat="server">
  </asp:TextBox>
  </FooterTemplate>
  </asp:TemplateField>
</Columns>
</asp:GridView>
 
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:DBConString%>"
DeleteCommand="DELETE FROM [Employees] WHERE [ID] = @ID"
InsertCommand="INSERT INTO [Employees] ([FirstName],
[LastName],[Department], [Location])
VALUES (@FirstName, @LastName, @Department, @Location)"
SelectCommand="SELECT [ID], [FirstName], [LastName],
[Department], [Location] FROM [Employees]"
UpdateCommand="UPDATE [Employees] SET
[FirstName] = @FirstName, [LastName] = @LastName,
[Department] = @Department, [Location] = @Location
WHERE [ID] = @ID" OnInserted="SqlDataSource1_Inserted">
 
<DeleteParameters>
  <asp:Parameter Name="ID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
  <asp:Parameter Name="FirstName" Type="String" />
  <asp:Parameter Name="LastName" Type="String" />
  <asp:Parameter Name="Department" Type="String" />
  <asp:Parameter Name="Location" Type="String" />
  <asp:Parameter Name="ID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
  <asp:Parameter Name="FirstName" Type="String" />
  <asp:Parameter Name="LastName" Type="String" />
  <asp:Parameter Name="Department" Type="String" />
  <asp:Parameter Name="Location" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<asp:Label ID="lblMessage" runat="server"
       Font-Bold="True"></asp:Label><br />
</div>
</form>

on aspx.vb page
Protected Sub GridView1_RowCommand(ByVal sender As Object,
             ByVal e As GridViewCommandEventArgs)
    If e.CommandName = "Add" Then
      Dim strFirstName As String =
      DirectCast(GridView1.FooterRow.
      FindControl("txtFname"), TextBox).Text()
 
      Dim strLastName As String =
      DirectCast(GridView1.FooterRow.
      FindControl("txtLname"), TextBox).Text()
 
      Dim strDepartment As String =
      DirectCast(GridView1.FooterRow.
      FindControl("txtDept"), TextBox).Text()
      Dim strLocation As String =
      DirectCast(GridView1.FooterRow.
      FindControl("txtLoc"), TextBox).Text()
 
      'SqlDataSource1.InsertParameters.Clear();
      'SqlDataSource1.InsertParameters.Add
      '("FirstName", strFirstName);
      'SqlDataSource1.InsertParameters.Add
      '("LastName", strLastName);
      'SqlDataSource1.InsertParameters.Add
      '("Department", strDepartment);
      'SqlDataSource1.InsertParameters.Add
      '("Location", strLocation);
 
      SqlDataSource1.InsertParameters("FirstName").
      DefaultValue = strFirstName
      SqlDataSource1.InsertParameters("LastName").
      DefaultValue = strLastName
      SqlDataSource1.InsertParameters("Department").
      DefaultValue = strDepartment
      SqlDataSource1.InsertParameters("Location").
      DefaultValue = strLocation
      SqlDataSource1.Insert()
    End If
  End Sub