Hi Bruce,

Thanks For Reply.

U were right, Needed to pass string , but also need to pass size of
Data( instead of 16, passed actual length of data). So that worked for
me & didn't get any error.

But now problem in fetching, not able to fetch data from table
correctly. Here is my code. I hav following problems with this coding
I hav applied through my logic.

[1] able to fetch more than 8000 chars, but it gives more data than my
actual data, i dont know how some extra char hav been appended to my
actual data which fetching.

[2] and for fetching data with less that 8000 chars, it give me error,
& i know that its because of  "SizeParam.Value = 4000" statement.  And
also getting LengthOutParam.Value = 0 (ZERO) .

So is it like that DataLength will work for data with more that 8000
chars.?

And my data may be of less that 8000 & may be of more than 8000
chars.

ple reply asap

Thanks

   Public Function StoreBLOBIntoFile()
       Dim szBlobColumnName As String = "TestText"
       Dim szTableName As String = "TempTable"
       Dim szConstraint As String = " WHERE  STUDNo = 3"

       Dim msg As String = "Blob data not stored successfully in
File !"
       Dim sqlQuery As String
       Try
           Dim szCon As String
szCon = "Data Source=localhost;uid=t;pwd=t;Initial Catalog=myDb"

           sqlQuery = "Select @Pointer=TEXTPTR(" & szBlobColumnName &
"), @Length=DataLength(" & szBlobColumnName & ") from " & szTableName
& " " & szConstraint
           Dim imageCol As Integer = 0 ' position of image column in
DataReader
           Dim cn As New SqlConnection(szCon)
           '
           ' Make sure that Photo is non-NULL and return TEXTPTR to
it.
           '
           Dim cmdGetPointer As New SqlCommand(sqlQuery, cn)
           Dim PointerOutParam As SqlParameter =
cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100)
           PointerOutParam.Direction = ParameterDirection.Output
           Dim LengthOutParam As SqlParameter =
cmdGetPointer.Parameters.Add("@Length", SqlDbType.BigInt)
           LengthOutParam.Direction = ParameterDirection.Output
           cn.Open()
           cmdGetPointer.ExecuteNonQuery()
           If PointerOutParam.Value Is DBNull.Value Then
               cn.Close()
               Exit Try
           End If
           '
           ' Set up READTEXT command, parameters, and open
BinaryReader.
           '
           Dim cmdReadBinary As New SqlCommand("READTEXT " &
szTableName & "." & szBlobColumnName & " @Pointer @Offset @Size
HOLDLOCK", cn)
           Dim PointerParam As SqlParameter =
cmdReadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
           Dim OffsetParam As SqlParameter =
cmdReadBinary.Parameters.Add("@Offset", SqlDbType.Int)
           Dim SizeParam As SqlParameter =
cmdReadBinary.Parameters.Add("@Size", SqlDbType.Int)
           Dim dr As SqlDataReader

           'Dim fs As New System.IO.FileStream(DestFilePath,
IO.FileMode.OpenOrCreate, IO.FileAccess.Write)

           Dim Offset As Integer = 0
           OffsetParam.Value = Offset
           Dim Buffer(LengthOutParam.Value - 1) As Byte
           '
           ' Read buffer full of data and write to the file stream.
           '
           PointerParam.Value = PointerOutParam.Value
           Do
               ' Calculate buffer size - may be less than
BUFFER_LENGTH for the last block.
               '
               If (Offset + SizeParam.Value) >= LengthOutParam.Value
Then
                   SizeParam.Value = LengthOutParam.Value - Offset
               Else
                   SizeParam.Value = 4000
               End If
               dr =
cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult)
               dr.Read()
               dr.GetBytes(imageCol, 0, Buffer, 0, SizeParam.Value)
               dr.Close()

               txtDesc.Text = txtDesc.Text &
ConvertByteArrayToString(Buffer)

               Offset += SizeParam.Value
               OffsetParam.Value = Offset
           Loop Until Offset >= LengthOutParam.Value

           cn.Close()
           msg = "Blob data stored successfully in File !"
       Catch ex As Exception
           msg = ex.Message
       End Try
       StoreBLOBIntoFile = msg
   End Function