SubsDotNet: Search Result through TelerikRadComboBox in VB.NET

Ans:-
Here i have taken two TelerikRadComboBox,one is state and another is city. While search any letter or word,if that is available in DB that will show in state RadComboBox.And then according to state,we can get city.

See in Below Pics for better understanding..

1.

SearchOnTelerikComboBox.aspx:-

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

<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>

<!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>DDlwthTelerikComboBox Page</title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<center>
<div>
<table>
<tr>
<td>
<asp:Label ID="Label34" runat="server" Text="State" Width="118px" Style="color: Maroon;
font-family: Verdana; font-size: 8pt"></asp:Label>
</td>
<td>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<%-- <asp:DropDownList ID="ddlState1" runat="server" Width="1px" Enabled="False" TabIndex="41"
AutoPostBack="True" Height="22px" Font-Names="Verdana" Font-Size="8.5pt" Visible="False">
</asp:DropDownList>--%>
<telerik:RadComboBox ID="ddlState" runat="server" EnableViewState="false" EmptyMessage="Select a State"
EnableLoadOnDemand="True" AllowCustomText="True" MarkFirstMatch="True" Style="color: Black;
background-color: White; font-family: Verdana; font-size: 8.5pt;" TabIndex="43"
AutoPostBack="True" Width="240px" Skin="Sunset" DropDownCssClass="RadComboBox_Sunset"
EnableEmbeddedSkins="False" BorderColor="#999999" BorderStyle="Solid" EnableVirtualScrolling="True"
BorderWidth="1px" ShowMoreResultsBox="True">
</telerik:RadComboBox>
<span style="font-size: 8pt; color: maroon">*</span>
</ContentTemplate>
</asp:UpdatePanel>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label1" runat="server" Text="City" Width="118px" Style="color: Maroon;
font-family: Verdana; font-size: 8pt"></asp:Label>
</td>
<td>
<asp:UpdatePanel ID="UpdatePanel2" runat="server">
<ContentTemplate>
<%-- <asp:DropDownList ID="ddlCity1" runat="server" Width="1px" AutoPostBack="True" TabIndex="42"
Font-Size="8.5pt" Height="22px" Enabled="False" Font-Names="Verdana" Visible="False">
</asp:DropDownList>--%>
<telerik:RadComboBox ID="ddlCity" runat="server" EnableViewState="false" EmptyMessage="Select a City"
EnableLoadOnDemand="True" AllowCustomText="True" MarkFirstMatch="True" Style="color: Black;
background-color: White; font-family: Verdana; font-size: 8.5pt;" TabIndex="44"
Width="240px" AutoPostBack="True" Skin="Sunset" DropDownCssClass="RadComboBox_Sunset"
EnableEmbeddedSkins="False" BorderColor="#999999" BorderStyle="Solid" EnableVirtualScrolling="True"
BorderWidth="1px" ShowMoreResultsBox="True">
</telerik:RadComboBox>
<span style="font-size: 8pt; color: maroon">*</span>
</ContentTemplate>
</asp:UpdatePanel>
</td>
</tr>
<tr>
<td>
 
</td>
</tr>
<tr>
<td>
<%--<asp:ImageButton ID="btnAdd" runat="server" AccessKey="A" ImageUrl="~/Images/Add.gif"
TabIndex="52" ToolTip="Alt+a" />--%>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblMsg" runat="server" Text="Label"></asp:Label>
</td>
</tr>
</table>
</div>
</center>
</form>
</body>
</html>

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports Telerik.Web.UI

Partial Class DDlwthTelerikComboBox

Inherits System.Web.UI.Page

Dim obj As New Class1

Dim cn As SqlConnection

Dim cmd As SqlCommand

Dim da As SqlDataAdapter

Dim dt As New DataTable

Dim ds As New DataSet

Dim spStr() As String

Dim sFlag As Integer = 0

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

Try

cn = New SqlConnection(obj.GetConnection()) : cn.Open()

Catch ex As Exception

lblMsg.Text = ex.Message

End Try

End Sub

Protected Sub ddlState_ItemsRequested(ByVal sender As Object, ByVal e As Telerik.Web.UI.RadComboBoxItemsRequestedEventArgs) Handles ddlState.ItemsRequested

Try

da = New SqlDataAdapter("Select distinct ltrim(rtrim(cast(sid as varchar)))+'-'+ltrim(rtrim(sname)) State from state where sname LIKE '" + e.Text + "%'", cn)

da.Fill(dt)

Dim itemsPerRequest As Integer = 20

Dim itemOffset As Integer = e.NumberOfItems

Dim endOffset As Integer = itemOffset + itemsPerRequest

If endOffset > dt.Rows.Count Then

endOffset = dt.Rows.Count

End If

Dim i As Integer = itemOffset

While i < endOffset

spStr = Split(dt.Rows(i)("State").ToString(), "-")

ddlState.Items.Add(New RadComboBoxItem(dt.Rows(i)("State").ToString()))

i = i + 1

End While

If dt.Rows.Count > 0 Then

e.Message = [String].Format("Items <b>1</b>-<b>{0}</b> out of <b>{1}</b>", endOffset.ToString(), dt.Rows.Count.ToString())

