Another SQL vs. Access

[lgbjr]

Hi All,

As you know, I've made the switch from Access/Jet to MSDE 2K. And I'm now
encountering my first little problem. I have some pictureboxes that display
images from the DB. For each picturebox, I have some buttons (Add/Change
Image, Rotate Image, Delete Image) and a context menu (Copy/Paste, Open,
Edit, and Preview). For open, Edit, and Preview, I'm creating a temporary
file on disk, then writing the file back to the DB and deleting it once the
user is finished with it. this is how I'm creating the temp file:

Dim conn As OLEDBConnection = New OLEDBConnection
conn.Connection String = XConn.Connectio nString
If conn.State <> ConnectionState .Open Then
conn.Open()
End If

Dim cmd As OLEDBCommand = New OLEDBCommand(sq l, conn)
Dim fs As FileStream
Dim bw As BinaryWriter
Dim bufferSize As Integer = 300000
Dim outbyte(300000 - 1) As Byte
Dim retval As Long
Dim startIndex As Long = 0
Try
Dim reader As OLEDBDataReader =
cmd.ExecuteRead er(CommandBehav ior.SequentialA ccess)
reader.Read()
fs = New FileStream(pbfi le, FileMode.OpenOr Create,
FileAccess.Writ e)
bw = New BinaryWriter(fs )
startIndex = 0
retval = reader.GetBytes (0, 0, outbyte, 0, bufferSize)
bw.Write(outbyt e)
bw.Flush()
bw.Close()
fs.Close()
reader.Close()
If conn.State = ConnectionState .Open Then
conn.Close()
conn.Dispose()
End If

When I was using OLE to connect to the JetDB:

If a user pasted an image into a picturebox, then immediately tried to open,
edit, or preview it, an exception was thrown by the OLEDBDataReader ,
because, since an update wasn't done after the paste, the image didn't exist
in the DB. I was catching this exception and telling the user that the image
had to be saved before any further actions could be taken with the image.

Upon switching to MSDE 2K:

I switched from using the OLEDBDataReader to using the SQLDataReader (Just
replace the OLEDB stuff above with SQL stuff). Under the same circumstances,
an exception is not thrown. I just get an empty temp file.

What I don't know is this: is it a difference in the reader or in the DB? My
guess is that it's a difference in the DBs, specifically something to do
with how an empty cell is represented. Using Jet, the empty cell was
represented with a DBNull, which caused the reader to throw an exception.
Does an SQL DB use DBNull? If not, any ideas on how to fix this?

TIA
Lee

[Ken Tucker [MVP]]

Hi,

Here is a quick example of how to display an image saved in a
database. Loads the northwind databases category names into a listbox
(listbox1) and displays the image in a picture box (picturebox1).

Dim ds As DataSet

Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load

Dim strConn As String

Dim conn As SqlClient.SqlCo nnection

Dim daCustomer As SqlClient.SqlDa taAdapter

ds = New DataSet

' strConn = "Provider = Microsoft.Jet.O LEDB.4.0;"

'strConn &= "Data Source = Northwind.mdb;"

strConn = "Server = " + Environment.Mac hineName + "\VSdotNet; "

strConn &= "Database = NorthWind;"

strConn &= "Integrated Security = SSPI;"

conn = New SqlClient.SqlCo nnection(strCon n)

daCustomer = New SqlClient.SqlDa taAdapter("Sele ct * from Categories", conn)

ds = New DataSet

daCustomer.Fill (ds, "Categories ")

ListBox1.DataSo urce = ds.Tables("Cate gories")

ListBox1.Displa yMember = "CategoryNa me"

End Sub

Private Sub ListBox1_Select edValueChanged( ByVal sender As Object, ByVal e As
System.EventArg s) Handles ListBox1.Select edValueChanged

Dim dr As DataRow = ds.Tables("Cate gories").Rows(L istBox1.Selecte dIndex)

Dim ms As New System.IO.Memor yStream

