The best way is store the files in the hard disk. ( - jbassmanp - )
I don't store images in an Access DB. Only in SQL Server.
Access corrupts files easily. And you lost all the images at once.
But some times I need to store them on SQL Server.
The only files I store there are quick snapshots for a short time, and
when I dont want to give full (write) access to drives or directories.
Access 2000...
CREATE TABLE [Tabla1] (
[ID] [int] NOT NULL PRIMARY KEY, -- Number
[Nameb] [varchar] (80) NOT NULL , -- Text
[Imageb] [<pre>image</pre>] NULL , -- OLE
)
GO
Private Sub Insert1()
'CONNECTION
Dim OleDbConnection1 As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documen" & _
"ts and Settings\Administrator\My documents\Access1.mdb;.......")
OleDbConnection1.Open()
Dim OleDbInsertCommand1 As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand()
'Access (OleDb)
OleDbInsertCommand1.CommandText = "INSERT INTO Table1(ID, Nameb, Imageb) VALUES (@ID, @Nameb, @Imageb)"
OleDbInsertCommand1.Connection = OleDbConnection1
OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("@ID", System.Data.OleDb.OleDbType.Integer, 4, "ID"))
OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("@Nameb", System.Data.OleDb.OleDbType.VarChar, 80, "Nameb"))
OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("@Imageb", System.Data.OleDb.OleDbType.VarBinary, 2147483647, "Imageb"))
Dim FileStream1 As System.IO.FileStream
Dim FileInfo1 As System.IO.FileInfo
' READ THE FILE INTO MEMORY
FileInfo1 = New System.IO.FileInfo("C:\jessica-alba-100204-01.jpg")
FileStream1 = New System.IO.FileStream("C:\jessica-alba-100204-01.jpg", IO.FileMode.Open)
Dim Array1(CInt(FileInfo1.Length - 1)) As Byte
Debug.WriteLine(FileStream1.Read(Array1, 0, CInt(FileInfo1.Length)))
FileStream1.Close()
Dim String1 As String
Dim ASCIIEncoding1 As New System.Text.ASCIIEncoding()
String1 = ASCIIEncoding1.GetString(Array1)
' RUN THE COMMAND
OleDbInsertCommand1.Parameters("@ID").Value = 1
OleDbInsertCommand1.Parameters("@Nameb").Value = "Jessica_Alba"
OleDbInsertCommand1.Parameters("@Imageb").Value = Array1
OleDbInsertCommand1.ExecuteNonQuery()
OleDbConnection1.Close()
End Sub
Private Sub Display1()
'CONNECTION
Dim OleDbConnection1 As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documen" & _
"ts and Settings\Administrator\My documents\Access1.mdb;.......")
OleDbConnection1.Open()
Dim OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand()
Dim OleDbReader1 As System.Data.OleDb.OleDbDataReader
'Access (OleDb)
OleDbSelectCommand1.CommandText = "SELECT Imageb FROM Table1 WHERE ID = 1"
OleDbSelectCommand1.Connection = OleDbConnection1
' RUN THE COMMAND
OleDbReader1 = OleDbSelectCommand1.ExecuteReader()
OleDbReader1.Read()
Dim Len1 As Long = OleDbReader1.GetBytes(0, 0, Nothing, 0, 0)
Dim Array1(CInt(Len1)) As Byte
OleDbReader1.GetBytes(0, 0, Array1, 0, CInt(Len1))
OleDbConnection1.Close()
Dim MemoryStream1 As New System.IO.MemoryStream(Array1)
Dim Bitmap1 As New System.Drawing.Bitmap(MemoryStream1)
Dim Form1 As New System.Windows.Forms.Form()
Dim PictureBox1 As New System.Windows.Forms.PictureBox()
PictureBox1.Dock = DockStyle.Fill
PictureBox1.Image = Bitmap1
Form1.Controls.Add(PictureBox1)
Form1.ShowDialog()
End Sub
SQL Server 2000...
CREATE TABLE [Table1] (
[ID] [int] NOT NULL ,
[Name] [varchar] (80) NOT NULL ,
[<pre>Image] [image</pre>] NULL ,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Private Sub Insert1()
'CONNECTION
Dim SqlConnection1 As New System.Data.SqlClient.SqlConnection("data source=(local);initial catalog=MyDB;password" & _
"=MyPass;persist security info=True;user id=sa;workstation id=SRCDEV;packet size=4096")
SqlConnection1.Open()
Dim SqlInsertCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
'SQL (SqlServer)
SqlInsertCommand1.CommandText = "INSERT INTO Table1(ID, Name, Image) VALUES (@ID, @Name, @Image); SELECT ID, Name, Image FROM Table1 WHERE (ID = @ID)"
SqlInsertCommand1.Connection = SqlConnection1
SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ID", System.Data.SqlDbType.Int, 4, "ID"))
SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.VarChar, 80, "Name"))
SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Image", System.Data.SqlDbType.VarBinary, 2147483647, "Image"))
Dim FileStream1 As System.IO.FileStream
Dim FileInfo1 As System.IO.FileInfo
' READ THE FILE INTO MEMORY
FileInfo1 = New System.IO.FileInfo("C:\jessica-alba-100204-01.jpg")
FileStream1 = New System.IO.FileStream("C:\jessica-alba-100204-01.jpg", IO.FileMode.Open)
Dim Array1(CInt(FileInfo1.Length - 1)) As Byte
Debug.WriteLine(FileStream1.Read(Array1, 0, CInt(FileInfo1.Length)))
FileStream1.Close()
Dim String1 As String
Dim ASCIIEncoding1 As New System.Text.ASCIIEncoding()
String1 = ASCIIEncoding1.GetString(Array1)
' RUN THE COMMAND
SqlInsertCommand1.Parameters("@ID").Value = 1
SqlInsertCommand1.Parameters("@Name").Value = "Jessica_Alba"
SqlInsertCommand1.Parameters("@Image").Value = Array1
SqlInsertCommand1.ExecuteNonQuery()
SqlConnection1.Close()
End Sub
Private Sub Display1()
'CONNECTION
Dim SqlConnection1 As New System.Data.SqlClient.SqlConnection("data source=(local);initial catalog=MyDB;password" & _
"=MyPass;persist security info=True;user id=sa;workstation id=SRCDEV;packet size=4096")
SqlConnection1.Open()
Dim SqlSelectCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
Dim SqlReader1 As System.Data.SqlClient.SqlDataReader
'SQL (SqlServer)
SqlSelectCommand1.CommandText = "SELECT Image FROM Table1 WHERE ID = 1"
SqlSelectCommand1.Connection = SqlConnection1
' RUN THE COMMAND
SqlReader1 = SqlSelectCommand1.ExecuteReader()
SqlReader1.Read()
Dim Len1 As Long = SqlReader1.GetBytes(0, 0, Nothing, 0, 0)
Dim Array1(CInt(Len1)) As Byte
SqlReader1.GetBytes(0, 0, Array1, 0, CInt(Len1))
SqlConnection1.Close()
Dim MemoryStream1 As New System.IO.MemoryStream(Array1)
Dim Bitmap1 As New System.Drawing.Bitmap(MemoryStream1)
Dim Form1 As New System.Windows.Forms.Form()
Dim PictureBox1 As New System.Windows.Forms.PictureBox()
PictureBox1.Dock = DockStyle.Fill
PictureBox1.Image = Bitmap1
Form1.Controls.Add(PictureBox1)
Form1.ShowDialog()
End Sub
Good luck!
Hackman
