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

        Try
            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"
            Catch
                ' 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
                connection.Open()

                ' _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)
            'con.Open()
            '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

SeanWarnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.