Fetching & Inserting Data into a column of TEXT Data type in SQL server 2000 Using ASP.NET - DOTNET

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