Dim bm As Bitmap

Dim arData() As Byte = dr.Item("Pictur e")

ms.Write(arData , 78, arData.Length - 78)

bm = New Bitmap(ms)

PictureBox1.Ima ge = bm

End Sub

Ken

-----------------
"lgbjr" <lg***@online.n ospam> wrote in message
news:ul******** ******@tk2msftn gp13.phx.gbl...
Hi All,

As you know, I've made the switch from Access/Jet to MSDE 2K. And I'm now
encountering my first little problem. I have some pictureboxes that display
images from the DB. For each picturebox, I have some buttons (Add/Change
Image, Rotate Image, Delete Image) and a context menu (Copy/Paste, Open,
Edit, and Preview). For open, Edit, and Preview, I'm creating a temporary
file on disk, then writing the file back to the DB and deleting it once the
user is finished with it. this is how I'm creating the temp file:

Dim conn As OLEDBConnection = New OLEDBConnection
conn.Connection String = XConn.Connectio nString
If conn.State <> ConnectionState .Open Then
conn.Open()
End If

Dim cmd As OLEDBCommand = New OLEDBCommand(sq l, conn)
Dim fs As FileStream
Dim bw As BinaryWriter
Dim bufferSize As Integer = 300000
Dim outbyte(300000 - 1) As Byte
Dim retval As Long
Dim startIndex As Long = 0
Try
Dim reader As OLEDBDataReader =
cmd.ExecuteRead er(CommandBehav ior.SequentialA ccess)
reader.Read()
fs = New FileStream(pbfi le, FileMode.OpenOr Create,
FileAccess.Writ e)
bw = New BinaryWriter(fs )
startIndex = 0
retval = reader.GetBytes (0, 0, outbyte, 0, bufferSize)
bw.Write(outbyt e)
bw.Flush()
bw.Close()
fs.Close()
reader.Close()
If conn.State = ConnectionState .Open Then
conn.Close()
conn.Dispose()
End If

When I was using OLE to connect to the JetDB:

If a user pasted an image into a picturebox, then immediately tried to open,
edit, or preview it, an exception was thrown by the OLEDBDataReader ,
because, since an update wasn't done after the paste, the image didn't exist
in the DB. I was catching this exception and telling the user that the image
had to be saved before any further actions could be taken with the image.

Upon switching to MSDE 2K:

I switched from using the OLEDBDataReader to using the SQLDataReader (Just
replace the OLEDB stuff above with SQL stuff). Under the same circumstances,
an exception is not thrown. I just get an empty temp file.

What I don't know is this: is it a difference in the reader or in the DB? My
guess is that it's a difference in the DBs, specifically something to do
with how an empty cell is represented. Using Jet, the empty cell was
represented with a DBNull, which caused the reader to throw an exception.
Does an SQL DB use DBNull? If not, any ideas on how to fix this?

TIA
Lee

[Cor Ligthert]

Lee,

Do you know this dataset sample that I made for handling images?

This is one of the messages where it is in. Have a look. It handles easier
in my opinion than the datareader. (for what I have as well samples)
http://groups-beta.google.com/group/...75e8834d?hl=en

I hope this helps,

Cor

[lgbjr]

Hi Guys,

thanks for the replies, but maybe I wasn't clear on the exact problem. I
don't have any problem storing a picture from a picturebox in the DB, and I
don't have any problem showing a picture from the DB in a picturebox. Also,
I don't have any problem extracting a picture from the DB and saving
directly to a disk file. The problem is this:

A user adds a picture to an empty picturebox, but doesn't click save (Update
the DB).

If he then right-clicks on the picture and chooses to open, edit, or preview
the picture, the code goes to the DB to retrieve the picture and write it to
a temp disk file (see code in original post), which is then opened in
whatever program the registry says to open it in for the particular command
selected (open, edit, or preview).

Using the OLEDBDataReader and the JetDB, if the user does this, the reader
throws an exception, which I catch and tell the user he has to update the DB
before doing anything with the picture.

