使用* .xls文件中每个单元格中的数据更新* .dbf文件 [英] update *.dbf file using data in each cell in *.xls file

问题描述

我编写了一个程序，我需要使用特定的id从* xls文件中的数据更新* dbf文件。现在我有2个问题，我需要帮助。 1，错误显示MsgBox（无法打开连接！）我放置后：

i write a program that i need to update in the *dbf file from data in the *xls file using specific id. now i have 2 problem that i need a help. 1, the error is display MsgBox("Can not open connection ! ") after i placed this:

Dim dBaseCommand As New System.Data.OleDb.OleDbCommand("UPDATE paytran.DBF SET OT1 = 2  WHERE EMPNO = 102", dBaseConnection)

   Dim dBaseDataReader As System.Data.OleDb.OleDbDataReader = dBaseCommand.ExecuteReader(CommandBehavior.SequentialAccess)

这是我的代码：

here is my code:

Dim strpath As String = File_Name
Dim dbfpath As String = File_dbf

Dim ConnectionString As String

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim range As Excel.Range
Dim rCnt As Integer

Dim Empno As Object

Dim empno1 As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\file\;Extended Properties=dBase IV"
Dim dBaseConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)

Try
    dBaseConnection.Open()

    xlApp = CreateObject("Excel.Application")
    xlWorkBook = xlApp.Workbooks.Open(strpath)
    xlWorkSheet = xlWorkBook.Worksheets("sheet1")

    range = xlWorkSheet.UsedRange
    For rCnt = 1 To range.Rows.Count 'count row

        Empno = CType(range.Cells(rCnt, 1), Excel.Range)
        empno1 = Empno.value

    Next

    Dim dBaseCommand As New System.Data.OleDb.OleDbCommand("UPDATE paytran.DBF SET OT1 = 2  WHERE EMPNO = 102", dBaseConnection)
    Dim dBaseDataReader As System.Data.OleDb.OleDbDataReader = dBaseCommand.ExecuteReader(CommandBehavior.SequentialAccess)

    xlWorkBook.Close()
    xlApp.Quit()

    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkSheet)

    dBaseConnection.Close()

Catch ex As Exception
    MsgBox("Can not open connection ! ")
End Try

Private Sub releaseObject(ByVal obj As Object)
Try
    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
    obj = Nothing
Catch ex As Exception
    obj = Nothing
Finally
    GC.Collect()
End Try
End Sub

2，我认为我的查询更新错误的位置和错误的语法。这就是我想要的

2, i think my query update at the wrong place and wrong syntax. this is what i want

update paytran.dbf set OT1 = [column in xls file H1] where EMPNO = [column in xls file A1]

i hope codeproject成员可以帮助我..

i hope codeproject member can help me..

推荐答案

你需要这样的东西：

You need something like this:

Dim ConnectionString As String = String.Empty, sCommand As String = String.Empty
Dim rCnt As Integer = 0, Empno As Integer = 0, Otvalue As Integer = 0, retVal As Integer = 0
Dim xlApp As Excel.Application = Nothing, xlWorkBook As Excel.Workbook = Nothing, xlWorkSheet As Excel.Worksheet = Nothing, xlRange As Excel.Range = Nothing
'or
'Dim xlApp As Object = Nothing, xlWorkBook As Object = Nothing, xlWorkSheet As Object = Nothing, xlRange As Object = Nothing
Dim dBaseConnection As OleDb.OleDbConnection = Nothing, dBaseCommand As OleDb.OleDbCommand = Nothing

Try
    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & IO.Path.GetDirectoryName(File_dbf) & ";Extended Properties=dBase IV"
    dBaseConnection = New OleDb.OleDbConnection(ConnectionString)
    dBaseConnection.Open()

    xlApp  = New Excel.Application
    'or - if xlApp is declared as Object
    'xlApp = CreateObject("Excel.Application")
    xlWorkBook = xlApp.Workbooks.Open(File_Name)
    xlWorkSheet = xlWorkBook.Worksheets("sheet1")
    xlRange = xlWorkSheet.UsedRange

    For rCnt = 1 To xlRange.Rows.Count 'count row
        Empno = xlRange.Cells(rCnt, 1).Value2
        Otvalue = xlRange.Cells(rCnt, 3).Value2
        '("UPDATE paytran.DBF SET OT1 = 2  WHERE EMPNO = 102", dBaseConnection)
        sCommand = "UPDATE paytran.DBF SET OT1 = " & Otvalue.ToString & "  WHERE EMPNO = " & Empno.ToString
        dBaseCommand = New OleDb.OleDbCommand(sCommand, dBaseConnection)
        'how many records updated?
        retVal = dBaseCommand.ExecuteNonQuery()
    Next

    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
    'if dbconnection is opened, close it
    'check dBaseConnection.State
    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkSheet)
End Try

这篇关于使用* .xls文件中每个单元格中的数据更新* .dbf文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！