KevinInIndy


Our company has an online product ordering program that accesses our VFP 8 accounting package through an ODBC connection on our server. Occasionally, our system error log table will trap the following error:

"ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]This file is incompatible with the current version of Visual FoxPro. Run 30UPDATE.PRG to update the file to the current version.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr)."

All the offending lines are doing are forming a SELECT statement:

OdbcConnection ODBCConn = null;
OdbcCommand ODBCComm =
null;
OdbcDataReader ODBCReader =
null;
GarmentInformation ReturnValue =
new GarmentInformation();
string sSQL = "";

//Upper case the item number.
Item = Item.ToUpper();
try
{
sSQL = "SELECT * FROM icitem01 WHERE item = '" + Item + "'";

//Create a new connection object.
ODBCConn = new OdbcConnection(m_Parent.SBTICITEMConnectionString);
ODBCConn.Open();

//Setup the new command object.
ODBCComm = new OdbcCommand(sSQL, ODBCConn);

ODBCReader = ODBCComm.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

}

I've read articles about previous versions of VFP doing this, but this isn't accessing a particular program, it's just a SELECT against a table. Can anyone help me as to why we're sporadically getting this error Even better; can anyone help us keep this error from happening Thanks!




Re: Accessing VFP 8 through C# and ODBC

AndyKr


This could be happening because the VFP ODBC driver only supports data tables that use the features that were available in Version 6.0 of VFP. If a table uses Auto-Incrementing columns, or data types introduced since Version 7.0 the ODBC driver is not able to read the table.

You really should use the OLEDB driver for VFP in preference to the ODBC driver - it is fully compatible with all versions of VFP and offers additional functionality over the ODBC driver as well (e.g. the ability to call Stored Procedures)







Re: Accessing VFP 8 through C# and ODBC

Alex Feldstein

KevinIn(cold)Indy:

I have not seen that particular error but remember you are trying to access VFP8 data with a very old version of the ODBC Driver (6.0). I would suggest you change your code to use the VFPOleDB provider which will give you better support of your version 8.0 data.

You can find the Provider here.

AlexIn(warm)Miami :)






Re: Accessing VFP 8 through C# and ODBC

KevinInIndy

While I'm not entirely certain why we're using system DSNs for our data connections, we are. This is the current VFP ODBC driver we're using: 6.00.8167.00. Pardon my ignorance, but I'm from a SQL Server background. We just built our connection strings into the program. How would I go about creating an OLEDB connection through C# 2003 to VFP 8



Re: Accessing VFP 8 through C# and ODBC

KevinInIndy

AlexIn(warm)Miami...

As I recall, the shuttle had to be taken to the local shop to have hail damage fixed, right Well, after this morning, we can add my car to that list. I'm afraid I'm not that experienced in using VFP. My previous places of employment all used either Oracle or SQL Server. We just wrote the connection into the code. How would I use OLEDB to connect a C#2003 program to VFP8 data

Thanks for your help!

KevinIn(not-so-cold-but-REALLY-wet)Indy





Re: Accessing VFP 8 through C# and ODBC

AndyKr

oConn.Open "Provider=vfpoledb;" & _ 
      "Data Source=C:\vfp8\Samples\Data\myVFPDB.dbc;" & _ 
      "Mode=ReadWrite|Share Deny None;" & _ 
      "Collating Sequence=MACHINE;" & _ 
      "Password=''" 
You can find all the relevant information here:
http://www.carlprothman.net/Default.aspx tabid=87#OLEDBProviderForVisualFoxPro
or at 
http://connectionstrings.com
 
 





Re: Accessing VFP 8 through C# and ODBC

Alex Feldstein

Kevin,

Andy got to your question ("how do I use OleDB") faster than me. Basically you use it the same way you would connecting to any other DB (like SQL Server). Just provide the connection string example he pointed to. Then access the data using ADO.NET normally.





Re: Accessing VFP 8 through C# and ODBC

CetinBasoz

It's no different than using any other OLEDB providers. You use OleDbConnection, OleDbCommand, OleDbParameter ... You can directly use wizards like dataset wizard with OleDb for VFP selected. Data source would be full path and name of the DBC. In code here is a sample (this is a very simple one but no need to clutter with more advanced ones:)

using System;
using System.Data;
using System.Data.OleDb;
class test
{
static void Main()
{
string strCon = @"Provider=VFPOLEDB;Data Source=c:\temp\aitest.dbc"; // this dbc exists
OleDbConnection cn = new OleDbConnection(strCon) ;
cn.Open();
OleDbCommand cmdCreateTable = new OleDbCommand("create table myAITest (f1 i autoinc nextvalue 1 step 1 primary key, f2 c(20), f3 d)",cn);
cmdCreateTable.ExecuteNonQuery();


string strInsert = "insert into myAITest (f2,f3) values ( , )";

// Prepare command and parameters
OleDbCommand cmd = new OleDbCommand(strInsert,cn);
OleDbParameter f2 = cmd.Parameters.Add("parm2",OleDbType.Char);
OleDbParameter f3 = cmd.Parameters.Add("parm3",OleDbType.Date);

// insert 5 records
for (int i=0;i<5;i++)
{
f2.Value = String.Format("TestRecord #{0}",i);
f3.Value = DateTime.Now.AddDays(i);
cmd.ExecuteNonQuery();
}
cn.Close();

// Check what is inserted after disconnecting and reconnecting
cn.Open();

OleDbCommand cmdCheck = new OleDbCommand("select * from myAITest",cn);
OleDbDataReader rdr = cmdCheck.ExecuteReader();

while (rdr.Read())
{
Console.WriteLine("Autoinc PK:{0}, F2:{1}, F3:{2}",
rdr["f1"],
rdr["f2"],
rdr["f3"]);
}
rdr.Close();
cn.Close();
}
}





Re: Accessing VFP 8 through C# and ODBC

KevinInIndy

Thank you very much, everyone. This was extremely helpful!



Re: Accessing VFP 8 through C# and ODBC

danadanny

Has anyone tried connecting to a .dbc file which resides on a server, instead of local (C:\)

I point the connection to the server which contains the .dbc file but i keep getting {Invalid path or file name} exception.

this.oleVFPconnection.ConnectionString = "Provider=vfpoledb.1;Data Source=\\\\passrv\\SAMPLE DATA\\test\\tax.dbc;Collating Sequence=machine;";

Any ideas

Thanks





Re: Accessing VFP 8 through C# and ODBC

CetinBasoz

It's not important if it's c: d: or a UNC path. You can access to all. However you should have an accessible path. That is you should have browse,read rights at the very least for the path given. If it's saying "invalid path or filename" it means you don't have access to such a path even if it did exist physically. You can check that situation by putting a txt file there and trying a simple read/write using a Stream/TextReader/Writer.

It's seen more often with ASP.Net applications where the ASPNET/Network service account is not trusted by the other PC on which data resides. IOW a security issue.





Re: Accessing VFP 8 through C# and ODBC

danadanny

Thanks for your reply.

How can I go about giving access rights to the ASPNET service account on the server





Re: Accessing VFP 8 through C# and ODBC

Craig Berntson

That's an Admin issue you should ask in an ASP.Net forum.