SqlDataReader.Read() with image data type size 65,535

G

Guest

Hi Guy,

I've found that SqlDataReader.Read() (with do not read value from row) has a
problem when reading image data type from Ms SqlServer 2000 if data size is
65,535. I've found this error in .NET 1.1. Any suggestions?

Thanks,

The followings are sql script and VB.NET code:

----- sql script for table -----
CREATE TABLE [TB_TEST] (
[ID] [int] NOT NULL ,
[Data] [image] NULL ,
CONSTRAINT [PK_TB_Test] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

----- code ------
' insert test data
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim conn As New SqlConnection(connStr)
Dim cmd As SqlCommand
Dim qrt As String = "INSERT INTO TB_TEST ([ID], Data) VALUES (@id,
@data)"
Dim params(1) As SqlParameter
Dim data() As Byte
Try
conn.Open()
cmd = New SqlCommand(qrt)
cmd.Connection = conn
For i As Integer = 1 To 10
params(0) = New SqlParameter("@id", i)
Dim str As New String(ChrW(i + 65), 65535)
data = System.Text.Encoding.ASCII.GetBytes(str.ToCharArray())
params(1) = New SqlParameter("@data", data)

cmd.Parameters.Clear()
cmd.Parameters.Add(params(0))
cmd.Parameters.Add(params(1))
cmd.ExecuteNonQuery()
Next
Catch ex As Exception
Console.WriteLine("Exception: " & ex.ToString())
Finally
If Not conn Is Nothing Then
conn.Close()
conn.Dispose()
conn = Nothing
End If

If Not cmd Is Nothing Then
cmd.Dispose()
cmd = Nothing
End If
End Try

MsgBox("Success!")
End Sub

' retrieve data
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim conn As New SqlConnection(connStr)
Dim cmd As SqlCommand = Nothing
Dim qrt As String = "SELECT [ID], Data FROM TB_TEST"
Dim rdr As SqlDataReader = Nothing
Dim i As Integer = 0

Try
conn.Open()
cmd = New SqlCommand(qrt)
cmd.Connection = conn
rdr = cmd.ExecuteReader()
While Not rdr Is Nothing AndAlso rdr.Read() ' <--- Can read 1
record
i += 1
End While
Catch ex As Exception
Console.WriteLine("Exception: " & ex.ToString())
Finally
If Not conn Is Nothing Then
conn.Close() ' <--- InvalidOperationException
conn.Dispose()
conn = Nothing
End If

If Not cmd Is Nothing Then
cmd.Dispose()
cmd = Nothing
End If
End Try

MsgBox("Success!")
End Sub
 
W

William \(Bill\) Vaughn

Try adding CommandBehavior.SequentialAccess to the ExecuteReader arguments.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Thana N. said:
Hi Guy,

I've found that SqlDataReader.Read() (with do not read value from row) has
a
problem when reading image data type from Ms SqlServer 2000 if data size
is
65,535. I've found this error in .NET 1.1. Any suggestions?

Thanks,

The followings are sql script and VB.NET code:

----- sql script for table -----
CREATE TABLE [TB_TEST] (
[ID] [int] NOT NULL ,
[Data] [image] NULL ,
CONSTRAINT [PK_TB_Test] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

----- code ------
' insert test data
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim conn As New SqlConnection(connStr)
Dim cmd As SqlCommand
Dim qrt As String = "INSERT INTO TB_TEST ([ID], Data) VALUES (@id,
@data)"
Dim params(1) As SqlParameter
Dim data() As Byte
Try
conn.Open()
cmd = New SqlCommand(qrt)
cmd.Connection = conn
For i As Integer = 1 To 10
params(0) = New SqlParameter("@id", i)
Dim str As New String(ChrW(i + 65), 65535)
data =
System.Text.Encoding.ASCII.GetBytes(str.ToCharArray())
params(1) = New SqlParameter("@data", data)

cmd.Parameters.Clear()
cmd.Parameters.Add(params(0))
cmd.Parameters.Add(params(1))
cmd.ExecuteNonQuery()
Next
Catch ex As Exception
Console.WriteLine("Exception: " & ex.ToString())
Finally
If Not conn Is Nothing Then
conn.Close()
conn.Dispose()
conn = Nothing
End If

If Not cmd Is Nothing Then
cmd.Dispose()
cmd = Nothing
End If
End Try

