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

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

  1. Bhavesh

    Bhavesh Guest

    Hello genious people,

    I m trying to insert a LARGE text from Multiline Textbox into my
    table
    of sqlserver2000. I m using vs-2005.


    Please note that I dont want to store blob data From FILE TO TABLE,
    like storing IMAGE into DB.


    I hav searched lots of articles on that but didn't get success.


    I hav tried following code from somewhere i found.
    But I m getting Error that


    "Failed to convert parameter value from a Byte[] to a String." (This
    Error is displayed by smart tag of VS-2005) And then when I press
    F5 ,
    my IE shows following Error.


    /
    ***************************************************************************­
    *****************

    *************
    Object must implement IConvertible.
    Description: An unhandled exception occurred during the execution of
    the current web request. Please review the stack trace for more
    information about the error and where it originated in the code.


    Exception Details: System.InvalidCastException: Object must implement
    IConvertible.


    Source Error:
    Line 102: Try
    Line 103: myConnection.Open()
    Line 104: myCommand.ExecuteNonQuery() <---- Here Line is
    highlighted by IE
    Line 105: myConnection.Close()
    Line 106: Response.Write("New TestText successfully
    added!")


    ***************************************************************************­
    *****************

    *************/


    Please tell me wht is wrong with my following code. & if any wrong
    then pls.


    Also pls tell me how to fetch all that data once at a time. Or
    required ro use loop like . But dont know syntax. pls help me its
    urgent for me.


    Public Sub StoreTextInDB()
    Dim intTextSize As Int64
    '''''''Dim TextStream As Stream


    '''''' Gets the Size of the Text
    intTextSize = Len(Trim(txtDesc.Text))
    Dim TextContent(intTextSize) As Byte
    '*********************************************************
    ' '''''Reads the Image
    TextContent =
    System.Text.Encoding.Unicode.GetBytes(Trim(txtDesc.Text))
    '' ''TextStream = Request.InputStream
    '' ''Dim intStatus As Integer
    '' ''intStatus = TextStream.Read(TextContent, 0, intTextSize)
    ''
    ''*********************************************************
    '' '' Create Instance of Connection and Command Object
    Dim szCon As String
    szCon = "Data Source=localhost;uid=t;pwd=t;Initial
    Catalog=myDb"
    Dim myConnection As New SqlConnection(szCon)
    Dim myCommand As New
    SqlCommand("myStoredProcedure", myConnection)


    '''' Mark the Command as a SPROC
    myCommand.CommandType = CommandType.StoredProcedure


    Dim prmStudNo As New SqlParameter("@StudNo", SqlDbType.Int,
    4)
    prmStudNo.Value = 1
    myCommand.Parameters.Add(prmStudNo)


    '''''''' Add Parameters to SPROC
    Dim prmTestText As New SqlParameter("@TestText",
    SqlDbType.Text, 16)
    prmTestText.Value = TextContent
    myCommand.Parameters.Add(prmTestText)


    Try
    myConnection.Open()
    myCommand.ExecuteNonQuery()
    myConnection.Close()
    Response.Write("New TestText successfully added!")
    Catch SQLexc As SqlException
    Response.Write("Insert Failed. Error Details are: " &
    SQLexc.ToString())
    End Try
    End Sub
    Bhavesh, Jul 16, 2007
    #1
    1. Advertising

  2. Bhavesh

    Bhavesh Guest

    Bhavesh, Jul 16, 2007
    #2
    1. Advertising

  3. Bhavesh

    bruce barker Guest

    Re: Fetching & Inserting Data into a column of TEXT Data type inSQL server 2000 Using ASP.NET

    TextContent is a byte array but you are trying to use it as a string.
    declare it as a string, and remove the convert code.


    -- bruce (sqlwork.com)

    Bhavesh wrote:
    > Hello genious people,
    >
    > I m trying to insert a LARGE text from Multiline Textbox into my
    > table
    > of sqlserver2000. I m using vs-2005.
    >
    >
    > Please note that I dont want to store blob data From FILE TO TABLE,
    > like storing IMAGE into DB.
    >
    >
    > I hav searched lots of articles on that but didn't get success.
    >
    >
    > I hav tried following code from somewhere i found.
    > But I m getting Error that
    >
    >
    > "Failed to convert parameter value from a Byte[] to a String." (This
    > Error is displayed by smart tag of VS-2005) And then when I press
    > F5 ,
    > my IE shows following Error.
    >
    >
    > /
    > ***************************************************************************­
    > *****************
    >
    > *************
    > Object must implement IConvertible.
    > Description: An unhandled exception occurred during the execution of
    > the current web request. Please review the stack trace for more
    > information about the error and where it originated in the code.
    >
    >
    > Exception Details: System.InvalidCastException: Object must implement
    > IConvertible.
    >
    >
    > Source Error:
    > Line 102: Try
    > Line 103: myConnection.Open()
    > Line 104: myCommand.ExecuteNonQuery() <---- Here Line is
    > highlighted by IE
    > Line 105: myConnection.Close()
    > Line 106: Response.Write("New TestText successfully
    > added!")
    >
    >
    > ***************************************************************************­
    > *****************
    >
    > *************/
    >
    >
    > Please tell me wht is wrong with my following code. & if any wrong
    > then pls.
    >
    >
    > Also pls tell me how to fetch all that data once at a time. Or
    > required ro use loop like . But dont know syntax. pls help me its
    > urgent for me.
    >
    >
    > Public Sub StoreTextInDB()
    > Dim intTextSize As Int64
    > '''''''Dim TextStream As Stream
    >
    >
    > '''''' Gets the Size of the Text
    > intTextSize = Len(Trim(txtDesc.Text))
    > Dim TextContent(intTextSize) As Byte
    > '*********************************************************
    > ' '''''Reads the Image
    > TextContent =
    > System.Text.Encoding.Unicode.GetBytes(Trim(txtDesc.Text))
    > '' ''TextStream = Request.InputStream
    > '' ''Dim intStatus As Integer
    > '' ''intStatus = TextStream.Read(TextContent, 0, intTextSize)
    > ''
    > ''*********************************************************
    > '' '' Create Instance of Connection and Command Object
    > Dim szCon As String
    > szCon = "Data Source=localhost;uid=t;pwd=t;Initial
    > Catalog=myDb"
    > Dim myConnection As New SqlConnection(szCon)
    > Dim myCommand As New
    > SqlCommand("myStoredProcedure", myConnection)
    >
    >
    > '''' Mark the Command as a SPROC
    > myCommand.CommandType = CommandType.StoredProcedure
    >
    >
    > Dim prmStudNo As New SqlParameter("@StudNo", SqlDbType.Int,
    > 4)
    > prmStudNo.Value = 1
    > myCommand.Parameters.Add(prmStudNo)
    >
    >
    > '''''''' Add Parameters to SPROC
    > Dim prmTestText As New SqlParameter("@TestText",
    > SqlDbType.Text, 16)
    > prmTestText.Value = TextContent
    > myCommand.Parameters.Add(prmTestText)
    >
    >
    > Try
    > myConnection.Open()
    > myCommand.ExecuteNonQuery()
    > myConnection.Close()
    > Response.Write("New TestText successfully added!")
    > Catch SQLexc As SqlException
    > Response.Write("Insert Failed. Error Details are: " &
    > SQLexc.ToString())
    > End Try
    > End Sub
    >
    bruce barker, Jul 16, 2007
    #3
  4. Bhavesh

    Bhavesh Guest

    Hi Bruce,

    Thanks for Reply.

    You are right, I had already achieved that functionality after posting
    my que. But didnt get success in Fetching.
    So if anybody can give sample code for fetching Text datatype with
    more than 8000 chars & then showing it to textbox, then it would be
    great for me.

    Thanks
    Bhavesh, Jul 17, 2007
    #4
  5. 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
    #5
  6. Bhavesh

    Bhavesh Guest

    hi all frienrds,

    I found solution for fetching Text column of any length (with its max
    limit 2^31 -1).

    Here is code . If this one will be useful to anyone then it would be
    great for me.

    Cheers

    Public Function FetchBLOBDataFromDB(ByVal szBlobColumnName As String,
    ByVal szTableName As String, ByVal szConstraint As String) As String
    Dim TextCol As Integer = 0 ' the column # of the BLOB field
    Dim cn As New SqlConnection()
    cn = Dal.getConnection
    Dim cmd As New SqlCommand("SELECT " & szBlobColumnName & "
    FROM " & szTableName & " " & szConstraint, cn)
    Dim dr As SqlDataReader = cmd.ExecuteReader()
    dr.Read()
    Dim Buffer(dr.GetBytes(TextCol, 0, Nothing, 0,
    Integer.MaxValue) - 1) As Byte
    dr.GetBytes(TextCol, 0, Buffer, 0, Buffer.Length)
    dr.Close()
    cn.Close()
    Return ConvertByteArrayToString(Buffer)
    End Function

    Public Function ConvertByteArrayToString(ByVal byteArray As Byte()) As
    String
    Dim enc As Encoding = Encoding.Default
    Dim strText As String = enc.GetString(byteArray)
    Return strText
    End Function
    Bhavesh, Jul 18, 2007
    #6
    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. Replies:
    10
    Views:
    2,652
    Alan Silver
    May 25, 2005
  2. =?Utf-8?B?UCBvZiBEaHVtcA==?=

    Problem (Inserting records into sql server 2000 using asp.net)

    =?Utf-8?B?UCBvZiBEaHVtcA==?=, Aug 18, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    630
    =?Utf-8?B?UCBvZiBEaHVtcA==?=
    Aug 18, 2005
  3. Replies:
    0
    Views:
    320
  4. Bhavesh
    Replies:
    0
    Views:
    422
    Bhavesh
    Jul 16, 2007
  5. Bhavesh
    Replies:
    1
    Views:
    450
    Bhavesh
    Jul 17, 2007
Loading...

Share This Page