Re: Is This Possible ... ? Yes - Upload images to an Access database

Hi Wayne,

Okay, here's the code that should get you going. You'll need an Access
database (called imagedb.mdb) in the App_Data folder with this schema:

id: AutoNumber
FileName:Text
FileSize: Number (long integer)
ContentType: Text
FileData: OLE Object

There are two ASP.NET files. Upload.aspx handles the upload and display.
Imagefetch.aspx is a helper page that provides the image to the datagrid.

This is rough code - barebones stuff with no error checking to speak of.
Most of the code is ripped off from other articles, especially from

File Uploading to Access Database using ASP.NET
by Faisal Khan.

http://www.stardeveloper.com/articles/display.html?article=2003031201&page=1

Anyway, let us know if it helps?

Ken
Microsoft MVP [ASP.NET]

-- upload.aspx
<%@ page language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>

<script runat="server">

Protected Sub btnDisplay_Click _
(ByVal sender As Object, ByVal e As System.EventArgs)

' Gets the image data and displays it
' in the datagrid
Dim strConnection As String
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" _
& Server.MapPath("App_Data/imagedb.mdb")
Dim con As New Data.OleDb.OleDbConnection(strConnection)
Dim cmdtext As String = "SELECT ID, FileName," _
& " FileSize, ContentType FROM Files"
Dim cmd As New Data.OleDb.OleDbCommand(cmdtext, con)
Dim ds As New Data.DataSet
Dim da As New Data.OleDb.OleDbDataAdapter _
(cmdtext, strConnection)
da.Fill(ds)
dg2.DataSource = ds
dg2.DataBind()
End Sub


