Problem while Fetching Text type of DATA in ASP.NET

Discussion in 'ASP .Net' started by Bhavesh, Jul 17, 2007.

  1. Bhavesh

    Bhavesh Guest

    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
    Bhavesh, Jul 17, 2007
    #1
    1. Advertising

  2. Bhavesh

    Bhavesh Guest

    Hello Everyone,

    I solved my problem. Here is code to fetch Content of type Text
    Datatype from Sqlserver 2000 using asp.net 2.0

    Cheers

    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 = "Place UR Connectionstring "

    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() 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 LengthOutParam.Value > 8000 Then
    If (Offset + SizeParam.Value) >=
    LengthOutParam.Value Then
    SizeParam.Value = LengthOutParam.Value -
    Offset
    Else
    SizeParam.Value = 8000
    End If
    Else
    SizeParam.Value = LengthOutParam.Value
    End If

    ReDim Buffer(SizeParam.Value)

    dr =
    cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult)
    dr.Read()
    dr.GetBytes(imageCol, 0, Buffer, 0, SizeParam.Value)
    dr.Close()

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

    If LengthOutParam.Value <= 8000 Then
    Exit Do
    End If

    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
    Bhavesh, Jul 17, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Smugsboy
    Replies:
    6
    Views:
    4,516
    Hywel Jenkins
    Nov 30, 2005
  2. Bhavesh
    Replies:
    0
    Views:
    422
    Bhavesh
    Jul 16, 2007
  3. Bhavesh
    Replies:
    5
    Views:
    626
    Bhavesh
    Jul 18, 2007
  4. Bhavesh
    Replies:
    1
    Views:
    450
    Bhavesh
    Jul 17, 2007
  5. Prad
    Replies:
    0
    Views:
    218
Loading...

Share This Page