Visual FoxPro 9 C# OleDbAdapter Insert - Feature is Not Available

Question

How do I insert into a FoxPro table with a primary key whose default value isnewid("tablename") using Microsoft's OLE DB Provider for Visual Foxpro 9.0?

I have two tables, one where the primary key has a default value of newid("tablename"), and the other whose data type is set to Integer (AutoInc).

When I try to run the same insert command on the newid table, I get the following OleDbException:

Feature is not available

When I run insert on the table with Integer (AutoInc) as the primary key, it works.

Here is the code I am trying to execute:

public void InsertData()
{
    using(var connectionHandler = new OleDbConnection("Provider=VFPOLEDB.1;Data Source=\"C:\\path\\to\\db.dbc\";"))
    {
        var insertStatement = @"INSERT INTO mytable (mycol) values (?)";

        var insertCommand = new OleDbCommand(insertStatement, connectionHandler);

        insertCommand.Parameters.Add("mycol", OleDbType.Char).Value="blue";

        connectionHandler.Open();
        insertCommand.ExecuteNonQuery();
        connectionHandler.Close();
    }
}

Is there any reason OLE would not be able to execute an insert because of this newid() default value?

---

Another case that fixes my issue is if I manually specify an id in the insert clause. ex:

var insertStatement = @"INSERT INTO mytable (id, mycol) values (?, ?)";
insertCommand.Parameters.Add("id", OleDbType.Integer).Value = 199;
insertCommand.Parameters.Add("mycol", OleDbType.Char).Value = "blue";

Additionally, I am able to run a select * from tablename on this table.

select snippet:

public DataTable GetData()
{
    var myData = new DataTable();

    using(var connectionHandler = new OleDbConnection("Provider=VFPOLEDB.1;Data Source=\"C:\\path\\to\\db.dbc\";"))
    {       
        var da = new OleDbDataAdapter();
        var mySQL = "select * from mytable";

        var myQuery = new OleDbCommand(mySQL, connectionHandler);
        connectionHandler.Open();

        da.SelectCommand = myQuery;

        da.Fill(myData);

        connectionHandler.Close();
    }

    return myData;
}

newid() SP:

function newId
parameter thisdbf
regional keynm, newkey, cOldSelect, lDone
keynm=padr(upper(thisdbf),50)
cOldSelect=alias()
lDone=.f.
do while not lDone
    select keyvalue from main!idkeys where keyname=keynm into array akey
    if _tally=0
        insert into main!idkeys (keyname) value (keynm)
        loop
    endif
    newkey=akey+1
    update main!idkeys set keyvalue=newkey where keyname=keynm and keyvalue=akey
    if _tally=1
        lDone=.t.
    endif
enddo
if not empty(cOldSelect)
    select &cOldSelect
else
    select 0
endif
return newkey

In a separate stored procedure, I have tried the following to isolate the key word regional:

FUNCTION GetHello

regional hello

RETURN "HELLO WORLD"

Which seems to work (for example, setting the default value of a column to GetHello() then running an insert over OLEDB).

The following causes my stored procedure to fail with "Feature is not available" over OLEDB:

FUNCTION GetHello
LOCAL test
test="select * from customer"
&test
RETURN "hello"

Answer 1

Thanks for sharing the SP code! It shows what was the culprit.

Though my nerves tend to do many corrections on that code I will only modify enough to make it work from both VFP and C#:

function newId
parameter thisdbf
regional keynm, newkey, nOldSelect, lDone
keynm=padr(upper(thisdbf),50)
nOldSelect=select()
lDone=.f.
do while not lDone
    select keyvalue from main!idkeys where keyname=keynm into array akey
    if _tally=0
        insert into main!idkeys (keyname) value (keynm)
        loop
    endif
    newkey=akey+1
    update main!idkeys set keyvalue=newkey where keyname=keynm and keyvalue=akey
    if _tally=1
        lDone=.t.
    endif
enddo
Select (m.nOldSelect)
return newkey

The changed part is only related to that (if !empty(...)) block.