Using the SQLDataReader and MSDE 2K, the exception is not thrown, an empty
file is written to disk, which is then opened by whatever program, but of
course, the picture isn't there ('cause it wasn't in the DB).

As a side note, the context menu items are disabled (except for Paste) if
the Picturebox is empty. Once the user adds or pastes a picture into the
picturebox, all of the menu features are enabled.

So, the main question is why doesn't the SQLDataReader / MSDE 2K throw the
same exception as the OLEDBDataReader / JetDB when trying to read an empty
cell?

If I can understand why they act differently, it would give me some insight
as to the best fix (I know there are several ways to fix this, but
understanding why it happens should lead to the best fix).

TIA,
Lee
"Ken Tucker [MVP]" <vb***@bellsout h.net> wrote in message
news:e8******** ******@TK2MSFTN GP09.phx.gbl...

Hi,

Here is a quick example of how to display an image saved in a
database. Loads the northwind databases category names into a listbox
(listbox1) and displays the image in a picture box (picturebox1).

Dim ds As DataSet

Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load

Dim strConn As String

Dim conn As SqlClient.SqlCo nnection

Dim daCustomer As SqlClient.SqlDa taAdapter

ds = New DataSet

' strConn = "Provider = Microsoft.Jet.O LEDB.4.0;"

'strConn &= "Data Source = Northwind.mdb;"

strConn = "Server = " + Environment.Mac hineName + "\VSdotNet; "

strConn &= "Database = NorthWind;"

strConn &= "Integrated Security = SSPI;"

conn = New SqlClient.SqlCo nnection(strCon n)

daCustomer = New SqlClient.SqlDa taAdapter("Sele ct * from Categories",
conn)

ds = New DataSet

daCustomer.Fill (ds, "Categories ")

ListBox1.DataSo urce = ds.Tables("Cate gories")

ListBox1.Displa yMember = "CategoryNa me"

End Sub

Private Sub ListBox1_Select edValueChanged( ByVal sender As Object, ByVal e
As
System.EventArg s) Handles ListBox1.Select edValueChanged

Dim dr As DataRow = ds.Tables("Cate gories").Rows(L istBox1.Selecte dIndex)

Dim ms As New System.IO.Memor yStream

Dim bm As Bitmap

Dim arData() As Byte = dr.Item("Pictur e")

ms.Write(arData , 78, arData.Length - 78)

bm = New Bitmap(ms)

PictureBox1.Ima ge = bm

End Sub

Ken

-----------------
"lgbjr" <lg***@online.n ospam> wrote in message
news:ul******** ******@tk2msftn gp13.phx.gbl...
Hi All,

As you know, I've made the switch from Access/Jet to MSDE 2K. And I'm now
encountering my first little problem. I have some pictureboxes that
display
images from the DB. For each picturebox, I have some buttons (Add/Change
Image, Rotate Image, Delete Image) and a context menu (Copy/Paste, Open,
Edit, and Preview). For open, Edit, and Preview, I'm creating a temporary
file on disk, then writing the file back to the DB and deleting it once
the
user is finished with it. this is how I'm creating the temp file:

Dim conn As OLEDBConnection = New OLEDBConnection
conn.Connection String = XConn.Connectio nString
If conn.State <> ConnectionState .Open Then
conn.Open()
End If

Dim cmd As OLEDBCommand = New OLEDBCommand(sq l, conn)
Dim fs As FileStream
Dim bw As BinaryWriter
Dim bufferSize As Integer = 300000
Dim outbyte(300000 - 1) As Byte
Dim retval As Long
Dim startIndex As Long = 0
Try
Dim reader As OLEDBDataReader =
cmd.ExecuteRead er(CommandBehav ior.SequentialA ccess)
reader.Read()
fs = New FileStream(pbfi le, FileMode.OpenOr Create,
FileAccess.Writ e)
bw = New BinaryWriter(fs )
startIndex = 0
retval = reader.GetBytes (0, 0, outbyte, 0, bufferSize)
bw.Write(outbyt e)
bw.Flush()
bw.Close()
fs.Close()
reader.Close()
If conn.State = ConnectionState .Open Then
conn.Close()
conn.Dispose()
End If

