update .dbf file using data in each cell in .xls file

Question

4.00/5 (1 vote)

See more:

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:

VB

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:

VB

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, i think my query update at the wrong place and wrong syntax. this is what i want

VB

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

i hope codeproject member can help me..

Posted 20-Mar-13 5:49am

xana7900

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2013-03-20T09:09:00

Solution 1

You need something like this:

VB

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

Posted 20-Mar-13 9:09am

Maciej Los

Comments

xana7900 20-Mar-13 21:24pm

i think im getting close to solution...
can i know if i need to place FOR..NEXT loop to update every single row data from excel to dbf,where can i place it...?
i hope u can teach me...thanks for your help..

Maciej Los 21-Mar-13 2:55am

xana7900, please, have a look at code. Inside for... loop i create command to update database.

xana7900 20-Mar-13 21:54pm

i get this error 'External table is not in the expected format'..is it related to Dim ConnectionString As String = String.Empty..

Maciej Los 21-Mar-13 2:53am

No, it's not "related to" Dim ConnectionString AS String=String.Empty, because, ConnectionString was initiated in Try ... Catch ... Finally code block.

xana7900 21-Mar-13 2:57am

why have an error 'External table is not in the expected format'...i try to change the provider..but new error was appear....

Maciej Los 21-Mar-13 3:02am

Does your file is password protected?
Create copy of your dbf file. Try to open it (copy!!!!) in MS Excel. What you see?
Which version of dBase? Maybe you got old version of dBase file... See this: http://www.connectionstrings.com [^].

xana7900 21-Mar-13 3:17am

im sorry to ask again....where can i check the version dbase...i open the dbf file using ms excel...only the dialog box ask want to open and text import wizard appear..
about the http://www.connectionstrings.com, i already try change the provider,but new error..' operator/operand mismatch'.

Maciej Los 21-Mar-13 3:29am

Excel... Use File->Open, in "Open file..." dialog - "File type:" combobox choose "dBase files (*.dbf)".
Version of dbf... you should know it, but you can always check it (http://superuser.com/questions/307773/how-can-i-tell-what-version-of-the-dbase-format-a-dbf-file-is [^]). Have you read it: For your foxpro 2.x DBF you do not need to use dBase or Jet driver, check out the Visual FoxPro / FoxPro 2.x section instead and use the VFP driver. Visual FoxPro ODBC and OLEDB drivers work for all Foxpro tables including the oldest foxbase tables and dBaseII, dBaseIII, Clipper files as well.?

xana7900 21-Mar-13 3:19am

dont have password protected....i try so many ways..but in the end,only error......

xana7900 21-Mar-13 3:46am

i think my dbf use dbase IV...
i already try using visual foxpro odbc,but the error is operator/operand mismatch.

Maciej Los 21-Mar-13 4:06am

Do not change driver. Use OLEDB with jet 4.0. In which line error occurs?

xana7900 21-Mar-13 4:09am

in msg box error...
if i use the OLEDB with jet 4.0, this error 'External table is not in the expected format'.
if i change to Provider=vfpoledb, get this error 'operator/operand mismatch.'

Maciej Los 21-Mar-13 4:13am

Add a break point in the first line of procedure/function (F9). When program stops, bebug it (using F8) and tell me, in which line error occurs.
As i wrote. Do not change driver!

xana7900 21-Mar-13 4:25am

Dim ConnectionString As String = String.Empty

Maciej Los 21-Mar-13 4:41am

Is it joke?
Read carefully my instructions and do exactly as i wrote.

xana7900 21-Mar-13 4:47am

u said add a break line in the procedure right,i click f9 at first line..then i press f8, but doesnt show anything..just show the yellow line...is it wrong? =(
i use microsoft visual studio 2012..i think dont have function for f8....

Maciej Los 21-Mar-13 4:58am

OK, when you add a break point, run your program. When it stops in the line where you've put break point, use Debug->Step into (F8). Push F8 until error occurs.

xana7900 21-Mar-13 5:05am

i push step into until this error occur..at right here
Catch ex As OleDb.OleDbException
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error...")

Maciej Los 21-Mar-13 5:20am

I'm interested a line before ;) Remember this line.

xana7900 21-Mar-13 5:23am

what do u mean? i dont get it...i already do what u said before this..u want me to push until the error occurs right?

Maciej Los 21-Mar-13 6:49am

Give me a line in which error occurs, just before program jump into Catch block.
Is it more readible?
Sorry for my bad english ;(

xana7900 21-Mar-13 6:03am

in the output,have this
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

xana7900 21-Mar-13 10:16am

its not your english bad..but its about me not understand where the error occurs.. =(
im so sorry...
a line which error occurs before step into catch block is
sCommand = "UPDATE paytran.dbf SET WORKHR = " & totalhrs.ToString & " WHERE EMPNO = " & Empno.ToString
dBaseCommand = New OleDb.OleDbCommand(sCommand, dBaseConnection)

retVal = dBaseCommand.ExecuteNonQuery()

xana7900 21-Mar-13 10:18am

im sorry if i still cant understand what u try to say...i already try step by step what u teach me....so sorry..

update *.dbf file using data in each cell in *.xls file