NTEXT, ASP and SQLServer2000 1

[SKYplus]

Hi,

I have a an asp page where large ammounts of text are keyed in and saved to an sql server 2000 database in an ntext field type.

Now according to the SQL Books online although the ntext can store up to 2GB of data it won't insert/update more than 8000 characters if used in a normal INSERT/UPDATE statemnet. From the same SQl server books online references are made to the SQLPutData and AppendChunk functions. However i have no idea on how i could implement these in my ASP page.

Any help is much appreciated!

btw i'm using ADO.

 

[shaddow]

here is an example i've found for VB, it works fine in ASP but u need to declare the Recordset and the connection as in ASP style and the rest of variable just Dim varname...

Public Sub AppendChunkX()

   Dim cn As ADODB.Connection
   Dim rstPubInfo As ADODB.Recordset
   Dim strCn As String
   Dim strPubID As String
   Dim strPRInfo As String
   Dim lngOffset As Long
   Dim lngLogoSize As Long
   Dim varLogo As Variant
   Dim varChunk As Variant

   Const conChunkSize = 100

   ' Open a connection.
   Set cn = New ADODB.Connection
   strCn = "Server=srv;Database=pubs;UID=sa;Pwd=;"

   cn.Provider = "sqloledb"
   cn.Open strCn

   'Open the pub_info_x table.
   Set rstPubInfo = New ADODB.Recordset
   rstPubInfo.CursorType = adOpenDynamic
   rstPubInfo.LockType = adLockOptimistic
   rstPubInfo.Open "pub_info_x", cn, , , adCmdTable

   'Prompt for a logo to copy.
   strMsg = "Available logos are : " & vbCr & vbCr

   Do While Not rstPubInfo.EOF
      strMsg = strMsg & rstPubInfo!pub_id & vbCr & _ 
        Left(rstPubInfo!pr_info,
         InStr(rstPubInfo!pr_info, ",") - 1) & vbCr & vbCr
      rstPubInfo.MoveNext
   Loop

   strMsg = strMsg & "Enter the ID of a logo to copy:"
   strPubID = InputBox(strMsg)

   ' Copy the logo to a variable in chunks.
   rstPubInfo.Filter = "pub_id = '" & strPubID & "'"
   lngLogoSize = rstPubInfo!logo.ActualSize
   Do While lngOffset < lngLogoSize
      varChunk = rstPubInfo!logo.GetChunk(conChunkSize)
      varLogo = varLogo & varChunk
      lngOffset = lngOffset + conChunkSize
   Loop

   ' Get data from the user.
   strPubID = Trim(InputBox(&quot;Enter a new pub ID:&quot;))
   strPRInfo = Trim(InputBox(&quot;Enter descriptive text:&quot;))

   ' Add a new record, copying the logo in chunks.
   rstPubInfo.AddNew
   rstPubInfo!pub_id = strPubID
   rstPubInfo!pr_info = strPRInfo
   lngOffset = 0   ' Reset offset.

   Do While lngOffset < lngLogoSize
      varChunk = LeftB(RightB(varLogo, lngLogoSize - _ 
        lngOffset),conChunkSize)
      rstPubInfo!logo.AppendChunk varChunk
      lngOffset = lngOffset + conChunkSize
   Loop

   rstPubInfo.Update

   ' Show the newly added data.
   MsgBox &quot;New record: &quot; & rstPubInfo!pub_id & vbCr & _ 
     &quot;Description: &quot; & rstPubInfo!pr_info & vbCr & _ 
     &quot;Logo size: &quot; & rstPubInfo!logo.ActualSize

   rstPubInfo.Close
   cn.Close

End Sub

________
George, M

 

[SKYplus]

Thanks George! you've been very helpful!!

 

[yeltom]

Thanks for the posting. I also needed to use the GetChunk method for a similar scenario.