none
vb.net + connect and read memo field of vfp RRS feed

  • Question

  •  

    I've tried at least 5 different options and am still coming up empty.

     

    I have a foxpro database that I'm attempting to connect to and read the memo field.  I can connect to the table and read the 'normal' rows.  Row(56) = the memo field which is supposed to somehow link back to a fpt file.  The fpt file contains the memo fields that I need.  Below is the code that I'm trying to use.  Memo fields that I'm attempting to pull out are A_TotalChg and A_FuelSurcharge.  The first message box shows me the first 2 columns of row 1.  However the second message box shows me: An unhandled exception of type 'System.ArgumentException' occurred in system.data.dll

    Additional information: Column 'A_TotalChg' does not belong to table Table. 

     

    Dim cn As New OleDbConnection("Provider=VFPOLEDB;Data Source=C:\Apps\Data\Dana\Database\;")

    cn.Open()

    Dim da As New OleDbDataAdapter("Select * From Dana", cn)

    Dim ds As New DataSet

    da.Fill(ds)

    '-- See what we've got

    MsgBox(ds.Tables(0).Rows(0).Item(0).ToString() + ", " + ds.Tables(0).Rows(0).Item(1).ToString())

    Dim dr As DataRow = ds.Tables(0).Rows(0)

    Dim strb As New System.Text.StringBuilder

    strb.Append("A_TotalChg: ").Append(ds.Tables(0).Rows(0).Item("A_TotalChg").ToString()).Append(Microsoft.VisualBasic.Constants.vbCrLf)

    strb.Append("A_FuelSurcharge: ").Append(ds.Tables(0).Rows(0).Item("A_FuelSurcharge").ToString()).Append(Microsoft.VisualBasic.Constants.vbCrLf)

    MsgBox(strb.ToString())

     

     

    How do I go about obtaining the values of this memo field?

     

    I have ensured that the cdx, dbf, and fpt files all have the same name and are all located in the same folder and I've ensured that I have the current downloads that I need in order to connect to vfp databases...

    Tuesday, January 22, 2008 9:24 PM

Answers

  • " I got the point that    = a new row.  I know that I can link the two fp files via Account.  I can get the info out of the dbf file.  The only thing I need now is the A_TotalChg 'column' out of the fpt file..."

     

    It doesn't matter that you don't have VFP samples. What you should understand is the way how to read a memo field which is no different than reading a character field. It's just a column of "DBF".

     

    You got it wrong. From what I see, they store a plain text info to a memo field. Get the name of this memo field from its table (DBF). Query as usual to get the content, parse with anything you like (StreamReader, String.Split, RegularExpression ...). All you need is to find the line that starts with 'A_TotalChg' and parse it (ie: Split('=')).

     

    If you want to: Zip the dbf,cdx,fpt of that file and upload to www.rapidshare.com and give the link. I can download, check and write .Net code for you (at night - after 9PM UTC, 4PM EST) at least in C# (I can hardly write VB.Net).

    Wednesday, January 23, 2008 11:03 AM
  • Sad I replied but lost.

     

    Anyway, an FPT doesn't have any rows/columns. It's only storage for large data and should never be dirctly accessed unless you know what you're doing.

     

    Wednesday, January 23, 2008 4:13 PM