Else

e.Message = "No matches"

End If

Catch ex As Exception

lblMsg.Text = ex.Message

End Try

End Sub

Protected Sub ddlState_SelectedIndexChanged(ByVal sender As Object, ByVal e As Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs) Handles ddlState.SelectedIndexChanged

If ddlState.Text <> "" Then

Dim r As Integer = obj.FetchValue("select Count(*) from state where ltrim(rtrim(cast(sid as varchar)))+'-'+ltrim(rtrim(sname))='" + ddlState.Text + "'", cn)

If r = 0 Then

ddlState.Focus()

'MsgBox("State not Found", MsgBoxStyle.OkOnly)

ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), Guid.NewGuid().ToString(), "alert('State Not Found...');", True)

ddlState.Text = "" : ScriptManager1.SetFocus(ddlState.ClientID + "_Input") : sFlag = 1 : Exit Sub

Else

ddlCity.Text = ""

ScriptManager1.SetFocus(ddlCity.ClientID + "_Input")

End If

End Sub

SearchOnTelerikComboBox.aspx.vb:-

Protected Sub ddlCity_ItemsRequested(ByVal sender As Object, ByVal e As Telerik.Web.UI.RadComboBoxItemsRequestedEventArgs) Handles ddlCity.ItemsRequested

Try

If ddlState.Text <> "" Then

spStr = Split(ddlState.Text, "-")

da = New SqlDataAdapter("select distinct ltrim(rtrim(cast(c.cid as varchar)))+'-'+ltrim(rtrim(c.cname)) City from city c,[state] s where c.sid='" + spStr(0) + "' and c.cname LIKE '" + e.Text + "%'", cn)

da.Fill(dt)

Dim itemsPerRequest As Integer = 20

Dim itemOffset As Integer = e.NumberOfItems

Dim endOffset As Integer = itemOffset + itemsPerRequest

If endOffset > dt.Rows.Count Then

endOffset = dt.Rows.Count

End If

Dim i As Integer = itemOffset

While i < endOffset

spStr = Split(dt.Rows(i)("City").ToString(), "-")

ddlCity.Items.Add(New RadComboBoxItem(dt.Rows(i)("City").ToString()))

i = i + 1

End While

If dt.Rows.Count > 0 Then

e.Message = [String].Format("Items <b>1</b>-<b>{0}</b> out of <b>{1}</b>", endOffset.ToString(), dt.Rows.Count.ToString())

Else

e.Message = "No matches"

End If

Catch ex As Exception

lblMsg.Text = ex.Message

End Try

End Sub

Protected Sub ddlCity_SelectedIndexChanged(ByVal sender As Object, ByVal e As Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs) Handles ddlCity.SelectedIndexChanged

If ddlCity.Text <> "" Then

Dim r As Integer = obj.FetchValue("select Count(*) from city where ltrim(rtrim(cast(cid as varchar)))+'-'+ltrim(rtrim(cname))='" + ddlCity.Text + "'", cn)

If r = 0 Then

ddlCity.Focus()

'MsgBox("State not Found", MsgBoxStyle.OkOnly)

ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), Guid.NewGuid().ToString(), "alert('City Not Found...');", True)

ddlState.Text = "" : ScriptManager1.SetFocus(ddlState.ClientID + "_Input") : sFlag = 1 : Exit Sub

End If

End Sub

'Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnAdd.Click

' Try

' obj.AddComboTi(ddlState, "select ltrim(rtrim(cast(sid as varchar)))+'-'+ltrim(rtrim(sname)) from state", cn)

' Catch ex As Exception

' lblMsg.Text = ex.Message

' End Try

'End Sub

End Class

Class1.vb:-

Imports Microsoft.VisualBasic

Imports System.Data.SqlClient

Imports Telerik.Web.UI

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

Public Sub AddComboTi(ByVal CntName As RadComboBox, ByVal SqlStr As String, ByVal sCon As SqlConnection)

'Which will fill the DropdownList Based on Query

Try

CntName.Items.Clear()

'Dim Str As String = String.Empty

' CntName.Items.Add(New RadComboBoxItem(""))

sqlcmd = New SqlCommand(SqlStr, sCon)

sqldr = sqlcmd.ExecuteReader()

While sqldr.Read

CntName.Items.Add(New RadComboBoxItem(sqldr(0)))

End While

sqldr.Close() : sqlcmd.Dispose()

Catch ex As Exception

'MsgBox(ex.Message)

End Try

End Sub

Public Function FetchValue(ByVal qStr As String, ByVal sCon As SqlConnection) As String

'Which will give the Find Value Based on Query

Try

Dim sqlFetch = New SqlCommand(qStr, sCon)

FetchValue = sqlFetch.ExecuteScalar()

sqlFetch.Dispose()

Catch ex As Exception

FetchValue = ""

End Try

End Function

End Class

Create Table:-

create table state(sid int constraint p1 primary key,sname varchar(30))

create table city(cid int primary key,cname varchar(30),sid int constraint f1 foreign key references state(sid))

SubsDotNet

Friday, 25 April 2014

Search Result through TelerikRadComboBox in VB.NET

No comments:

Post a Comment