|
Hi bro, wow very nice bro like it. Thank you so much bro, i really appreciate your code project "Reading and writing excel file using VB.NET."
|
|
|
|
|
Please remove yourself from the computer programming field, jesus.
|
|
|
|
|
Thanks for the suggention. I will definatly think about this, but could you please let me know why you want me to quite from my job. If you are having some issue with the code then let me know. I will try my best to reslove your issue.
Regards
Raman Tayal
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
Because the code you posted doesn't not run "out of the box". What's the point of posting code that doesn't work? When I say doesn't work, I should be able to download a code example and it should run without an issue.
|
|
|
|
|
Hi
I really appologies for the inconvenience caused. I forget to include the sample excel file in the source code. This code is running perfectly and thousands for users are using this code. As of now to run this code please create an excel file on c drive name as test.xls. Add the four column in the excel as shown in the image given in the article.
e.g.: -
1. FirstName
2. LastName
3. Age
4. Phone.
Now try to run the code, this should work as expected. If you are still facing any issue please feel free to contact me.
Thanks
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
Thanks bro, don't listen to lazy people that instead of critizicing should thank you for your time uploading this solution.
Regards,
|
|
|
|
|
Thanks for the appriciation
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
Thanks alot, I love it when people post code that simple doesn't run out of the box. Please, remove yourself from the field.
|
|
|
|
|
Please reply me ASAP. Thanks
Hi, I used your code for select, insert data in excel.
I implemented it and runs successfully.
If number of rows in dataset is less, i.e. aroud 100 then it works fast. But I have more than 2000 rows in my dataset and the mathod oleDataAdepter.InsertCommand = cmdInsertCommand is taking too much time to write thode data in excel. it takes around 10 minutes.
whereas Time taken when I manually loop throw my dataset and wrte data using wrksheet.Range("A" & rowNum).Value = dsPlan.Tables(0).Rows(i)("mphId") is arond 3-4 minutes.
I thought your code will work fast as it does not involves looping. and use Inser, Update methods of oleDBComman. But I dont know why it takes much time.
Here is my method which accepts dataset as input parameter which contains data selected from database tables of SQL Server. I want to write those data in excel sheet.
Private Sub WriteVegUnitData(ByVal ds As DataSet)
'Parameter dataset "ds" which contains data selected from SQL Server
Dim olecon As OleDbConnection
Dim oleSelectCmd As OleDbCommand
Dim oleInsertCmd As OleDbCommand
Dim oleadpt As OleDbDataAdapter
Dim dsExcel As DataSet
Dim pram As OleDbParameter
Dim drExcel As DataRow
Try
olecon = New OleDbConnection
olecon.ConnectionString = connstring
oleSelectCmd = New OleDbCommand
oleSelectCmd.CommandText = "Select ID, VEGWORKPLANID, ACTION, VEGSPECIESNAME, VEGSIZENAME, ACTIONDETAILNAME, ACTIONDETAILDISTANCE, VEGSPECIESGROWTHTYPEID, MAPPING_ID, NEW_REC_ID, DEL from [VEGUNIT$]"
oleSelectCmd.Connection = olecon
oleInsertCmd = New OleDbCommand
oleInsertCmd.CommandText = "Insert into [VEGUNIT$] " & _
"(ID, VEGWORKPLANID, ACTION, VEGSPECIESNAME, VEGSIZENAME, ACTIONDETAILNAME, ACTIONDETAILDISTANCE, VEGSPECIESGROWTHTYPEID, MAPPING_ID, NEW_REC_ID, DEL) values (@ID, @VEGWORKPLANID, @ACTION, @VEGSPECIESNAME, @VEGSIZENAME, @ACTIONDETAILNAME, @ACTIONDETAILDISTANCE, @VEGSPECIESGROWTHTYPEID, @MAPPING_ID, @NEW_REC_ID, @DEL)"
oleInsertCmd.Connection = olecon
pram = oleInsertCmd.Parameters.Add("@ID", OleDbType.VarChar)
pram.SourceColumn = "ID"
pram = oleInsertCmd.Parameters.Add("@VEGWORKPLANID", OleDbType.VarChar)
pram.SourceColumn = "VEGWORKPLANID"
pram = oleInsertCmd.Parameters.Add("@ACTION", OleDbType.VarChar)
pram.SourceColumn = "ACTION"
pram = oleInsertCmd.Parameters.Add("@VEGSPECIESNAME", OleDbType.VarChar)
pram.SourceColumn = "VEGSPECIESNAME"
pram = oleInsertCmd.Parameters.Add("@VEGSIZENAME", OleDbType.VarChar)
pram.SourceColumn = "VEGSIZENAME"
pram = oleInsertCmd.Parameters.Add("@ACTIONDETAILNAME", OleDbType.VarChar)
pram.SourceColumn = "ACTIONDETAILNAME"
pram = oleInsertCmd.Parameters.Add("@ACTIONDETAILDISTANCE", OleDbType.VarChar)
pram.SourceColumn = "ACTIONDETAILDISTANCE"
pram = oleInsertCmd.Parameters.Add("@VEGSPECIESGROWTHTYPEID", OleDbType.VarChar)
pram.SourceColumn = "VEGSPECIESGROWTHTYPEID"
pram = oleInsertCmd.Parameters.Add("@MAPPING_ID", OleDbType.VarChar)
pram.SourceColumn = "MAPPING_ID"
pram = oleInsertCmd.Parameters.Add("@NEW_REC_ID", OleDbType.VarChar)
pram.SourceColumn = "NEW_REC_ID"
pram = oleInsertCmd.Parameters.Add("@DEL", OleDbType.VarChar)
pram.SourceColumn = "DEL"
oleadpt = New OleDbDataAdapter(oleSelectCmd)
dsExcel = New DataSet
olecon.Open()
oleadpt.Fill(dsExcel)
If IsNothing(dsExcel) = False Then
For Each dr As DataRow In ds.Tables(0).Rows
drExcel = dsExcel.Tables(0).NewRow
drExcel("ID") = dr("strID")
drExcel("VEGWORKPLANID") = dr("wpdWphID")
drExcel("ACTION") = dr("wpdPruneRemove")
drExcel("VEGSPECIESNAME") = dr("vspSpeciesDesc")
drExcel("VEGSIZENAME") = dr("vszSizeDesc")
drExcel("ACTIONDETAILNAME") = dr("vacActionDesc")
drExcel("ACTIONDETAILDISTANCE") = dr("vdsDistanceDesc")
drExcel("VEGSPECIESGROWTHTYPEID") = dr("wpdGrowthTypeId")
drExcel("MAPPING_ID") = dr("wpdID")
drExcel("NEW_REC_ID") = "A"
drExcel("DEL") = dr("dlt")
dsExcel.Tables(0).Rows.Add(drExcel)
Next
oleadpt = New OleDbDataAdapter
oleadpt.InsertCommand = oleInsertCmd
Dim i As Integer = oleadpt.Update(dsExcel)
MessageBox.Show(i & " row affected")
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
olecon.Close()
olecon = Nothing
oleSelectCmd = Nothing
oleInsertCmd = Nothing
oleadpt = Nothing
dsExcel = Nothing
drExcel = Nothing
pram = Nothing
End Try
End Sub
|
|
|
|
|
Hi
Sorry for my delayed response to your query. I have gone through your code and my understanding is you are trying to fetch the data from one excel or any database and put the same into some other excel file or any database. I have found some performance issues in your code. I have try to simulate the same case at my end and which is working perfectly and take approximate some couple of seconds to do all the processing.
Following are the details for the same
I have taken 2 buttons on my windows application one for fetching the data and one for inserting the data. In this code I am reading more than 2000 records from Test.xls file and inserting the same in Test1.xls file. One thing I am assuming here is xls file are already present on your system.
Dim olecon As OleDbConnection
Dim oleadpt As OleDbDataAdapter
Dim ds As DataSet
Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
Private Const connstring1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test1.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
Private Sub btnFetch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim olecomm As OleDbCommand
Try
olecon = New OleDbConnection
olecon.ConnectionString = connstring
olecomm = New OleDbCommand
olecomm.CommandText = "Select FirstName, LastName, Age, Phone from [Sheet1$]"
olecomm.Connection = olecon
oleadpt = New OleDbDataAdapter(olecomm)
ds = New DataSet
olecon.Open()
oleadpt.Fill(ds, "Sheet1")
If (IsNothing(ds) = False) Then
DataGridView1.DataSource = ds.Tables(0)
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
olecon.Close()
olecon = Nothing
olecomm = Nothing
oleadpt = Nothing
End Try
End Sub
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim pram As OleDbParameter
Dim dr As DataRow
Dim olecomm1 As OleDbCommand
Try
olecon = New OleDbConnection
olecon.ConnectionString = connstring1
olecomm1 = New OleDbCommand
olecomm1.CommandText = "Insert into [Sheet1$] " & _
"(FirstName, LastName, Age, Phone) values (@FName, @LName, @Age, @Phone)"
olecomm1.Connection = olecon
pram = olecomm1.Parameters.Add("@FName", OleDbType.VarChar)
pram.SourceColumn = "FirstName"
pram = olecomm1.Parameters.Add("@LName", OleDbType.VarChar)
pram.SourceColumn = "LastName"
pram = olecomm1.Parameters.Add("@Age", OleDbType.VarChar)
pram.SourceColumn = "Age"
pram = olecomm1.Parameters.Add("@Phone", OleDbType.VarChar)
pram.SourceColumn = "Phone"
If (IsNothing(ds) = False) Then
For Each dr In ds.Tables(0).Rows
dr.SetAdded()
Next
oleadpt = New OleDbDataAdapter
oleadpt.InsertCommand = olecomm1
olecon.Open()
Dim i As Integer = oleadpt.Update(ds, "Sheet1")
MessageBox.Show(i & " row affected")
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If (IsNothing(olecon) = False) Then
olecon.Close()
End If
olecon = Nothing
olecomm1 = Nothing
dr = Nothing
pram = Nothing
End Try
End Sub
I hope this will answer your query.
Please feel free to write me in case of any query
Raman Tayal
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
Dataset ds contains data from SQL Server.
I have more than 32 colums in dataset which I need to transform to excel sheet.
Earlier I was assigning values to each cell by looping through dataset as below.
dim i as integer
dim rowNum as integer
Do While i < ds.Tables(0).Rows.Count
wrksheet.Range("A" & rowNum).Value = ds.Tables(0).Rows(i)("wphId")
wrksheet.Range("B" & rowNum).Value = ds.Tables(0).Rows(i)("wphMpdId")
wrksheet.Range("C" & rowNum).Value = ds.Tables(0).Rows(i)("wphCustId")
wrksheet.Range("D" & rowNum).Value = ds.Tables(0).Rows(i)("wphComnts")
'
'
'
'
so on up to total 32 colums
'
'
i += 1
rowNum += 1Loop
Loop
But I found that, instead of going trough all cells one by one we can directly assign values to all cells by using following code
which gives me result within couple if seconds
compare to 32 lines above now i have to write onlyn one line.
Do While i < ds.Tables(0).Rows.Count
wrksheet.Range("A1:AF1").Value = ds.Tables(0).Rows(i).ItemArray
i += 1
Loop
of course your code will give even faster result than above one, but as I was in hurry and need to complete task within my time limit, I found above one as a faster alternative.
today I tried your code, you used something like, "dr.SetAdded()", I am using VB.NET2003 and this gives me error like datarow have no method named SetAdded()
Any way, thanks a lot for answering my question. It was my first question in this website and had good experience.
Thanks
|
|
|
|
|
Dear,,
after I read the excel file and converted it into a dataset
how can I insert these dataset into my dataase(SQLserver database)?
Thanks in advance
gho;.lkijh
|
|
|
|
|
|
|
|
Hello all,
Please help me.
I am reading the excel data using ADO.NET. however when the user selects the excel file from the openfile dialog box it throws the message
"object reference not set to an instance" on the target machine.
however same executable does work in the development enviornment. Development enviornment has Office 2003 and target machine has 2000. I have tried running the applicatino on a computer which has Office 2000. It works there as well but not on the target machine. The target machine is a CITRIX SERVER. However an old exe of the same application works fine on the target machine.
Please help me urgently.
Thanks in advance
|
|
|
|
|
Please send me the piece of code. Also mentioned the location where you get error and at what condition
Raman Tayal
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
Here is the code:;
User get the error when he browses to the location of the file and click on the open button of a file dialog box.
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Imports System.Runtime.Serialization.Formatters
Imports System.Runtime.Serialization.Formatters.Binary
'Imports System.Xml.Serialization
Public Class frmUpload
Inherits System.Windows.Forms.Form
'Sorted List for Storing Information about Sheets of the Selected .xls file
Private objSheetList As New ArrayList
Dim strFileName As String
Dim strArr()
Dim arrInt As Integer
Dim xlsApp As Excel.Application
Dim xlsWB As Excel.Workbook
Dim xlsSheet As Excel.Worksheet
Dim xlsRng As Excel.Range
Dim MerchantDealRowStart As Int32 = 0 ' Stores the row where Merchant deals start.
Dim MerchantContractStart As Int32 = 0 'Stores the row where Merchant contract deals start
Dim sheetEnd As Int32 = 0 'stores the last row number
Dim conConnection As New OleDb.OleDbConnection
Dim conConnection1 As New OleDb.OleDbConnection
Dim cmdCommand As New OleDb.OleDbCommand
Dim cmdCommand1 As New OleDb.OleDbCommand
Dim dr As OleDb.OleDbDataReader
Dim dr1 As OleDb.OleDbDataReader
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents lblSelectFile As System.Windows.Forms.Label
Friend WithEvents btnOpenDlg As System.Windows.Forms.Button
Friend WithEvents cmbWorksheet As System.Windows.Forms.ComboBox
Friend WithEvents lblWorkSheet As System.Windows.Forms.Label
Friend WithEvents Label2 As System.Windows.Forms.Label
Friend WithEvents dtpUpload As System.Windows.Forms.DateTimePicker
Friend WithEvents btnRead As System.Windows.Forms.Button
Friend WithEvents cmdClose As System.Windows.Forms.Button
Friend WithEvents FlexUpload As AxMSFlexGridLib.AxMSFlexGrid
Friend WithEvents txtFileName As System.Windows.Forms.TextBox
Friend WithEvents btnUpload As System.Windows.Forms.Button
Friend WithEvents btnAbort As System.Windows.Forms.Button
Friend WithEvents OpenFileDialog1 As System.Windows.Forms.OpenFileDialog
Friend WithEvents ProgressBar1 As System.Windows.Forms.ProgressBar
Friend WithEvents lblFlxGrdTitle1 As System.Windows.Forms.Label
Friend WithEvents lblFlxGrdTitle2 As System.Windows.Forms.Label
Friend WithEvents FlxMerchantDeals As AxMSFlexGridLib.AxMSFlexGrid
<system.diagnostics.debuggerstepthrough()> Private Sub InitializeComponent()
Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(frmUpload))
Me.lblSelectFile = New System.Windows.Forms.Label
Me.txtFileName = New System.Windows.Forms.TextBox
Me.btnOpenDlg = New System.Windows.Forms.Button
Me.lblWorkSheet = New System.Windows.Forms.Label
Me.cmbWorksheet = New System.Windows.Forms.ComboBox
Me.Label2 = New System.Windows.Forms.Label
Me.dtpUpload = New System.Windows.Forms.DateTimePicker
Me.btnRead = New System.Windows.Forms.Button
Me.cmdClose = New System.Windows.Forms.Button
Me.FlexUpload = New AxMSFlexGridLib.AxMSFlexGrid
Me.btnUpload = New System.Windows.Forms.Button
Me.btnAbort = New System.Windows.Forms.Button
Me.OpenFileDialog1 = New System.Windows.Forms.OpenFileDialog
Me.ProgressBar1 = New System.Windows.Forms.ProgressBar
Me.FlxMerchantDeals = New AxMSFlexGridLib.AxMSFlexGrid
Me.lblFlxGrdTitle1 = New System.Windows.Forms.Label
Me.lblFlxGrdTitle2 = New System.Windows.Forms.Label
CType(Me.FlexUpload, System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.FlxMerchantDeals, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'lblSelectFile
'
Me.lblSelectFile.AutoSize = True
Me.lblSelectFile.Location = New System.Drawing.Point(23, 18)
Me.lblSelectFile.Name = "lblSelectFile"
Me.lblSelectFile.Size = New System.Drawing.Size(23, 16)
Me.lblSelectFile.TabIndex = 0
Me.lblSelectFile.Text = "File"
'
'txtFileName
'
Me.txtFileName.Location = New System.Drawing.Point(104, 16)
Me.txtFileName.Name = "txtFileName"
Me.txtFileName.Size = New System.Drawing.Size(760, 20)
Me.txtFileName.TabIndex = 1
Me.txtFileName.Text = ""
'
'btnOpenDlg
'
Me.btnOpenDlg.Location = New System.Drawing.Point(872, 14)
Me.btnOpenDlg.Name = "btnOpenDlg"
Me.btnOpenDlg.Size = New System.Drawing.Size(64, 24)
Me.btnOpenDlg.TabIndex = 2
Me.btnOpenDlg.Text = "Browse"
'
'lblWorkSheet
'
Me.lblWorkSheet.AutoSize = True
Me.lblWorkSheet.Location = New System.Drawing.Point(23, 58)
Me.lblWorkSheet.Name = "lblWorkSheet"
Me.lblWorkSheet.Size = New System.Drawing.Size(63, 16)
Me.lblWorkSheet.TabIndex = 3
Me.lblWorkSheet.Text = "Work Sheet"
'
'cmbWorksheet
'
Me.cmbWorksheet.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
Me.cmbWorksheet.Location = New System.Drawing.Point(104, 58)
Me.cmbWorksheet.Name = "cmbWorksheet"
Me.cmbWorksheet.Size = New System.Drawing.Size(192, 21)
Me.cmbWorksheet.TabIndex = 4
'
'Label2
'
Me.Label2.Location = New System.Drawing.Point(23, 100)
Me.Label2.Name = "Label2"
Me.Label2.Size = New System.Drawing.Size(48, 16)
Me.Label2.TabIndex = 7
Me.Label2.Text = "For Date"
'
'dtpUpload
'
Me.dtpUpload.CustomFormat = "dd MMM yyyy"
Me.dtpUpload.Format = System.Windows.Forms.DateTimePickerFormat.Custom
Me.dtpUpload.ImeMode = System.Windows.Forms.ImeMode.Hiragana
Me.dtpUpload.Location = New System.Drawing.Point(104, 98)
Me.dtpUpload.Name = "dtpUpload"
Me.dtpUpload.Size = New System.Drawing.Size(96, 20)
Me.dtpUpload.TabIndex = 8
'
'btnRead
'
Me.btnRead.BackColor = System.Drawing.SystemColors.ActiveBorder
Me.btnRead.Enabled = False
Me.btnRead.Location = New System.Drawing.Point(328, 96)
Me.btnRead.Name = "btnRead"
Me.btnRead.Size = New System.Drawing.Size(72, 24)
Me.btnRead.TabIndex = 10
Me.btnRead.Text = "&Read"
'
'cmdClose
'
Me.cmdClose.BackColor = System.Drawing.SystemColors.ActiveBorder
Me.cmdClose.DialogResult = System.Windows.Forms.DialogResult.Cancel
Me.cmdClose.Location = New System.Drawing.Point(544, 96)
Me.cmdClose.Name = "cmdClose"
Me.cmdClose.Size = New System.Drawing.Size(64, 24)
Me.cmdClose.TabIndex = 12
Me.cmdClose.Text = "&Close"
'
'FlexUpload
'
Me.FlexUpload.Location = New System.Drawing.Point(8, 176)
Me.FlexUpload.Name = "FlexUpload"
Me.FlexUpload.OcxState = CType(resources.GetObject("FlexUpload.OcxState"), System.Windows.Forms.AxHost.State)
Me.FlexUpload.Size = New System.Drawing.Size(984, 192)
Me.FlexUpload.TabIndex = 13
Me.FlexUpload.Visible = False
'
'btnUpload
'
Me.btnUpload.Location = New System.Drawing.Point(400, 96)
Me.btnUpload.Name = "btnUpload"
Me.btnUpload.Size = New System.Drawing.Size(72, 24)
Me.btnUpload.TabIndex = 14
Me.btnUpload.Text = "Upload"
'
'btnAbort
'
Me.btnAbort.Enabled = False
Me.btnAbort.Location = New System.Drawing.Point(472, 96)
Me.btnAbort.Name = "btnAbort"
Me.btnAbort.Size = New System.Drawing.Size(72, 24)
Me.btnAbort.TabIndex = 15
Me.btnAbort.Text = "&Reset"
'
'OpenFileDialog1
'
'
'ProgressBar1
'
Me.ProgressBar1.Location = New System.Drawing.Point(11, 119)
Me.ProgressBar1.Name = "ProgressBar1"
Me.ProgressBar1.Size = New System.Drawing.Size(957, 14)
Me.ProgressBar1.TabIndex = 16
Me.ProgressBar1.Visible = False
'
'FlxMerchantDeals
'
Me.FlxMerchantDeals.Location = New System.Drawing.Point(8, 176)
Me.FlxMerchantDeals.Name = "FlxMerchantDeals"
Me.FlxMerchantDeals.OcxState = CType(resources.GetObject("FlxMerchantDeals.OcxState"), System.Windows.Forms.AxHost.State)
Me.FlxMerchantDeals.Size = New System.Drawing.Size(984, 416)
Me.FlxMerchantDeals.TabIndex = 17
'
'lblFlxGrdTitle1
'
Me.lblFlxGrdTitle1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.lblFlxGrdTitle1.Location = New System.Drawing.Point(8, 152)
Me.lblFlxGrdTitle1.Name = "lblFlxGrdTitle1"
Me.lblFlxGrdTitle1.Size = New System.Drawing.Size(104, 16)
Me.lblFlxGrdTitle1.TabIndex = 18
Me.lblFlxGrdTitle1.Text = "InterBank Deals"
Me.lblFlxGrdTitle1.Visible = False
'
'lblFlxGrdTitle2
'
Me.lblFlxGrdTitle2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.lblFlxGrdTitle2.Location = New System.Drawing.Point(8, 152)
Me.lblFlxGrdTitle2.Name = "lblFlxGrdTitle2"
Me.lblFlxGrdTitle2.Size = New System.Drawing.Size(144, 16)
Me.lblFlxGrdTitle2.TabIndex = 19
Me.lblFlxGrdTitle2.Text = "Merchant Deals (Contract)"
'
'frmUpload
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(992, 589)
Me.Controls.Add(Me.lblFlxGrdTitle2)
Me.Controls.Add(Me.lblFlxGrdTitle1)
Me.Controls.Add(Me.FlxMerchantDeals)
Me.Controls.Add(Me.ProgressBar1)
Me.Controls.Add(Me.btnAbort)
Me.Controls.Add(Me.btnUpload)
Me.Controls.Add(Me.FlexUpload)
Me.Controls.Add(Me.btnRead)
Me.Controls.Add(Me.cmdClose)
Me.Controls.Add(Me.Label2)
Me.Controls.Add(Me.dtpUpload)
Me.Controls.Add(Me.cmbWorksheet)
Me.Controls.Add(Me.lblWorkSheet)
Me.Controls.Add(Me.txtFileName)
Me.Controls.Add(Me.lblSelectFile)
Me.Controls.Add(Me.btnOpenDlg)
Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon)
Me.MaximizeBox = False
Me.MinimizeBox = False
Me.Name = "frmUpload"
Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
Me.Text = "File Upload Utility"
CType(Me.FlexUpload, System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.FlxMerchantDeals, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub frmUpload_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
btnUpload.Enabled = False
dtpUpload.Value = dtServerDate
InitializeFlexGrid()
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), "File Upload Utility", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Sub btnOpenDlg_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenDlg.Click
'''''''''--------------------------Created By Satheesh On 16/11/2006 for improve the Preformace of Sheet fetching activity
Try
txtFileName.Text = ""
OpenFileDialog1.Filter = "Excel File (*.xls)|*.xls"
OpenFileDialog1.ShowDialog()
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), "File Upload Utility", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Sub btnRead_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRead.Click
Try
Me.Cursor = Cursors.WaitCursor
Read()
Me.Cursor = Cursors.Default
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), "File Upload Utility", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
#Region "General Functions"
Private Sub InitializeFlexGrid()
Try
'''Modified By Satheesh.T.S on 21 March 2007 for Implimenting E Treasury Related Changes
'''''''With FlexUpload
''''''' .Cols = 23
''''''' .Rows = 2
''''''' .FixedRows = 1
''''''' .set_TextMatrix(0, 0, "Sr.No")
''''''' .set_TextMatrix(0, 1, "Ext Link Id")
''''''' .set_TextMatrix(0, 2, "Deal No")
''''''' .set_TextMatrix(0, 3, "Remarks")
''''''' .set_TextMatrix(0, 4, "Customer Code")
''''''' .set_TextMatrix(0, 5, "Branch")
''''''' .set_TextMatrix(0, 6, "Customer Name")
''''''' .set_TextMatrix(0, 7, "Posting Date")
''''''' .set_TextMatrix(0, 8, "Deal Date")
''''''' .set_TextMatrix(0, 9, "Buy Currency Code")
''''''' .set_TextMatrix(0, 10, "Buy Amount")
''''''' .set_TextMatrix(0, 11, "Deal Rate")
''''''' .set_TextMatrix(0, 12, "Sell Currency Code")
''''''' .set_TextMatrix(0, 13, "Sell Amount")
''''''' .set_TextMatrix(0, 14, "Funds Delivery Date1")
''''''' .set_TextMatrix(0, 15, "Funds Delivery Date2")
''''''' .set_TextMatrix(0, 16, "Product Code")
''''''' .set_TextMatrix(0, 17, "Narration")
''''''' .set_TextMatrix(0, 18, "Cancellation Rate")
''''''' .set_TextMatrix(0, 19, "Cancellation Date")
''''''' .set_TextMatrix(0, 20, "Cancellation Charges")
''''''' .set_TextMatrix(0, 21, "Activity Date")
'''''''End With
'Modidied By Neelesh
'Date 11 august
With FlexUpload
.Cols = 27
.Rows = 2
.FixedRows = 1
.set_TextMatrix(0, 0, "DEALNO")
.set_TextMatrix(0, 1, "DEALACTIVITY")
.set_TextMatrix(0, 2, "OPERTYP")
.set_TextMatrix(0, 3, "CUSTOMERID")
.set_TextMatrix(0, 4, "CUSTOMER NAME")
.set_TextMatrix(0, 5, "POSTING DATE")
.set_TextMatrix(0, 6, "DEAL DATE")
.set_TextMatrix(0, 7, "CURRENCY BOUGHT")
.set_TextMatrix(0, 8, "AMOUNT BOUGHT")
.set_TextMatrix(0, 9, "RATE")
.set_TextMatrix(0, 10, "CURRENCY SOLD")
.set_TextMatrix(0, 11, "AMOUNT SOLD")
.set_TextMatrix(0, 12, "MATURITY DATE")
.set_TextMatrix(0, 13, "OPTION DATE")
.set_TextMatrix(0, 14, "PRODUCT")
.set_TextMatrix(0, 15, "BROKER")
.set_TextMatrix(0, 16, "BROKERAGE AMOUNT")
.set_TextMatrix(0, 17, "LCY EQUIVALENT")
.set_TextMatrix(0, 18, "TRADER ID")
.set_TextMatrix(0, 19, "CANCELLATION DATE")
.set_TextMatrix(0, 20, "CANCELLATION RATE")
.set_TextMatrix(0, 21, "CANCELLATION CHAREGES")
.set_TextMatrix(0, 22, "UNDERLYING EXPOSURE")
.set_TextMatrix(0, 23, "DEALSTAT")
.set_TextMatrix(0, 24, "DEALLEG NO")
.set_TextMatrix(0, 25, "BO AURTHORISER")
.set_TextMatrix(0, 26, "AURTHORISED DATE")
.set_ColWidth(0, 900)
.set_ColWidth(1, 1000)
.set_ColWidth(2, 1300)
.set_ColWidth(3, 1300)
.set_ColWidth(4, 1500)
.set_ColWidth(5, 2000)
.set_ColWidth(6, 1600)
.set_ColWidth(7, 1600)
.set_ColWidth(8, 1000)
.set_ColWidth(9, 1500)
.set_ColWidth(10, 1500)
.set_ColWidth(11, 1500)
.set_ColWidth(12, 1500)
.set_ColWidth(13, 1500)
.set_ColWidth(14, 1500)
.set_ColWidth(15, 1500)
.set_ColWidth(16, 1500)
.set_ColWidth(17, 1500)
.set_ColWidth(18, 1500)
.set_ColWidth(19, 1500)
.set_ColWidth(20, 1500)
.set_ColWidth(21, 1500)
.set_ColWidth(22, 1500)
.set_ColWidth(23, 1500)
.set_ColWidth(24, 1500)
.set_ColWidth(25, 1500)
.set_ColWidth(26, 1500)
End With
With FlxMerchantDeals
.Cols = 28
.Rows = 2
.FixedRows = 1
.set_TextMatrix(0, 0, "DEALNO")
.set_TextMatrix(0, 1, "DEALACTIVITY")
.set_TextMatrix(0, 2, "OPERTYP")
.set_TextMatrix(0, 3, "CUSTOMERID")
.set_TextMatrix(0, 4, "CUSTOMER NAME")
.set_TextMatrix(0, 5, "POSTING DATE")
.set_TextMatrix(0, 6, "DEAL DATE")
.set_TextMatrix(0, 7, "CURRENCY BOUGHT")
.set_TextMatrix(0, 8, "AMOUNT BOUGHT")
.set_TextMatrix(0, 9, "RATE")
.set_TextMatrix(0, 10, "CURRENCY SOLD")
.set_TextMatrix(0, 11, "AMOUNT SOLD")
.set_TextMatrix(0, 12, "MATURITY DATE")
.set_TextMatrix(0, 13, "OPTION DATE")
.set_TextMatrix(0, 14, "PRODUCT")
.set_TextMatrix(0, 15, "BROKER")
.set_TextMatrix(0, 16, "Margin Rate")
.set_TextMatrix(0, 17, "LCY EQUIVALENT")
.set_TextMatrix(0, 18, "TRADER ID")
.set_TextMatrix(0, 19, "CANCELLATION DATE")
.set_TextMatrix(0, 20, "CANCELLATION RATE")
.set_TextMatrix(0, 21, "CANCELLATION CHAREGES")
.set_TextMatrix(0, 22, "UNDERLYING EXPOSURE")
.set_TextMatrix(0, 23, "DEALSTAT")
.set_TextMatrix(0, 24, "NARRATION")
.set_TextMatrix(0, 25, "BO AURTHORISER")
.set_TextMatrix(0, 26, "AURTHORISED DATE")
.set_TextMatrix(0, 27, "REGION")
.set_ColWidth(0, 900)
.set_ColWidth(1, 1000)
.set_ColWidth(2, 1300)
.set_ColWidth(3, 1300)
.set_ColWidth(4, 1500)
.set_ColWidth(5, 2000)
.set_ColWidth(6, 1600)
.set_ColWidth(7, 1600)
.set_ColWidth(8, 1000)
.set_ColWidth(9, 1500)
.set_ColWidth(10, 1500)
.set_ColWidth(11, 1500)
.set_ColWidth(12, 1500)
.set_ColWidth(13, 1500)
.set_ColWidth(14, 1500)
.set_ColWidth(15, 1500)
.set_ColWidth(16, 1500)
.set_ColWidth(17, 1500)
.set_ColWidth(18, 1500)
.set_ColWidth(19, 1500)
.set_ColWidth(20, 1500)
.set_ColWidth(21, 1500)
.set_ColWidth(22, 1500)
.set_ColWidth(23, 1500)
.set_ColWidth(24, 1500)
.set_ColWidth(25, 1500)
.set_ColWidth(26, 1500)
End With
'End of Modification
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), "File Upload Utility", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Public Function checkFile(ByVal strFileName As String, ByVal worksheet As String) As Boolean
'This function checks if the file is in correct format
'Function by Neelesh
'This function checks the sequence of column headers for the Interbank deals
'Then it stores the row number where merchant deals and merchant contract deals start
'and finally checks that the sequence of column headers for the merchant contract deals deals
'Dim xlsApp As Excel.Application
'Dim xlsWB As Excel.Workbook
'Dim xlsSheet As Excel.Worksheet
'Dim xlsRng As Excel.Range
' Dim xlsDatei As String
Dim count As Int16
Dim strRange As String
checkFile = True
xlsApp = New Excel.Application
xlsApp.Visible = False
xlsWB = xlsApp.Workbooks.Open(strFileName)
xlsSheet = xlsWB.Worksheets(worksheet)
xlsRng = xlsSheet.Range("A1")
If Not (xlsRng.Value = "INTERBANK DEALS:") Then
Return False
End If
xlsRng = xlsSheet.Range("A2")
If Not (xlsRng.Value = "DEALNO") Then
Return False
End If
xlsRng = xlsSheet.Range("B2")
If Not (xlsRng.Value = "DEALACTIVITY") Then
Return False
End If
xlsRng = xlsSheet.Range("C2")
If Not (xlsRng.Value = "OPERTYP") Then
Return False
End If
xlsRng = xlsSheet.Range("D2")
If Not (xlsRng.Value = "CUSTOMERID") Then
Return False
End If
xlsRng = xlsSheet.Range("E2")
If Not (xlsRng.Value = "CUSTOMER NAME") Then
Return False
End If
xlsRng = xlsSheet.Range("F2")
If Not (xlsRng.Value = "POSTING DATE") Then
Return False
End If
xlsRng = xlsSheet.Range("G2")
If Not (xlsRng.Value = "DEAL DATE") Then
Return False
End If
xlsRng = xlsSheet.Range("H2")
If Not (xlsRng.Value = "CURRENCY BOUGHT") Then
Return False
End If
xlsRng = xlsSheet.Range("I2")
If Not (xlsRng.Value = "AMOUNT BOUGHT") Then
Return False
End If
xlsRng = xlsSheet.Range("J2")
If Not (xlsRng.Value = "RATE") Then
Return False
End If
xlsRng = xlsSheet.Range("K2")
If Not (xlsRng.Value = "CURRENCY SOLD") Then
Return False
End If
xlsRng = xlsSheet.Range("L2")
If Not (xlsRng.Value = "AMOUNT SOLD") Then
Return False
End If
xlsRng = xlsSheet.Range("M2")
If Not (xlsRng.Value = "MATURITY DATE") Then
Return False
End If
xlsRng = xlsSheet.Range("N2")
If Not (xlsRng.Value = "OPTION DATE") Then
Return False
End If
xlsRng = xlsSheet.Range("O2")
If Not (xlsRng.Value = "PRODUCT") Then
Return False
End If
xlsRng = xlsSheet.Range("P2")
If Not (xlsRng.Value = "BROKER") Then
Return False
End If
xlsRng = xlsSheet.Range("Q2")
If Not (xlsRng.Value = "BROKERAGE AMOUNT") Then
Return False
End If
xlsRng = xlsSheet.Range("R2")
If Not (xlsRng.Value = "LCY EQUIVALENT") Then
Return False
End If
xlsRng = xlsSheet.Range("S2")
If Not (xlsRng.Value = "TRADER ID") Then
Return False
End If
xlsRng = xlsSheet.Range("T2")
If Not (xlsRng.Value = "CANCELLATION DATE") Then
Return False
End If
xlsRng = xlsSheet.Range("U2")
If Not (xlsRng.Value = "CANCELLATION RATE") Then
Return False
End If
xlsRng = xlsSheet.Range("V2")
If Not (xlsRng.Value = "CANCELLATION CHARGES") Then
Return False
End If
xlsRng = xlsSheet.Range("W2")
If Not (xlsRng.Value = "UNDERLYING EXPOSURE") Then
Return False
End If
xlsRng = xlsSheet.Range("X2")
If Not (xlsRng.Value = "DEALSTAT") Then
Return False
End If
xlsRng = xlsSheet.Range("Y2")
If Not (xlsRng.Value = "DEALLEG NO") Then
Return False
End If
xlsRng = xlsSheet.Range("Z2")
If Not (xlsRng.Value = "BO AURTHORISER") Then
Return False
End If
xlsRng = xlsSheet.Range("AA2")
If Not (xlsRng.Value = "AURTHORISED DATE") Then
Return False
End If
count = 3
xlsRng = xlsSheet.Range("A2")
'Finding the row number where the merchant deals and merchant contract deals starts
While Not xlsRng.Value = "MERCHANT CONTRACT"
If xlsRng.Value = "MERCHANT DEAL:" Then
MerchantDealRowStart = count + 1
End If
count = count + 1
strRange = "A" & count.ToString
xlsRng = xlsSheet.Range(strRange)
End While
MerchantContractStart = count + 1
count = count + 1
'checking for the correct format of merchant deals
xlsRng = xlsSheet.Range("A" & count.ToString)
If Not (xlsRng.Value = "DEALNO") Then
Return False
End If
xlsRng = xlsSheet.Range("B" & count.ToString)
If Not (xlsRng.Value = "DEALACTIVITY") Then
Return False
End If
xlsRng = xlsSheet.Range("C" & count.ToString)
If Not (xlsRng.Value = "OPERTYP") Then
Return False
End If
xlsRng = xlsSheet.Range("D" & count.ToString)
If Not (xlsRng.Value = "CUSTOMERID") Then
Return False
End If
xlsRng = xlsSheet.Range("E" & count.ToString)
If Not (xlsRng.Value = "CUSTOMER NAME") Then
Return False
End If
xlsRng = xlsSheet.Range("F" & count.ToString)
If Not (xlsRng.Value = "POSTING DATE") Then
Return False
End If
xlsRng = xlsSheet.Range("G" & count.ToString)
If Not (xlsRng.Value = "DEAL DATE") Then
Return False
End If
xlsRng = xlsSheet.Range("H" & count.ToString)
If Not (xlsRng.Value = "CURRENCY BOUGHT") Then
Return False
End If
xlsRng = xlsSheet.Range("I" & count.ToString)
If Not (xlsRng.Value = "AMOUNT BOUGHT") Then
Return False
End If
xlsRng = xlsSheet.Range("J" & count.ToString)
If Not (xlsRng.Value = "RATE") Then
Return False
End If
xlsRng = xlsSheet.Range("K" & count.ToString)
If Not (xlsRng.Value = "CURRENCY SOLD") Then
Return False
End If
xlsRng = xlsSheet.Range("L" & count.ToString)
If Not (xlsRng.Value = "AMOUNT SOLD") Then
Return False
End If
xlsRng = xlsSheet.Range("M" & count.ToString)
If Not (xlsRng.Value = "MATURITY DATE") Then
Return False
End If
xlsRng = xlsSheet.Range("N" & count.ToString)
If Not (xlsRng.Value = "OPTION DATE") Then
Return False
End If
xlsRng = xlsSheet.Range("O" & count.ToString)
If Not (xlsRng.Value = "PRODUCT") Then
Return False
End If
xlsRng = xlsSheet.Range("P" & count.ToString)
If Not (xlsRng.Value = "BROKER") Then
Return False
End If
xlsRng = xlsSheet.Range("Q" & count.ToString)
If Not (xlsRng.Value = "Margin Rate") Then
Return False
End If
xlsRng = xlsSheet.Range("R" & count.ToString)
If Not (xlsRng.Value = "LCY EQUIVALENT") Then
Return False
End If
xlsRng = xlsSheet.Range("S" & count.ToString)
If Not (xlsRng.Value = "TRADER ID") Then
Return False
End If
xlsRng = xlsSheet.Range("T" & count.ToString)
If Not (xlsRng.Value = "CANCELLATION DATE") Then
Return False
End If
xlsRng = xlsSheet.Range("U" & count.ToString)
If Not (xlsRng.Value = "CANCELLATION RATE") Then
Return False
End If
xlsRng = xlsSheet.Range("V" & count.ToString)
If Not (xlsRng.Value = "CANCELLATION CHARGES") Then
Return False
End If
xlsRng = xlsSheet.Range("W" & count.ToString)
If Not (xlsRng.Value = "UNDERLYING EXPOSURE") Then
Return False
End If
xlsRng = xlsSheet.Range("X" & count.ToString)
If Not (xlsRng.Value = "DEALSTAT") Then
Return False
End If
xlsRng = xlsSheet.Range("Y" & count.ToString)
If Not (xlsRng.Value = "NARRATION") Then
Return False
End If
xlsRng = xlsSheet.Range("Z" & count.ToString)
If Not (xlsRng.Value = "BO AURTHORISER") Then
Return False
End If
xlsRng = xlsSheet.Range("AA" & count.ToString)
If Not (xlsRng.Value = "AURTHORISED DATE") Then
Return False
End If
xlsRng = xlsSheet.Range("AB" & count.ToString)
If Not (xlsRng.Value = "REGION") Then
Return False
End If
While Not xlsRng.Value = ""
count = count + 1
strRange = "A" & count.ToString
xlsRng = xlsSheet.Range(strRange)
End While
sheetEnd = count - 1
'clean up 2
If Not (xlsSheet Is Nothing) Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet)
xlsSheet = Nothing
End If
If Not (xlsWB Is Nothing) Then
xlsWB.Close(False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWB)
xlsWB = Nothing
End If
If Not (xlsApp Is Nothing) Then
xlsApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp)
xlsApp = Nothing
End If
End Function
Public Sub Read()
If txtFileName.Text = "" Then
MsgBox("Please select File")
btnRead.Enabled = True
btnUpload.Enabled = False
Exit Sub
End If
strFileName = txtFileName.Text
strArr = strFileName.Split("\")
If UBound(strArr) >= 0 Then
arrInt = UBound(strArr)
strFileName = strArr(arrInt)
End If
'If rdDeal.Checked = True Then
' If txtFileName.Text <> "" Then
' If Strings.InStr(txtFileName.Text, "BO", CompareMethod.Text) <> 0 Then
' AxMSFlxGrd.Rows = 1
' AxMSFlxGrd.Cols = 13
' clsGeneral.ImportDataFromText(txtFileName.Text, AxMSFlxGrd)
' btnUpload.Enabled = True
' btnRead.Enabled = False
' Else
' MsgBox("Please select Deal Sheet Text File")
' End If
' End If
'ElseIf rdBankPosition.Checked = True Then
' ImportDataFromTextBanKPos(txtFileName.Text)
' btnUpload.Enabled = True
' btnRead.Enabled = False
If cmbWorksheet.SelectedIndex > -1 Then
'Commented by Neelesh
'FlexUpload.Cols = 22
'FlexUpload.Rows = 1
'End of Comment block
If checkFile(txtFileName.Text, cmbWorksheet.Items(cmbWorksheet.SelectedIndex)) = False Then
MessageBox.Show("File not in acceptable format.")
Exit Sub
End If
'Commented by Neelesh
'The function ClearInputFile has been replaced by checkFile
' If ClearInputFile(txtFileName.Text, cmbWorksheet.Items(cmbWorksheet.SelectedIndex)) = 1 Then
' ImportDataFromExcel(txtFileName.Text, cmbWorksheet.Items(cmbWorksheet.SelectedIndex))
' End If
' ImportDataFromExcel replaced by GetData
' ImportDataFromExcel(txtFileName.Text, cmbWorksheet.Items(cmbWorksheet.SelectedIndex))
'End of Comment block
GetData(txtFileName.Text, cmbWorksheet.Items(cmbWorksheet.SelectedIndex))
End If
Exit Sub
End Sub
'''''''''''---------------Created By Satheesh on 22-Mar-2007 for Filtering E-Treasury file
'this function checks if the columns in the input file are same as that of the format
Private Function ClearInputFile(ByVal PrmPathExcelFile As String, ByVal PrmWorksheet As String) As Integer
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim AdaXL As System.Data.OleDb.OleDbDataAdapter
Dim DstXL As New DataSet
Dim DstXLFilter As New DataSet
Dim DtblXLFilter As New DataTable
Dim cmd As SqlCommand
Dim Rd As SqlDataReader
Dim strProdList As String
Dim strEvntList As String
Dim DrXL As DataRow
Dim DrXLFil As DataRow
Dim intColCnt As Integer
Dim intTotColCnt As Integer
Dim intBlankColCnt As Integer
Dim StrSQLXL As String
'Modified by Neelesh
'10 august
Try
ClearInputFile = 0
With DtblXLFilter.Columns
.Add("DEALNO")
.Add("DEALACTIVITY")
.Add("OPERTYP")
.Add("CUSTOMERID")
.Add("CUSTOMER NAME")
.Add("POSTING DATE")
.Add("DEAL DATE")
.Add("CURRENCY BOUGHT")
.Add("AMOUNT BOUGHT")
.Add("RATE")
.Add("CURRENCY SOLD")
.Add("AMOUNT SOLD")
.Add("MATURITY DATE")
.Add("OPTION DATE")
.Add("PRODUCT")
.Add("DEAL TYPE")
.Add("BROKERAGE")
.Add("LCY EQUIVALENT")
.Add("TRADER ID")
.Add("CANCELLATION DATE")
.Add("CANCELLATION RATE")
.Add("CANCELLATION CHARGES")
.Add("UNDERLYING EXPOSURE")
.Add("DEALSTAT")
.Add("NARRATION")
.Add("BO AURTHORISER")
.Add("AURTHORISED DATE")
.Add("CONTRACTID")
'.Add("ACTIVITYDATE")
'end modification
End With
DstXLFilter.Tables.Add(DtblXLFilter)
cmd = New SqlCommand("select prdct from exmproductmas", conn)
Rd = cmd.ExecuteReader()
While Rd.Read
strProdList = strProdList + "'" + Rd.Item(0) + "',"
End While
If Len(strProdList) > 0 Then
strProdList = Mid(Trim(strProdList), 1, Len(Trim(strProdList)) - 1)
End If
cmd.Dispose()
Rd.Close()
cmd = New SqlCommand("select event from exmeventmas", conn)
Rd = cmd.ExecuteReader()
While Rd.Read
strEvntList = strEvntList + "'" + Rd.Item(0) + "',"
End While
If Len(strEvntList) > 0 Then
strEvntList = Mid(Trim(strEvntList), 1, Len(Trim(strEvntList)) - 1)
End If
cmd.Dispose()
Rd.Close()
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source='" & PrmPathExcelFile & " '; " & "Extended Properties=""Excel 8.0;HDR=YES;FMT=Delimited;IMEX=1""")
''''StrSQLXL = "select * from [" & PrmWorksheet & "$] where DEALNO not like '%DEALNO%' and PRODUCT in (" + strProdList + ") and DEALACTIVITY not in (" + strEvntList + ")"
StrSQLXL = "select * from [" & PrmWorksheet & "$]"
If Len(strProdList) > 0 Then
StrSQLXL = StrSQLXL + " and PRODUCT in (" + strProdList + ")"
End If
If Len(strEvntList) > 0 Then
StrSQLXL = StrSQLXL + " and DEALACTIVITY not in (" + strEvntList + ")"
StrSQLXL = StrSQLXL + " and OPERTYP not in (" + strEvntList + ")"
End If
AdaXL = New System.Data.OleDb.OleDbDataAdapter(StrSQLXL, MyConnection)
AdaXL.Fill(DstXL)
intTotColCnt = DstXL.Tables(0).Columns.Count
For Each DrXL In DstXL.Tables(0).Rows
intBlankColCnt = 0
For intColCnt = 0 To intTotColCnt - 2
If IsDBNull(DrXL.Item(intColCnt)) Then
intBlankColCnt = intBlankColCnt + 1
End If
Next
If intBlankColCnt < intTotColCnt - 2 Then
DrXLFil = DstXLFilter.Tables(0).NewRow()
With DrXLFil
'Commented by Neelesh
'Date 11 august
'.Item(0) = DrXL.Item(0)
'.Item(1) = DrXL.Item(1)
'.Item(2) = DrXL.Item(3)
'.Item(3) = DrXL.Item(4)
'.Item(4) = DrXL.Item(6)
'.Item(5) = DrXL.Item(7)
'.Item(6) = DrXL.Item(8)
'.Item(7) = DrXL.Item(9)
'.Item(8) = DrXL.Item(10)
'.Item(9) = DrXL.Item(11)
'.Item(10) = DrXL.Item(12)
'.Item(11) = DrXL.Item(14)
'.Item(12) = DrXL.Item(20)
'.Item(13) = DrXL.Item(19)
'.Item(14) = DrXL.Item(21)
'.Item(15) = DrXL.Item(5)
'End Comment
.Item(0) = DrXL.Item(0)
.Item(1) = DrXL.Item(1)
.Item(2) = DrXL.Item(2)
.Item(3) = DrXL.Item(3)
.Item(4) = DrXL.Item(4)
.Item(5) = DrXL.Item(5)
.Item(6) = DrXL.Item(6)
.Item(7) = DrXL.Item(7)
.Item(8) = DrXL.Item(8)
.Item(9) = DrXL.Item(9)
.Item(10) = DrXL.Item(10)
.Item(11) = DrXL.Item(11)
.Item(12) = DrXL.Item(12)
.Item(13) = DrXL.Item(13)
.Item(14) = DrXL.Item(14)
.Item(15) = DrXL.Item(15)
.Item(16) = DrXL.Item(16)
.Item(17) = DrXL.Item(17)
.Item(18) = DrXL.Item(18)
.Item(19) = DrXL.Item(19)
.Item(20) = DrXL.Item(20)
.Item(21) = DrXL.Item(21)
.Item(22) = DrXL.Item(22)
.Item(23) = DrXL.Item(23)
.Item(24) = DrXL.Item(24)
.Item(25) = DrXL.Item(25)
.Item(26) = DrXL.Item(26)
End With
DstXLFilter.Tables(0).Rows.Add(DrXLFil)
End If
Next
'''---Serializing The Dataset for further use
File.Delete(Application.StartupPath & "\" & Mid(strFileName, 1, Len(strFileName) - 4) & ".bin")
Dim Stre As Stream = File.Open(Application.StartupPath & "\" & Mid(strFileName, 1, Len(strFileName) - 4) & ".bin", FileMode.Create, FileAccess.ReadWrite)
Dim Bf As New BinaryFormatter
Bf.Serialize(Stre, DstXLFilter)
Stre.Close()
ClearInputFile = 1
Catch ex As Exception
If ex.Message = "No value given for one or more required parameters." Then
MessageBox.Show("Invalid Column Header,Modify the File and try to Upload", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
Finally
MyConnection.Close()
End Try
End Function
Private Sub FillGrid(ByVal strGrid As String, ByRef drDataReader As OleDb.OleDbDataReader)
Dim rowCount As Int32 = 1
Dim colCount As Int32 = 0
Dim dtrwDataRow As DataRow
'Fill the interbank dealsGrid
Dim intR As Integer = 0
ProgressBar1.Visible = True
ProgressBar1.Value = 0
If strGrid = "InterBank" Then
With FlexUpload
While drDataReader.Read = True
.Rows = .Rows + 1
colCount = 0
While colCount < drDataReader.FieldCount
If Not IsDBNull(drDataReader.Item(colCount)) Then
.set_TextMatrix(rowCount, colCount, drDataReader.Item(colCount))
Else
.set_TextMatrix(rowCount, colCount, "")
End If
colCount = colCount + 1
End While
rowCount = rowCount + 1
'ProgressBar1.Value = ProgressBar1.Value + 1
End While
End With
'Fill the Merchant Contract deals Grid
ElseIf strGrid = "Merchant" Then
With FlxMerchantDeals
While drDataReader.Read = True
.Rows = .Rows + 1
colCount = 0
While colCount < drDataReader.FieldCount
If Not IsDBNull(drDataReader.Item(colCount)) Then
.set_TextMatrix(rowCount, colCount, drDataReader.Item(colCount))
Else
.set_TextMatrix(rowCount, colCount, "")
End If
colCount = colCount + 1
End While
rowCount = rowCount + 1
' ProgressBar1.Value = ProgressBar1.Value + 1
End While
End With
End If
End Sub
Private Function GetData(ByVal PrmPathExcelFile As String, ByVal PrmWorksheet As String)
'function by Neelesh
'This function reads the data from the excel file.
'Dim conConnection As New OleDb.OleDbConnection
'Dim cmdCommand As New OleDb.OleDbCommand
'Dim dr As OleDb.OleDbDataReader
conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PrmPathExcelFile & _
";Extended Properties=""Excel 8.0;HDR=NO"""
cmdCommand.Connection = conConnection
cmdCommand.CommandText = "Select * from [" & PrmWorksheet & "$A3:AA" & (MerchantDealRowStart - 2).ToString & _
"]" '& "Select * from [" & PrmWorksheet & "$A" & &":AA" & (MerchantDealRowStart - 2).ToString & _
'"]"
conConnection.Open()
' Where" & PrmWorksheet & "$A3:A" & MerchantDealRowStart.ToString & " LIKE '3-%'"
dr = cmdCommand.ExecuteReader()
' FillGrid("InterBank", dr)
' get the merchant deals
conConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PrmPathExcelFile & _
";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
cmdCommand1.Connection = conConnection1
cmdCommand1.CommandText = "Select * from [" & PrmWorksheet & "$A" & MerchantContractStart.ToString & _
":AB" & sheetEnd.ToString & "] Where F1 LIKE '20-%' OR F1 LIKE '21-%'"
conConnection1.Open()
dr1 = cmdCommand1.ExecuteReader()
'Dim date1 As String = CStr(dr1("F1"))
FillGrid("Merchant", dr1)
btnUpload.Enabled = True
'Clean up
If Not (xlsSheet Is Nothing) Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet)
xlsSheet = Nothing
End If
If Not (xlsWB Is Nothing) Then
xlsWB.Close(False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWB)
xlsWB = Nothing
End If
If Not (xlsApp Is Nothing) Then
xlsApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp)
xlsApp = Nothing
End If
conConnection.Close()
conConnection1.Close()
End Function
'''''''''''---------------Created By Satheesh on 16-Nov-2006 for increasing the Performance of Excel File Read Process
Private Function ImportDataFromExcel(ByVal PrmPathExcelFile As String, ByVal PrmWorksheet As String, Optional ByVal whereClause As String = "") As Integer
'Commented by Neelesh
'Date 18 August
'Dim DtSet As System.Data.DataSet
'Dim myRow As DataRow
'Dim I, J As Integer
'Dim Rd As SqlDataReader
'Dim cmd As SqlCommand
'Dim strProdList As String
'Dim strEvntList As String
'Dim strSQLXL As String
'Dim strLst As String
'Dim strWrongData As String
'Dim dtblWrongDeal As New DataTable
'Dim dr As DataRow
' ImportDataFromExcel = -1
' If File.Exists(Application.StartupPath & "\" & Mid(strFileName, 1, Len(strFileName) - 4) & ".bin") = False Then
' Exit Function
' End If
' cmd = New SqlCommand("Select count(1) from fctGlobal where FileName ='" + Trim(Mid(PrmPathExcelFile, PrmPathExcelFile.LastIndexOf("\") + 2) + "'"), conn)
' If Convert.ToInt32(cmd.ExecuteScalar()) <> 0 Then
' MessageBox.Show("The Selected File is already Uploaded", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning)
' Exit Function
' End If
' dtblWrongDeal.Columns.Add("Id").AutoIncrement = True
' dtblWrongDeal.Columns(0).AutoIncrementSeed = 1
' dtblWrongDeal.Columns.Add("Log")
' ''''Deserialize the Serialized Dataset
' Dim Bf As New BinaryFormatter
' Dim Ds As New DataSet
' Dim Sr As New StreamReader(Application.StartupPath & "\" & Mid(strFileName, 1, Len(strFileName) - 4) & ".bin")
' DtSet = CType(Bf.Deserialize(Sr.BaseStream), DataSet)
' Sr.Close()
' If DtSet.Tables(0).Columns.Count < 16 Then
' MessageBox.Show("Selected File is not having expected number of columns.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
' Exit Function
' End If
' If DtSet.Tables(0).Rows.Count > 0 Then
' btnUpload.Enabled = True
' btnAbort.Enabled = True
' btnRead.Enabled = False
' Else
' btnUpload.Enabled = False
' btnAbort.Enabled = False
' btnRead.Enabled = True
' End If
' I = 1
' With FlexUpload
' Dim Dealno As String
' Dim ExtLinkId As String
' Dim CustCd As String
' .Clear()
'InitializeFlexGrid()
' .Rows = 2
' ProgressBar1.Visible = True
' ProgressBar1.Value = 0
' ProgressBar1.Minimum = 0
' ProgressBar1.Maximum = DtSet.Tables(0).Rows.Count
' For Each myRow In DtSet.Tables(0).Rows
' ProgressBar1.Value = ProgressBar1.Value + 1
' .set_TextMatrix(I, 0, I)
' If IsDBNull(myRow.Item("DEALNO")) Then
' Dealno = ""
' Else
' Dealno = myRow.Item("DEALNO")
' End If
' If IsDBNull(myRow.Item("CUSTOMERID")) Then
' CustCd = " "
' Else
' CustCd = myRow.Item("CUSTOMERID")
' End If
' '"" or value
' If IIf(IsDBNull(myRow.Item("OPERTYP")), "", myRow.Item("OPERTYP")) = "" Then
' strWrongData = "DEAL ACTIVITY column is blank for deal no: " + Dealno
' dr = dtblWrongDeal.NewRow()
' dr(1) = strWrongData
' dtblWrongDeal.Rows.Add(dr)
' End If
' If IIf(IsDBNull(myRow.Item("CUSTOMERID")), "", myRow.Item("CUSTOMERID")) = "" Then
' strWrongData = "CUSTOMER ID column is blank for deal no " + Dealno
' dr = dtblWrongDeal.NewRow()
' dr(1) = strWrongData
' dtblWrongDeal.Rows.Add(dr)
' End If
' If IIf(IsDBNull(myRow.Item("CUSTOMER NAME")), "", myRow.Item("CUSTOMER NAME")) = "" Then
' strWrongData = "CUSTOMER NAME column is blank for deal no " + Dealno
' dr = dtblWrongDeal.NewRow()
' dr(1) = strWrongData
' dtblWrongDeal.Rows.Add(dr)
' End If
' If IIf(IsDBNull(myRow.Item("CURRENCY BOUGHT")), "", myRow.Item("CURRENCY BOUGHT")) = "" Then
' strWrongData = "CURRENCYBOUGHT column is blank for deal no " + Dealno + " and Customer Id:" + CustCd
' dr = dtblWrongDeal.NewRow()
' dr(1) = strWrongData
' dtblWrongDeal.Rows.Add(dr)
' End If
' If CType(IIf(IsDBNull(myRow.Item("AMOUNT BOUGHT")), "", myRow.Item("AMOUNT BOUGHT")), String) = "" Then
' strWrongData = "AMOUNT BOUGHT column is blank for deal no " + Dealno + " and Customer Id:" + CustCd
' dr = dtblWrongDeal.NewRow()
' dr(1) = strWrongData
' dtblWrongDeal.Rows.Add(dr)
' End If
' If CType(IIf(IsDBNull(myRow.Item("RATE")), 0, myRow.Item("RATE")), String) = "" Then
' strWrongData = "RATE column is blank for deal no " + Dealno + ", and Customer Id:" + CustCd
' dr = dtblWrongDeal.NewRow()
' dr(1) = strWrongData
' dtblWrongDeal.Rows.Add(dr)
' End If
' If IIf(IsDBNull(myRow.Item("CURRENCY SOLD")), "", myRow.Item("CURRENCY SOLD")) = "" Then
' strWrongData = "CURRENCY SOLD column is blank for deal no " + Dealno + " and Customer Id:" + CustCd
' dr = dtblWrongDeal.NewRow()
' dr(1) = strWrongData
' dtblWrongDeal.Rows.Add(dr)
' End If
' If CType(IIf(IsDBNull(myRow.Item("AMOUNT SOLD")), "", myRow.Item("AMOUNT SOLD")), String) = "" Then
' strWrongData = "AMOUNT SOLD column is blank for deal no " + Dealno + ", and Customer Code:" + CustCd
' dr = dtblWrongDeal.NewRow()
' dr(1) = strWrongData
' dtblWrongDeal.Rows.Add(dr)
' End If
' 'Error Here
' If CType(IIf(IsDBNull(myRow.Item("MATURITY DATE")), "01/01/1900", myRow.Item("MATURITY DATE")), Date) = "01/01/1900" Then
' strWrongData = "METURITY DATE column is blank for deal no " + Dealno + ", and Customer Id:" + CustCd
' dr = dtblWrongDeal.NewRow()
' dr(1) = strWrongData
' dtblWrongDeal.Rows.Add(dr)
' End If
' If IIf(IsDBNull(myRow.Item("PRODUCT")), "", myRow.Item("PRODUCT")) = "" Then
' strWrongData = "PRODUCT column is blank for deal no " + Dealno + ", and Customer Id:" + CustCd
' dr = dtblWrongDeal.NewRow()
' dr(1) = strWrongData
' dtblWrongDeal.Rows.Add(dr)
' End If
' If CType(IIf(IsDBNull(myRow.Item("OPTION DATE")), "01/01/1900", myRow.Item("OPTION DATE")), Date) = "01/01/1900" Then
' strWrongData = "ACTIVITY DATE column is blank for deal no " + Dealno + ", and Customer Code:" + CustCd
' dr = dtblWrongDeal.NewRow()
' dr(1) = strWrongData
' dtblWrongDeal.Rows.Add(dr)
' End If
' For J = 0 To 15
' .set_TextMatrix(I, J + 1, IIf(IsDBNull(myRow.Item(J)), "", myRow.Item(J)))
' Next
' .Rows = .Rows + 1
' I = I + 1
' Next
' .Rows = .Rows - 1
' End With
' ProgressBar1.Visible = False
' IntLog = 1
' If dtblWrongDeal.Rows.Count > 0 Then
' MessageBox.Show("Upload not possible , Blank data in mandatory columns !!!", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Stop)
' Dim dst As New DataSet
' Dim FSI As New System.IO.FileInfo(Application.StartupPath + "\WrongDealInfo.xml")
' If FSI.Exists = True Then
' FSI.Delete()
' End If
' dst.Tables.Add(dtblWrongDeal)
' dst.WriteXml(Application.StartupPath + "\WrongDealInfo.xml")
' Dim frm As New frmLOG
' frm.Show()
' 'btnUpload.Enabled = False
' End If
' ImportDataFromExcel = 1
'End of comment Block
Try
Catch ex As Exception
If ex.Message = "Not a legal OleAut date." Then
MessageBox.Show("Invalid value in Date/Numeric Column(s).", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
ProgressBar1.Value = 0
ProgressBar1.Visible = False
Finally
End Try
End Function
'''''''''''---------------Created By Satheesh on 16-Nov-2006 for Fetching Sheets from GIven Excel File
Private Sub FetchSheetDetails(ByVal strpath As String)
Dim xlsApp As Excel.Application
Dim xlsWB As Excel.Workbook
Dim xlsSheet As Excel.Worksheet
Try
xlsApp = New Excel.Application
xlsWB = xlsApp.Workbooks.Open(strpath, 0)
With cmbWorksheet
.Items.Clear()
For Each xlsSheet In xlsWB.Worksheets
.Items.Add(xlsSheet.Name.ToString())
Next
If .Items.Count > 0 Then
.SelectedIndex = 0
End If
End With
If xlsWB.Worksheets.Count > 0 Then
btnRead.Enabled = True
Else
btnRead.Enabled = False
End If
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), "File Upload Utility", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
If Not (xlsSheet Is Nothing) Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet)
xlsSheet = Nothing
End If
If Not (xlsWB Is Nothing) Then
xlsWB.Close(False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWB)
xlsWB = Nothing
End If
If Not (xlsApp Is Nothing) Then
xlsApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp)
xlsApp = Nothing
End If
End Try
End Sub
#End Region
Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click
Me.Close()
End Sub
Private Sub btnUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpload.Click
Me.Cursor = Cursors.WaitCursor
Dim Sa As Integer
Dim I, intTemp, intPos As Integer
Dim strErr As String
Dim strSql As String
Dim intCount As Integer
Dim blnIsNewDeal As Boolean
Dim strErrInfo As String
Dim blnIsCustExist As Boolean
Dim blnBlnkDealNo As Boolean
Dim Com As New SqlClient.SqlCommand
Dim ComDel As New SqlClient.SqlCommand
Dim dr As DataRow
Dim dtblWrongDeal As New DataTable
Dim dtblDuplicate As New DataTable
Dim Trn As SqlTransaction
Dim ParDelDate As SqlClient.SqlParameter
Dim ParDelFileName As SqlClient.SqlParameter
Dim Param1 As SqlClient.SqlParameter
Dim Param2 As SqlClient.SqlParameter
Dim Param3 As SqlClient.SqlParameter
Dim Param4 As SqlClient.SqlParameter
Dim Param5 As SqlClient.SqlParameter
Dim Param6 As SqlClient.SqlParameter
Dim Param7 As SqlClient.SqlParameter
Dim Param8 As SqlClient.SqlParameter
Dim Param9 As SqlClient.SqlParameter
Dim Param10 As SqlClient.SqlParameter
Dim Param11 As SqlClient.SqlParameter
Dim Param12 As SqlClient.SqlParameter
Dim Param13 As SqlClient.SqlParameter
Dim Param14 As SqlClient.SqlParameter
Dim Param15 As SqlClient.SqlParameter
Dim Param16 As SqlClient.SqlParameter
Dim Param17 As SqlClient.SqlParameter
Dim Param18 As SqlClient.SqlParameter
Dim Param19 As SqlClient.SqlParameter
Dim Param20 As SqlClient.SqlParameter
Dim Param21 As SqlClient.SqlParameter
Dim Param22 As SqlClient.SqlParameter
Dim Param23 As SqlClient.SqlParameter
Dim Param24 As SqlClient.SqlParameter
Dim Param25 As SqlClient.SqlParameter
Dim Param26 As SqlClient.SqlParameter
Dim Param27 As SqlClient.SqlParameter
Dim Param28 As SqlClient.SqlParameter
Dim Param29 As SqlClient.SqlParameter
Dim Param30 As SqlClient.SqlParameter
Try
intPos = -1
strSql = "Select Count(*) from TestTable Where ForDate = '" & dtpUpload.Text & "'"
' Where FileName = '" & strFileName & "' and ForDate = '" & dtpUpload.Text & "'"
ComDel.CommandText = strSql
ComDel.Connection = conn
intCount = ComDel.ExecuteScalar()
If intCount > 0 Then
If MsgBox("Data exists for the same Date, You want to upload again", MsgBoxStyle.YesNo + MsgBoxStyle.Information) = MsgBoxResult.Yes Then
Me.Cursor = Cursors.WaitCursor
With ComDel
.Connection = conn
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_fwcDeletefctGlobal"
End With
ParDelDate = New SqlClient.SqlParameter
With ParDelDate
.ParameterName = "@UploadDate"
.SqlDbType = SqlDbType.DateTime
.Value = Convert.ToDateTime(dtpUpload.Text)
End With
ComDel.Parameters.Add(ParDelDate)
ParDelFileName = New SqlClient.SqlParameter
With ParDelFileName
.ParameterName = "@FileName"
.SqlDbType = SqlDbType.VarChar
.Value = strFileName
End With
ComDel.Parameters.Add(ParDelFileName)
ComDel.ExecuteNonQuery()
End If
End If
''--------------For Checking the Existence of customer for Uploaded Deal
'Dim CommCust As New DataSet
'Dim AdaCust As New SqlDataAdapter("select distinct CustomerCode CustomerCode from ExmCustomerMas", conn)
'AdaCust.Fill(CommCust)
Trn = conn.BeginTransaction
Com = conn.CreateCommand()
With Com
.Transaction = Trn
.CommandType = CommandType.StoredProcedure
' .CommandText = "sp_fwcInsertfctGlobal"
.CommandText = "sp_fwcInsertfctGlobal1"
End With
dtblWrongDeal.Columns.Add("Id").AutoIncrement = True
dtblWrongDeal.Columns(0).AutoIncrementSeed = 1
dtblWrongDeal.Columns.Add("Log")
dtblDuplicate.Columns.Add("DealNo")
dtblDuplicate.Columns.Add("Evenet")
dtblDuplicate.Columns.Add("ExternalLinkId")
With ProgressBar1
.Visible = True
.Value = 0
.Minimum = 0
.Maximum = FlxMerchantDeals.Rows
End With
For I = 1 To FlxMerchantDeals.Rows - 2
ProgressBar1.Value = ProgressBar1.Value + 1
'Dim dtv As New DataView
'blnIsNewDeal = True
'blnIsCustExist = True
''-----------Checking the deal with No Deal Number
'commented on 27 august
'If IsDBNull(FlexUpload.get_TextMatrix(I, 1)) Or FlexUpload.get_TextMatrix(I, 1) = "" Then
' ' strErrInfo = "External Link Id " + FlexUpload.get_TextMatrix(I, 1) + " not having Deal Number."
' strErrInfo = "Customer Id " + FlexUpload.get_TextMatrix(I, 3) + " and DEAL ACTIVITY " + FlexUpload.get_TextMatrix(I, 2) + " not having Deal Number."
' dr = dtblWrongDeal.NewRow()
' dr(1) = strErrInfo
' dtblWrongDeal.Rows.Add(dr)
'ElseIf dtblDuplicate.Rows.Count > 0 Then
' ''----------Checking Duplicate Deal
' dtv = dtblDuplicate.DefaultView
' dtv.RowFilter = "DealNo='" + FlexUpload.get_TextMatrix(I, 1) + "' and Evenet='" + FlexUpload.get_TextMatrix(I, 2) + "'"
' If dtv.Count > 0 Then
' strErrInfo = "Cannot Upload the The deal no " + FlexUpload.get_TextMatrix(I, 1) + " and DEAL ACTIVITY '" + FlexUpload.get_TextMatrix(I, 2) + "' Becouse The Deal is already Uploaded into Database."
' dr = dtblWrongDeal.NewRow()
' dr(1) = strErrInfo
' dtblWrongDeal.Rows.Add(dr)
' blnIsNewDeal = False
' End If
'End If
'If blnIsNewDeal = True Then
' ''------------Checking the Customer entry in the Customer master
' dtv = CommCust.Tables(0).DefaultView
' dtv.RowFilter = "CustomerCode='" + Trim(FlexUpload.get_TextMatrix(I, 3)) + "'"
' If dtv.Count = 0 Then
' strErrInfo = "Deal No " + FlexUpload.get_TextMatrix(I, 1) + " Uploaded Succesfully,Add Customer with Cusomer ID '" + Trim(FlexUpload.get_TextMatrix(I, 3)) + "' in Customer Master for Include the Deal in Exception Report."
' dr = dtblWrongDeal.NewRow()
' dr(1) = strErrInfo
' dtblWrongDeal.Rows.Add(dr)
' ''blnIsCustExist = False
' End If
' Sa = Sa + 1
intPos = 0
'Commented By Neelesh
'Param1 = New SqlClient.SqlParameter
'With Param1
' .ParameterName = "@strext_link_id"
' .SqlDbType = SqlDbType.VarChar
' .Direction = ParameterDirection.Input
' .Value = ""
'End With
'Com.Parameters.Add(Param1)
'intPos = intPos + 1
'Param2 = New SqlClient.SqlParameter
'With Param2
' .ParameterName = "@Deal_no"
' .SqlDbType = SqlDbType.VarChar
' .Direction = ParameterDirection.Input
' .Size = 50
' If IsDBNull(FlexUpload.get_TextMatrix(I, 1)) = False Then
' .Value = FlexUpload.get_TextMatrix(I, 1)
' Else
' .Value = ""
' End If
'End With
'Com.Parameters.Add(Param2)
'intPos = intPos + 1
'Param3 = New SqlClient.SqlParameter
'With Param3
' .ParameterName = "@Deal_Type"
' .SqlDbType = SqlDbType.VarChar
' .Direction = ParameterDirection.Input
' If IsDBNull(FlexUpload.get_TextMatrix(I, 2)) = False Then
' .Value = FlexUpload.get_TextMatrix(I, 2)
' Else
' .Value = ""
' End If
'End With
'Com.Parameters.Add(Param3)
'intPos = intPos + 1
'Param4 = New SqlClient.SqlParameter
'With Param4
' .ParameterName = "@Cust_Code"
' .SqlDbType = SqlDbType.VarChar
' .Direction = ParameterDirection.Input
' If IsDBNull(FlexUpload.get_TextMatrix(I, 3)) = False Then
' .Value = FlexUpload.get_TextMatrix(I, 3)
' Else
' .Value = ""
' End If
'End With
'Com.Parameters.Add(Param4)
'intPos = intPos + 1
'Param5 = New SqlClient.SqlParameter
'With Param5
' .ParameterName = "@Branch"
' .SqlDbType = SqlDbType.VarChar
' .Direction = ParameterDirection.Input
' .Value = ""
'End With
'Com.Parameters.Add(Param5)
'intPos = intPos + 1
'Param6 = New SqlClient.SqlParameter
'With Param6
' .ParameterName = "@CustomerName"
' .SqlDbType = SqlDbType.VarChar
' .Direction = ParameterDirection.Input
' If IsDBNull(FlexUpload.get_TextMatrix(I, 4)) = False Then
' .Value = FlexUpload.get_TextMatrix(I, 4)
' Else
' .Value = ""
' End If
'End With
'Com.Parameters.Add(Param6)
'intPos = intPos + 1
'Param7 = New SqlClient.SqlParameter
'With Param7
' .ParameterName = "@PostingDate"
' .SqlDbType = SqlDbType.DateTime
' .Direction = ParameterDirection.Input
' .Value = Convert.DBNull
'End With
'Com.Parameters.Add(Param7)
'intPos = intPos + 1
'Param8 = New SqlClient.SqlParameter
'With Param8
' .ParameterName = "@DealDate"
' .SqlDbType = SqlDbType.DateTime
' .Direction = ParameterDirection.Input
' If FlexUpload.get_TextMatrix(I, 5) <> "" Or IsDate(FlexUpload.get_TextMatrix(I, 5)) = True Then
' .Value = Convert.ToDateTime(FlexUpload.get_TextMatrix(I, 5))
' Else
' .Value = Convert.DBNull
' End If
'End With
'Com.Parameters.Add(Param8)
'intPos = intPos + 1
'Param9 = New SqlClient.SqlParameter
'With Param9
' .ParameterName = "@BuyCurrCode"
' .SqlDbType = SqlDbType.VarChar
' .Direction = ParameterDirection.Input
' If IsDBNull(FlexUpload.get_TextMatrix(I, 6)) = False Then
' .Value = FlexUpload.get_TextMatrix(I, 6)
' Else
' .Value = ""
' End If
'End With
'Com.Parameters.Add(Param9)
'intPos = intPos + 1
'Param10 = New SqlClient.SqlParameter
'With Param10
' .ParameterName = "@BuyAmt"
' .SqlDbType = SqlDbType.Decimal
' .Direction = ParameterDirection.Input
' If FlexUpload.get_TextMatrix(I, 7) <> "" Or IsNumeric(FlexUpload.get_TextMatrix(I, 7)) = True Then
' .Value = FlexUpload.get_TextMatrix(I, 7)
' Else
' .Value = 0
' End If
'End With
'Com.Parameters.Add(Param10)
'intPos = intPos + 1
'Param11 = New SqlClient.SqlParameter
'With Param11
' .ParameterName = "@DealRate"
' .SqlDbType = SqlDbType.Decimal
' .Direction = ParameterDirection.Input
' If FlexUpload.get_TextMatrix(I, 8) <> "" Or IsNumeric(FlexUpload.get_TextMatrix(I, 8)) = True Then
' .Value = FlexUpload.get_TextMatrix(I, 8)
' Else
' .Value = 0
' End If
'End With
'Com.Parameters.Add(Param11)
'intPos = intPos + 1
'Param12 = New SqlClient.SqlParameter
'With Param12
' .ParameterName = "@SellCurrCode"
' .SqlDbType = SqlDbType.VarChar
' .Direction = ParameterDirection.Input
' If IsDBNull(FlexUpload.get_TextMatrix(I, 9)) = False Then
' .Value = FlexUpload.get_TextMatrix(I, 9)
' Else
' .Value = ""
' End If
'End With
'Com.Parameters.Add(Param12)
'intPos = intPos + 1
'Param13 = New SqlClient.SqlParameter
'With Param13
' .ParameterName = "@SellAmt"
' .SqlDbType = SqlDbType.Decimal
' .Direction = ParameterDirection.Input
' If IsDBNull(FlexUpload.get_TextMatrix(I, 10)) = False Or IsNumeric(FlexUpload.get_TextMatrix(I, 10)) = True Then
' .Value = FlexUpload.get_TextMatrix(I, 10)
' Else
' .Value = 0
' End If
'End With
'Com.Parameters.Add(Param13)
'intPos = intPos + 1
'Param14 = New SqlClient.SqlParameter
'With Param14
' .ParameterName = "@FndDlvryDate"
' .SqlDbType = SqlDbType.DateTime
' .Direction = ParameterDirection.Input
' .Value = Convert.DBNull
'End With
'Com.Parameters.Add(Param14)
'intPos = intPos + 1
'Param15 = New SqlClient.SqlParameter
'With Param15
' .ParameterName = "@FndDlvryDt"
' .SqlDbType = SqlDbType.DateTime
' .Direction = ParameterDirection.Input
' If FlexUpload.get_TextMatrix(I, 11) <> "" Or IsDate(FlexUpload.get_TextMatrix(I, 11)) Then
' .Value = Convert.ToDateTime(FlexUpload.get_TextMatrix(I, 11))
' Else
' .Value = Convert.DBNull
' End If
'End With
'Com.Parameters.Add(Param15)
'intPos = intPos + 1
'Param16 = New SqlClient.SqlParameter
'With Param16
' .ParameterName = "@ProdCode"
' .SqlDbType = SqlDbType.VarChar
' .Direction = ParameterDirection.Input
' If IsDBNull(FlexUpload.get_TextMatrix(I, 12)) = False Then
' .Value = FlexUpload.get_TextMatrix(I, 12)
' Else
' .Value = ""
' End If
'End With
'Com.Parameters.Add(Param16)
'intPos = intPos + 1
'Param17 = New SqlClient.SqlParameter
'With Param17
' .ParameterName = "@Narration"
' .SqlDbType = SqlDbType.VarChar
' .Direction = ParameterDirection.Input
' .Value = ""
'End With
'Com.Parameters.Add(Param17)
'intPos = intPos + 1
'Param18 = New SqlClient.SqlParameter
'With Param18
' .ParameterName = "@CancRate"
' .SqlDbType = SqlDbType.Decimal
' .Direction = ParameterDirection.Input
' If IsDBNull(FlexUpload.get_TextMatrix(I, 13)) = False Or IsNumeric(FlexUpload.get_TextMatrix(I, 13)) = True Then
' .Value = Val(FlexUpload.get_TextMatrix(I, 13))
' Else
' .Value = 0
' End If
'End With
'Com.Parameters.Add(Param18)
'intPos = intPos + 1
'Param19 = New SqlClient.SqlParameter
'With Param19
' .ParameterName = "@CancDate"
' .SqlDbType = SqlDbType.DateTime
' .Direction = ParameterDirection.Input
' If FlexUpload.get_TextMatrix(I, 14) <> "" Or IsDate(FlexUpload.get_TextMatrix(I, 14)) = True Then
' .Value = Convert.ToDateTime(FlexUpload.get_TextMatrix(I, 14))
' Else
' .Value = Convert.DBNull
' End If
'End With
'Com.Parameters.Add(Param19)
'intPos = intPos + 1
'Param20 = New SqlClient.SqlParameter
'With Param20
' .ParameterName = "@CancCharges"
' .SqlDbType = SqlDbType.Decimal
' .Direction = ParameterDirection.Input
' If IsDBNull(FlexUpload.get_TextMatrix(I, 15)) = False Or IsNumeric(FlexUpload.get_TextMatrix(I, 15)) = True Then
' .Value = Val(FlexUpload.get_TextMatrix(I, 15))
' Else
' .Value = 0
' End If
'End With
'Com.Parameters.Add(Param20)
'intPos = intPos + 1
'Param21 = New SqlClient.SqlParameter
'With Param21
' .ParameterName = "@ActivityDate"
' .SqlDbType = SqlDbType.DateTime
' .Direction = ParameterDirection.Input
' If FlexUpload.get_TextMatrix(I, 16) <> "" Or IsDate(FlexUpload.get_TextMatrix(I, 16)) = True Then
' .Value = Convert.ToDateTime(FlexUpload.get_TextMatrix(I, 16))
' Else
' .Value = Convert.DBNull
' End If
'End With
'Com.Parameters.Add(Param21)
'intPos = intPos + 1
'Param22 = New SqlClient.SqlParameter
'With Param22
' .ParameterName = "@FileName"
' .SqlDbType = SqlDbType.VarChar
' .Direction = ParameterDirection.Input
' .Value = strFileName
'End With
'Com.Parameters.Add(Param22)
'intPos = intPos + 1
'Param23 = New SqlClient.SqlParameter
'With Param23
' .ParameterName = "@UploadDate"
' .SqlDbType = SqlDbType.DateTime
' .Direction = ParameterDirection.Input
' .Value = Convert.ToDateTime(dtpUpload.Value)
'End With
'Com.Parameters.Add(Param23)
'intPos = -1
'End of Commented block
Param1 = New SqlClient.SqlParameter
With Param1
.ParameterName = "@Deal_no"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
.Value = FlxMerchantDeals.get_TextMatrix(I, 0)
End With
Com.Parameters.Add(Param1)
intPos = intPos + 1
Param2 = New SqlClient.SqlParameter
With Param2
.ParameterName = "@DEALACTIVITY"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
'.Size = 50
If IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 1)) = False Then
.Value = FlxMerchantDeals.get_TextMatrix(I, 1)
Else
.Value = Convert.DBNull
End If
End With
Com.Parameters.Add(Param2)
intPos = intPos + 1
Param3 = New SqlClient.SqlParameter
With Param3
.ParameterName = "@OPERTYP"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
If IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 2)) = False Then
.Value = FlxMerchantDeals.get_TextMatrix(I, 2)
Else
.Value = Convert.DBNull
End If
End With
Com.Parameters.Add(Param3)
intPos = intPos + 1
Param4 = New SqlClient.SqlParameter
With Param4
.ParameterName = "@CUSTOMERID"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
If IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 3)) = False Then
.Value = FlxMerchantDeals.get_TextMatrix(I, 3)
Else
.Value = Convert.DBNull
End If
End With
Com.Parameters.Add(Param4)
intPos = intPos + 1
Param5 = New SqlClient.SqlParameter
With Param5
.ParameterName = "@CUSTOMER_NAME"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
.Value = FlxMerchantDeals.get_TextMatrix(I, 4)
End With
Com.Parameters.Add(Param5)
intPos = intPos + 1
Param6 = New SqlClient.SqlParameter
With Param6
.ParameterName = "@PostingDate"
.SqlDbType = SqlDbType.DateTime
.Direction = ParameterDirection.Input
If FlxMerchantDeals.get_TextMatrix(I, 5) = "" Or _
IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 5)) = True Then
.Value = Convert.DBNull
Else
.Value = Convert.ToDateTime(FlxMerchantDeals.get_TextMatrix(I, 5)) ', "yyyy/MM/dd")
'Format(FlxMerchantDeals.get_TextMatrix(I, 5), "yyyy/MM/dd")
End If
End With
Com.Parameters.Add(Param6)
intPos = intPos + 1
Param7 = New SqlClient.SqlParameter
With Param7
.ParameterName = "@DealDate"
.SqlDbType = SqlDbType.DateTime
.Direction = ParameterDirection.Input
If FlxMerchantDeals.get_TextMatrix(I, 6) = "" Or _
IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 6)) = True Then
.Value = Convert.DBNull
Else
.Value = Convert.ToDateTime(FlxMerchantDeals.get_TextMatrix(I, 6)) ', "yyyy/MM/dd")
'.Value = FlxMerchantDeals.get_TextMatrix(I, 6)
End If
End With
Com.Parameters.Add(Param7)
intPos = intPos + 1
Param8 = New SqlClient.SqlParameter
With Param8
.ParameterName = "@CURRENCY_BOUGHT"
.SqlDbType = NVarChar
.Direction = ParameterDirection.Input
.Value = FlxMerchantDeals.get_TextMatrix(I, 7)
End With
Com.Parameters.Add(Param8)
intPos = intPos + 1
Param9 = New SqlClient.SqlParameter
With Param9
.ParameterName = "@AMOUNT_BOUGHT"
.SqlDbType = NVarChar
.Direction = ParameterDirection.Input
.Value = FlxMerchantDeals.get_TextMatrix(I, 8)
End With
Com.Parameters.Add(Param9)
intPos = intPos + 1
Param10 = New SqlClient.SqlParameter
With Param10
.ParameterName = "@Rate"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
.Value = FlxMerchantDeals.get_TextMatrix(I, 9)
End With
Com.Parameters.Add(Param10)
intPos = intPos + 1
Param11 = New SqlClient.SqlParameter
With Param11
.ParameterName = "@CURRENCY_SOLD"
.SqlDbType = NVarChar
.Direction = ParameterDirection.Input
.Value = FlxMerchantDeals.get_TextMatrix(I, 10)
End With
Com.Parameters.Add(Param11)
intPos = intPos + 1
Param12 = New SqlClient.SqlParameter
With Param12
.ParameterName = "@AMOUNT_SOLD"
.SqlDbType = NVarChar
.Direction = ParameterDirection.Input
.Value = FlxMerchantDeals.get_TextMatrix(I, 11)
End With
Com.Parameters.Add(Param12)
intPos = intPos + 1
Param13 = New SqlClient.SqlParameter
With Param13
.ParameterName = "@MATURITY_DATE"
.SqlDbType = DateTime
.Direction = ParameterDirection.Input
If FlxMerchantDeals.get_TextMatrix(I, 12) = "" Or _
IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 12)) = True Then
.Value = Convert.DBNull
Else
.Value = Convert.ToDateTime(FlxMerchantDeals.get_TextMatrix(I, 12)) ', "yyyy/MM/dd")
End If
End With
Com.Parameters.Add(Param13)
intPos = intPos + 1
Param14 = New SqlClient.SqlParameter
With Param14
.ParameterName = "@OPTION_DATE"
.SqlDbType = SqlDbType.DateTime
.Direction = ParameterDirection.Input
If FlxMerchantDeals.get_TextMatrix(I, 13) = "" Or _
IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 13)) = True Then
.Value = Convert.DBNull
Else
.Value = Convert.ToDateTime(FlxMerchantDeals.get_TextMatrix(I, 13)) ', "yyyy/MM/dd")
End If
End With
Com.Parameters.Add(Param14)
intPos = intPos + 1
Param15 = New SqlClient.SqlParameter
With Param15
.ParameterName = "@PRODUCT"
.SqlDbType = NVarChar
.Direction = ParameterDirection.Input
.Value = FlxMerchantDeals.get_TextMatrix(I, 14)
End With
Com.Parameters.Add(Param15)
intPos = intPos + 1
Param16 = New SqlClient.SqlParameter
With Param16
.ParameterName = "@BROKER"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
.Value = FlxMerchantDeals.get_TextMatrix(I, 15)
End With
Com.Parameters.Add(Param16)
intPos = intPos + 1
Param17 = New SqlClient.SqlParameter
With Param17
.ParameterName = "@Margin_Rate"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
If FlxMerchantDeals.get_TextMatrix(I, 16) = "" Or _
IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 16)) = True Then
.Value = "0"
Else
.Value = FlxMerchantDeals.get_TextMatrix(I, 16)
End If
End With
Com.Parameters.Add(Param17)
intPos = intPos + 1
Param18 = New SqlClient.SqlParameter
With Param18
.ParameterName = "@LCY_EQUIVALENT"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
.Value = FlxMerchantDeals.get_TextMatrix(I, 17)
End With
Com.Parameters.Add(Param18)
intPos = intPos + 1
Param19 = New SqlClient.SqlParameter
With Param19
.ParameterName = "@TRADER_ID"
.SqlDbType = NVarChar
.Direction = ParameterDirection.Input
.Value = FlxMerchantDeals.get_TextMatrix(I, 18)
End With
Com.Parameters.Add(Param19)
intPos = intPos + 1
Param20 = New SqlClient.SqlParameter
With Param20
.ParameterName = "@CANCELLATION_DATE"
.SqlDbType = DateTime
.Direction = ParameterDirection.Input
If FlxMerchantDeals.get_TextMatrix(I, 19) = "" Or _
IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 19)) = True Then
.Value = Convert.DBNull
Else
.Value = Convert.ToDateTime(FlxMerchantDeals.get_TextMatrix(I, 19)) ', "yyyy/MM/dd")
End If
End With
Com.Parameters.Add(Param20)
intPos = intPos + 1
Param21 = New SqlClient.SqlParameter
With Param21
.ParameterName = "@CANCELLATION_RATE"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
If FlxMerchantDeals.get_TextMatrix(I, 20).Trim = "" Or _
IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 20)) = True Then
.Value = "0"
Else
.Value = CDec(FlxMerchantDeals.get_TextMatrix(I, 20))
End If
'.Value = FlxMerchantDeals.get_TextMatrix(I, 20)
End With
Com.Parameters.Add(Param21)
intPos = intPos + 1
Param22 = New SqlClient.SqlParameter
With Param22
.ParameterName = "@CANCELLATION_CHARGES"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
If FlxMerchantDeals.get_TextMatrix(I, 21) = "" Or _
IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 21)) = True Then
.Value = "0"
Else
.Value = FlxMerchantDeals.get_TextMatrix(I, 21)
End If
' .Value = FlxMerchantDeals.get_TextMatrix(I, 21)
End With
Com.Parameters.Add(Param22)
intPos = intPos + 1
Param23 = New SqlClient.SqlParameter
With Param23
.ParameterName = "@UNDERLYING_EXPOSURE"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
If FlxMerchantDeals.get_TextMatrix(I, 22) = "" Or _
IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 22)) = True Then
.Value = ""
Else
.Value = FlxMerchantDeals.get_TextMatrix(I, 22)
End If
End With
Com.Parameters.Add(Param23)
intPos = intPos + 1
Param24 = New SqlClient.SqlParameter
With Param24
.ParameterName = "@DEALSTAT"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
.Value = FlxMerchantDeals.get_TextMatrix(I, 23)
End With
Com.Parameters.Add(Param24)
intPos = intPos + 1
Param25 = New SqlClient.SqlParameter
With Param25
.ParameterName = "@NARRATION"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
If FlxMerchantDeals.get_TextMatrix(I, 24) = "" Or _
IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 24)) = True Then
.Value = ""
Else
.Value = FlxMerchantDeals.get_TextMatrix(I, 24)
End If
End With
Com.Parameters.Add(Param25)
intPos = intPos + 1
Param26 = New SqlClient.SqlParameter
With Param26
.ParameterName = "@BO_AURTHORISER"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
.Value = FlxMerchantDeals.get_TextMatrix(I, 25)
End With
Com.Parameters.Add(Param26)
intPos = intPos + 1
Param27 = New SqlClient.SqlParameter
With Param27
.ParameterName = "@AURTHORISED_DATE"
.SqlDbType = DateTime
.Direction = ParameterDirection.Input
.Value = Convert.ToDateTime(FlxMerchantDeals.get_TextMatrix(I, 26)) ', "yyyy/MM/dd")
'.Value = FlxMerchantDeals.get_TextMatrix(I, 26)
End With
Com.Parameters.Add(Param27)
intPos = intPos + 1
Param28 = New SqlClient.SqlParameter
With Param28
.ParameterName = "@REGION"
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
If FlxMerchantDeals.get_TextMatrix(I, 27) = "" Or _
IsDBNull(FlxMerchantDeals.get_TextMatrix(I, 27)) = True Then
.Value = ""
Else
.Value = FlxMerchantDeals.get_TextMatrix(I, 27)
End If
End With
Com.Parameters.Add(Param28)
'intPos = intPos + 1
'Param29 = New SqlClient.SqlParameter
'With Param29
' .ParameterName = "@REGION"
' .SqlDbType = SqlDbType.VarChar
' .Direction = ParameterDirection.Input
' .Value = strFileName
'End With
'Com.Parameters.Add(Param29)
intPos = -1
Com.ExecuteNonQuery()
'With FlxMerchantDeals
' Dim drDup As DataRow
' drDup = dtblDuplicate.NewRow()
' drDup(0) = .get_TextMatrix(I, 2)
' drDup(1) = .get_TextMatrix(I, 3)
' drDup(2) = .get_TextMatrix(I, 1)
' dtblDuplicate.Rows.Add(drDup)
'End With
Com.Parameters.Clear()
blnBlnkDealNo = False
' End If
''----------------------Adding the Log Information for Display Log to User.
'If IsDBNull(FlexUpload.get_TextMatrix(I, 2)) Or FlexUpload.get_TextMatrix(I, 2) = "" Then
'Else
' strErrInfo = "Deal No " + FlexUpload.get_TextMatrix(I, 2) + " Uploaded Succesfully."
' dr = dtblWrongDeal.NewRow()
' dr(1) = strErrInfo
' dtblWrongDeal.Rows.Add(dr)
'End If
Next
Trn.Commit()
MsgBox("File Uploaded successfully !!!", MsgBoxStyle.OKOnly + MsgBoxStyle.Information)
ProgressBar1.Visible = False
IntLog = 0
If dtblWrongDeal.Rows.Count > 0 Then
Dim dst As New DataSet
Dim FSI As New System.IO.FileInfo(Application.StartupPath + "\WrongDealInfo.xml")
If FSI.Exists = True Then
FSI.Delete()
End If
dst.Tables.Add(dtblWrongDeal)
dst.WriteXml(Application.StartupPath + "\WrongDealInfo.xml")
Dim frm As New frmLOG
frm.Show()
End If
' FlexUpload.Clear()
InitializeFlexGrid()
Catch ex As Exception
If intPos >= 0 Then
Select Case intPos
Case 0
strErr = "Ext Link Id"
Case 1
strErr = "Deal No"
Case 2
strErr = "Remarks"
Case 3
strErr = "Customer Code"
Case 4
strErr = "Branch"
Case 5
strErr = "Customer Name"
Case 6
strErr = "Posting Date"
Case 7
strErr = "Deal Date"
Case 8
strErr = "Buy Currency Code"
Case 9
strErr = "Buy Amount"
Case 10
strErr = "Deal Rate"
Case 11
strErr = "Sell Currency Code"
Case 12
strErr = "Sell Amount"
Case 13
strErr = "Funds Delivery Date1"
Case 14
strErr = "Funds Delivery Date2"
Case 15
strErr = "Product Code"
Case 16
strErr = "Narration"
Case 17
strErr = "Cancellation Rate"
Case 18
strErr = "Cancellation Date"
Case 19
strErr = "Cancellation Charges"
Case 20
strErr = "Activity Date"
Case 21
strErr = "File Name"
Case 22
strErr = "UploadDate"
End Select
MessageBox.Show("Error while reading data from '" + strErr + "' , Transaction will rollback ;Error Description:" + ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
MessageBox.Show("Error While Writting data to Database , Transaction will rollback!!!" + ex.Message.ToString(), "File Upload Utility", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
Me.Cursor = Cursors.Default
Finally
Dim FSI As New System.IO.FileInfo(Application.StartupPath + "\WrongDealInfo.xml")
If FSI.Exists = True Then
FSI.Delete()
End If
btnUpload.Enabled = False
btnRead.Enabled = True
btnAbort.Enabled = False
Trn.Dispose()
Me.Cursor = Cursors.Default
End Try
Me.Cursor = Cursors.Default
End Sub
Private Sub btnAbort_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAbort.Click
btnUpload.Enabled = False
btnRead.Enabled = True
FlexUpload.Clear()
InitializeFlexGrid()
End Sub
Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
If OpenFileDialog1.FileName.ToString() <> "" Then
FlexUpload.Clear()
InitializeFlexGrid()
txtFileName.Text = OpenFileDialog1.FileName.ToString()
FetchSheetDetails(OpenFileDialog1.FileName.ToString())
End If
End Sub
Private Function ValidateExcelData(ByVal ds As DataSet) As String
'+--------------------------------------------------------------------------------------+
' Method Name : ValidateExcelData
' Author : Satheesh.T.S
' Description : Function for comparing the datatype of excel file data with expected datatype.
' Created Date : 30-11-2006
' Return Type : String
' Modifications :
'+--------------------------------------------------------------------------------------+
Dim strCl As String
With ds.Tables(0)
'''''If .Columns(0).DataType.ToString <> "System.String" Then
''''' strCl = strCl + "," + .Columns(0).ColumnName.ToString()
'''''End If
If .Columns(1).DataType.ToString <> "System.String" Then
strCl = strCl + "," + .Columns(1).ColumnName.ToString()
End If
If .Columns(2).DataType.ToString <> "System.String" Then
strCl = strCl + "," + .Columns(2).ColumnName.ToString()
End If
If .Columns(3).DataType.ToString <> "System.String" Then
strCl = strCl + "," + .Columns(3).ColumnName.ToString()
End If
If .Columns(4).DataType.ToString <> "System.String" Then
strCl = strCl + "," + .Columns(4).ColumnName.ToString()
End If
If .Columns(5).DataType.ToString <> "System.String" Then
strCl = strCl + "," + .Columns(5).ColumnName.ToString()
End If
If .Columns(6).DataType.ToString <> "System.DateTime" Then
strCl = strCl + "," + .Columns(6).ColumnName.ToString()
End If
If .Columns(7).DataType.ToString <> "System.DateTime" Then
strCl = strCl + "," + .Columns(7).ColumnName.ToString()
End If
If .Columns(8).DataType.ToString <> "System.String" Then
strCl = strCl + "," + .Columns(8).ColumnName.ToString()
End If
If .Columns(9).DataType.ToString <> "System.Double" Then
strCl = strCl + "," + .Columns(9).ColumnName.ToString()
End If
If .Columns(10).DataType.ToString <> "System.Double" Then
strCl = strCl + "," + .Columns(10).ColumnName.ToString()
End If
If .Columns(11).DataType.ToString <> "System.String" Then
strCl = strCl + "," + .Columns(11).ColumnName.ToString()
End If
If .Columns(12).DataType.ToString <> "System.Double" Then
strCl = strCl + "," + .Columns(12).ColumnName.ToString()
End If
If .Columns(13).DataType.ToString <> "System.DateTime" Then
strCl = strCl + "," + .Columns(13).ColumnName.ToString()
End If
If .Columns(14).DataType.ToString <> "System.DateTime" Then
strCl = strCl + "," + .Columns(14).ColumnName.ToString()
End If
If .Columns(15).DataType.ToString <> "System.String" Then
strCl = strCl + "," + .Columns(15).ColumnName.ToString()
End If
If .Columns(16).DataType.ToString <> "System.String" Then
strCl = strCl + "," + .Columns(16).ColumnName.ToString()
End If
''''''If .Columns(17).DataType.ToString <> "System.Double" Then
'''''' strCl = strCl + "," + .Columns(17).ColumnName.ToString()
''''''End If
If .Columns(18).DataType.ToString <> "System.DateTime" Then
strCl = strCl + "," + .Columns(18).ColumnName.ToString()
End If
If .Columns(19).DataType.ToString <> "System.Double" Then
strCl = strCl + "," + .Columns(19).ColumnName.ToString()
End If
If .Columns(20).DataType.ToString <> "System.DateTime" Then
strCl = strCl + "," + .Columns(20).ColumnName.ToString()
End If
End With
If Len(strCl) > 0 Then
ValidateExcelData = strCl.Substring(1)
Else
ValidateExcelData = ""
End If
End Function
'Function GetFileName(ByVal strPath As String, Optional ByRef strRemain As String = "") As String
' 'extract filename only from full path
' If InStr(1, strPath, "\") = 0 Then
' GetFileName = strPath
' strRemain = ""
' Else
' GetFileName = StrReverse(strPath)
' strRemain = Mid(GetFileName, InStr(1, GetFileName, "\"))
' strRemain = StrReverse(strRemain)
' strRemain = strRemain(strRemain, Len(strRemain) - 1)
' GetFileName = StrReverse(strPath)
' GetFileName = Left(GetFileName, InStr(1, GetFileName, "\") - 1)
' GetFileName = StrReverse(GetFileName)
' End If
'End Function
''''''''---------------Commented By satheesh on 16-11-2006 ,bcz This function is not using anywhere else in the form.
''''''''Private Sub FillWorksheet()
'''''''' Dim MyConnection As System.Data.OleDb.OleDbConnection
'''''''' Dim dtTables As System.Data.DataTable
'''''''' Dim i As Integer
'''''''' Try
'''''''' MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
'''''''' "data source='" & txtFileName.Text & " '; " & "Extended Properties=Excel 8.0;")
'''''''' MyConnection.Open()
'''''''' dtTables = MyConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Nothing)
'''''''' For i = 0 To dtTables.Rows.Count - 1
'''''''' cmbWorksheet.Items.Add(dtTables.Rows(i).Item("Table_Name"))
'''''''' Next
'''''''' If cmbWorksheet.Items.Count > 0 Then
'''''''' cmbWorksheet.SelectedIndex = 0
'''''''' End If
'''''''' MyConnection.Close()
'''''''' Catch ex As Exception
'''''''' MsgBox(ex.Message)
'''''''' MyConnection.Close()
'''''''' End Try
''''''''End Sub
''''''''---------------Commented By satheesh on 16-11-2006 ,bcz This function is not using anywhere else in the form.
''''''''-------------------------------------------Old Method(Slow)
''''''''Private Function ImportDataFromExcel(ByVal PrmPathExcelFile As String, ByVal PrmWorksheet As String, ByVal whereClause As String)
'''''''' Dim excel_app As Object
'''''''' Dim excel_sheet As Object
'''''''' Dim iRow As Integer, iCol As Integer, intSrNo As Int32
'''''''' Try
'''''''' FlexUpload.Redraw = False
'''''''' intSrNo = 1
'''''''' excel_app = CreateObject("Excel.Application")
'''''''' excel_app.Workbooks.Open(FileName:=PrmPathExcelFile)
'''''''' Call excel_app.Sheets(Replace(PrmWorksheet, "$", "")).Activate()
'''''''' If Val(excel_app.Application.Version) >= 8 Then
'''''''' excel_sheet = excel_app.ActiveSheet
'''''''' Else
'''''''' excel_sheet = excel_app
'''''''' End If
'''''''' FlexUpload.Rows = excel_sheet.UsedRange.Rows.Count + 1
'''''''' FlexUpload.Cols = IIf(excel_sheet.UsedRange.Columns.Count + 1 > FlexUpload.Cols, excel_sheet.UsedRange.Columns.Count + 1, FlexUpload.Cols)
'''''''' For iCol = 1 To FlexUpload.Cols - 1
'''''''' FlexUpload.set_TextMatrix(FlexUpload.Rows - 1, iCol - 1, "")
'''''''' Next
'''''''' For iRow = 2 To FlexUpload.Rows - 1
'''''''' FlexUpload.set_TextMatrix(intSrNo, 0, intSrNo)
'''''''' For iCol = 1 To FlexUpload.Cols - 1
'''''''' If iCol <= 21 Then
'''''''' FlexUpload.set_TextMatrix(intSrNo, iCol, Trim$(excel_sheet.Cells(iRow, iCol).Value))
'''''''' End If
'''''''' Next
'''''''' intSrNo = intSrNo + 1
'''''''' Next
'''''''' FlexUpload.Rows = FlexUpload.Rows - 1
'''''''' FlexUpload.Redraw = True
'''''''' Catch ex As Exception
'''''''' MessageBox.Show("Error in Line :" + CStr(intSrNo) + " " + ex.Message)
'''''''' Finally
'''''''' Call ExcelShutdown(excel_app, excel_sheet)
'''''''' End Try
''''''''End Function
''''''''''--------------Commented By Satheesh on 16-Nov-2006 for modifying the function
'Imports data from Excel file which contains latest currency rates
'''''''''''Private Function ImportDataFromExcel(ByVal PrmPathExcelFile As String, ByVal PrmWorksheet As String, ByVal whereClause As String)
''''''''''' Dim dtTables As DataTable
''''''''''' Dim myColumn As DataColumn
''''''''''' Dim colCount As Integer
''''''''''' Dim myTable As System.Data.DataTable
''''''''''' Dim MyConnection As System.Data.OleDb.OleDbConnection
''''''''''' Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
''''''''''' Dim DtSet As System.Data.DataSet
''''''''''' Dim myRow As DataRow
''''''''''' Dim I, J As Integer
''''''''''' I = 1 : J = 0
''''''''''' Try
''''''''''' '''MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
''''''''''' ''' "data source='" & PrmPathExcelFile & " '; " & "Extended Properties=Excel 8.0;")
''''''''''' ' Select the data from Sheet1 of the workbook.
''''''''''' MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [" & PrmWorksheet & "$]" & whereClause & "", MyConnection)
''''''''''' DtSet = New System.Data.DataSet
''''''''''' MyCommand.Fill(DtSet)
''''''''''' 'fill the flexgrid
''''''''''' myTable = DtSet.Tables(0)
''''''''''' FlexUpload.Rows = 2
''''''''''' For Each myRow In myTable.Rows
'''''''''''
''''''''''' 'If IsDBNull(myRow(0)) = False Then
''''''''''' '''''''''''With myRow
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 0, I)
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 1, IIf(IsDBNull(.Item("ext_link_id")), "", .Item("ext_link_id")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 2, IIf(IsDBNull(.Item("deal_no")), "", .Item("deal_no")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 3, IIf(IsDBNull(.Item("Remark")), "", .Item("Remark")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 4, IIf(IsDBNull(.Item("cust_cd")), "", .Item("cust_cd")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 5, IIf(IsDBNull(.Item("Branch")), "", .Item("Branch")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 6, IIf(IsDBNull(.Item("cust_name")), "", .Item("cust_name")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 7, IIf(IsDBNull(.Item("pstng_dt")), "", .Item("pstng_dt")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 8, IIf(IsDBNull(.Item("deal_dt")), "", .Item("deal_dt")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 9, IIf(IsDBNull(.Item("buy_crncy_cd")), "", .Item("buy_crncy_cd")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 10, IIf(IsDBNull(.Item("rmng_amnt_b")), "", .Item("rmng_amnt_b")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 11, IIf(IsDBNull(.Item("deal_rt")), "", .Item("deal_rt")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 12, IIf(IsDBNull(.Item("sell_crncy_cd")), "", .Item("sell_crncy_cd")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 13, IIf(IsDBNull(.Item("rmng_amnt_s")), "", .Item("rmng_amnt_s")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 14, IIf(IsDBNull(.Item("fnds_dlvry")), "", .Item("fnds_dlvry")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 15, IIf(IsDBNull(.Item("fnds_dlvry")), "", .Item("fnds_dlvry")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 16, IIf(IsDBNull(.Item("prdct_cd")), "", .Item("prdct_cd")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 17, IIf(IsDBNull(.Item("nrrtn")), "", .Item("nrrtn")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 18, IIf(IsDBNull(.Item("cancrt")), "", .Item("cancrt")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 19, IIf(IsDBNull(.Item("canc_dt")), "", .Item("canc_dt")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 20, IIf(IsDBNull(.Item("Cancellation Charges")), 0, .Item("Cancellation Charges")))
''''''''''' ''''''''''' FlexUpload.set_TextMatrix(I, 21, IIf(IsDBNull(.Item("Activity Date")), "", .Item("Activity Date")))
''''''''''' ''''''''''' FlexUpload.Rows = FlexUpload.Rows + 1
''''''''''' '''''''''''End With
''''''''''' I = I + 1
''''''''''' J = J + 1
''''''''''' Next
''''''''''' FlexUpload.Rows = FlexUpload.Rows - 1
''''''''''' MyConnection.Close()
''''''''''' Catch ex As Exception
''''''''''' MsgBox(ex.Message)
''''''''''' MyConnection.Close()
''''''''''' End Try
'''''''''''End Function
'''''''---------------Commented By satheesh on 16-11-2006 ,bcz This function is not using anywhere else in the form.
'''''''Public Function GetSheetsFromFile(ByVal strFileName As String) As ArrayList
''''''' 'Dim objExcel As BCExcelWrapper
''''''' Dim objExcel As Excel.Application
''''''' Dim objArrayList As ArrayList
''''''' Dim WBook As Excel.Workbook
''''''' Dim WSheet As Excel.Worksheet
''''''' Dim intIncr As Integer
''''''' Try
''''''' 'objExcel = New BCExcelWrapper
''''''' objExcel = New Excel.Application
''''''' objArrayList = New ArrayList
''''''' 'commentred By Prasad On 6 Apr 2006
''''''' 'WBook = objExcel.xlApp.Workbooks.Open(strFileName)
''''''' WBook = objExcel.Workbooks.Open(strFileName, 0)
''''''' 'WSheet = CType(WBook.ActiveSheet, Excel.Worksheet)
''''''' 'Set the First Sheet as the Current Sheet
''''''' WSheet = CType(WBook.Sheets.Item(1), Excel.Worksheet)
''''''' For intIncr = 1 To WBook.Sheets.Count
''''''' objArrayList.Add(WSheet.Name)
''''''' WSheet = WSheet.Next
''''''' Next
''''''' Return objArrayList
''''''' Catch ex As Exception
''''''' Return Nothing
''''''' Finally
''''''' Try
''''''' 'Added By Prasad Puranik just to remove "RPC Server is Unavailable."
''''''' '-------------------------------------------------------------------
''''''' If Not (WSheet Is Nothing) Then
''''''' System.Runtime.InteropServices.Marshal.ReleaseComObject(WSheet)
''''''' WSheet = Nothing
''''''' End If
''''''' If Not (WBook Is Nothing) Then
''''''' 'this should be done only if open
''''''' WBook.Close(False)
''''''' System.Runtime.InteropServices.Marshal.ReleaseComObject(WBook)
''''''' WBook = Nothing
''''''' End If
''''''' If Not (objExcel Is Nothing) Then
''''''' objExcel.Quit()
''''''' System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel)
''''''' objExcel = Nothing
''''''' End If
''''''' 'objWrapper = Nothing
''''''' GC.Collect()
''''''' 'System.Diagnostics.Process.GetProcessById(objExcel.ProcId).Kill()
''''''' Catch ex As Exception
''''''' 'Do Nothing
''''''' End Try
''''''' End Try
'''''''End Function
'''''''---------------Commented By satheesh on 16-11-2006
'''''''Private Sub btnOpenDlg_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenDlg.Click
'''''''Dim myStream As Stream
'''''''Dim openFileDialog1 As New OpenFileDialog
'''''''Dim intSheets As Integer
''''''''Dim itemSegment As New clsGeneral.Segment
'''''''Try
''''''' openFileDialog1.InitialDirectory = "c:\"
''''''' openFileDialog1.Filter = "Excel File (*.xls)|*.xls"
''''''' cmbWorksheet.Enabled = True
''''''' openFileDialog1.RestoreDirectory = True
''''''' If openFileDialog1.ShowDialog() = DialogResult.OK Then
''''''' myStream = openFileDialog1.OpenFile()
''''''' If Not (myStream Is Nothing) Then
''''''' ' Insert code to read the stream here.
''''''' txtFileName.Text = openFileDialog1.FileName
''''''' myStream.Close()
''''''' cmbWorksheet.Items.Clear()
''''''' objSheetList = GetSheetsFromFile(txtFileName.Text)
''''''' If Not objSheetList Is Nothing Then
''''''' If objSheetList.Count <> 0 Then
''''''' For intSheets = 0 To objSheetList.Count - 1
''''''' cmbWorksheet.Items.Add(objSheetList.Item(intSheets))
''''''' Next
''''''' cmbWorksheet.SelectedIndex = 0
''''''' End If
''''''' End If
''''''' 'FillWorksheet()
''''''' Else
''''''' txtFileName.Text = ""
''''''' cmbWorksheet.Items.Clear()
''''''' End If
''''''' Else
''''''' txtFileName.Text = ""
''''''' End If
'''''''Catch ex As System.Exception
''''''' MsgBox(ex.Message)
'''''''End Try
'''''''End Sub
'''''''''' Public Sub ExcelShutdown(ByVal excel_app As Object, ByVal excel_sheet As Object)
'''''''''' On Error GoTo ExcelShutdown_ERROR
'''''''''' excel_app.ActiveWorkbook.Close(True)
'''''''''' excel_app.Quit()
'''''''''' excel_sheet = Nothing
'''''''''' excel_app = Nothing
'''''''''' Exit Sub
''''''''''ExcelShutdown_ERROR:
'''''''''' MsgBox("Excel Shutdown ERROR" & vbCrLf & Err.Description, vbCritical, "ExcelShutdown_ERROR")
''''''''''' End Sub
'''''Commented by Satheesh .T.S on 21March2007 for implimenting E Treasury Related Changes
'''''''''''''''''''Private Function ImportDataFromExcel(ByVal PrmPathExcelFile As String, ByVal PrmWorksheet As String, Optional ByVal whereClause As String = "") As Integer
''''''''''''''''''' Dim MyConnection As System.Data.OleDb.OleDbConnection
''''''''''''''''''' Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
''''''''''''''''''' Dim DtSet As System.Data.DataSet
''''''''''''''''''' Dim myRow As DataRow
''''''''''''''''''' Dim I, J As Integer
''''''''''''''''''' Dim Rd As SqlDataReader
''''''''''''''''''' Dim cmd As SqlCommand
''''''''''''''''''' Dim strProdList As String
''''''''''''''''''' Dim strEvntList As String
''''''''''''''''''' Dim strSQLXL As String
''''''''''''''''''' Dim strLst As String
''''''''''''''''''' Dim strWrongData As String
''''''''''''''''''' Dim dtblWrongDeal As New DataTable
''''''''''''''''''' Dim dr As DataRow
''''''''''''''''''' Try
''''''''''''''''''' ImportDataFromExcel = -1
''''''''''''''''''' cmd = New SqlCommand("Select count(1) from fctGlobal where FileName ='" + Trim(Mid(PrmPathExcelFile, PrmPathExcelFile.LastIndexOf("\") + 2) + "'"), conn)
''''''''''''''''''' If Convert.ToInt32(cmd.ExecuteScalar()) <> 0 Then
''''''''''''''''''' MessageBox.Show("The Selected File is already Uploaded", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning)
''''''''''''''''''' Exit Function
''''''''''''''''''' End If
''''''''''''''''''' dtblWrongDeal.Columns.Add("Id").AutoIncrement = True
''''''''''''''''''' dtblWrongDeal.Columns(0).AutoIncrementSeed = 1
''''''''''''''''''' dtblWrongDeal.Columns.Add("Log")
''''''''''''''''''' cmd = New SqlCommand("select prdct from exmproductmas", conn)
''''''''''''''''''' Rd = cmd.ExecuteReader()
''''''''''''''''''' While Rd.Read
''''''''''''''''''' strProdList = strProdList + "'" + Rd.Item(0) + "',"
''''''''''''''''''' End While
''''''''''''''''''' strProdList = Mid(Trim(strProdList), 1, Len(Trim(strProdList)) - 1)
''''''''''''''''''' cmd.Dispose()
''''''''''''''''''' Rd.Close()
''''''''''''''''''' cmd = New SqlCommand("select event from exmeventmas", conn)
''''''''''''''''''' Rd = cmd.ExecuteReader()
''''''''''''''''''' While Rd.Read
''''''''''''''''''' strEvntList = strEvntList + "'" + Rd.Item(0) + "',"
''''''''''''''''''' End While
''''''''''''''''''' strEvntList = Mid(Trim(strEvntList), 1, Len(Trim(strEvntList)) - 1)
''''''''''''''''''' cmd.Dispose()
''''''''''''''''''' Rd.Close()
''''''''''''''''''' MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
''''''''''''''''''' "data source='" & PrmPathExcelFile & " '; " & "Extended Properties=""Excel 8.0;HDR=YES;FMT=Delimited;IMEX=1""")
''''''''''''''''''' strSQLXL = "select * from [" & PrmWorksheet & "$] where prdct_cd in (" + strProdList + ") and Remark not in (" + strEvntList + ")"
''''''''''''''''''' MyCommand = New System.Data.OleDb.OleDbDataAdapter(strSQLXL, MyConnection)
''''''''''''''''''' DtSet = New System.Data.DataSet
''''''''''''''''''' MyCommand.Fill(DtSet)
''''''''''''''''''' If DtSet.Tables(0).Columns.Count < 21 Then
''''''''''''''''''' MessageBox.Show("Selected File is not having expected number of columns.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
''''''''''''''''''' Exit Function
''''''''''''''''''' End If
''''''''''''''''''' ''''''''strLst = Trim(ValidateExcelData(DtSet))
''''''''''''''''''' ''''''''If Len(strLst) > 0 Then
''''''''''''''''''' '''''''' MessageBox.Show("Wrong Type of Data in Column(s) '" + strLst + "', Unable to read data from File.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
''''''''''''''''''' '''''''' Exit Function
''''''''''''''''''' ''''''''End If
''''''''''''''''''' If DtSet.Tables(0).Rows.Count > 0 Then
''''''''''''''''''' btnUpload.Enabled = True
''''''''''''''''''' btnAbort.Enabled = True
''''''''''''''''''' btnRead.Enabled = False
''''''''''''''''''' Else
''''''''''''''''''' btnUpload.Enabled = False
''''''''''''''''''' btnAbort.Enabled = False
''''''''''''''''''' btnRead.Enabled = True
''''''''''''''''''' End If
''''''''''''''''''' I = 1
''''''''''''''''''' With FlexUpload
''''''''''''''''''' Dim Dealno As String
''''''''''''''''''' Dim ExtLinkId As String
''''''''''''''''''' Dim CustCd As String
''''''''''''''''''' .Clear()
''''''''''''''''''' InitializeFlexGrid()
''''''''''''''''''' .Rows = 2
''''''''''''''''''' ProgressBar1.Visible = True
''''''''''''''''''' ProgressBar1.Value = 0
''''''''''''''''''' ProgressBar1.Minimum = 0
''''''''''''''''''' ProgressBar1.Maximum = DtSet.Tables(0).Rows.Count
''''''''''''''''''' For Each myRow In DtSet.Tables(0).Rows
''''''''''''''''''' ProgressBar1.Value = ProgressBar1.Value + 1
''''''''''''''''''' .set_TextMatrix(I, 0, I)
''''''''''''''''''' If IsDBNull(myRow.Item("deal_no")) Then
''''''''''''''''''' Dealno = ""
''''''''''''''''''' Else
''''''''''''''''''' Dealno = myRow.Item("deal_no")
''''''''''''''''''' End If
''''''''''''''''''' If IsDBNull(myRow.Item("ext_link_id")) Then
''''''''''''''''''' ExtLinkId = " "
''''''''''''''''''' Else
''''''''''''''''''' ExtLinkId = myRow.Item("ext_link_id")
''''''''''''''''''' End If
''''''''''''''''''' If IsDBNull(myRow.Item("cust_cd")) Then
''''''''''''''''''' CustCd = " "
''''''''''''''''''' Else
''''''''''''''''''' CustCd = myRow.Item("cust_cd")
''''''''''''''''''' End If
''''''''''''''''''' If IIf(IsDBNull(myRow.Item("Remark")), "", myRow.Item("Remark")) = "" Then
''''''''''''''''''' strWrongData = "Remark column is blank for deal no: " + Dealno + ", External Link Id:" + ExtLinkId + " and Customer Code:" + CustCd
''''''''''''''''''' dr = dtblWrongDeal.NewRow()
''''''''''''''''''' dr(1) = strWrongData
''''''''''''''''''' dtblWrongDeal.Rows.Add(dr)
''''''''''''''''''' End If
''''''''''''''''''' If IIf(IsDBNull(myRow.Item("cust_cd")), "", myRow.Item("cust_cd")) = "" Then
''''''''''''''''''' strWrongData = "cust_cd column is blank for deal no " + Dealno + ", External Link Id:" + ExtLinkId
''''''''''''''''''' dr = dtblWrongDeal.NewRow()
''''''''''''''''''' dr(1) = strWrongData
''''''''''''''''''' dtblWrongDeal.Rows.Add(dr)
''''''''''''''''''' End If
''''''''''''''''''' If IIf(IsDBNull(myRow.Item("city")), "", myRow.Item("city")) = "" Then
''''''''''''''''''' strWrongData = "city column is blank for deal no " + Dealno + ", External Link Id:" + ExtLinkId + " and Customer Code:" + CustCd
''''''''''''''''''' dr = dtblWrongDeal.NewRow()
''''''''''''''''''' dr(1) = strWrongData
''''''''''''''''''' dtblWrongDeal.Rows.Add(dr)
''''''''''''''''''' End If
''''''''''''''''''' If IIf(IsDBNull(myRow.Item("cust_name")), "", myRow.Item("cust_name")) = "" Then
''''''''''''''''''' strWrongData = "cust_name column is blank for deal no " + Dealno + ", External Link Id:" + ExtLinkId + " and Customer Code:" + CustCd
''''''''''''''''''' dr = dtblWrongDeal.NewRow()
''''''''''''''''''' dr(1) = strWrongData
''''''''''''''''''' dtblWrongDeal.Rows.Add(dr)
''''''''''''''''''' End If
''''''''''''''''''' If IIf(IsDBNull(myRow.Item("buy_crncy_cd")), "", myRow.Item("buy_crncy_cd")) = "" Then
''''''''''''''''''' strWrongData = "buy_crncy_cd column is blank for deal no " + Dealno + ", External Link Id:" + ExtLinkId + " and Customer Code:" + CustCd
''''''''''''''''''' dr = dtblWrongDeal.NewRow()
''''''''''''''''''' dr(1) = strWrongData
''''''''''''''''''' dtblWrongDeal.Rows.Add(dr)
''''''''''''''''''' End If
''''''''''''''''''' If CType(IIf(IsDBNull(myRow.Item("rmng_amnt_b")), "", myRow.Item("rmng_amnt_b")), String) = "" Then
''''''''''''''''''' strWrongData = "rmng_amnt_b column is blank for deal no " + Dealno + ", External Link Id:" + ExtLinkId + " and Customer Code:" + CustCd
''''''''''''''''''' dr = dtblWrongDeal.NewRow()
''''''''''''''''''' dr(1) = strWrongData
''''''''''''''''''' dtblWrongDeal.Rows.Add(dr)
''''''''''''''''''' End If
''''''''''''''''''' If CType(IIf(IsDBNull(myRow.Item("deal_rt")), 0, myRow.Item("deal_rt")), String) = "" Then
''''''''''''''''''' strWrongData = "deal_rt column is blank for deal no " + Dealno + ", External Link Id:" + ExtLinkId + " and Customer Code:" + CustCd
''''''''''''''''''' dr = dtblWrongDeal.NewRow()
''''''''''''''''''' dr(1) = strWrongData
''''''''''''''''''' dtblWrongDeal.Rows.Add(dr)
''''''''''''''''''' End If
''''''''''''''''''' If IIf(IsDBNull(myRow.Item("sell_crncy_cd")), "", myRow.Item("sell_crncy_cd")) = "" Then
''''''''''''''''''' strWrongData = "sell_crncy_cd column is blank for deal no " + Dealno + ", External Link Id:" + ExtLinkId + " and Customer Code:" + CustCd
''''''''''''''''''' dr = dtblWrongDeal.NewRow()
''''''''''''''''''' dr(1) = strWrongData
''''''''''''''''''' dtblWrongDeal.Rows.Add(dr)
''''''''''''''''''' End If
''''''''''''''''''' If CType(IIf(IsDBNull(myRow.Item("rmng_amnt_s")), "", myRow.Item("rmng_amnt_s")), String) = "" Then
''''''''''''''''''' strWrongData = "rmng_amnt_s column is blank for deal no " + Dealno + ", External Link Id:" + ExtLinkId + " and Customer Code:" + CustCd
''''''''''''''''''' dr = dtblWrongDeal.NewRow()
''''''''''''''''''' dr(1) = strWrongData
''''''''''''''''''' dtblWrongDeal.Rows.Add(dr)
''''''''''''''''''' End If
''''''''''''''''''' If CType(IIf(IsDBNull(myRow.Item("fnds_dlvry2")), "01/01/1900", myRow.Item("fnds_dlvry2")), Date) = "01/01/1900" Then
''''''''''''''''''' strWrongData = "fnds_dlvry2 column is blank for deal no " + Dealno + ", External Link Id:" + ExtLinkId + " and Customer Code:" + CustCd
''''''''''''''''''' dr = dtblWrongDeal.NewRow()
''''''''''''''''''' dr(1) = strWrongData
''''''''''''''''''' dtblWrongDeal.Rows.Add(dr)
''''''''''''''''''' End If
''''''''''''''''''' If IIf(IsDBNull(myRow.Item("prdct_cd")), "", myRow.Item("prdct_cd")) = "" Then
''''''''''''''''''' strWrongData = "prdct_cd column is blank for deal no " + Dealno + ", External Link Id:" + ExtLinkId + " and Customer Code:" + CustCd
''''''''''''''''''' dr = dtblWrongDeal.NewRow()
''''''''''''''''''' dr(1) = strWrongData
''''''''''''''''''' dtblWrongDeal.Rows.Add(dr)
''''''''''''''''''' End If
''''''''''''''''''' If CType(IIf(IsDBNull(myRow.Item("act_date")), "01/01/1900", myRow.Item("act_date")), Date) = "01/01/1900" Then
''''''''''''''''''' strWrongData = "act_date column is blank for deal no " + Dealno + ", External Link Id:" + ExtLinkId + " and Customer Code:" + CustCd
''''''''''''''''''' dr = dtblWrongDeal.NewRow()
''''''''''''''''''' dr(1) = strWrongData
''''''''''''''''''' dtblWrongDeal.Rows.Add(dr)
''''''''''''''''''' End If
''''''''''''''''''' For J = 0 To 20
''''''''''''''''''' .set_TextMatrix(I, J + 1, IIf(IsDBNull(myRow.Item(J)), "", myRow.Item(J)))
''''''''''''''''''' Next
''''''''''''''''''' .Rows = .Rows + 1
''''''''''''''''''' I = I + 1
''''''''''''''''''' Next
''''''''''''''''''' .Rows = .Rows - 1
''''''''''''''''''' End With
''''''''''''''''''' ProgressBar1.Visible = False
''''''''''''''''''' IntLog = 1
''''''''''''''''''' If dtblWrongDeal.Rows.Count > 0 Then
''''''''''''''''''' MessageBox.Show("Upload not possible , Blank data in mandatory columns !!!", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Stop)
''''''''''''''''''' Dim dst As New DataSet
''''''''''''''''''' Dim FSI As New System.IO.FileInfo(Application.StartupPath + "\WrongDealInfo.xml")
''''''''''''''''''' If FSI.Exists = True Then
''''''''''''''''''' FSI.Delete()
''''''''''''''''''' End If
''''''''''''''''''' dst.Tables.Add(dtblWrongDeal)
''''''''''''''''''' dst.WriteXml(Application.StartupPath + "\WrongDealInfo.xml")
''''''''''''''''''' Dim frm As New frmLOG
''''''''''''''''''' frm.Show()
''''''''''''''''''' 'btnUpload.Enabled = False
''''''''''''''''''' End If
''''''''''''''''''' ImportDataFromExcel = 1
''''''''''''''''''' MyConnection.Close()
''''''''''''''''''' MyCommand.Dispose()
''''''''''''''''''' MyConnection.Close()
''''''''''''''''''' Catch ex As Exception
''''''''''''''''''' If ex.Message = "Not a legal OleAut date." Then
''''''''''''''''''' MessageBox.Show("Invalid value in Date/Numeric Column(s).", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
''''''''''''''''''' Else
''''''''''''''''''' MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
''''''''''''''''''' End If
''''''''''''''''''' ProgressBar1.Value = 0
''''''''''''''''''' ProgressBar1.Visible = False
''''''''''''''''''' MyCommand.Dispose()
''''''''''''''''''' MyConnection.Close()
''''''''''''''''''' Finally
''''''''''''''''''' End Try
'''''''''''''''''''End Function
'''''Commented by Satheesh .T.S on 21March2007 for implimenting E Treasury Related Changes
End Class
-- modified at 13:06 Thursday 1st November, 2007
|
|
|
|
|
i'd tried this code, but i got error such as :
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Lat_Excel.exe
Additional information: Old format or invalid type library.
this error occured when my program try to execute line :
Dim xlsApp As Excel.ApplicationClass
Dim xlsWB As Excel.WorkbookClass
Dim xlsSheet As Excel.WorksheetClass
xlsApp = New Excel.ApplicationClass
xlsApp.Visible = True
xlsWB = xlsApp.Workbooks.Add --> error occured...
what wrong ???
|
|
|
|
|
Which Microsoft Excel Object Library you are refering to...
I have write a sample code using Microsoft Excel 10.0 Object Library. When you add reference to this library it will automatically add support library to your project. Following code is working perfectly in my case
Try
Dim app As New Excel.Application()
Dim doc As Excel.Workbook = app.Workbooks.Add()
Dim sheet As Excel.Worksheet = doc.Worksheets.Add()
sheet.Range("A1").Value = "Month"
sheet.Range("B1").Value = "Number of Days"
app.Visible = True
Catch ex As Exception
MessageBox.Show(ex.StackTrace)
End Try
Please try this. I hope this will resolve your query
Raman Tayal
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
hi,
i'm experiencing same error,
Old Format or invalid type library.
i'm using excel11 library.
the code :
Dim oXL As New Excel.Application
Dim oWB As Excel.Workbook = oXL.Workbooks.Add() --> error occured
Dim oSheet As Excel.Worksheet = oWB.Worksheets.Add()
oSheet.Range("A1").Value = "Month"
oSheet.Range("B1").Value = "Number of Days"
oXL.Visible = True
please advice. thx
|
|
|
|
|
Can I use your code with a password protected excel file? How do I connect to it?
Ken
|
|
|
|
|
|
Hi,
Could you please tell me how to use the Delete and Update commands in the excelsheet.
Amudhan.R
|
|
|
|
|
Hi
iam inserting data into an excel using vb.net2003(windows application)
we are inserting an formula
st = "Insert into [Sheet1$] values ('=(A1+B1)')"
it is succesfully inserting .. whats the problem is at the time of inserting in the excel sheet its inserting like
'=(A1+B1)
its going to append an single quote before the actual expression
its very urgent
Gangireddy
|
|
|
|
|