How To Use AppendChunk and GetChunk with NText Columns in a SQL Server CE Database (280514)

MORE INFORMATION

1. Create a new form in a Microsoft eMbedded Visual Basic (eVB) application, and then add a command button to the form.
2. Reference the following libraries in your project:
   • Microsoft CE SQL Server Control 1.0
     
     
   • Microsoft CE ADOCE Control 3.1
     
     
   • Microsoft CE ADO Extensions 3.1 for DDL
   
   
   
3. Type or paste the following code into the Click event of the command button:

   Option Explicit
   
   Private Sub cmd01_Click()
   
       On Error Resume Next
       
       Dim cn As ADOCE.Connection
       Dim strConn As String
       Dim rs As ADOCE.Recordset
       Dim Cat As ADOXCE.Catalog
           
       Dim Notesfld, vData, vDisplay
       Dim j
       
       Const adCmdTableDirect = 512
       Const adOpenKeyset = 1
       Const adLockOptimistic = 3
       
       Set cn = CreateObject("adoce.connection.3.1")
       Set rs = CreateObject("adoce.recordset.3.1")
       
       'Create a sample database
       Set Cat = CreateObject("ADOXCE.Catalog.3.1")
       Cat.Create "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0;" & _
           "Data source=\AppendChunk.sdf"
   
       Set Cat = Nothing
       
       strConn = "Provider=microsoft.sqlserver.oledb.ce.1.0;" & _
                     "Data Source=\AppendChunk.sdf;"
       
       cn.Open strConn
           
       cn.Execute "Create Table Employees (EmpName NVarchar(30), Notes ntext)"
       
       rs.Open "Employees", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
       
       ' Assign the 'Notes' field to a variable
       Set Notesfld = rs("Notes")
       
       rs.AddNew
       rs.Fields("EmpName") = "Bill"
       
       ' Example data to insert into the 'Notes' field
       vData = "This is an example to show AppendChunk / GetChunk usage. "
       
       ' Append the data in vData to the 'Notes' field
       ' The first AppendChunk call on a Field object writes data to the field, overwriting any existing data.
       ' Subsequent AppendChunk calls add to existing data
       For j = 1 To 10
           Notesfld.AppendChunk vData
       Next
       
       rs.Update
       
       ' Get the first 100 characters from the 'Notes' field
       vDisplay = Notesfld.GetChunk(100)
       MsgBox vDisplay
               
       rs.Close
       cn.Close
       Set rs = Nothing
       Set cn = Nothing
   
   End Sub