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

[Bhavesh]

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]

I hav also seen arcticle from Microsoft

http://support.microsoft.com/kb/308042/EN-US/

but not much helpful

[bruce barker]

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

[Bhavesh]

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]

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.Single Result)
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]

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