Re: Another SQL vs. Access

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim strConn As String

Dim conn As SqlClient.SqlConnection

Dim daCustomer As SqlClient.SqlDataAdapter

ds = New DataSet

' strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

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

strConn = "Server = " + Environment.MachineName + "\VSdotNet;"

strConn &= "Database = NorthWind;"

strConn &= "Integrated Security = SSPI;"

conn = New SqlClient.SqlConnection(strConn)

daCustomer = New SqlClient.SqlDataAdapter("Select * from Categories", conn)

ds = New DataSet

daCustomer.Fill(ds, "Categories")

ListBox1.DataSource = ds.Tables("Categories")

ListBox1.DisplayMember = "CategoryName"

End Sub



Private Sub ListBox1_SelectedValueChanged(ByVal sender As Object, ByVal e As
System.EventArgs) Handles ListBox1.SelectedValueChanged

Dim dr As DataRow = ds.Tables("Categories").Rows(ListBox1.SelectedIndex)

Dim ms As New System.IO.MemoryStream

Dim bm As Bitmap

Dim arData() As Byte = dr.Item("Picture")

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

bm = New Bitmap(ms)

PictureBox1.Image = bm

End Sub



Ken

-----------------
"lgbjr" <lgbjr@xxxxxxxxxxxxx> wrote in message
news:ultEYQkSFHA.1096@xxxxxxxxxxxxxxxxxxxxxxx
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.ConnectionString = XConn.ConnectionString
If conn.State <> ConnectionState.Open Then
conn.Open()
End If

Dim cmd As OLEDBCommand = New OLEDBCommand(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 OLEDBDataReader =
cmd.ExecuteReader(CommandBehavior.SequentialAccess)
reader.Read()
fs = New FileStream(pbfile, FileMode.OpenOrCreate,
FileAccess.Write)
bw = New BinaryWriter(fs)
startIndex = 0
retval = reader.GetBytes(0, 0, outbyte, 0, bufferSize)
bw.Write(outbyte)
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



.

Quantcast