SubsDotNet: Upload Bulk Datas through Xls and Save in DB using VB.NET

here in first page upload bulk amount of datas through Xls,and in 2nd page show the uploaded datas..
Ans:-
1.

UploadXls.aspx:-

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

<!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>Upload Xls</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<center>
<table>
<tr>
<td>
<asp:FileUpload ID="fuMemberImport" runat="server" Font-Names="Verdana" Font-Size="8.5pt"
ForeColor="Black" Width="716px" /></td>
<td>
<asp:ImageButton ID="BtnUpload" runat="server" AccessKey="U" ToolTip="Alt+u"
ImageUrl="~/Images/btnupload.gif" />
<%--<asp:Button ID="BtnUpload" runat="server" Text="Upload" Width="72px" AccessKey="U" ToolTip="Alt+u" />--%>

</td>
<td style="width: 82px">
<asp:ImageButton ID="btnVerify" runat="server" AccessKey="V" ToolTip="Alt+v"
ImageUrl="~/Images/btnverify.gif"/>
<%--<asp:Button ID="btnVerify" runat="server" Text="Verify" Width="76px" AccessKey="V" ToolTip="Alt+v"/>--%>

</td>
<td style="width: 77px">
<asp:ImageButton ID="btnSave" runat="server" AccessKey="S" ToolTip="Alt+s" ImageUrl="~/Images/save.gif"/>
<%--<asp:Button ID="btnSave" runat="server" Text="Save" Width="76px"
AccessKey="S" ToolTip="Alt+s" Enabled="False"/>--%>

</td>
<td>
<asp:ImageButton ID="btnback" runat="server"
AccessKey="A" ToolTip="Alt+a" ImageUrl="~/Images/btnback.gif"/>
<%--<asp:Button ID="btnback" runat="server" Text="Approve" Width="76px"
AccessKey="A" ToolTip="Alt+a" Enabled="true"/>--%>

</td>
<td style="width: 77px">
<asp:ImageButton ID="btnClear" runat="server" AccessKey="C"
ImageUrl="~/Images/clear.gif" TabIndex="57" ToolTip="Alt+c" />
</td>
<td style="width: 77px">
 <asp:Image ID="ImgWait" runat="server" ImageUrl="~/Images/Progress.gif" Visible="False" />
<asp:Label ID="lblWMsg" runat="server" Text="Please Wait ......"
Visible="False"></asp:Label></td>

</tr>
</table>

<br />
<asp:Label ID="lblResult1" runat="server" Font-Bold="True" Font-Names="Verdana" Font-Size="Small"
ForeColor="Maroon"
style="height: 16px; text-align: center;" Height="22px"></asp:Label>
<br />

<asp:Panel ID="PnlGrid" runat="server" Height="500px" ScrollBars="Horizontal"
Width="1244px" Visible="False">
<asp:DataGrid ID="CommGrid" runat="server" CellPadding="4"
Font-Bold="False" Font-Names="Verdana" Font-Size="7pt" ForeColor="#333333"
GridLines="None" Height="1px" Width="1170px"
AutoGenerateColumns="False" Font-Italic="False" Font-Overline="False"
Font-Strikeout="False" Font-Underline="False" HorizontalAlign="Justify"
style="margin-bottom: 0px">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White"
Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" Wrap="False" />
<EditItemStyle BackColor="#2461BF" Font-Bold="False" Font-Italic="False"
Font-Overline="False" Font-Strikeout="False" Font-Underline="False"
Wrap="False" />
<SelectedItemStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333"
Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" Wrap="False" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Left"
Font-Bold="False" Font-Italic="False" Font-Overline="False"
Font-Strikeout="False" Font-Underline="False" Wrap="False" />
<AlternatingItemStyle BackColor="White" Font-Bold="False" Font-Italic="False"
Font-Overline="False" Font-Strikeout="False" Font-Underline="False"
Wrap="False" />
<ItemStyle BackColor="#EFF3FB" Wrap="False" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White"
Wrap="False" />
<Columns>
<asp:TemplateColumn>
<ItemTemplate>
<asp:Image runat="server" id="StImage" Visible="false" Width="20px"/>
</ItemTemplate>
</asp:TemplateColumn>

