• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1488
  • Last Modified:

how do I Select/Update/Insert a FoxPro 2.5 data table (DBF) via ASP.Net (VB) WebService to

I am trying to put together a prototype of a Web Service that connects to a Fox Pro 2.5 Database.  I'm not sure I have the correct
The attched sample Web Service that is connecting to the table and I'm getting some results, but I'm not sure that I am using the best method, and I can seem to get the Insert statement to work.
I need to be able to select, interogate the data and then insert or update the table.
Code attached is a subset from a Web Data Service
Note I've included the "Imports" I've tried a number of different approaches, and I'm sure that I've left Imports that I don't need, but I a new to this and I'm not always sure what to use. and sometimes samples don't show these.
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data.Services
Imports System.Linq
Imports System.ServiceModel.Web
Imports System.Data.Odbc
Imports System.Data.OleDb
Imports System.Data
Imports System.Data.SqlClient

'FoxPro ODBC Connection String
'Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=c:\demo.dbc;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO 
'FoxPro OLEDB Connection String
'    Provider=vfpoledb.1;Data Source=c:\directory\demo.dbc;Collating Sequence=machine 

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://Bepl.EMS3.com/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class PaymentProcessing
      Inherits System.Web.Services.WebService
    Public StatusText As String

    <System.Web.Services.WebMethod()> _
    Public Function LogTransaction(ByVal PayPalID As String, ByVal ItemID As String) As Integer
        ' Based on Item Passed in Parse the information
        ' Optional Validations:
        ' _tranAmt + _calcPayFee = _totPayAmt
        ' Dim connString As System.Configuration.ConnectionStringSettings = rootWebConfig.ConnectionStrings.ConnectionStrings("EMSMstrConnectionString")
        Dim _errorCode As Integer

            Dim _paymntId As String
            Dim _itemID As String
            Dim _tranStat As String
            Dim _own As String
            Dim _Property As String
            Dim _tranDate As Date
            Dim _tranTime As String
            Dim _tranTimeId As String
            Dim _tranFeeCd As String
            Dim _tranPmtCd As String
            Dim _tranAmt As Double
            Dim _calcPayFee As Double
            Dim _totPayAmt As Double
            Dim _actpayFee As Double
            Dim _LogDate As Date
            Dim _LogTime As String
            Dim _PostDate As Date
            Dim _PostTime As String
            Dim _mDataPath As String
            Dim _mMstrPath As String
            Dim _month As String
            Dim _day As String
            Dim _year As String

            _mDataPath = "C:\Fox\EMS\"
            _mMstrPath = "c:\Fox\EMS\000"

            _paymntId = PayPalID
            _itemID = ItemID
            Try ' Do not cause the Logging to fail if the routine errors
                _own = Mid(ItemID, 1, 4)
                _Property = Mid(ItemID, 6, 6)
                _day = Mid(ItemID, 17, 2)
                _month = Mid(ItemID, 15, 2)
                ' For Date conversion add Year prefix to "20" to assure that year is not defaulted to 19??
                _year = "20" + Mid(ItemID, 13, 2)
                _tranDate = System.Convert.ToDateTime(_month + "/" + _day + "/" + _year)
                _tranTime = Mid(ItemID, 20, 8)
                _tranTimeId = Mid(ItemID, 29, 3)
                _tranAmt = Mid(ItemID, 33, 11)
                _calcPayFee = Mid(ItemID, 45, 9)
                _totPayAmt = Mid(ItemID, 55, 11)
                _actpayFee = Mid(ItemID, 1, 3)
                'Select Case _payFeeAmt
                ' Case 0
                ' _tranFeeCd = ""
                ' Case Is > 0
                ' _tranFeeCd = "ETD"
                ' Case Else
                ' _tranFeeCd = "ETC"
                'End Select
                '_tranPmtCd = Mid(ItemID, 1, 3)
                'TODO: Build your sql statement here
                _tranStat = "L"
                ' An error  occurred while parsing / converting Items
                _tranStat = "E"
                ' Nada Proceed with Logging the data
                ' Send a message to raise the Error

                _errorCode = 1002 ' Parsing / Conversion Error  most likly bad data

            End Try
            ' Dim queryString As String = "INSERT INTO mPayTran.dbf (PaymntID, ItemID) Values('" + _paymntId + "', '" + _itemID + "')"
            Dim queryString As String = "Select * FROM mPayTran.dbf"
            '            Dim mstrConnectionString As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=c:\fox\ems\000;Exclusive=No;Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;"
            Dim mstrConnectionString As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=c:\fox\ems\000;Exclusive=No;BACKGROUNDFETCH=NO;"
            Dim command As New OdbcCommand(queryString)
            Dim _numRows As Integer

            'Dim _sqlResults As  Odbc.OdbcDataReader=

            Using connection As New OdbcConnection(mstrConnectionString)
                command.Connection = connection

                ' _numRows = command.ExecuteReader().FieldCount
                Dim _sqlResults As Odbc.OdbcDataReader = command.ExecuteReader()

                '_numRows = command.ExecuteNonQuery()
                ' The connection is automatically closed at
                ' the end of the Using block.
            End Using

            _errorCode = _numRows

            'Dim con As New SqlConnection(getConnectionString)
            'Dim Cmd As New SqlCommand(queryString, con)
            'Dim RDR As SqlDataReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection)

        Catch ex As Exception
            ' Unexpected Error

            _errorCode = 1001 ' General Exception Error

        End Try

        Return _errorCode

    End Function
End Class

Open in new window

1 Solution
Beside the fact the code is unreadable mixture of comments and code both your SQL commands are correct. You should just be aware of apostrophs in _paymntId and _itemID, because they could cause problems in string delimited by apostrophs.

Also .DBF extension is not necessary in your commands. And even when connection is closed automatically when Using block is ended I would close it rather explicitly by appropriate command.

The "Select * FROM mPayTran.dbf" command could be not so efficient when the number of table records exceeds certain level and you should use appropriate WHERE clause accompanied with index to optimize performance.

If you would ask Microsoft they'll say "Of course, your method isn't good because you are not using SQL Server"... And they are right because SQL Server is more reliable than FoxPro namely when the driver is under a big pressure caused by many connected clients.

To query/update data by above commands is good in .NET environment. OLE DB is newer but it does not necessarily mean better.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now