Click here to Skip to main content
15,885,278 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi codeproject member, i have code here that i want to write error message in text file if each row of data is not update. can i know is it my try..catch block is right or wrong?
VB
If triallicense = "Demo" Then
            
            File_dbf = "C:\ddd" & Companydbf & "\paytran.dbf"
           
            Dim ConnectionString As String = String.Empty, sCommand As String = String.Empty
            Dim rCnt As Integer = 0, Empno As String, Otvalue As Integer = 0, retVal As Integer = 0, totalhrs As Integer = 0
            Dim xlApp As Excel.Application = Nothing, xlWorkBook As Excel.Workbook = Nothing, xlWorkSheet As Excel.Worksheet = Nothing, xlRange As Excel.Range = Nothing
            Dim latehr As Double, earlyhr As Double, norOT As Double, holOT As Double, otherOT As Double
            Dim attend As Double, absent As Double, mc As Double
            
            Dim dBaseConnection As OleDb.OleDbConnection = Nothing, dBaseCommand As OleDb.OleDbCommand = Nothing
            Try
                
                ConnectionString = "Provider=vfpoledb;Data Source=" & IO.Path.GetDirectoryName(File_dbf) & ";Collating Sequence=machine;"
                dBaseConnection = New OleDb.OleDbConnection(ConnectionString)
                dBaseConnection.Open()

                xlApp = New Excel.Application
                
                xlWorkBook = xlApp.Workbooks.Open(xlsName)
                xlWorkSheet = xlWorkBook.Worksheets("sheet1")
                xlRange = xlWorkSheet.UsedRange



                For rCnt = 3 To 4


                    Empno = xlRange.Cells(rCnt, 1).Value
                    totalhrs = xlRange.Cells(rCnt, 3).Value 'workhr
                    latehr = xlRange.Cells(rCnt, 4).Value 'latehr
                    earlyhr = xlRange.Cells(rCnt, 5).Value 'earlyhr
                    norOT = xlRange.Cells(rCnt, 6).Value 'nor OT
                    holOT = xlRange.Cells(rCnt, 7).Value 'hol ot
                    otherOT = xlRange.Cells(rCnt, 8).Value 'other ot
                    attend = xlRange.Cells(rCnt, 9).Value 'attendace
                    absent = xlRange.Cells(rCnt, 10).Value 'absent
                    mc = xlRange.Cells(rCnt, 11).Value 'leave

                    sCommand = "UPDATE paytran.dbf SET paytran.workhr = " & totalhrs & ", paytran.latehr = " & latehr & ", paytran.earlyhr = " & earlyhr & ", paytran.ot1 = " & norOT & ",  paytran.ot2 = " & holOT & ",  paytran.ot3 = " & otherOT & ", paytran.dw = " & attend & ", paytran.ab = " & absent & ", paytran.mc = " & mc & " , paytran.payyes = 'Y' WHERE paytran.empno == '" & Empno & "'"
                    dBaseCommand = New OleDbCommand(sCommand, dBaseConnection)

                    Try
                        retVal = dBaseCommand.ExecuteNonQuery()

                    Catch ex As Exception
                        'write_txt("Error", ex.Message.ToString()) 'here my catch block that write the file..
                    End Try
                Next rCnt

                'MsgBox("Data Updated!!")
            xlWorkBook.Close()
            xlApp.Quit()
            dBaseConnection.Close()


            Catch ex As System.InvalidCastException
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error...")

            Catch ex As OleDb.OleDbException
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error...")

            Catch ex As System.NullReferenceException
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error...")

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error...")

            Finally
                
                releaseObject(xlApp)
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)
            End Try
End If
Posted
Updated 4-Jul-13 16:45pm
v3

1 solution

Your try...catch block will only get executed if there is an error that causes an exception. If the SQL is valid but simply has nothing to update then it won't throw an exception. In order to check this, you look at your retval value. If the value is zero, then no rows were updated by the SQL statement.

You should also learn to work with SQL parameters, it would greatly simplify your SQL statement and make it easier to work with, for example you could write the statement:

UPDATE myTable set MyColumn = @colValue WHERE MyID = @updateID;

Then in your command, you just use the Parameters.AddWithValue("@parameterName", value) and it automatically escapes characters or uses the right format for dates.

Otherwise, you really open yourself up to SQL injection attacks, it would be simple to write something in a textbox that erases the entire table, or exposes your user data.
 
Share this answer
 
Comments
xana7900 4-Jul-13 23:40pm    
hi ron..i have try the catch...right now its worked to write the error message in text file...but,it not looping of each error message.. how can i do to make the each of error write in text file?
Ron Beyer 5-Jul-13 9:25am    
You can't, you only get one exception per statement, not one per row. The exception is being thrown because something is wrong. The update statement is only run once, not for each row. In order to do that you would have to update each row in a loop one at a time.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900