<asp:BoundColumn DataField="SNo" HeaderText="S.No" ReadOnly="True">
<ItemStyle Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" Wrap="False" />
<HeaderStyle Font-Bold="True" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" Width="20px" Wrap="False" />
</asp:BoundColumn>
<asp:BoundColumn DataField="policyid" HeaderText="policyid" ReadOnly="True">
<ItemStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" Wrap="False" />
<HeaderStyle Font-Bold="True" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" Width="50px" Wrap="False" />
</asp:BoundColumn>
<asp:BoundColumn DataField="policyname" HeaderText="policyname" ReadOnly="True">
<ItemStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" Wrap="False" />
<FooterStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" Wrap="False" />
<HeaderStyle Font-Bold="True" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" Width="200px" Wrap="False" />
</asp:BoundColumn>
<asp:BoundColumn DataField="providerid" HeaderText="providerid" ReadOnly="True">
<ItemStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" Wrap="False" />
<FooterStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" Wrap="False" />
<HeaderStyle Font-Bold="True" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" Width="200px" Wrap="False" />
</asp:BoundColumn>
<asp:BoundColumn DataField="providername" HeaderText="providername" ReadOnly="True">
<ItemStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" Wrap="False" />
<FooterStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" Wrap="False" />
<HeaderStyle Font-Bold="True" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" Width="200px" Wrap="False" />
</asp:BoundColumn>

<asp:BoundColumn DataField="uid" HeaderText="uid" ReadOnly="True">
<ItemStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" Wrap="False" />
<FooterStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" Wrap="False" />
<HeaderStyle Font-Bold="True" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Center" Width="30px" Wrap="False" />
</asp:BoundColumn>
<asp:BoundColumn DataField="Status" HeaderText=" Error_Description" ReadOnly="True">
<ItemStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" Wrap="False" />
<FooterStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" Wrap="False" />
<HeaderStyle Font-Bold="True" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
Font-Underline="False" HorizontalAlign="Left" Width="200px" Wrap="False" />
</asp:BoundColumn>

</Columns>
</asp:DataGrid>
</asp:Panel>

</center>
</div>
</form>
</body>
</html>

UploadXls.aspx.vb:-

Imports System

Imports System.Configuration

Imports System.Collections

Imports System.Web

Imports System.Web.Security

Imports System.Web.UI

Imports System.Web.UI.WebControls.WebParts

Imports System.Web.UI.HtmlControls

Imports System.Web.UI.WebControls

Imports System.Data

Imports System.Data.SqlClient

Imports System.ComponentModel

Imports System.IO

Imports System.IO.MemoryStream

Imports System.Text.RegularExpressions

Partial Class UploadXls

Inherits System.Web.UI.Page

Dim sqlcon As SqlConnection

Dim sqlcmd As SqlCommand

Dim sqldr As SqlDataReader

Dim sqlda As SqlDataAdapter

Dim dsImport As New DataSet

Dim obj As New Class1

Dim StImage As Image

Dim Errors As String = ""

Dim DumpError As String = ""

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

sqlcon = New SqlConnection(obj.Connect) : sqlcon.Open()

End Sub

Protected Sub BtnUpload_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles BtnUpload.Click

Dim flags As Integer = 0

Try

If fuMemberImport.HasFile = True Then

Dim xlQstr As String

Dim xlsStr As String

Dim fpath As String

Dim srvPath As String = Server.MapPath("~") & "\XLS\" & fuMemberImport.FileName

fpath = Path.GetFullPath(fuMemberImport.PostedFile.FileName)

fuMemberImport.SaveAs(srvPath)

LblConn:

If flags = 0 Then

xlsStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & srvPath & ";Extended Properties=Excel 12.0"

Else

xlsStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & srvPath & ";Extended Properties=Excel 8.0"

End If

Dim xlsCon As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(xlsStr)

ViewState("srvPath") = srvPath

xlQstr = "select SNO,policyid,policyname,providerid,providername,uid,status from [MemberDetails$]"

If xlsCon.State = 0 Then xlsCon.Close()

Dim da As New OleDb.OleDbDataAdapter(xlQstr, xlsCon)

da.Fill(dsImport, "MemberDetails")

CommGrid.DataSource = dsImport

CommGrid.DataBind()

PnlGrid.Visible = True

ViewState("Data") = dsImport

btnVerify.Enabled = True

btnVerify.ImageUrl = "Images/btnverify.gif"

lblResult1.Text = "Rows uploaded Successfully."

xlsCon.Close() : xlsCon.Dispose()

CommGrid.Visible = True

End If

Catch ex As Exception

If Err.Number = 5 Then

flags = flags + 1

If flags > 1 Then

