>Is it really true that you can not insert data into a Visual FoxPro table's memo field with either length greater than 254 characters or having line breaks from ASP.NET thru ODBC or OLE DB providern? My colleague is trowing away the FoxPro database/tables in favour for Access because of this problem. If there are no line breaks and string is shorter than 254 characters everything works Ok, but if longer or having line breaks the insert fails. He has found several articles about this problem on internet from 2004, but I just told him that this must have been solved since that.
>
>Or can there be any problems with the code he is using?
>
>
>

>
>      Dim BegDB As String
>
>        'Unmodified text to the database
>        BegDB = Begaran.Text  && multi-line textbox from aspx page
>
>       'Create a data connection and insert the data into DB: ceramicsweb.dbc  - Table:  beredn_log
>        '--------------------------------------------------------------------------------------------
>        Dim conCeramicsWeb As OleDbConnection
>        Dim strInsert As String
>        Dim cmdInsert As OleDbCommand
>
>        'Connection to FoxPro
>        conCeramicsWeb = New OleDbConnection("Provider=VFPOLEDB.1;Data Source=D:\Intranet\CeramicsWeb\data\ceramicsweb.dbc")
>
>        'Insert string
>        strInsert = "Insert Into beredn_log" &_
" (case_type, artnr, departm, requests, req_by, comp_name)" &_
" Values ('ablad', '" & ArtikelNr.Text & "', '" & _
AvdelningsID.Text & "', '" & BegDB & "', '" & Namn.Text & "', '" & hostn & "')"
>
>        'Constructor for the connection and insert string
>        cmdInsert = New OleDbCommand(strInsert, conCeramicsWeb)
>
>        'Open database run the query and then close the database again
>        conCeramicsWeb.Open()
>        cmdInsert.ExecuteScalar()  && generates error Command contains unrecognized phrase/keyword
>        conCeramicsWeb.Close()
>
>

Torgny,
Problem actually even did not exist before 2004 but users who do not understand the difference between a literal and string variable faced the problem and unfortunately might have wrote articles claiming that there is such a problem.

He has problem in his code:

strInsert = "Insert Into beredn_log (case_type, artnr, departm, requests, req_by, comp_name) Values ('ablad', '" & ArtikelNr.Text & "', '" & AvdelningsID.Text & "', '" & BegDB & "', '" & Namn.Text & "', '" & hostn & "')"

When he does construct such an insert command he is passing a "literal" string to foxpro which cannot be over 255 in length (that style doesn't even make sense for other backends and have other problems like SQL injection). Solution is very simple though. He'd instead put parameter placeholders and fill the data with OleDbParameter objects. Here is a sample (he can easily convert C# code to VB code):

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

class test
{
  static void Main()
 {
   string myMemo = @"This is a long memo content with 
carrige returns and 
line feeds in it.
It was inserted via ExecuteNonQuery from C#.
-----------------------------------------
This memo content is over 255 in length
This memo content is over 255 in length
This memo content is over 255 in length
This memo content is over 255 in length
This memo content is over 255 in length
This memo content is over 255 in length
This memo content is over 255 in length
This memo content is over 255 in length
This memo content is over 255 in length
This memo content is over 255 in length
This memo content is over 255 in length
This memo content is over 255 in length
-----------------------------------------
	Time inserted:"+DateTime.Now.ToString("MMM dd,yyyy hh:mm:ss.fffffff tt");

   OleDbConnection cn = new OleDbConnection(@"Provider=VFPOLEDB;Data source=c:\Temp\");
   cn.Open();

   FileInfo fi = new FileInfo(@"c:\temp\vfptest.dbf");
   if (!fi.Exists)
   {
      OleDbCommand cmd = cn.CreateCommand();
      cmd.CommandText = "create table vfptest (myID i, myMemo m)";
      cmd.ExecuteNonQuery();
   }

   OleDbCommand cmdUpdate = cn.CreateCommand();
   OleDbCommand cmdInsert = cn.CreateCommand();
   OleDbCommand cmdSelect = cn.CreateCommand();

   cmdInsert.CommandText = "insert into vfptest (myID, myMemo) values (?,?)";
   cmdUpdate.CommandText = "update vfptest set myMemo = ? where myId = ?";
   cmdSelect.CommandText = "select * from vfptest";

   OleDbParameter id1   = new OleDbParameter("id",OleDbType.Integer);
   OleDbParameter id2   = new OleDbParameter("id",OleDbType.Integer);
   OleDbParameter memo1 = new OleDbParameter("memo",OleDbType.Char);
   OleDbParameter memo2 = new OleDbParameter("memo",OleDbType.Char);

   cmdInsert.Parameters.Add(id1);
   cmdInsert.Parameters.Add(memo1);

   cmdUpdate.Parameters.Add(memo2);
   cmdUpdate.Parameters.Add(id2);

   memo2.SourceColumn = "myMemo";
   id2.SourceColumn = "myId";
   id2.SourceVersion = DataRowVersion.Original;

   memo1.SourceColumn = "myMemo";
   id1.SourceColumn = "myId";
   id1.SourceVersion = DataRowVersion.Proposed;
  
   id1.Value = 1;
   memo1.Value = myMemo;
   cmdInsert.ExecuteNonQuery();

   id1.Value = 2;
   memo1.Value = "Another with CR in it:\n" + myMemo;
   cmdInsert.ExecuteNonQuery();
   cn.Close();

  // Add update using datadapter
  OleDbDataAdapter da = new OleDbDataAdapter();
  da.SelectCommand = cmdSelect;
  da.UpdateCommand = cmdUpdate;
  da.InsertCommand = cmdInsert;

   DataSet ds = new DataSet();
   da.Fill(ds);

   DataRow row = ds.Tables[0].Rows[1];
   row["myMemo"] = "Update with a CR in it\nusing dataadapter on"+
     DateTime.Now.ToString("MMM dd,yyyy hh:mm:ss.fffffff tt")+
     "\n"+(string)row["myMemo"];
   DataRow newRow = ds.Tables[0].NewRow();
   newRow["myId"] = 3;
   // insert source file in a new record
   using (StreamReader sr = new StreamReader("vfpupdatememo.cs")) 
   {
     newRow["myMemo"] = sr.ReadToEnd();
   }
   ds.Tables[0].Rows.Add(newRow);
   da.Update(ds);
 }
}

PS: Another sample:
Re: Using OleDbParameters and FOXPRO MEMO Fields Thread #1026872 Message #1026953

Cetin