如果错误更新dbf文件中的每一行数据，则在txt文件中写入错误消息 [英] write error message in txt file if error update each row of data in dbf file

问题描述

在codeproject成员中，我在这里有代码，如果每行数据都没有更新，我想在文本文件中写错误信息。我可以知道这是我的尝试..关闭块是对还是错？

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?

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

推荐答案

只有在出现导致错误的情况下才会执行try ... catch块一个例外。如果SQL有效但只是没有任何更新，那么它不会抛出异常。为了检查这一点，您可以查看您的retval值。如果值为零，则SQL语句不会更新任何行。



您还应该学会使用SQL参数，这将大大简化您的SQL语句并使其更易于使用，例如您可以编写语句：< br $>


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



然后在你的命令中，你只需使用Parameters.AddWithValue（@ parameterName，value）它就会自动转义字符或使用正确的日期格式。



否则，你真的打开了自己对于SQL注入攻击，在删除整个表的文本框中编写内容或公开用户数据会很简单。

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.

这篇关于如果错误更新dbf文件中的每一行数据，则在txt文件中写入错误消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！