0

I have a very big string to be updated to the memo field of FoxPro Table, I tried

 cmd = db.GetSqlStringCommandWrapper("UPDATE xxx SET memo1 = "adfsd" WHERE condition1 = 'satisfied'");
 db.ExecuteNonQuery(cmd);

This query overwrites the previous values in the memo1.

I Cannot use MODIFY memo in C#.

How do I append a string to a already existing memo field in Foxpro using C# ?

Thiru
  • 3,293
  • 7
  • 35
  • 52
  • You cannot append a string to a memo field only. There is only an opportunity to append a whole row by using _INSERT_ clause. – Oleg Aug 04 '14 at 17:50
  • Oleg, that's not the case. There are any number of ways to append to a memo field. update mytable set mymemo = alltrim(mymemo) + "more stuff" .. or append memo mymemo from myfile.txt ... for example. – Alan B Aug 05 '14 at 15:40

3 Answers3

0

Try making the command say:

UPDATE xxx SET memo1 = memo1 + "adfsd"
Tamar E. Granor
  • 3,817
  • 1
  • 21
  • 29
0

I think the issue is probably with GetSqlStringCommandWrapper which as far as I can see is deprecated.

This shouldn't be a problem to do, for example using the OLEDB provider:

var DBC = @"C:\mydata.dbc";
string ConnectionString = string.Format("Provider=VFPOLEDB.1;Data Source={0};Exclusive=false;Ansi=true;OLE DB Services = 0", DBC);
using (OleDbConnection testConnection = new OleDbConnection(ConnectionString))
{
    OleDbCommand updateCommand = new OleDbCommand(@"update mytable set mymemo=alltrim(mymemo)+ttoc(datetime()) where thisfield='THISVALUE'", testConnection);
    testConnection.Open();
    updateCommand.ExecuteNonQuery();
    Console.WriteLine(@"Finished - press ENTER.");
    Console.ReadLine();
}
Alan B
  • 4,086
  • 24
  • 33
  • I tried this, but doesn't work as expected, means it shows string too long error sometimes. My complete string length to be updated is `132,370,292`. – Thiru Aug 07 '14 at 05:27
  • Hmmm in that case I wonder are you hitting a VFP system limit: While a memo file can be 2GB, the maximum size of a character string is 16,777,184 – Alan B Aug 07 '14 at 07:54
  • Nope, The size of the string is 256 MB(length of char * 2bytes). I'm using C# StringBuilder to create the string and use .ToString method to pass the string via Parameter. Refer this SO Question[http://stackoverflow.com/questions/25010604/stringbuilder-tostring-throws-outofmemory-exception] – Thiru Aug 07 '14 at 09:55
0

You need to parameterize your query.. Assuming your query wrapper gets the sql connection handle to the database. The VFP OleDB Provider uses "?" as a "place-holder" for parameters and must match the order as associated to your query.

I have a more detailed sample to a very similar question here...

Try something like

string whatToSetItTo = "this is a test string that can even include 'quotes'";

cmd = db.GetSqlStringCommandWrapper("UPDATE YourTable SET memo1 = ? WHERE someKeyColumn = ?");
cmd.Parameters.Add( "parmForMemoField", whatToSetItTo);
cmd.Parameters.Add( "parmForKeyColumn", "satisfied" );


db.ExecuteNonQuery(cmd);

Notice the parameters added in same sequence. whatever the string value is (or could even be integer, date, etc respective to your table structure, but your sample only was based on strings) the place-holders are filled in order. The values would update accordingly.

Community
  • 1
  • 1
DRapp
  • 47,638
  • 12
  • 72
  • 142
  • Since he doesn't have any user-supplied info in his example, are parameters necessary? – Alan B Aug 05 '14 at 15:42
  • 1
    @AlanB, I would recommend it as the sample was just that... sample. The person COULD be getting from another source and having a parameterized function to do the update with values passed for such message(s) and/or key(s) is better than hard-coding as his sample has. – DRapp Aug 05 '14 at 18:43
  • Oh absolutely - not using parameters in general is a very bad idea. – Alan B Aug 06 '14 at 08:14
  • You are right @DRapp I'm using the parameterized function to do the update in my actual program. – Thiru Aug 07 '14 at 05:26