Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Protected Sub ddlCode_ItemsRequested(ByVal sender As Object, ByVal e As Telerik.Web.UI.RadComboBoxItemsRequestedEventArgs) Handles ddlCode.ItemsRequested
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)
ddlCode.Items.Add(New RadComboBoxItem(dt.Rows(i)("Policy").ToString()))
e.Message = [String].Format("Items <b>1</b>-<b>{0}</b> out of <b>{1}</b>", endOffset.ToString(), dt.Rows.Count.ToString())
Protected Sub ddlCode_SelectedIndexChanged(ByVal sender As Object, ByVal e As Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs) Handles ddlCode.SelectedIndexChanged
Dim r As Integer = obj.FetchValue("select COUNT(*) from policytbl where ltrim(rtrim(policyid))+'-'+ltrim(rtrim(policyname))='" + ddlCode.Text + "'", sqlcon)
sqlcmd = New SqlCommand("sp_XlsUploadAndValidation", sqlcon)
sqlcmd.Parameters.Add(New SqlParameter("@policyid", spStr(0)))
Protected Sub btnback_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnback.Click
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
Next
Next
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)
as
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
as
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
--get first of date and month of the year
