Click here to Skip to main content
15,867,838 members
Articles / Programming Languages / ASP
Article

Save binary data to SQL Server

Rate me:
Please Sign up or sign in to vote.
3.50/5 (4 votes)
25 Apr 2002 108.2K   25   5
ASP can save binary data to a table in SQL Server

Introduction

First install SoftArtisans.FileUp component

to upload the files. Or you can use any other file upload component that you have access to.

Then, create the table into SQL Server</P>

SQL
create table test (
file_id int identity(1,1),
file_type varchar(50),
file_name varchar(50),
file_data image,
date_created smalldatetime)

Create the ASP File (upload.asp) to save the file into SQL Server

Html Code

HTML
<form ENCTYPE="multipart/form-data" METHOD="post" 
    ACTION="upload.asp" name="frmUpload"> 
<input type="file" size="15" src="" width="67" height="18" 
    id="FILE1" NAME="FILE1" value="Search"> 
<input TYPE="submit" NAME="btnEnviar" VALUE="Submit">
</form>

ASP Code

VBScript
Dim objUpload
Set objUpload = Server.CreateObject("SoftArtisans.FileUp")

Set MSCS = Server.CreateObject("ADODB.Connection")
MSCS .ConnectionTimeout = 90
MSCS .Open "DSN=DSN_NAME;UID=USERNAME;PWD=PASSWORD;DATABASE=DATABASE_NAME"

If objUpload.Form("FILE1") <> "" Then
    Set Rs = Server.CreateObject ("ADODB.Recordset")
    Rs.Open "test", MSCS, 2, 3

    Rs.AddNew 

    Rs("file_name") = objUpload.Form("FILE1").UserFilename
    objUpload.Form("FILE1").SaveAsBlob Rs("file_data")    
    Rs("file_type") = objUpload.Form("FILE1").contentType
    Rs("date_created") = now()
    
    Rs.upDate()

    Rs.Close 

    Response.Write "Uploaded Successfully"
End If    
Set objUpload = Nothing
%>

Now create the view_upload.asp to view the file

VBScript
<%
Set MSCS= Server.CreateObject("ADODB.Connection")
MSCS.ConnectionTimeout = 90
MSCS.Open "DSN=DSN_NAME;UID=USERNAME;PWD=PASSWORD;DATABASE=DATABASE_NAME"

Set Rs = Server.CreateObject ("ADODB.Recordset")
strSQL = "SELECT * FROM test WHERE file_id = " & request("id")
Rs.Open strSQL, MSCS, 2, 3

Response.ContentType = Rs("file_type")
Response.BinaryWrite Rs("file_data")
Rs.close
%>
To view the file saved number 1 browse to http://server.com/view_upload.asp?id=1

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
Argentina Argentina
Lic. Fernando Finelli

Comments and Discussions

 
GeneralSQL Column DataType Pin
Karthick_Accet2-Jan-09 9:56
Karthick_Accet2-Jan-09 9:56 
GeneralCool Man Pin
naveenieus20-Apr-06 0:11
naveenieus20-Apr-06 0:11 
QuestionPlease give me a way for display pictures which store in DB Access 2003 by &quot;OLE object&quot;? Pin
tuyenhnp25-Sep-05 16:52
tuyenhnp25-Sep-05 16:52 
Generalcant get to connect with SQL server 2000 Pin
jaf16-Jan-04 4:30
jaf16-Jan-04 4:30 
GeneralRe: cant get to connect with SQL server 2000 Pin
Valeria Bogdevich16-Jan-04 5:28
Valeria Bogdevich16-Jan-04 5:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.