R
Rob Panosh
Hello,
The code listed below will insert a Microsoft Word or XLS file into my sql
table when the column is defined as an "Image" type. But if I change the
column to a "Text" type the provider will throw "Operand type clash: Image
is incompatible with text" error.
Any ideas why I can't save my documents to Text or NText columns?
My guess is maybe I have to read the file differently when saving to
Text/NText columns, can't figure this one out for the life of me any help
would be GREATLY appreciated.
Thanks,
Rob Panosh
Advanced Software Designs
Managing ntext, text, and image Data
The Microsoft® SQL ServerT ntext, text, and image data types are capable of
holding extremely large amounts of data (up to 2 GB) in a single value. A
single data value is typically larger than can be retrieved by an
application in one step; some values may be larger than the virtual memory
available on the client. Therefore, special steps are usually needed to
retrieve these values.
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000 bytes
respectively), the value can be referenced in SELECT, UPDATE, and INSERT
statements much the same way as the smaller data types. For example, an
ntext column with a short value can be referenced in a SELECT statement
select list the same way an nvarchar column is referenced. Some restrictions
that must be observed, such as not being able to directly reference an
ntext, text, or image column in a WHERE clause. These columns can be
included in a WHERE clause as parameters of a function that returns another
data type (such as ISNULL, SUBSTRING or PATINDEX) or in an IS NULL, IS NOT
NULL, or LIKE expression.
Sample Code:
================================
Dim loConnection As SqlClient.SqlConnection
Dim loCommand As SqlClient.SqlCommand
Dim loProductImage As SqlClient.SqlParameter
Dim loImageValue() As Byte
Dim loFile As IO.FileStream
Dim lnByteCount As Integer
'Instantiate new connection object
loConnection = New SqlClient.SqlConnection
With loConnection
'Prepare connection string and open connection to database
..ConnectionString = "Data Source='DualASD';" & "Initial
Catalog=Provis50;Integrated Security=SSPI"
..Open()
'Open file with product image as a stream
loFile = New System.IO.FileStream("C:\myTestWord.Doc", IO.FileMode.Open)
'Get length of stream (lengt of file) in bytes
lnByteCount = loFile.Length()
'Reallocate storage space for an array variable to the
'size of image file
ReDim loImageValue(lnByteCount - 1)
'Load stream into array of bytes.
'lnByteCount will get real number of bytes which
'were retrieved from stream
lnByteCount = loFile.Read(loImageValue, 0, lnByteCount)
'Close stream
loFile.Close()
loFile = Nothing
'Instantiate new command object which we will
'use to insert image into table
loCommand = New SqlClient.SqlCommand
loCommand.CommandType = CommandType.Text
loCommand.CommandText = "INSERT INTO myTable (myDoc) VALUES (@myDoc)"
loCommand.Connection = loConnection
'Add parameter, which will accept image value,
'to command object
loProductImage = loCommand.Parameters.Add("@myDoc", Data.SqlDbType.Image)
'Set type of parameter to Image
loProductImage.SqlDbType = SqlDbType.Image
'Load parameter with actual data from array of bytes
loProductImage.Value = loImageValue
'Execute prepared query to provide actual inserting of
'prepared data
loCommand.ExecuteNonQuery()
loCommand.Dispose()
loCommand = Nothing
..Close()
..Dispose()
End With
loConnection = Nothing
The code listed below will insert a Microsoft Word or XLS file into my sql
table when the column is defined as an "Image" type. But if I change the
column to a "Text" type the provider will throw "Operand type clash: Image
is incompatible with text" error.
Any ideas why I can't save my documents to Text or NText columns?
My guess is maybe I have to read the file differently when saving to
Text/NText columns, can't figure this one out for the life of me any help
would be GREATLY appreciated.
Thanks,
Rob Panosh
Advanced Software Designs
Managing ntext, text, and image Data
The Microsoft® SQL ServerT ntext, text, and image data types are capable of
holding extremely large amounts of data (up to 2 GB) in a single value. A
single data value is typically larger than can be retrieved by an
application in one step; some values may be larger than the virtual memory
available on the client. Therefore, special steps are usually needed to
retrieve these values.
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000 bytes
respectively), the value can be referenced in SELECT, UPDATE, and INSERT
statements much the same way as the smaller data types. For example, an
ntext column with a short value can be referenced in a SELECT statement
select list the same way an nvarchar column is referenced. Some restrictions
that must be observed, such as not being able to directly reference an
ntext, text, or image column in a WHERE clause. These columns can be
included in a WHERE clause as parameters of a function that returns another
data type (such as ISNULL, SUBSTRING or PATINDEX) or in an IS NULL, IS NOT
NULL, or LIKE expression.
Sample Code:
================================
Dim loConnection As SqlClient.SqlConnection
Dim loCommand As SqlClient.SqlCommand
Dim loProductImage As SqlClient.SqlParameter
Dim loImageValue() As Byte
Dim loFile As IO.FileStream
Dim lnByteCount As Integer
'Instantiate new connection object
loConnection = New SqlClient.SqlConnection
With loConnection
'Prepare connection string and open connection to database
..ConnectionString = "Data Source='DualASD';" & "Initial
Catalog=Provis50;Integrated Security=SSPI"
..Open()
'Open file with product image as a stream
loFile = New System.IO.FileStream("C:\myTestWord.Doc", IO.FileMode.Open)
'Get length of stream (lengt of file) in bytes
lnByteCount = loFile.Length()
'Reallocate storage space for an array variable to the
'size of image file
ReDim loImageValue(lnByteCount - 1)
'Load stream into array of bytes.
'lnByteCount will get real number of bytes which
'were retrieved from stream
lnByteCount = loFile.Read(loImageValue, 0, lnByteCount)
'Close stream
loFile.Close()
loFile = Nothing
'Instantiate new command object which we will
'use to insert image into table
loCommand = New SqlClient.SqlCommand
loCommand.CommandType = CommandType.Text
loCommand.CommandText = "INSERT INTO myTable (myDoc) VALUES (@myDoc)"
loCommand.Connection = loConnection
'Add parameter, which will accept image value,
'to command object
loProductImage = loCommand.Parameters.Add("@myDoc", Data.SqlDbType.Image)
'Set type of parameter to Image
loProductImage.SqlDbType = SqlDbType.Image
'Load parameter with actual data from array of bytes
loProductImage.Value = loImageValue
'Execute prepared query to provide actual inserting of
'prepared data
loCommand.ExecuteNonQuery()
loCommand.Dispose()
loCommand = Nothing
..Close()
..Dispose()
End With
loConnection = Nothing