When I was using OLE to connect to the JetDB:

If a user pasted an image into a picturebox, then immediately tried to
open,
edit, or preview it, an exception was thrown by the OLEDBDataReader ,
because, since an update wasn't done after the paste, the image didn't
exist
in the DB. I was catching this exception and telling the user that the
image
had to be saved before any further actions could be taken with the image.

Upon switching to MSDE 2K:

I switched from using the OLEDBDataReader to using the SQLDataReader (Just
replace the OLEDB stuff above with SQL stuff). Under the same
circumstances,
an exception is not thrown. I just get an empty temp file.

What I don't know is this: is it a difference in the reader or in the DB?
My
guess is that it's a difference in the DBs, specifically something to do
with how an empty cell is represented. Using Jet, the empty cell was
represented with a DBNull, which caused the reader to throw an exception.
Does an SQL DB use DBNull? If not, any ideas on how to fix this?

TIA
Lee

[Cor Ligthert]

Lee,

Your code snippet is to small to give all that information to use you are
telling now.

However what tackled me direct. Why are you writing something to a temporaly
disk file?

Are you sure you would not have a look at my sample where the Memory Stream
is used?

Cor

[lgbjr]

Hey Cor,

OK, I've copied the entire CM_Pic_Edit Sub to the bottom of this post. when
a user right-clicks on a picturebox (that has an image), the context menu
has a choice for editing the pic. I don't decide which program to use for
editing. when my app loads, I get all of the information regarding the
current users preferences from the registry.

So, this is the flow:

User right-clicks on picturebox and selects Edit
Create an SQL Statement related to whichever picturebox was the source
Open a connection to the DB
Use the SQLDataReader and a filestream to write the pic to disk
shell to whatever the users preferred editor for pics is
write the edited pic back to the DB
Delete the disk file

Using the OLEDBDataReader with a JetDB throws an exception if the pic isn't
actually in the DB (only in the picbox because the user didn't save/update
the DB yet)

Using the SQLDataReader with MSDE2K, in the same situation does not throw an
exception. It just writes an empty file to disk.

As I said, I'd like to understand why no exception is thrown with the SQL /
MSDE version, but is with the OLE / JetDB version. Once I understand why, it
should be relatively easy to fix. I already know I can check the size of the
disk file and if it's 0, I can throw my own exception. However, my
preference would be to handle this before I get to file creation.

Again, my guess is that it has something to do with the different ways the
JetDB and the SQL DB represent an empty field. DBNull is what causes the
exception to be thrown in the OLE / JetDB case. But I don't know what the
equivalent to a DBNull is in an SQL DB.

regarding the memorystream, I'm not sure a memorystream would work for
sending an image from the DB to an editor, having it edited, then getting it
back to the DB.

Here's the code:

Private Sub CM_Pic_Edit_Cli ck(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles CM_Pic_Edit.Cli ck
Dim pb, pm, pbfile, sql As String

sql = ""
pm = ""
pbfile = ""

If CM_Pics.SourceC ontrol Is BTS_Pic1 Then
pm = "Picture 1"
pb = "Pic1"
sql = "SELECT BTSDB." + pb + " FROM BTSDB Where
(BTSDB.BTSIndex ='" +
CStr(BP_BTS_Pic k.Splits(0).Dis playColumns("BT SIndex").DataCo lumn.Value) +
"')"
pbfile =
Environment.Get FolderPath(Envi ronment.Special Folder.Personal ) + "\" + pb +
".jpg"
ElseIf CM_Pics.SourceC ontrol Is BTS_Pic2 Then
pm = "Picture 2"
pb = "Pic2"
sql = "SELECT BTSDB." + pb + " FROM BTSDB Where
(BTSDB.BTSIndex ='" +
CStr(BP_BTS_Pic k.Splits(0).Dis playColumns("BT SIndex").DataCo lumn.Value) +
"')"
pbfile =
Environment.Get FolderPath(Envi ronment.Special Folder.Personal ) + "\" + pb +
".jpg"
ElseIf CM_Pics.SourceC ontrol Is BTS_Pic3 Then
pm = "Picture 3"
pb = "Pic3"
sql = "SELECT BTSDB." + pb + " FROM BTSDB Where
(BTSDB.BTSIndex ='" +
CStr(BP_BTS_Pic k.Splits(0).Dis playColumns("BT SIndex").DataCo lumn.Value) +
"')"
pbfile =
Environment.Get FolderPath(Envi ronment.Special Folder.Personal ) + "\" + pb +
".jpg"
End If
If CM_Pics.SourceC ontrol Is Sec_Pic1 Then
pm = "Picture 1"
pb = "Pic1"
sql = "SELECT SectorDB." + pb + " FROM SectorDB Where
(SectorDB.SECTO RIndex='" +
CStr(SI_Sector_ Pick.Splits(0). DisplayColumns( "SECTORIndex"). DataColumn.Valu e)
+ "')"
pbfile =
Environment.Get FolderPath(Envi ronment.Special Folder.Personal ) + "\" + pb +
".jpg"
ElseIf CM_Pics.SourceC ontrol Is Sec_Pic2 Then
pm = "Picture 2"
pb = "Pic2"
sql = "SELECT SectorDB." + pb + " FROM SectorDB Where
(SectorDB.SECTO RIndex='" +
CStr(SI_Sector_ Pick.Splits(0). DisplayColumns( "SECTORIndex"). DataColumn.Valu e)
+ "')"
pbfile =
Environment.Get FolderPath(Envi ronment.Special Folder.Personal ) + "\" + pb +
".jpg"
ElseIf CM_Pics.SourceC ontrol Is Sec_Pic3 Then
pm = "Picture 3"
pb = "Pic3"
sql = "SELECT SectorDB." + pb + " FROM SectorDB Where
(SectorDB.SECTO RIndex='" +
CStr(SI_Sector_ Pick.Splits(0). DisplayColumns( "SECTORIndex"). DataColumn.Valu e)
+ "')"
pbfile =
Environment.Get FolderPath(Envi ronment.Special Folder.Personal ) + "\" + pb +
".jpg"
End If

Dim conn As SqlClient.SqlCo nnection = New SqlClient.SqlCo nnection
conn.Connection String = XConn.Connectio nString
If conn.State <> ConnectionState .Open Then
conn.Open()
End If

Dim cmd As SqlClient.SqlCo mmand = New SqlClient.SqlCo mmand(sql,
conn)
Dim fs As FileStream
Dim bw As BinaryWriter
Dim bufferSize As Integer = 300000
Dim outbyte(300000 - 1) As Byte
Dim retval As Long
Dim startIndex As Long = 0
Try
Dim reader As SqlClient.SqlDa taReader =
cmd.ExecuteRead er(CommandBehav ior.SequentialA ccess)
reader.Read()
fs = New FileStream(pbfi le, FileMode.OpenOr Create,
FileAccess.Writ e)
bw = New BinaryWriter(fs )
startIndex = 0
retval = reader.GetBytes (0, 0, outbyte, 0, bufferSize)
bw.Write(outbyt e)
bw.Flush()
bw.Close()
fs.Close()
reader.Close()
If conn.State = ConnectionState .Open Then
conn.Close()
conn.Dispose()
End If

Dim go_edit As String
go_edit = CM_Pic_EditC(1) + " " + """" + pbfile + """"
Shell(go_edit, AppWinStyle.Nor malFocus, True)
fs = New FileStream(pbfi le, FileMode.OpenOr Create,
FileAccess.Read )
Dim rawData() As Byte = New Byte(CInt(fs.Le ngth)) {}
fs.Read(rawData , 0, System.Convert. ToInt32(fs.Leng th))
fs.Close()

If CM_Pics.SourceC ontrol Is BTS_Pic1 Then
_XBTS.DataTable .Rows.Find(BP_B TS_Pick.Splits( 0).DisplayColum ns("BTSIndex"). DataColumn.Valu e).Item("Pic1")
= rawData
ElseIf CM_Pics.SourceC ontrol Is BTS_Pic2 Then
_XBTS.DataTable .Rows.Find(BP_B TS_Pick.Splits( 0).DisplayColum ns("BTSIndex"). DataColumn.Valu e).Item("Pic2")
= rawData
ElseIf CM_Pics.SourceC ontrol Is BTS_Pic3 Then
_XBTS.DataTable .Rows.Find(BP_B TS_Pick.Splits( 0).DisplayColum ns("BTSIndex"). DataColumn.Valu e).Item("Pic3")
= rawData
End If
If CM_Pics.SourceC ontrol Is Sec_Pic1 Then
_XSector.DataTa ble.Rows.Find(S I_Sector_Pick.S plits(0).Displa yColumns("Secto rIndex").DataCo lumn.Value).Ite m("Pic1")
= rawData
ElseIf CM_Pics.SourceC ontrol Is Sec_Pic2 Then
_XSector.DataTa ble.Rows.Find(S I_Sector_Pick.S plits(0).Displa yColumns("Secto Index").DataCol umn.Value).Item ("Pic2")
= rawData
ElseIf CM_Pics.SourceC ontrol Is Sec_Pic3 Then
_XSector.DataTa ble.Rows.Find(S I_Sector_Pick.S plits(0).Displa yColumns("Secto rIndex").DataCo lumn.Value).Ite m("Pic3")
= rawData
End If
File.Delete(pbf ile)
Catch ex As Exception
If SD_Tabs.Current = 4 Then
MessageBox.Show (BP_BTS_Pick.Sp lits(0).Display Columns("BTSID" ).DataColumn.Va lue.ToString
+ " - " + pm + " has not been saved to the Database. Click Update/Save, then
try Opening the picture again.", "Picture Not in DB", MessageBoxButto ns.OK,
MessageBoxIcon. Information)
Else
MessageBox.Show (SI_Sector_Pick .Splits(0).Disp layColumns("Sec torID").DataCol umn.Value.ToStr ing
+ " - " + pm + " has not been saved to the Database. Click Update/Save, then
try Opening the picture again.", "Picture Not in DB", MessageBoxButto ns.OK,
MessageBoxIcon. Information)
End If
End Try
End Sub
"Cor Ligthert" <no************ @planet.nl> wrote in message
news:%2******** **********@TK2M SFTNGP14.phx.gb l...

Lee,

Your code snippet is to small to give all that information to use you are
telling now.

However what tackled me direct. Why are you writing something to a
temporaly disk file?

Are you sure you would not have a look at my sample where the Memory
Stream is used?

Cor

[lgbjr]

Hi guys,

I just realized what might be causing some of the confusion related to my
actual question. My pictureboxes are bound to a datatable, so if there's an
image to display, it's displayed via the binding, and if a user adds an
image to the picturebox, it's saved to the table via the binding.

The issue is related to getting the pic from the DB to an external editor
(and back to the DB once the editing is done). Well, actually, I can do that
part. I guess the issue is just the lack of an exception if the image
doesn't exist yet in the DB.

thanks,
Lee

"Cor Ligthert" <no************ @planet.nl> wrote in message
news:%2******** **********@TK2M SFTNGP14.phx.gb l...

Lee,

Your code snippet is to small to give all that information to use you are
telling now.

However what tackled me direct. Why are you writing something to a
temporaly disk file?

Are you sure you would not have a look at my sample where the Memory
Stream is used?

Cor