ownedobarrawn
New Member
I am creating a page where the user uploads a excel sheet (.csv) which is directly displayed on my asp page. Now I want to add the data of this sheet into the database. I have the code (a vb dll) which will help me do it, but i am confused as to how to link this code with my asp page.the code is as below:\[code\]Public Sub update_feedback()Dim rs1 As New ADODB.Recordset, rs2 As New ADODB.RecordsetDim rst As New ADODB.RecordsetDim eno As LongDim fname As StringDim fname1 As StringDim fno1 As IntegerDim fno As IntegerDim mstr As StringDim flds() As StringDim valid_data As BooleanDim errstr As StringDim errcnt As IntegerDim pickupid As LongDim cancel_rec As StringDim bkno As DoubleDim sReceipt_no As Longeno = 1'CommonDialog1.ShowOpen'fname = CommonDialog1.FileNamerst.Open "select * from upload_schedule where UPPER(status)='PENDING'", db.ConnectionString, adOpenDynamic, adLockOptimisticDo While Not rst.EOF'fname = "d:\redefine\" & nvl(rst!file_name)fname = "e:\field_crm\upload_data\feedback\" & nvl(rst!file_name)fno = FreeFileOpen fname For Input As #fnoIf Left(Right(fname, 4), 1) = "." Then fname1 = Mid(fname, 1, Len(fname) - 4) & "_err.csv"Else fname1 = fname & "_err.csv"End Iffno1 = FreeFileOpen fname1 For Output As #fno1errcnt = 0Line Input #fno, mstrPrint #fno1, mstrDo While Not EOF(fno)Line Input #fno, mstrmstr = Replace(mstr, ",", "|")If InStr(mstr, "|") > 0 Thenflds = Split(mstr, "|")valid_data = http://stackoverflow.com/questions/12798401/Trueerrstr =""cancel_rec = ""bkno = 0For i = 0 To UBound(flds) Select Case i Case Is = 2 If IsNull(flds(i)) Or flds(i) = "" Then valid_data = http://stackoverflow.com/questions/12798401/False errstr = errstr & IIf(errstr ="", "", "|") & "LOAN NO" End If Case Is = 5 If IsNull(flds(i)) Or flds(i) = "" Then valid_data = http://stackoverflow.com/questions/12798401/False errstr = errstr & IIf(errstr ="", "", "|") & "AGENCY NAME" End If Case Is = 7 If IsNull(flds(i)) Or flds(i) = "" Then valid_data = http://stackoverflow.com/questions/12798401/False errstr = errstr & IIf(errstr ="", "", "|") & "FOS NAME" End If Case Is = 9 If IsNull(flds(i)) Or flds(i) = "" And flds(7) = "" Then valid_data = http://stackoverflow.com/questions/12798401/False errstr = errstr & IIf(errstr ="", "", "|") & "FEEDBACK DATE" End If Case Is = 11 If IsNull(flds(i)) Or flds(i) = "" And flds(7) = "" Then valid_data = http://stackoverflow.com/questions/12798401/False errstr = errstr & IIf(errstr ="", "", "|") & "FEEDBACK" End If Case Is = 12 If IsNull(flds(i)) Or flds(i) = "" And flds(7) = "" Then valid_data = http://stackoverflow.com/questions/12798401/False errstr = errstr & IIf(errstr ="", "", "|") & "REMARK" End If End Select '-------------- PAYMENTS If Not IsNull(flds(13)) And flds(13) <> "" Then If IsNumeric(flds(13)) Or Abs(Val(flds(13))) > 0 Then Select Case i Case Is = 14 If IsNull(flds(i)) Or flds(i) = "" Then valid_data = http://stackoverflow.com/questions/12798401/False errstr = errstr & IIf(errstr ="", "", "|") & "MOD OF PAYMENT" End If Case Is = 15 If InStr(UCase(flds(14)), "CHQ") > 0 Or InStr(UCase(flds(14)), "CHEQ") > 0 Then If IsNull(flds(i)) Or flds(i) = "" Then valid_data = http://stackoverflow.com/questions/12798401/False errstr = errstr & IIf(errstr ="", "", "|") & "CHQ NO" End If End If Case Is = 16 If InStr(UCase(flds(14)), "CHQ") > 0 Or InStr(UCase(flds(14)), "CHEQ") > 0 Then If IsNull(flds(i)) Or flds(i) = "" Then valid_data = http://stackoverflow.com/questions/12798401/False errstr = errstr & IIf(errstr ="", "", "|") & "CHQ DATE" End If End If Case Is = 19 If IsNull(flds(i)) Or flds(i) = "" Then valid_data = http://stackoverflow.com/questions/12798401/False errstr = errstr & IIf(errstr ="", "", "|") & "RECEIPT NO" End If rs.Open "select book_no from receipt_master where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and " & flds(i) & " > = receipt_no_from and " & flds(i) & " <= receipt_no_to", db.ConnectionString If rs.EOF Then valid_data = http://stackoverflow.com/questions/12798401/False errstr = errstr & IIf(errstr ="", "", "|") & "INCORRECT RECEIPT NO." Else bkno = rs!book_no End If rs.Close Case Is = 20 If IsNull(flds(i)) Or flds(i) = "" Then valid_data = http://stackoverflow.com/questions/12798401/False errstr = errstr & IIf(errstr ="", "", "|") & "RECEIPT DATE" End If End Select End If End If If (UCase(flds(15)) = "CANCEL" Or UCase(flds(15)) = "LOST") And IsNull(flds(19)) Then cancel_rec = "CANCEL" errstr = "" End IfNext i If cancel_rec <> "CANCEL" Then rs.Open "select count(0) from data_field01 where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no = '" & flds(2) & "'", db.ConnectionString If Val(rs.fields(0)) = 0 Then valid_data = http://stackoverflow.com/questions/12798401/False errstr = errstr & IIf(errstr ="", "", "|") & "LOAN NO " & flds(2) & " is invalid" End If rs.Close If errstr <> "" Then errcnt = errcnt + 1 Print #fno1, mstr End If End IfElse valid_data = http://stackoverflow.com/questions/12798401/False errstr ="Missing field seperator |" errcnt = errcnt + 1End IfLoopClose #fnoClose #fno1If errcnt = 0 Then '-------------------- update data fno = FreeFile Open fname For Input As #fno Line Input #fno, mstr Do While Not EOF(fno) Line Input #fno, mstr mstr = Replace(mstr, ",", "|") flds = Split(mstr, "|") rs.Open "select max(entry_no) from feedback_dump where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "'", db.ConnectionString If Not rs.EOF Then eno = nNV(rs.fields(0)) + 1 End If rs.Close rs.Open "select * from feedback_dump where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "'", db.ConnectionString, adOpenDynamic, adLockOptimistic rs.AddNew rs!region_id = mvarregion_id rs!cost_id = mvarcost_id rs!portfolio_id = mvarportfolio_id rs!portfolio_type = mvarportfolio_type rs!entry_no = eno rs!entry_date = Format(Now, "mm/dd/yyyy") rs!account_type = flds(1) rs!Lan_no = flds(2) rs!customer_name = flds(3) rs!pickup_date = flds(4) rs!agency_name = flds(5) rs!agency_location = flds(6) rs!fos_name = flds(7) rs!fos_contact_no = flds(8) rs!disposition_date = flds(9) rs!agency_disposition = flds(10) rs!disposition_code = flds(11) rs!remark = flds(12) rs!collected_amount = Val(flds(13)) rs!mode_of_payment = flds(14) rs!cheque_no = flds(15) rs!cheque_date = flds(16) rs!bank_name = flds(17) rs!branch_name = flds(18) rs!receipt_no = Val(flds(19)) rs!receipt_date = flds(20) rs!deposited_date = flds(21) rs!deposited_bank = flds(22) rs!customer_contact_no = flds(23) rs!region = mvarregion_id rs!product = flds(1) rs!status = "Pending" rs!entry_user_id = mvaruser_id rs!batch_no = batchno rs.Update rs.Close TrailId = 1 rs1.Open "select * from pickup_trail_sequence where region_id = " & mvarregion_id & " and billmonth = convert(Char(6), getdate(), 112) ", db.ConnectionString, adOpenDynamic, adLockOptimistic If Not rs1.EOF Then pickupid = rs1!trail_id + 1 rs1!trail_id = pickupid rs1.Update Else rs1.AddNew rs1!region_id = gRegion_id rs1!billmonth = Format(gDisp_date, "yyyymm") rs1!trail_id = 1 pickupid = 1 rs1!Prefix = Format(gDisp_date, "MMM") rs1.Update End If rs1.Close '---------- fos details If nvl(flds(7)) <> "" Then rs1.Open "select pickup_id from pickup_dtl where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no = '" & flds(2) & "' and convert(char(8),pickup_date,112) = '" & Format(flds(4), "yyyymmdd") & "'", db.ConnectionString If Not rs1.EOF Then pkid = rs1!pickup_id Else pkid = 0 End If rs1.Close rs1.Open "select * from agency_executive where upper(executive_name) = '" & UCase(flds(7)) & "' and region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and agency_id = " & rst!agency_id, db.ConnectionString, adOpenDynamic, adLockOptimistic If rs1.EOF Then If rs.State = 1 Then rs.Close End If rs.Open "select max(executive_id) from agency_executive where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and agency_id = " & rst!agency_id, db.ConnectionString rs1.AddNew rs1!region_id = mvarregion_id rs1!cost_id = mvarcost_id rs1!agency_id = rst!agency_id rs1!executive_id = nNV(rs.fields(0)) + 1 rs1!Executive_name = UCase(flds(7)) rs1!contact_no = nvl(flds(8)) rs1!date_of_joining = Now rs1.Update db.Execute "update data_field01 set executive_id = " & nNV(rs.fields(0)) + 1 & " where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no = '" & flds(2) & "' and pickup_id = '" & pkid & "'" rs.Close Else db.Execute "update data_field01 set executive_id = " & rs1.fields(0) & " where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no = '" & flds(2) & "' and pickup_id = '" & pkid & "'" db.Execute "update agency_executive set contact_no = '" & nvl(flds(8)) & "' where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and agency_id = " & rst!agency_id & " and executive_id = " & rs1!executive_id End If rs1.Close End If '------------- field disposition details If rs.State = 1 Then rs.Close End If rs.Open "select * from dispositions where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no = '" & flds(2) & "' and upper(disposition_code) = '" & UCase(flds(10)) & "' and convert(char(8),disposition_date,112) = '" & Format(flds(9), "yyyymmdd") & "'", db.ConnectionString, adOpenDynamic, adLockOptimistic If rs.EOF Then rs.AddNew rs!region_id = mvarregion_id rs!cost_id = mvarcost_id rs!portfolio_id = mvarportfolio_id rs!portfolio_type = mvarportfolio_type rs!Lan_no = flds(2) rs!trail_id = pickupid rs!disposition_code = flds(10) rs!disposition_date = flds(9) rs!remark = flds(12) rs!userid = mvaragency_id rs!dialed_no = "FIELD" rs.Update End If rs.Close '------------------- receipt details If Not IsNull(flds(13)) And flds(13) <> "" Then If IsNumeric(flds(13)) Or Abs(flds(13)) > 0 Then rs.Open "select * from receipt_detail where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and receipt_no = " & Val(flds(19)), db.ConnectionString, adOpenDynamic, adLockOptimistic If rs.EOF Then rs.AddNew rs!region_id = mvarregion_id rs!cost_id = mvarcost_id rs!portfolio_id = mvarportfolio_id rs!portfolio_type = mvarportfolio_type rs!Lan_no = flds(2) rs!receipt_no = flds(19) rs!issued_date = flds(20) rs!amount = Val(flds(13)) rs!payment_mode = nvl(flds(14)) rs!entry_date = Now rs!instrument_no = flds(15) If Not IsNull(flds(16)) And flds(16) <> "" Then rs!instrument_date = flds(16) End If rs!agency_id = mvaragency_id rs1.Open "select a.book_no,receipt_no_from,receipt_no_to from receipt_master a, receipt_detail b where a.region_id = " & mvarregion_id & " and a.cost_id = " & mvarcost_id & " and a.portfolio_id = " & mvarportfolio_id & " and a.portfolio_type = '" & mvarportfolio_type & "' and " & flds(19) & " >= receipt_no_from and " & flds(19) & " <= receipt_no_to and a.book_no = b.book_no", db.ConnectionString If Not rs1.EOF Then bkno = rs1!book_no sReceipt_no = rs1!receipt_no_from End If rs1.Close rs!book_no = bkno rs.Update rs.Close '------------- missing receipt control check If flds(19) > sReceipt_no Then rs1.Open "select * from receipt_detail where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and receipt_no = " & Val(flds(19) - 1), db.ConnectionString, adOpenDynamic, adLockOptimistic If rs1.EOF Then cancel_rec = "Missing receipt details ..." & flds(19) - 1 & "/" & bkno End If rs1.Close mailto = "" ccto = "" bccto = "" rs1.Open "select * from email_master where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and upper(process_name) = 'FEEDBACK UPDATE'", db.ConnectionString If Not rs1.EOF Then mailto = nvl(rs1!mail_to) ccto = nvl(rs1!cc_to) bccto = nvl(rs1!bcc_to) End If rs1.Close rs1.Open "select agency_name from agencymaster where agency_id = " & rst!agency_id, db.ConnectionString If Not rs1.EOF Then db.Execute "EXEC msdb.dbo.sp_send_dbmail @profile_name='Test Mail', @recipients='" & mailto & "', @copy_recipients = '" & ccto & ";" & bccto & "', @subject='MISSING RECEIPT DETAIL',@body= 'Payment detail missing from agency : " & UCase(rs1!agency_name) & "..." & cancel_rec & "'" End If rs1.Close End If End If '------------- missing receipt control check end If Val(flds(13)) > 0 Then Select Case UCase(flds(14)) Case Is = "CASH" db.Execute " update data_dump01 set mis_status='COLLECTED' where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no='" & flds(2) & "'" Case Is = "CHEQUE" db.Execute " update data_dump01 set mis_status='COLLECTED' where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no='" & flds(2) & "'" Case Is = "CHQ" db.Execute " update data_dump01 set mis_status='COLLECTED' where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no='" & flds(2) & "'" Case Is = "DD" db.Execute " update data_dump01 set mis_status='COLLECTED' where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no='" & flds(2) & "'" Case Is = "CARD" db.Execute " update data_dump01 set mis_status='COLLECTED' where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no='" & flds(2) & "'" Case Is = "CANCEL" Case Is = "LOST/STOLEN" End Select End If End If End IfLoopClose #fnorst!status = "Completed"rst.Update mailto = "" ccto = "" bccto = "" rs1.Open "select * from email_master where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and upper(process_name) = 'FEEDBACK UPDATE'", db.ConnectionString If Not rs1.EOF Then mailto = nvl(rs1!mail_to) ccto = nvl(rs1!cc_to) bccto = nvl(rs1!bcc_to) End If rs1.Close rs1.Open "select agency_name from agencymaster where agency_id = " & rst!agency_id, db.ConnectionString If Not rs1.EOF Then db.Execute "EXEC msdb.dbo.sp_send_dbmail @profile_name='Test Mail', @recipients='" & mailto & "', @copy_recipients = '" & ccto & ";" & bccto & "', @subject='FEEDBACK UPDATE',@body= 'Upload data successful for agency : " & UCase(rs1!agency_name) & " ... File Name: " & rst!file_name & "'" End If rs1.CloseElse '---------------- send error mail mailto = "" ccto = "" bccto = "" rs1.Open "select * from email_master where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and upper(process_name) = 'FEEDBACK UPDATE'", db.ConnectionString If Not rs1.EOF Then mailto = nvl(rs1!mail_to) ccto = nvl(rs1!cc_to) bccto = nvl(rs1!bcc_to) End If rs1.Close rs1.Open "select agency_name from agencymaster where agency_id = " & rst!agency_id, db.ConnectionString If Not rs1.EOF Then db.Execute "EXEC msdb.dbo.sp_send_dbmail @profile_name='Test Mail', @recipients='" & mailto & "', @copy_recipients = '" & ccto & ";" & bccto & "', @subject='FEEDBACK UPDATE',@body= 'Upload data error for agency : " & UCase(rs1!agency_name) & " ... File Name: " & rst!file_name & errstr & "'" End If rs1.CloseEnd Ifrst!status = "Completed"rst.Updaterst.MoveNextLooprst.Close'rs.CloseEnd Sub\[/code\]please guide me