SubsDotNet: Bind Data to DDL with RowDataBound and SelectedIndexChanged in VB.NET

Ans:-
Here i have taken one GridView and in Gridview i have taken DropDownList.
Now i have to bind/get data from DB in to DropDownList. And then while selecting DDL, indexchanged event will generate and according to that value Gridview row value will be change.

See the below is UI of small application..
1.

DDlSelectedIndexChangedInGv.aspx:-

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="DDlSelectedIndexChangedInGv.aspx.vb" Inherits="DDlSelectedIndexChangedInGv" %>

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

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>DDl in SelectedIndexChanged in GridView Page</title>
</head>
<body>
<form id="form1" runat="server">
<center>
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
PageSize="1" AllowPaging="true" >
<Columns>
<asp:TemplateField HeaderText="Select" SortExpression="id">

<ItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true"
onselectedindexchanged="DropDownList1_SelectedIndexChanged">
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<%-- <asp:BoundField DataField="id" HeaderText="id" SortExpression="id" />
<asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
<asp:BoundField DataField="descrptn" HeaderText="descrptn"
SortExpression="descrptn" />--%>

<asp:TemplateField HeaderText="id"
SortExpression="id">
<ItemTemplate>
<asp:Label ID="lblId" runat="server" Text='<%# Bind("id") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtId" runat="server" Text='<%# Bind("id") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="name" SortExpression="name">
<ItemTemplate>
<asp:Label ID="lblName" runat="server" Text='<%# Bind("name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtName" runat="server" Text='<%# Bind("name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="desc" SortExpression="descrptn">
<ItemTemplate>
<asp:Label ID="lblDesc" runat="server" Text='<%# Bind("descrptn") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtDesc" runat="server" Text='<%# Bind("descrptn") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</center>
</form>
</body>
</html>

DDlSelectedIndexChangedInGv.aspx.vb:-

Imports System

Imports System.Data

Imports System.Web.UI.WebControls

Imports System.Data.SqlClient

Partial Class DDlSelectedIndexChangedInGv

Inherits System.Web.UI.Page

Dim obj As New Class1

Dim cn As SqlConnection

Dim cmd As SqlCommand

Dim da As SqlDataAdapter

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

Me.BindData()

End If

End Sub

Private Sub BindData()

cn = New SqlConnection(obj.GetConnection())

cn.Open()

cmd = New SqlCommand("select * from test", cn)

da = New SqlDataAdapter(cmd)

Dim ds As New DataSet()

da.Fill(ds)

cn.Close()

GridView1.DataSource = ds

GridView1.DataBind()

End Sub

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound

If e.Row.RowType = DataControlRowType.DataRow Then

cn = New SqlConnection(obj.GetConnection())

cn.Open()

Dim ddl As DropDownList = DirectCast(e.Row.FindControl("DropDownList1"), DropDownList)

'Dim id As Integer = Convert.ToInt32(e.Row.Cells(1).Text) 'it returns value from gridview databound field

cmd = New SqlCommand("select * from test", cn)

da = New SqlDataAdapter(cmd)

Dim ds As New DataSet

da.Fill(ds)

cn.Close()

ddl.DataSource = ds

ddl.DataTextField = "id"

ddl.DataValueField = "id"

ddl.DataBind()

ddl.Items.Insert(0, New ListItem("-Select-", "0"))

End If

End Sub

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

cn = New SqlConnection(obj.GetConnection())

cn.Open()

Dim ddl As DropDownList = DirectCast(sender, DropDownList)

Dim id As Integer = Convert.ToInt32(ddl.SelectedValue)

cmd = New SqlCommand("select * from test where id=" & id, cn)

da = New SqlDataAdapter(cmd)

Dim dt As New DataTable

da.Fill(dt)

cn.Close()

Dim ddlist As New DropDownList

Dim lbId As Label

Dim lbName As Label

Dim lbDesc As Label

If dt.Rows.Count > 0 Then

Dim i As Integer = 0

For i = 0 To dt.Rows.Count - 1

ddlist = GridView1.Rows(i).FindControl("DropDownList1")

lbId = GridView1.Rows(i).FindControl("lblId")

lbName = GridView1.Rows(i).FindControl("lblName")

lbDesc = GridView1.Rows(i).FindControl("lblDesc")

If ddlist.SelectedIndex > 0 Then

lbId.Text = dt.Rows(i)("id").ToString()

lbName.Text = dt.Rows(i)("name").ToString()

lbDesc.Text = dt.Rows(i)("descrptn").ToString()

End If

End Sub

Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging

GridView1.PageIndex = e.NewPageIndex

BindData()

End Sub

End Class

Class1.vb:-

Imports Microsoft.VisualBasic

Imports System.Data.SqlClient

Public Class Class1

Dim sqlcmd As SqlCommand

Dim sqldr As SqlDataReader

Public Function GetConnection() As String

Dim conStr As String = "Server=192.168.0.200;database=SubsDB;UID=sa;Password=amtpl@123;Pooling=true;Min Pool Size=2;Max Pool Size=1000"

Return conStr

End Function

Public Sub AddCombo(ByVal CntName As DropDownList, ByVal SqlStr As String, ByVal sCon As SqlConnection)

'Which will fill the DropdownList Based on Query

Try

CntName.Items.Clear()

CntName.Items.Add("")

sqlcmd = New SqlCommand(SqlStr, sCon)

sqldr = sqlcmd.ExecuteReader()

While sqldr.Read

CntName.Items.Add(sqldr(0))

End While

sqldr.Close() : sqlcmd.Dispose()

Catch ex As Exception

'MsgBox(ex.Message)

End Try

End Sub

End Class

Create Table:-

create table test(id int primary key,name varchar(30),descrptn varchar(30))

SubsDotNet

Friday, 25 April 2014

Bind Data to DDL with RowDataBound and SelectedIndexChanged in VB.NET

No comments:

Post a Comment