lblResult1.Text = "Sorry, System could not found OLEDB 12.0 Provider, Please Convert XLSX to XLS format"

Exit Sub

End If

GoTo LblConn

Else

lblResult1.Text = obj.CustomEror(Err.Number)

End If

End Try

End Sub

Protected Sub Validations()

With CommGrid

For i = 0 To .Items.Count - 1

Errors = ""

If .Items(i).Cells(2).Text.Trim = " " Or .Items(i).Cells(2).Text.Trim = "" Then

lblResult1.Text = "Policyid should not be Empty."

Errors = Errors & lblResult1.Text & "," + "<br/>"

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

ElseIf .Items(i).Cells(2).Text <> " " Then

Dim policyid1 As String = .Items(i).Cells(2).Text.Trim

Dim length As Integer = Len(policyid1)

If length > 4 Then

lblResult1.Text = "policyid code should be in 4 Characters"

Errors = Errors & lblResult1.Text & "," + "<br/>"

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

End If

Dim count As String = obj.FetchValue("select COUNT(*) from tblpolicy where policyid='" + policyid1 + "'", sqlcon)

If count = 1 Then

lblResult1.Text = "policyid already Exists"

Errors = Errors & lblResult1.Text & "," + "<br/>"

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

End If

If .Items(i).Cells(3).Text.Trim = " " Or .Items(i).Cells(3).Text.Trim = "" Then

lblResult1.Text = "Policyname should not be Empty."

Errors = Errors & lblResult1.Text & "," + "<br/>"

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

ElseIf .Items(i).Cells(3).Text.Trim <> " " Then

If Split((.Items(i).Cells(3).Text), "-").Length > 1 Then

.Items(i).Cells(3).Text = .Items(i).Cells(3).Text.Replace("-", "_")

End If

If Regex.IsMatch(.Items(i).Cells(3).Text.ToString(), "[<>""'%~&!^?#@$|+*]") = True Then

lblResult1.Text = "Policyname should not Contain Special Characters(&,@,^,%,!,~,<,>,',|,#,+,*,?,"")"

Errors = Errors & lblResult1.Text & "," + "<br/>"

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

End If

If .Items(i).Cells(4).Text.Trim = " " Or .Items(i).Cells(4).Text.Trim = "" Then

lblResult1.Text = "providerid should not be Empty."

Errors = Errors & lblResult1.Text & "," + "<br/>"

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

ElseIf .Items(i).Cells(4).Text <> " " Then

Dim providerid1 As String = .Items(i).Cells(4).Text.Trim

Dim length As Integer = Len(providerid1)

If length > 3 Then

lblResult1.Text = "providerid code should be in 3 Characters"

Errors = Errors & lblResult1.Text & "," + "<br/>"

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

End If

Dim count As String = obj.FetchValue("select count(*) from tblprovider where providerid='" + providerid1 + "'", sqlcon)

If count = 1 Then

lblResult1.Text = "providerid already Exists"

Errors = Errors & lblResult1.Text & "," + "<br/>"

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

End If

If .Items(i).Cells(5).Text.Trim = " " Or .Items(i).Cells(5).Text.Trim = "" Then

lblResult1.Text = "Providername should not be Empty."

Errors = Errors & lblResult1.Text & "," + "<br/>"

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

ElseIf .Items(i).Cells(5).Text.Trim <> " " Then

If Split((.Items(i).Cells(5).Text), "-").Length > 1 Then

.Items(i).Cells(5).Text = .Items(i).Cells(5).Text.Replace("-", "_")

End If

If Regex.IsMatch(.Items(i).Cells(5).Text.ToString(), "[<>""'%~&!^?#@$|+*]") = True Then

lblResult1.Text = "Providername should not Contain Special Characters(&,@,^,%,!,~,<,>,',|,#,+,*,?,"")"

Errors = Errors & lblResult1.Text & "," + "<br/>"

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

End If

If .Items(i).Cells(6).Text.Trim = " " Or .Items(i).Cells(5).Text.Trim = "" Then

lblResult1.Text = "uid should not be Empty."

Errors = Errors & lblResult1.Text & "," + "<br/>"

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

End If

If Errors <> "" Then

StImage = CType(.Items(i).Cells(0).FindControl("StImage"), Image)

StImage.ImageUrl = "Images/Wrong.png"

Else

StImage = CType(.Items(i).FindControl("StImage"), Image)

StImage.ImageUrl = "Images/Right.png"

End If

StImage.Visible = True

.Items(i).Cells(7).Text = Errors

