>Language C#
>Database FoxPro 6.0
>
>Problem to insert data in table, specificlly MEMO fields with a value over 254 charaters in length
>
>We have been told by "experts" to us a parameter to insert a value into a memo field
>we can find no example and we are unable even to get simple parameters to work
>
>I have the following c# Code:
>
> string sSqlStmt = "";
> sSqlStmt += " INSERT INTO iwebillsettings ( ";
> sSqlStmt += " iwb_pk ";
> sSqlStmt += " ) VALUES ( ";
> sSqlStmt += " ? ";
> sSqlStmt += " )";
>
> decimal dClientId = 123;
>
> if(SqlConn.State == ConnectionState.Closed)
> {
> SqlConn.Open();
> }
>
> bGoodRecord = false;
>
> try
> {
> SqlCmd = new OleDbCommand(sSqlStmt, SqlConn );
> SqlCmd.Parameters.Add(new OleDbParameter("@pk", OleDbType.Numeric));
> SqlCmd.Parameters["@pk"].Precision = 11;
> SqlCmd.Parameters["@pk"].Scale = 0;
> SqlCmd.Parameters["@pk"].Value = dClientId;
>
> SqlCmd.Prepare();
> int iRowCheck = SqlCmd.ExecuteNonQuery();
> if (iRowCheck > 0)
> {
> bGoodRecord = true;
> }
> else
> {
> bGoodRecord = false;
> }
> }
> catch(Exception ex)
> {
> string sOutName = "";
> sOutName += "Err Source: " + ex.Source + "
\n";
>
> sOutName += "Message: " + ex.Message + "\n";
> bGoodRecord = false;
> }
> finally
> {
> if(SqlConn.State == ConnectionState.Open)
> {
> SqlConn.Close();
> }
> }
>
>I continue to get the following error
>
>{"Object reference not set to an instance of an object." }
>
>Important notes .. the database is fine.. if I run a text insert statement I have no issues
>
>IF we could get at least the ID field to work, it would be a start but what I would really like is a working example of code that inserts a value into a MEMO fields . and the value being more than 255 char in length


Here is a sample inserting a record into employees table. You might need to adjust paths if not same on yours:

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

class Test
{
 static void Main()
 {
     string strCon = @"Provider=VFPOLEDB;
Data source=C:\Program Files\Microsoft Visual Studio\MSDN\2001OCT\1033\SAMPLES\VFP98\data\Testdata.dbc";

     string strSQL = "insert into employee (emp_id,first_name,last_name,notes) values (?,?,?,?)";

     OleDbConnection cn = new OleDbConnection(strCon) ;
     cn.Open();
     // First send "set null off" command to VFP so it wouldn't error for missing fields
     // but instead fill them with empty values if default is not specified
     OleDbCommand cmdInit = new OleDbCommand("set null off",cn);  
     cmdInit.ExecuteNonQuery();
 
     // Prepare command and parameters
     OleDbCommand cmd = new OleDbCommand(strSQL,cn);
     OleDbParameter empID     = cmd.Parameters.Add("empID",OleDbType.Char);
     OleDbParameter firstName = cmd.Parameters.Add("firstName",OleDbType.Char);
     OleDbParameter lastName  = cmd.Parameters.Add("lastName",OleDbType.Char);
     OleDbParameter notes     = cmd.Parameters.Add("notes",OleDbType.Char); 
                                // might be VarChar, LongVarChar etc

     // Important
     // Note that unlike MSSQL, VFP parameters are not named but positional. 
     // Parameter name only helps to developer. It's their position that provides mapping
     // parameter to its related field. IOW:
     // (emp_id,first_name,last_name,notes) values (?,?,?,?)
     // first parameter added (empID) is for emp_id, 2nd (firstName) is for first_Name and so on.


     empID.Value = "C#2VFP";
     firstName.Value = "TestName";
     lastName.Value  = "TestLast";

     // Get an existing text file to put into VFP memo field
     string file2read = @"C:\Program Files\Microsoft Visual Studio\Vfp98\VFP6FAQ.txt";
     string memoContent;
     using (StreamReader sr = new StreamReader(file2read)) 
     {
        memoContent = sr.ReadToEnd();
     }
     notes.Value = memoContent; // Got via another var just for clarity

     // insert the record
     cmd.ExecuteNonQuery();
     cn.Close();
 }
}

Cetin