oledb for vfp error : invalid path or file name when run asp.net on webserver

Question

i develop web application asp.net connect to visual fox pro database free table(.dbf) with microsoft oledb for visualfoxpro driver. when i run program with vs2013 while developing connection is working fine. but when my website run on webserver it can't to search or connect to vfp database and i get this error : invalid path or file name

this is my code to connect vfp database.

Try
            'if use visual fox pro Connect to a single DBF-file
            ConnectionString = "Provider=vfpoledb;Data Source=//database/event/event/OPD/OUT.DBF;sourcetype=DBF;exclusive=No;Collating Sequence=machine; User ID=stat1;Password = stat1;"
            dBaseConnection = New OleDb.OleDbConnection(ConnectionString)
            dBaseConnection.Open()

            sCommand = "SELECT lastdate,save_id,count(*) AS Tcoder FROM OUT.DBF WHERE lastdate BETWEEN CTOD('" & TextBox1.Text & "') and CTOD('" & TextBox2.Text & "') AND typeevent LIKE '%" & TextBox3.Text & "%' "
            sCommand &= "AND lasttime >= '" & ddlFromTime.SelectedValue & "' AND lasttime <= '" & ddlToTime.SelectedValue & "' GROUP BY lastdate,save_id ORDER BY Tcoder DESC "
            dBaseCommand = New OleDb.OleDbCommand(sCommand, dBaseConnection)
            '
            Dim dt As DataTable = GetData(dBaseCommand)
            GridView1.DataSource = dt
            GridView1.DataBind()
            '
            dBaseConnection.Close()
        Catch ex As OleDb.OleDbException
            Response.Write("Error: " + ex.Message)
        End Try

how to fix this issue? thank in advance. -/-

score 0 · Answer 1 · answered Jul 23 '15 at 04:52

First, your connection string should only point to the PATH where the tables are located and NOT the actual name of the .dbf file. Then you can query from ANY .dbf that is located in the connected to path.

Second, when building queries, AVOID SQL-Injection as you are wide open now by doing string concatenation. To prevent, VFP uses the "?" as a place-holder for parameters and the parameters need to be added in the same sequence as they appear in the query.

ConnectionString = "Provider=vfpoledb;Data Source=//database/event/event/OPD;sourcetype=DBF;exclusive=No;Collating Sequence=machine; User ID=stat1;Password = stat1;"
dBaseConnection = New OleDb.OleDbConnection(ConnectionString)
dBaseConnection.Open()

' Notice all the "?" place-holders for the parameters.
sCommand = 
  @"SELECT lastdate, save_id, count(*) AS Tcoder    
       FROM OUT.DBF
       WHERE BETWEEN( lastdate, ?, ? )
          AND typeevent LIKE ?
          AND lasttime >= ?
          AND lasttime <= ?
       GROUP BY 
          lastdate,
          save_id 
       ORDER BY 
          Tcoder DESC ";

' get the command instance
dBaseCommand = New OleDb.OleDbCommand(sCommand, dBaseConnection)
' NOW, add the parameters IN ORDER as they appear... starting with the dates

' but you will have to convert the ".Text" values to a date field in VB.net 
' first, then the date-based field will be properly recognized in the VFP call.  Sorry not fluent in VB to convert, but you should be able to get that.
dBaseCommand.Parameters.AddWithValue( "parmFromDate", TextBox1.Text );
dBaseCommand.Parameters.AddWithValue( "parmFromDate", TextBox2.Text );

' now for the "TypeEvent" criteria, no wrapping the text within quotes
' as the query knows it is a string, so don't explicitly add quotes.
dBaseCommand.Parameters.AddWithValue( "parmTypeEvent", "%"+ TextBox3.Text +"%");

' It appears your LASTTIME field are string-based, so no conversion
' just strings like other.
dBaseCommand.Parameters.AddWithValue( "parmTime1", ddlFromTime.SelectedValue );
dBaseCommand.Parameters.AddWithValue( "parmTime2", ddlToTime.SelectedValue );

' Now, finish getting the data and setting the Data source to the result
Dim dt As DataTable = GetData(dBaseCommand)
GridView1.DataSource = dt
GridView1.DataBind()

dBaseConnection.Close()

oledb for vfp error : invalid path or file name when run asp.net on webserver

1 Answers1