MsgBox("Success!")
End Sub

' retrieve data
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim conn As New SqlConnection(connStr)
Dim cmd As SqlCommand = Nothing
Dim qrt As String = "SELECT [ID], Data FROM TB_TEST"
Dim rdr As SqlDataReader = Nothing
Dim i As Integer = 0

Try
conn.Open()
cmd = New SqlCommand(qrt)
cmd.Connection = conn
rdr = cmd.ExecuteReader()
While Not rdr Is Nothing AndAlso rdr.Read() ' <--- Can read
1
record
i += 1
End While
Catch ex As Exception
Console.WriteLine("Exception: " & ex.ToString())
Finally
If Not conn Is Nothing Then
conn.Close() ' <--- InvalidOperationException
conn.Dispose()
conn = Nothing
End If

If Not cmd Is Nothing Then
cmd.Dispose()
cmd = Nothing
End If
End Try

MsgBox("Success!")
End Sub
Click to expand...
 
G

Guest

No, it does not solve the problem. My problem occurs when it reads a record
that contain 65535 bytes of image data type. In normal situation, it works
find.

Another information, it happens when I change data type to 'text'.

Thanks,

William (Bill) Vaughn said:
Try adding CommandBehavior.SequentialAccess to the ExecuteReader arguments.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Thana N. said:
Hi Guy,

I've found that SqlDataReader.Read() (with do not read value from row) has
a
problem when reading image data type from Ms SqlServer 2000 if data size
is
65,535. I've found this error in .NET 1.1. Any suggestions?

Thanks,

The followings are sql script and VB.NET code:

----- sql script for table -----
CREATE TABLE [TB_TEST] (
[ID] [int] NOT NULL ,
[Data] [image] NULL ,
CONSTRAINT [PK_TB_Test] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

----- code ------
' insert test data
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim conn As New SqlConnection(connStr)
Dim cmd As SqlCommand
Dim qrt As String = "INSERT INTO TB_TEST ([ID], Data) VALUES (@id,
@data)"
Dim params(1) As SqlParameter
Dim data() As Byte
Try
conn.Open()
cmd = New SqlCommand(qrt)
cmd.Connection = conn
For i As Integer = 1 To 10
params(0) = New SqlParameter("@id", i)
Dim str As New String(ChrW(i + 65), 65535)
data =
System.Text.Encoding.ASCII.GetBytes(str.ToCharArray())
params(1) = New SqlParameter("@data", data)

cmd.Parameters.Clear()
cmd.Parameters.Add(params(0))
cmd.Parameters.Add(params(1))
cmd.ExecuteNonQuery()
Next
Catch ex As Exception
Console.WriteLine("Exception: " & ex.ToString())
Finally
If Not conn Is Nothing Then
conn.Close()
conn.Dispose()
conn = Nothing
End If

If Not cmd Is Nothing Then
cmd.Dispose()
cmd = Nothing
End If
End Try

MsgBox("Success!")
End Sub

' retrieve data
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim conn As New SqlConnection(connStr)
Dim cmd As SqlCommand = Nothing
Dim qrt As String = "SELECT [ID], Data FROM TB_TEST"
Dim rdr As SqlDataReader = Nothing
Dim i As Integer = 0

Try
conn.Open()
cmd = New SqlCommand(qrt)
cmd.Connection = conn
rdr = cmd.ExecuteReader()
While Not rdr Is Nothing AndAlso rdr.Read() ' <--- Can read
1
record
i += 1
End While
Catch ex As Exception
Console.WriteLine("Exception: " & ex.ToString())
Finally
If Not conn Is Nothing Then
conn.Close() ' <--- InvalidOperationException
conn.Dispose()
conn = Nothing
End If

If Not cmd Is Nothing Then
cmd.Dispose()
cmd = Nothing
End If
End Try

MsgBox("Success!")
End Sub
Click to expand...
Click to expand...
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Open transaction after TransactionScope.SetComplete 3
SqlParameter is already contained by another SqlParameterCollectio 2
Parameter has no default value 4
Open DataReader associated with this Command which must be closed first. 2
insert stored procedure does not commit to the database More options 4
Why isn't my SCOPE_IDENTITY() working? 3
General Network Error: query->restart server->query again 1
login routine with information returned from database 3

Top