DumpError = DumpError & Errors

If DumpError <> "" Then

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

lblResult1.Text = "Rows not Verified Successfully, Please check Error Description."

Else

btnSave.Enabled = True

btnSave.ImageUrl = "Images/save.gif"

lblResult1.Text = "All Rows Verified Successfully"

End If

End With

End Sub

Protected Sub btnVerify_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnVerify.Click

Call Validations()

CommGrid.Visible = True

End Sub

Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnSave.Click

Try

lblWMsg.Visible = True

ImgWait.Visible = True

Dim strCon As String

Dim policyid As Integer

Dim policyname As String

Dim providerid As Integer

Dim providername As String

Dim uid As Integer

strCon = "<Root> "

With CommGrid

For i = 0 To .Items.Count - 1

If Len(.Items(i).Cells(1).Text) > 0 Then

policyid = .Items(i).Cells(2).Text

policyname = .Items(i).Cells(3).Text

providerid = .Items(i).Cells(4).Text

providername = .Items(i).Cells(5).Text

uid = .Items(i).Cells(6).Text

strCon = strCon & "<strCon SNo = """ & .Items(i).Cells(1).Text & """ policyid=""" & policyid & """ policyname=""" & policyname & """ providerid=""" & providerid & """ providername=""" & providername & """ uid=""" & uid & """/>"

End If

End With

strCon = Trim(strCon) & "</Root>"

sqlcmd = New SqlCommand("sp_XlsUploadDataSave", sqlcon)

sqlcmd.CommandType = CommandType.StoredProcedure

sqlcmd.Parameters.Add(New SqlParameter("@xmlStr", strCon))

sqlcmd.CommandTimeout = 0

sqlcmd.ExecuteNonQuery()

lblResult1.Text = " Data Saved Sucessfully."

fuMemberImport.Visible = False

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

BtnUpload.Enabled = False

BtnUpload.ImageUrl = "Images/btnupload.gif"

lblWMsg.Visible = False

ImgWait.Visible = False

CommGrid.Visible = True

Catch ex As Exception

lblResult1.Text = obj.CustomEror(Err.Number)

End Try

End Sub

Protected Sub btnClear_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnClear.Click

fuMemberImport.Visible = True

BtnUpload.Enabled = True

BtnUpload.ImageUrl = "Images/btnupload.gif"

CommGrid.Visible = False

btnVerify.Enabled = False

btnVerify.ImageUrl = "Images/btnverify.gif"

btnSave.Enabled = False

btnSave.ImageUrl = "Images/save.gif"

lblResult1.Text = ""

End Sub

Protected Sub btnback_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnback.Click

Response.Redirect("FillValidateTextboxThroughUploadXls.aspx")

End Sub

End Class

FillValidateTextboxThroughUploadXls.aspx:-

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

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

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

<title>Fill & Validate Textboxes Through Upload Xls</title>

</head>

<body>

<asp:ScriptManager ID="ScriptManager1" runat="server">

</asp:ScriptManager>

<div>

<table>

<tr>

<td>

<asp:Label ID="Label1" Text="Policy : " runat="server" Font-Bold="True" ></asp:Label>

</td>

<td>

<telerik:radcombobox ID="ddlCode" runat="server" AllowCustomText="true" AutoPostBack="true"

BorderColor="#666666" BorderStyle="Solid" BorderWidth="1px" DropDownCssClass="RadComboBox_Sunset"

EmptyMessage="Select Insurance Company"

EnableEmbeddedSkins="False" EnableLoadOnDemand="true"

EnableViewState="false" EnableVirtualScrolling="True"

Font-Names="Verdana" Font-Size="8.5pt"

MarkFirstMatch="true" ShowMoreResultsBox="True" Skin="Sunset"

Width="300px" TabIndex="12">

</telerik:radcombobox>

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label2" Text="Policy Code : " runat="server" Font-Bold="True" ></asp:Label>

</td>

<td>

<asp:TextBox runat="server" ID="txtPolicyCode" Width="144" ReadOnly="True" Rows="10"

TabIndex="3"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label3" Text="Policy Name : " runat="server" Font-Bold="True" ></asp:Label>

</td>

<td>

<asp:TextBox runat="server" ID="txtPolicyName" Width="144" ReadOnly="True" Rows="10"

TabIndex="3"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label4" Text="Provider Code : " runat="server" Font-Bold="True" ></asp:Label>

</td>

<td>

<asp:TextBox runat="server" ID="txtProviderCode" Width="144" ReadOnly="True" Rows="10"

TabIndex="3"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label5" Text="Provider Name : " runat="server" Font-Bold="True" ></asp:Label>

</td>

<td>

<asp:TextBox runat="server" ID="txtProviderName" Width="144" ReadOnly="True" Rows="10"

TabIndex="3"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label6" Text="User Id : " runat="server" Font-Bold="True" ></asp:Label>

</td>

<td>

<asp:TextBox runat="server" ID="txtUserid" Width="144" ReadOnly="True" Rows="10"

TabIndex="3"></asp:TextBox>

</td>

</tr>

<tr>

<asp:Label ID="lblResult" runat="server" Font-Bold="True" Width="542px" ></asp:Label>

<asp:TextBox runat="server" ID="txtRowCount" Height="1" Width="1" ></asp:TextBox>

<asp:TextBox ID="txtCount" runat="server" BorderColor="ActiveCaptionText" BorderStyle="None"

Font-Size="3pt" ForeColor="White" Height="1px" Width="1px"></asp:TextBox>

<asp:TextBox ID="txtspStr1" runat="server" BorderColor="ActiveCaptionText" BorderStyle="None"

Font-Size="3pt" ForeColor="White" Height="1px" Width="1px"></asp:TextBox>

</td>

</tr>

<tr>

<asp:ImageButton ID="btnback" runat="server" AccessKey="D" ImageUrl="~/Images/btnback.gif"

TabIndex="56" ToolTip="Alt+d" Visible="true" />

</td>

</tr>

</table>

</center>

</div>

</form>

</body>

</html>

FillValidateTextboxThroughUploadXls.aspx.vb:-

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Web.UI.WebControls

Imports Telerik.Web.UI

Partial Class FillValidateTextboxThroughUploadXls

Inherits System.Web.UI.Page

Dim sqlcon As SqlConnection

Dim sqlcmd As SqlCommand

Dim sqlad As SqlDataAdapter

Dim sqldr As SqlDataReader

Dim dt As New DataTable

Dim ds As New DataSet

Dim obj As New Class1

Dim spStr() As String

Dim dsComm As New DataSet

Dim sFlag As Integer = 0

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

sqlcon = New SqlConnection(obj.Connect) : sqlcon.Open()

End Sub

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

Try

If sqlcon.State = ConnectionState.Closed Then sqlcon.Open()

sqlad = New SqlDataAdapter("select ltrim(rtrim(policyid))+'-'+ltrim(rtrim(policyname)) as Policy from policytbl where (policyname Like '" + e.Text + "%' or policyid Like '" + e.Text + "%')", sqlcon)

sqlad.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

ddlCode.Items.Add(New RadComboBoxItem(dt.Rows(i)("Policy").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

lblResult.Text = obj.CustomEror(Err.Number)

Finally

sqlcon.Close()

End Try

End Sub

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

Try

ddlCode.Attributes.Add("style", "display:''")

If Len(ddlCode.Text) > 0 Then

Dim r As Integer = obj.FetchValue("select COUNT(*) from policytbl where ltrim(rtrim(policyid))+'-'+ltrim(rtrim(policyname))='" + ddlCode.Text + "'", sqlcon)

If r = 0 Then

obj.MessageBox(Me, "Code not Found.") : ddlCode.Text = ""

ddlCode.Focus() : obj.PageClear(Me) : sFlag = 1 : Exit Sub

Else

spStr = ddlCode.Text.Split("-")

sqlcmd = New SqlCommand("sp_XlsUploadAndValidation", sqlcon)

sqlcmd.CommandType = CommandType.StoredProcedure

sqlcmd.Parameters.Add(New SqlParameter("@policyid", spStr(0)))

sqlad = New SqlDataAdapter(sqlcmd)

sqlad.Fill(dsComm, "policytbl")

With dsComm.Tables(0).Rows(0)

txtPolicyCode.Text = .Item("policyid")

txtPolicyName.Text = .Item("policyname")

txtProviderCode.Text = .Item("providerid")

txtProviderName.Text = .Item("providername")

txtUserid.Text = .Item("uid")

End With

ViewState("policytbl") = dsComm

txtRowCount.Text = dsComm.Tables("policytbl").Rows.Count

lblResult.Text = "" : sFlag = 0

End If

Catch ex As Exception

lblResult.Text = obj.CustomEror(Err.Number)

End Try

End Sub

Protected Sub btnback_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnback.Click

Response.Redirect("UploadXls.aspx")

End Sub

End Class

Class1.vb:-

Imports Microsoft.VisualBasic

Imports System.Web.UI.WebControls

Imports System

Imports System.Data

Imports System.Configuration

Imports System.Collections

Imports System.Web

Imports System.Web.Security

Imports System.Web.UI

Imports System.Web.UI.WebControls.WebParts

Imports System.Web.UI.HtmlControls

Imports System.Data.SqlClient

Imports System.Data.Odbc

Imports Telerik.Web.UI

Public Class Class1

Dim sqlcmd As SqlCommand

Dim sqldr As SqlDataReader

Public dsImport As New DataSet

Public Function Connect() As String

Connect = "Data source=192.168.0.200;Database=SubsDB;Uid=sa;Pwd=amtpl@123;Pooling=true;Min Pool Size=2;Max Pool Size=1000"

End Function

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 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 Function getDataSet(ByVal Sqlstr As String, ByVal Sqcon As SqlConnection) As DataSet

Dim getDS As New DataSet

Try

Dim sqlad As New SqlDataAdapter(Sqlstr, Sqcon)

sqlad.Fill(getDS)

getDataSet = getDS

Catch ex As Exception

getDataSet = getDS

End Try

End Function

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

Public Sub MessageBox(ByVal pName As Page, ByVal strMsg As String)

Dim lbl As New Label

lbl.Text = "<script language='javascript'>" & Environment.NewLine & "window.alert(" & "'" & strMsg & "'" & ")</script>"

pName.Controls.Add(lbl)

End Sub

Public Sub PageClear(ByVal pName As Page)

'-----------------------------------

'Which will Clear the Form

'-----------------------------------

Dim cCount As Control

Dim control As Control

For Each cCount In pName.Controls

For Each control In cCount.Controls

If TypeOf control Is TextBox Then

Dim TextBox As TextBox = control

TextBox.Text = ""

ElseIf TypeOf control Is DropDownList Then

Dim DropDownList As DropDownList = control

DropDownList.SelectedIndex = -1

ElseIf TypeOf control Is ListBox Then

Dim ListBox As ListBox = control

ListBox.SelectedIndex = -1

ElseIf TypeOf control Is RadioButton Then

Dim RadioButton As RadioButton = control

RadioButton.Checked = False

ElseIf TypeOf control Is CheckBox Then

Dim CheckBox As CheckBox = control

CheckBox.Checked = False

ElseIf TypeOf control Is RadDatePicker Then

Dim RadDatePicker As RadDatePicker = control

RadDatePicker.Clear()

RadDatePicker.Calendar.SelectedDates.Clear()

ElseIf TypeOf control Is RadDateTimePicker Then

Dim RadDateTimePicker As RadDateTimePicker = control

RadDateTimePicker.Clear()

RadDateTimePicker.Calendar.SelectedDates.Clear()

End If

End Sub

Public Function CustomEror(ByVal ErrCode As String) As String

If ErrCode = 5 Then

CustomEror = "Invalid procedure call or argument"

ElseIf ErrCode = 6 Then

CustomEror = "Overflow"

ElseIf ErrCode = 7 Then

CustomEror = "Out of memory"

ElseIf ErrCode = 9 Then

CustomEror = "Subscript out of range"

ElseIf ErrCode = 10 Then

CustomEror = "Array fixed or temporarily locked"

ElseIf ErrCode = 11 Then

CustomEror = "Division by zero"

ElseIf ErrCode = 13 Then

CustomEror = "Type mismatch"

ElseIf ErrCode = 14 Then

CustomEror = "Out of string space"

ElseIf ErrCode = 28 Then

CustomEror = "Out of stack space"

ElseIf ErrCode = 35 Then

CustomEror = "Sub or Function not defined"

ElseIf ErrCode = 48 Then

CustomEror = "Error in loading DLL"

ElseIf ErrCode = 51 Then

CustomEror = "Internal error"

ElseIf ErrCode = 53 Then

CustomEror = "File not found"

ElseIf ErrCode = 57 Then

CustomEror = "Device I/O error"

ElseIf ErrCode = 58 Then

CustomEror = "File already exists"

ElseIf ErrCode = 61 Then

CustomEror = "Disk full"

ElseIf ErrCode = 67 Then

CustomEror = "Too many files"

ElseIf ErrCode = 70 Then

CustomEror = "Permission denied"

ElseIf ErrCode = 75 Then

CustomEror = "Path/File access error"

ElseIf ErrCode = 76 Then

CustomEror = "Path not found"

ElseIf ErrCode = 91 Then

CustomEror = "Object variable or With block variable not set"

ElseIf ErrCode = 92 Then

CustomEror = "For loop not initialized"

ElseIf ErrCode = 94 Then

CustomEror = "Invalid use of Null"

ElseIf ErrCode = 322 Then

CustomEror = "Can't create necessary temporary file"

ElseIf ErrCode = 424 Then

CustomEror = "Object required"

ElseIf ErrCode = 429 Then

CustomEror = "ActiveX component can't create object"

ElseIf ErrCode = 430 Then

CustomEror = "Class doesn't support Automation"

ElseIf ErrCode = 432 Then

CustomEror = "File name or class name not found during Automation operation"

ElseIf ErrCode = 438 Then

CustomEror = "Object doesn't support this property or method"

ElseIf ErrCode = 440 Then

CustomEror = "Automation error"

ElseIf ErrCode = 445 Then

CustomEror = "Object doesn't support this action"

ElseIf ErrCode = 446 Then

CustomEror = "Object doesn't support named arguments"

ElseIf ErrCode = 447 Then

CustomEror = "Object doesn't support current local setting"

ElseIf ErrCode = 448 Then

CustomEror = "Named argument not found"

ElseIf ErrCode = 449 Then

CustomEror = "Argument not optional"

ElseIf ErrCode = 450 Then

CustomEror = "Wrong number of arguments or invalid property assignment"

ElseIf ErrCode = 451 Then

CustomEror = "Object not a collection"

ElseIf ErrCode = 453 Then

CustomEror = "Specified DLL function not found"

ElseIf ErrCode = 455 Then

CustomEror = "Code resource lock error"

ElseIf ErrCode = 457 Then

CustomEror = "This key already associated with an element of this "

ElseIf ErrCode = 458 Then

CustomEror = "Variable uses an Automation type not supported in VBScript"

ElseIf ErrCode = 500 Then

CustomEror = "Variable is undefined"

ElseIf ErrCode = 501 Then

CustomEror = "Illegal assignment"

ElseIf ErrCode = 502 Then

CustomEror = "Object not safe for scripting"

ElseIf ErrCode = 503 Then

CustomEror = "Object not safe for initializing"

ElseIf ErrCode = 1001 Then

CustomEror = "Out of memory"

ElseIf ErrCode = 1002 Then

CustomEror = "Syntax error"

ElseIf ErrCode = 1003 Then

CustomEror = "Expected ':'"

ElseIf ErrCode = 1004 Then

CustomEror = "Expected ';'"

ElseIf ErrCode = 1005 Then

CustomEror = "Expected '('"

ElseIf ErrCode = 1006 Then

CustomEror = "Expected ')'"

ElseIf ErrCode = 1007 Then

CustomEror = "Expected ']'"

ElseIf ErrCode = 1008 Then

CustomEror = "Expected '{'"

ElseIf ErrCode = 1009 Then

CustomEror = "Expected '}'"

ElseIf ErrCode = 1010 Then

CustomEror = "Expected identifier"

ElseIf ErrCode = 1011 Then

CustomEror = "Expected '='"

ElseIf ErrCode = 1012 Then

CustomEror = "Expected 'If'"

ElseIf ErrCode = 1013 Then

CustomEror = "Expected 'To'"

ElseIf ErrCode = 1014 Then

CustomEror = "Expected 'End'"

ElseIf ErrCode = 1015 Then

CustomEror = "Expected 'Function'"

ElseIf ErrCode = 1016 Then

CustomEror = "Expected 'Sub'"

ElseIf ErrCode = 1017 Then

CustomEror = "Expected 'Then'"

ElseIf ErrCode = 1018 Then

CustomEror = "Expected 'Wend'"

ElseIf ErrCode = 1019 Then

CustomEror = "Expected 'Loop'"

ElseIf ErrCode = 1020 Then

CustomEror = "Expected 'Next'"

ElseIf ErrCode = 1021 Then

CustomEror = "Expected 'Case'"

ElseIf ErrCode = 1022 Then

CustomEror = "Expected 'Select'"

ElseIf ErrCode = 1023 Then

CustomEror = "Expected expression"

ElseIf ErrCode = 1024 Then

CustomEror = "Expected statement"

ElseIf ErrCode = 1025 Then

CustomEror = "Expected end of statement"

ElseIf ErrCode = 1026 Then

CustomEror = "Expected integer constant"

ElseIf ErrCode = 1027 Then

CustomEror = "Expected 'While' or 'Until'"

ElseIf ErrCode = 1028 Then

CustomEror = "Expected 'While', 'Until', or end of statement"

ElseIf ErrCode = 1029 Then

CustomEror = "Too many locals or arguments"

ElseIf ErrCode = 1030 Then

CustomEror = "Identifier too long"

ElseIf ErrCode = 1031 Then

CustomEror = "Invalid number"

ElseIf ErrCode = 1032 Then

CustomEror = "Invalid character"

ElseIf ErrCode = 1033 Then

CustomEror = "Unterminated string constant"

ElseIf ErrCode = 1034 Then

CustomEror = "Unterminated comment"

ElseIf ErrCode = 1035 Then

CustomEror = "Nested comment"

ElseIf ErrCode = 1037 Then

CustomEror = "Invalid use of 'Me' keyword"

ElseIf ErrCode = 1038 Then

CustomEror = "'Loop' without 'Do'"

ElseIf ErrCode = 1039 Then

CustomEror = "Invalid 'Exit' statement"

ElseIf ErrCode = 1040 Then

CustomEror = "Invalid 'For' loop control variable"

ElseIf ErrCode = 1041 Then

CustomEror = "Name redefined"

ElseIf ErrCode = 1042 Then

CustomEror = "Must be first statement on the line"

ElseIf ErrCode = 1043 Then

CustomEror = "Can't assign to non-ByVal argument"

ElseIf ErrCode = 1044 Then

CustomEror = "Can't use parens when calling a Sub"

ElseIf ErrCode = 1045 Then

CustomEror = "Expected literal constant"

ElseIf ErrCode = 1046 Then

CustomEror = "Expected 'In'"

ElseIf ErrCode = 32766 Then

CustomEror = "TRUE"

ElseIf ErrCode = 32767 Then

CustomEror = "FALSE"

ElseIf ErrCode = 32811 Then

CustomEror = "Element not found"

Else

CustomEror = "Sorry, Unexception Error found"

End If

End Function

End Class

DB Script/SP:-

sp_helptext sp_xlsuploadandvalidation

CREATE proc sp_XlsUploadAndValidation(@policyid int)

begin

select distinct po.policyid,po.policyname,pr.providername,pr.providerid,po.[uid] from policytbl po inner join providertbl pr on po.providerid=pr.providerid

where po.policyid=@policyid

end

@@@@@@@@@@@@@@@@@@@@@@

sp_helptext sp_xlsuploaddatasave

CREATE proc sp_XlsUploadDataSave

@xmlStr text

begin transaction

Declare @sno int

declare @policyid int

declare @policyname varchar(30)

declare @providerid int

declare @providername varchar(30)

declare @uid int

declare @cDoc int

create table #tblSaveUploadXlsData(sno int,policyid int,policyname varchar(30),providerid int,providername varchar(30),uid int)

EXEC sp_xml_preparedocument @cDoc OUTPUT, @xmlStr

insert into #tblSaveUploadXlsData(sno,policyid,policyname,providerid,providername,uid)

Select * from OPENXML (@cDoc,'/Root/strCon',1)

With (sno int,policyid int,policyname varchar(30),providerid int,providername varchar(30),uid int)

EXEC sp_xml_removedocument @cDoc

Declare Cur_SaveBulkUploadXlsData Cursor local scroll for select sno,policyid,policyname,providerid,providername,uid from #tblSaveUploadXlsData

Open Cur_SaveBulkUploadXlsData

Fetch next from Cur_SaveBulkUploadXlsData into @sno,@policyid,@policyname,@providerid,@providername,@uid

While Not @@FETCH_STATUS <> 0

Begin

if exists(select providerid from providertbl where providerid!=@providerid)

Begin

Insert into providertbl(providerid,providername)

Select @providerid,@providername

Insert into policytbl(policyid,policyname,providerid,uid)

Select @policyid,@policyname,@providerid,@uid

End

Fetch next from Cur_SaveBulkUploadXlsData into @sno,@policyid,@policyname,@providerid,@providername,@uid

End

Close Cur_SaveBulkUploadXlsData

Deallocate Cur_SaveBulkUploadXlsData

If @@ERROR <> 0 ROLLBACK TRANSACTION

COMMIT TRANSACTION

SubsDotNet

Wednesday, 23 July 2014

Upload Bulk Datas through Xls and Save in DB using VB.NET

No comments:

Post a Comment