All replies

  • Code Snippet

    Imports System
    Imports System.Data
    Imports System.Data.OleDb

    Module Sample

        Sub Main()
            Dim cn As New OleDbConnection("Provider=VFPOLEDB;" & _
                "Data Source=C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\SAMPLES\data\")
            Dim cmd As New OleDbCommand("select * from employee", cn)
            Dim rdr As OleDbDataReader
            cn.Open()
            rdr = cmd.ExecuteReader
            Do While rdr.Read()
                Console.WriteLine(rdr("Notes"))
            Loop
            rdr.Close()
            cn.Close()
        End Sub

    End Module

     

    Notes is a memo field in employee table.

     

    You're saying A_TotalChg is not a column in that table but than trying to get it from datarow.

    Tuesday, January 22, 2008 10:23 PM
  • I receive the error:

    Run-time exception thrown : System.IndexOutOfRangeException - A_TotalChg

     

    I do not have ms visual foxpro installed on my machine, therefore no samples.  So I changed the data source to my data source and the table to my table.  I changed Notes with A_TotalChg.

     

     

     

     

     

    The contents of the fpt file is really what I need to link too.  When I open the fpt file in notepad I see what I pasted below.  I got the point that    = a new row.  I know that I can link the two fp files via Account.  I can get the info out of the dbf file.  The only thing I need now is the A_TotalChg 'column' out of the fpt file...

     

     

    When I open the fpt file I see:

       c   @                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ŽHndChgSrc=d
    TrkNoSrc=P
    Account=IL ###-###

    Period=1
    Company=test
    Contact=test
    Address1=test
    City=City
    State=St
    CarrLabel=UPSMBA
    CODInstr=
    WgtSrc=k
    HazMat=False
    Resident=False
    PPPRatedWt=1
    PPPShipCh=4.50
    PPPBaseRt=4.39
    PPPPckupNo=123456789
    PPPPckUpTp=PLD
    PPPGrpType=
    PPPGroupID=0
    BillBsRt=4.39
    BIBaseRate=4.39
    BITotalFee=0.11
    BITotalChg=4.50
    CODShFlag=False
    LTLShpChg=0.00
    LTLAccChg=0.00
    LTLUsr2=1234
    LTLUsr6=                                                           
    LTLUsr7=                                                           
    Contents=False
    TotalFees=0.11
    TotalChg=4.50
    FuelSurcharge=0.11
    S_ShipCharge=4.50
    S_SurCharge=0.00
    S_BaseRate=4.39
    S_TotalFees=0.11
    S_TotalChg=4.50
    S_FuelSurcharge=0.11
    A_ShipCharge=4.50
    A_SurCharge=0.00
    A_BaseRate=4.39
    A_TotalFees=0.11
    A_TotalChg=4.50
    A_FuelSurcharge=0.11
    S_CODFee=0.00
    S_DVFee=0.00
    S_PIPFee=0.00
    S_PODFee=0.00
    A_CODFee=0.00
    A_DVFee=0.00
    A_PIPFee=0.00
    A_PODFee=0.00
    BatchMailPieceCount=0
    BatchMailPiecePostage=0.00
    DimWeight=1
    OrgContact=Blah Blah

    OrgPhone=123 123-1233
    IataCode=US
    MLOpt20=3216549846213215465
    RoutingCode= St 123546 06541

                                                   lHndChgSrc=d

    Tuesday, January 22, 2008 10:39 PM
  • " I got the point that    = a new row.  I know that I can link the two fp files via Account.  I can get the info out of the dbf file.  The only thing I need now is the A_TotalChg 'column' out of the fpt file..."

     

    It doesn't matter that you don't have VFP samples. What you should understand is the way how to read a memo field which is no different than reading a character field. It's just a column of "DBF".

     

    You got it wrong. From what I see, they store a plain text info to a memo field. Get the name of this memo field from its table (DBF). Query as usual to get the content, parse with anything you like (StreamReader, String.Split, RegularExpression ...). All you need is to find the line that starts with 'A_TotalChg' and parse it (ie: Split('=')).

     

    If you want to: Zip the dbf,cdx,fpt of that file and upload to www.rapidshare.com and give the link. I can download, check and write .Net code for you (at night - after 9PM UTC, 4PM EST) at least in C# (I can hardly write VB.Net).

    Wednesday, January 23, 2008 11:03 AM
  • But when I query this Memo column, it always returns a null value.

     

    Should the fpt file contain as many 'rows' as the dbf file?  For the examples of fpt/dbf files that I'm looking at there are only a few number of rows (3-6) contained within the fpt file but hundreds in the dbf file......

     

    Wednesday, January 23, 2008 1:06 PM
  • "All you need is to find the line that starts with 'A_TotalChg' and parse it (ie: Split('='))."

     

    That would require me reading an ID of some sort in the fpt file and checking each 'row' in the fpt file against the current datarow in the dbf file to ensure that they match--as they may or may not be in the same order.  That just doesn't sound very good to me...

    Wednesday, January 23, 2008 4:08 PM
  • Sad I replied but lost.

     

    Anyway, an FPT doesn't have any rows/columns. It's only storage for large data and should never be dirctly accessed unless you know what you're doing.

     

    Wednesday, January 23, 2008 4:13 PM
  •  

    I have an identical problem, in memo field I have images, but I don’t know how can I retrieve them and save into physical file.

     

    Thanks

    Thursday, January 29, 2009 5:49 PM
  • Read it as a byte[].

     

    Thursday, January 29, 2009 10:37 PM