Protected Sub btnUpload_Click _
(ByVal sender As Object, ByVal e As System.EventArgs)
' Puts uploaded file in Access database
' Adapted for VB by Ken Cox [MVP] from
' File Uploading to Access Database using ASP.NET
' by Faisal Khan.
' http://www.stardeveloper.com/articles/display.html
'?article=2003031201&page=1
Dim strConnection As String
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" _
& Server.MapPath("App_Data/imagedb.mdb")
If Request.Files.Count > 0 Then
Dim files As HttpFileCollection
Dim afile As HttpPostedFile
files = Request.Files
afile = files(0)
Dim contentType As String = afile.ContentType
Dim filename As String = ""
Dim filelength As Integer = afile.ContentLength
Dim fileData As Byte() = New Byte(filelength) {}
Dim lastpos As Integer
lastpos = afile.FileName.LastIndexOf("\")
filename = afile.FileName.Substring(lastpos + 1)
afile.InputStream.Read(fileData, 0, filelength)
Dim con As New Data.OleDb.OleDbConnection(strConnection)
Dim cmdtext As String = "INSERT INTO Files(FileName," & _
" FileSize, ContentType, FileData) VALUES " & _
" (@FileName, @FileSize, @ContentType, @FileData)"
Dim cmd As New Data.OleDb.OleDbCommand(cmdtext, con)
Dim pms As Data.OleDb.OleDbParameterCollection
pms = cmd.Parameters
pms.Add("@FileName", Data.OleDb.OleDbType.VarChar, 50)
pms.Add("@FileSize", Data.OleDb.OleDbType.Integer)
pms.Add("@ContentType", Data.OleDb.OleDbType.VarChar, 50)
pms.Add("@FileData", Data.OleDb.OleDbType.VarBinary)

pms("@FileName").Value = filename
pms("@FileSize").Value = filelength
pms("@ContentType").Value = contentType
pms("@FileData").Value = fileData

con.Open()
cmd.ExecuteNonQuery()
con.Close()
End If
End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml";>
<head runat="server">
<title>Upload and View Images in MS Access</title>
</head>
<body>
<form id="Form1" runat="server" enctype="multipart/form-data">
<input name="thefile" type="file"><br>
<br />
<asp:button id="btnUpload" runat="server" text="Upload"
onclick="btnUpload_Click" />&nbsp;<br />
<br />
<asp:button id="btnDisplay" runat="server"
onclick="btnDisplay_Click" text="View Images" />&nbsp;<br />
<br />
<asp:datagrid id="dg2" runat="server" autogeneratecolumns="False">
<columns>
<asp:boundcolumn datafield="filename" headertext="Filename"
readonly="True"></asp:boundcolumn>
<asp:boundcolumn datafield="Filesize" headertext="Filesize"
readonly="True"></asp:boundcolumn>
<asp:boundcolumn datafield="contenttype"
headertext="Contenttype" readonly="True"></asp:boundcolumn>
<asp:templatecolumn headertext="Image">
<itemtemplate>
<asp:image runat="server" imageurl='<%#
"imagefetch.aspx?id=" & DataBinder.Eval(Container, "DataItem.id") %>' />
</itemtemplate>
</asp:templatecolumn>
</columns>
</asp:datagrid>
<br />
</form>
</body>
</html>

-- imagefetch.aspx

<%@ Page Language="VB" %>
<script runat="server">
Protected Sub Page_Load _
(ByVal sender As Object, ByVal e As System.EventArgs)
' Gets the image from the Access
' Database for inclusion in another page
' Adapted from an MS KB article by Ken Cox [MVP]
'
Dim recno As Integer
If Request("id") = "" Then
Exit Sub
End If
recno = Request("id")
Dim strConnection As String
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" _
& Server.MapPath("App_Data/imagedb.mdb")
Dim con As New Data.OleDb.OleDbConnection _
(strConnection)
Dim da As New Data.OleDb.OleDbDataAdapter _
("Select filedata From files where id=" & _
recno.ToString, con)
Dim MyCB As New Data.OleDb.OleDbCommandBuilder(da)
Dim ds As New Data.DataSet()
con.Open()
da.Fill(ds, "images")
Dim myRow As Data.DataRow
myRow = ds.Tables("images").Rows(0)
Dim MyData() As Byte
MyData = myRow("filedata")
Response.Buffer = True
Response.ContentType = "Image/JPEG"
Response.BinaryWrite(MyData)
MyCB = Nothing
ds = Nothing
da = Nothing
con.Close()
con = Nothing
End Sub
</script>



"Wayne Smith" <waynesmith1971(NoSpam)@hotmail.com> wrote in message
news:Omwi2WylGHA.492@xxxxxxxxxxxxxxxxxxxxxxx
I've come up against a major headache that I can't seem to find a solution
for but I'm sure there must be a workaround and I would really be grateful
of any help.

I'm currently building a web site for a small club I belong to and one of
the features I would like to include is the ability to allow users to
upload image files.

unfortunately the servers web root www folder only allows READ and EXECUTE
permissions, which makes it impossible to allow a user to upload a
gif/jpeg image. When I FTP into the site however, I can immediately see
two folders:

databases
www

the www folder is the root folder and all web pages need to be placed
there, but the databases folder does have Read, WRITE and Execute
permissions and I have been reliably informed that some ASP.NET code would
allow a user to send a picture file to an Access database in this folder,
the problem I have is not knowing how to reference it because it's not
directly accessible from the Internet - i.e. www.website.co.uk/databases
would not work.

The server is running ASP.NET 1.1 and the permissions setup has to stay
the way it is, I'm not able to get my host to change the permissions on
the root folder so I have to try and make use of the databases folder
instead. For arguments sake lets also assume I already have a simple
Access database called images.mdb with one table called Images, which in
turn contains two fields, one called ID (Autonumber) (Primary Key) and
another called images (OLE Object)

What I would really like, if possible, is a very simple sample page with a
text box and a Browse button, with a second button to 'Upload' the file
once selected, plus any supporting pages that may be required to complete
the operation, i.e.:

selectfile.aspx (simple page to select image file and send to database)
uploadfile.aspx (works behind the scenes to transfer the file)
confirmation.aspx (a confirmation page to indicate the transfer either
succeeded or failed)

I'm not a natural programmer by any stretch of the imagination, which is
why I'm being a bit cheeky and asking for a very simple layout to
accomplish this task, or a straight forward dummies tutorial which takes
into consideration the problem with uploading to a database outside of the
root web.

If anybody can help me solve this problem, I will forever be indebted to
you. I have searched the Internet for over a week and remarkably I cannot
find an answer to this problem, it's now driving me nuts - I just want to
solve this so I can move on with the rest of the site design.

Any help would be greatly appreciated.

Many thanks in advance
Wayne




.

Loading