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.InvalidC astException: Object must implement
IConvertible.
Source Error:
Line 102: Try
Line 103: myConnection.Op en()
Line 104: myCommand.Execu teNonQuery() <---- Here Line is
highlighted by IE
Line 105: myConnection.Cl ose()
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(txtDes c.Text))
Dim TextContent(int TextSize) As Byte
'************** *************** *************** *************
' '''''Reads the Image
TextContent =
System.Text.Enc oding.Unicode.G etBytes(Trim(tx tDesc.Text))
'' ''TextStream = Request.InputSt ream
'' ''Dim intStatus As Integer
'' ''intStatus = TextStream.Read (TextContent, 0, intTextSize)
''
''************* *************** *************** **************
'' '' Create Instance of Connection and Command Object
Dim szCon As String
szCon = "Data Source=localhos t;uid=t;pwd=t;I nitial
Catalog=myDb"
Dim myConnection As New SqlConnection(s zCon)
Dim myCommand As New
SqlCommand("myS toredProcedure" , myConnection)
'''' Mark the Command as a SPROC
myCommand.Comma ndType = CommandType.Sto redProcedure
Dim prmStudNo As New SqlParameter("@ StudNo", SqlDbType.Int,
4)
prmStudNo.Value = 1
myCommand.Param eters.Add(prmSt udNo)
'''''''' Add Parameters to SPROC
Dim prmTestText As New SqlParameter("@ TestText",
SqlDbType.Text, 16)
prmTestText.Val ue = TextContent
myCommand.Param eters.Add(prmTe stText)
Try
myConnection.Op en()
myCommand.Execu teNonQuery()
myConnection.Cl ose()
Response.Write( "New TestText successfully added!")
Catch SQLexc As SqlException
Response.Write( "Insert Failed. Error Details are: " &
SQLexc.ToString ())
End Try
End Sub 5 2995
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.InvalidC astException: Object must implement
IConvertible.
Source Error:
Line 102: Try
Line 103: myConnection.Op en()
Line 104: myCommand.Execu teNonQuery() <---- Here Line is
highlighted by IE
Line 105: myConnection.Cl ose()
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(txtDes c.Text))
Dim TextContent(int TextSize) As Byte
'************** *************** *************** *************
' '''''Reads the Image
TextContent =
System.Text.Enc oding.Unicode.G etBytes(Trim(tx tDesc.Text))
'' ''TextStream = Request.InputSt ream
'' ''Dim intStatus As Integer
'' ''intStatus = TextStream.Read (TextContent, 0, intTextSize)
''
''************* *************** *************** **************
'' '' Create Instance of Connection and Command Object
Dim szCon As String
szCon = "Data Source=localhos t;uid=t;pwd=t;I nitial
Catalog=myDb"
Dim myConnection As New SqlConnection(s zCon)
Dim myCommand As New
SqlCommand("myS toredProcedure" , myConnection)
'''' Mark the Command as a SPROC
myCommand.Comma ndType = CommandType.Sto redProcedure
Dim prmStudNo As New SqlParameter("@ StudNo", SqlDbType.Int,
4)
prmStudNo.Value = 1
myCommand.Param eters.Add(prmSt udNo)
'''''''' Add Parameters to SPROC
Dim prmTestText As New SqlParameter("@ TestText",
SqlDbType.Text, 16)
prmTestText.Val ue = TextContent
myCommand.Param eters.Add(prmTe stText)
Try
myConnection.Op en()
myCommand.Execu teNonQuery()
myConnection.Cl ose()
Response.Write( "New TestText successfully added!")
Catch SQLexc As SqlException
Response.Write( "Insert Failed. Error Details are: " &
SQLexc.ToString ())
End Try
End Sub
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
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.Valu e = 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 StoreBLOBIntoFi le()
Dim szBlobColumnNam e 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=localhos t;uid=t;pwd=t;I nitial Catalog=myDb"
sqlQuery = "Select @Pointer=TEXTPT R(" & szBlobColumnNam e &
"), @Length=DataLen gth(" & szBlobColumnNam e & ") from " & szTableName
& " " & szConstraint
Dim imageCol As Integer = 0 ' position of image column in
DataReader
Dim cn As New SqlConnection(s zCon)
'
' Make sure that Photo is non-NULL and return TEXTPTR to
it.
'
Dim cmdGetPointer As New SqlCommand(sqlQ uery, cn)
Dim PointerOutParam As SqlParameter =
cmdGetPointer.P arameters.Add(" @Pointer", SqlDbType.VarBi nary, 100)
PointerOutParam .Direction = ParameterDirect ion.Output
Dim LengthOutParam As SqlParameter =
cmdGetPointer.P arameters.Add(" @Length", SqlDbType.BigIn t)
LengthOutParam. Direction = ParameterDirect ion.Output
cn.Open()
cmdGetPointer.E xecuteNonQuery( )
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("REA DTEXT " &
szTableName & "." & szBlobColumnNam e & " @Pointer @Offset @Size
HOLDLOCK", cn)
Dim PointerParam As SqlParameter =
cmdReadBinary.P arameters.Add(" @Pointer", SqlDbType.Binar y, 16)
Dim OffsetParam As SqlParameter =
cmdReadBinary.P arameters.Add(" @Offset", SqlDbType.Int)
Dim SizeParam As SqlParameter =
cmdReadBinary.P arameters.Add(" @Size", SqlDbType.Int)
Dim dr As SqlDataReader
'Dim fs As New System.IO.FileS tream(DestFileP ath,
IO.FileMode.Ope nOrCreate, IO.FileAccess.W rite)
Dim Offset As Integer = 0
OffsetParam.Val ue = Offset
Dim Buffer(LengthOu tParam.Value - 1) As Byte
'
' Read buffer full of data and write to the file stream.
'
PointerParam.Va lue = 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.E xecuteReader(Co mmandBehavior.S ingleResult)
dr.Read()
dr.GetBytes(ima geCol, 0, Buffer, 0, SizeParam.Value )
dr.Close()
txtDesc.Text = txtDesc.Text &
ConvertByteArra yToString(Buffe r)
Offset += SizeParam.Value
OffsetParam.Val ue = Offset
Loop Until Offset >= LengthOutParam. Value
cn.Close()
msg = "Blob data stored successfully in File !"
Catch ex As Exception
msg = ex.Message
End Try
StoreBLOBIntoFi le = msg
End Function
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 FetchBLOBDataFr omDB(ByVal szBlobColumnNam e 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.getConnecti on
Dim cmd As New SqlCommand("SEL ECT " & szBlobColumnNam e & "
FROM " & szTableName & " " & szConstraint, cn)
Dim dr As SqlDataReader = cmd.ExecuteRead er()
dr.Read()
Dim Buffer(dr.GetBy tes(TextCol, 0, Nothing, 0,
Integer.MaxValu e) - 1) As Byte
dr.GetBytes(Tex tCol, 0, Buffer, 0, Buffer.Length)
dr.Close()
cn.Close()
Return ConvertByteArra yToString(Buffe r)
End Function
Public Function ConvertByteArra yToString(ByVal byteArray As Byte()) As
String
Dim enc As Encoding = Encoding.Defaul t
Dim strText As String = enc.GetString(b yteArray)
Return strText
End Function This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dan Sikorsky |
last post by:
How can I get the recordset attributes for a table field in SQL Server 2000
to report the field updatable attribute correctly ... mine keeps saying the
fields are not updatable? That is, ( oRecordSet ( oItem ).Attributes & 0x4)
is always 0 for every field on SQL Server 2000
Example code:
var sSQL = 'SELECT TOP 1 * FROM WHERE ' + sUniqueField
+ '=' + sUniqueValue + ';';
|
by: Tyler Hudson |
last post by:
/*Code below raises following errors:
Server: Msg 245, Level 16, State 1, Line 6
Syntax error converting the varchar value 'a' to a column of data type
int.
*/
create table #x (i integer, c char(1))
create table #y (c char(1), i integer)
insert into #x VALUES (1, 'a')
|
by: Iain Porter |
last post by:
Hi, I'm trying to store large strings to a database, so am using the
text field type (LongText). I have used this before when storing the
html of a webpage, and was able to store more than 255 characters by
using just a normal update sql statement. Now I'm trying to store the
body of research papers, and must be doing something different, as I
can only store 255 characters.
Can someone explain why SQL Server doesn't like what I am...
|
by: Sunny |
last post by:
I am using an asp page to access data stored by Microsoft SQL in a SQL server
database. I cannot get all values to return, some display as blanks.
I am using IIS v5, Microsoft SQL Server 2000, running on Windows Advanced
Server 2000 SP4.
Here is my asp code, SQL table create statements, CSV example data and
output. As can be seen by the output, the pc_model and manufacturer fields
display blank although these fields hold text data in...
|
by: fniles |
last post by:
I am using VB.NET 2003 and SQL Server 2000.
I have a table with a datetime column type. When inserting into the table
for that column, I set it to Date.Now.ToString("T")
, which is something like "2:50:54 PM". But after the row is inserted and I
check the data in the database, the column data is set to "1/7/2007 2:50:04
PM" (notice today's date in front of the time). If I insert data directly
into the table in the Enterprise Manager, the...
| |
by: gunimpi |
last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431
********************************************************
VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help
wanted
********************************************************
For this teeny job, please refer to:
http://feeds.reddit.com/feed/8fu/?o=25
|
by: Bhavesh |
last post by:
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.
|
by: Bhavesh |
last post by:
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
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |