If not mastering counter intuitive VFP object manipulation was not hard enough in the past until I finally got a handle of it, now trying to do it from OleDb .NET code is like moving through heavy water--it is 10 times more difficult.
I thought I reached the promised land when I finally made PACK work but my joy was premature. Now INSERT gives me the same trouble.
I really prefer to do REPLACE (as well as other commands) by writing a SP where the commands are located and storing them permanently at the DBs. That would be my preferred choice. t would allow me to reuse some of my old code which contains thousands such commands. I hoped that I would be able to write a simple SP and call some of my old .prg files from it. This is what Cetin has said should work. I now kind of doubt this although I haven't tried it yet.
OK, this is one SP that gives me exceptions. I commented out all commands that do not work starting with the first one:
PROCEDURE
replaceMemo ( numericid, memoed ) SET PATH TO "C:\VFP_Projects\Data\ComeAndGetDatBase\" OPEN DATABASE comeAndGet USE categories IN 0* SET ORDER TO numeric_id
* SEEK m.numericid
* IF FOUND()
* REPLACE memo1 WITH m.memoed FOR numeric_id = m.numericid
* ENDIF
ENDPROC
It is always: "Attempt to read/write protected memory" exception.
I tried to uncomment some of the statement one at a time sequentially and in various combinations and the result was the same.
My next attempt (and my next preferred choice) is Cetin/Naomi's execScript idea. It always gives me "Syntax Error" exception even in C# code at runtime. Again, many various combinations have been tried to no avail.
private void saveMemo( string dataTableName, string numeric_id, string memo )
{
string procedure = @"packTablesInComeAndGet( 'C:\VFP_Projects\Data\ComeAndGetDatBase\', '"+numeric_id+"', '"+memo+"' ) "+
" Procedure packTablesInComeAndGet ( tcPath, numericid, memoq ) "+
" Use ( Addbs(m.tcPath )+'categories') Exclusive "+
" REPLACE memo1 WITH memoq FOR numeric_id = numericid IN categories"+
" Endproc ";
OleDbConnectionStringBuilder scb = new OleDbConnectionStringBuilder ( );
scb.Provider = "VFPOLEDB";
scb.DataSource = "C:\\Temp";
string connString = scb.ToString ( );
using (OleDbConnection conn2 = new OleDbConnection ( connString ))
{
conn2.Open ( );
OleDbCommand cmdm2 = new OleDbCommand ( );
cmdm2.Connection = conn2;
cmdm2.CommandType = CommandType.StoredProcedure;
cmdm2.CommandText = "Execscript";
cmdm2.Parameters.Add ( "p", OleDbType.Char ).Value = procedure;
try
{
cmdm2.ExecuteNonQuery ( ); // INSERT'ed
Console.WriteLine ( "Done Alex." );
}
catch (Exception ex)
{
Console.WriteLine ( "2nd SB (REPLACE): \r\n{0}, \r\n{1}", ex.Message, ex.StackTrace );
}
}
} // saveMemo
I am almost certain there are no Syntax Errors in that code but you never know.
My next variant was:
private void saveMemo2( string dataTableName, string numeric_id, string memo )
{
// the connection string here is different. It connects directly to the DB in question
StringBuilder sb = new StringBuilder ( );
sb.Append ( "REPLACE memo1 WITH '" + memo + "' " );
sb.Append ( " WHILE numeric_id = '" + numeric_id + "' IN 'categories' " );
using (OleDbConnection conn2 = new OleDbConnection ( Globals.connStr ))
{
conn2.Open ( );
OleDbCommand cmdm2 = new OleDbCommand ( );
cmdm2.Connection = conn2;
cmdm2.CommandType = CommandType.Text;
cmdm2.CommandText = sb.ToString ( );
cmdm2.ExecuteNonQuery ( );
} // end of using conn2
} // saveMemo
Here I also got a Syntax Error exception. This code obviously a very poor choice since the delimiter ' (single quote mark) can and eventually will appear as a part of the memo text itself. I just wanted to prove the principle. It did not work either.
This is one of the strings I recovered out of Visual Studio 2005 Error Window:
"REPLACE memo1 WITH '{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\fonttbl{\\f0\\fnil\\fcharset0 Microsoft Sans Serif;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs24 bvcvc\\par\r\n}\r\n' WHILE numeric_id = 'C000149' IN 'categories' "
The above string is OleDbCommand.CommandText content.
I also tried FOR instead of WHILE. Actually the numeric_id field is the primary key and it does not matter. There should be only one record.
I think most of it RTF formatting. I typed in a few characters mindlessly. I think they are the ones highlighted.
This is where I stand. I you want to get rid of me, please help me resolve my few remaining problems and I will be out of here, hopefully for good
If no way around the problem is found then my default option would be to use the following sequence of commands: DELETE the sought for record (it has worked), PACK the Table (works), INSERT a similar record (it has worked as a stand alone operation) with a new MEMO field but all the other field contents saved and reloaded.
That may not work either. I noticed that OleDb does not seem to like when I do DELETE, PACK and INSERT immediately after that. It wants the app to be closed first but I haven't studied it in detail.
Please help.
Dave M. wrote: