Link to home
Start Free TrialLog in
Avatar of davidi1
davidi1Flag for India

asked on

upload and retrieve files in sql through classic asp

Team -

i have seen many articles on uploading and retrieving files in SQL from ASP.net. I was wondering if we have any code examples for the same on classic asp.

any assistance would be great.
Avatar of sybe
sybe

What has SQL to do with it? Do you want to store uploaded files as binary in a database?
Avatar of davidi1

ASKER

if there's a file from classic asp which i want to upload to db, sql needs to store the data. i do have sql query to store data in sql server. how do i retreive the data?
Use the ADODB.Stream object:
http://support.microsoft.com/kb/258038
Assuming that I understand your question correctly, you would first retrieve the data using standard query:

SQL = "field1, field2, etc " _
    & " FROM yourTable " _
    & " WHERE...   " _ 
& "ORDER BY field1DESC "
'response.write sql
'response.end
Set RS = conn.Execute( SQL )

Open in new window


Then loop through the table to get all records. Here, I am using getrows because it is faster, much faster:

'If no records found, let user know:

If RS.EOF Then
	Session("Message") = "<font color='firebrick'>No records found.</font>"
    response.end
End If

Open in new window



allRecs = RS.GetRows( ) ' get all records into an *ARRAY*
RS.Close ' no longer needed

Set FSO = Server.CreateObject("Scripting.FileSystemObject")

For row = 0 To UBound( allPrograms, 2 )
    field1= allRecs ( 0, row )
    field2= allRecs ( 1, row )
etc
etc

    ' show the directory where files are stored and tie each file to each record
    Set fldr = FSO.GetFolder(Server.MapPath("ProgramFiles/" & programTitle ))
    For Each fl In fldr.Files
       pFile = programTitle & "/" & fl.Name
       etc
     etc
%>
 <center><A HREF="getFile.asp?fname=<%=Escape(pFile)%>"><%=pFile%></A></br></center>
<%
    Next
%>
<%
Next

%>

Open in new window


Not tested so, may be some bugs but the context is solid.
Storing and retrieving BLOB data is not as simple as you may think, it has always been a headache with classic asp.  I do not store BLOB data in database files due to the stress it places on the server, I will store the images in a folder and then place the path to the file in the database.  Moving around binary data especially storing it in a database is just a nuisance and should be avoided.

There are two basic steps, first is uploading the data, regardless of what you do with it once it gets to the server.  In classic asp there are components and scripts that will transfer the binary data, you can read more about these at 4Guys Upload Help page.  Microsoft also wrote a KB article on the subject, http://support.microsoft.com/kb/299692.  

Reading data from a database is a basic process, there are a number of scripts available to detail how it's done, 4Guys has a good article on retrieving BLOB data, and there is a packaged example at FreeVBcode, although it uses MDB file, this is easily changed.

And finally, here is an article that covers both the uploading and the displaying of BLOB data.  http://www.stardeveloper.com/articles/display.html?article=2001033101
ASKER CERTIFIED SOLUTION
Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of davidi1

ASKER

Thanks sammySeltzer. please share any codes if you may have for upload an display of data.

store data in folder, save path in db & display data via asp.

Thanks,
David
Thats the answer you accepted?  How does that solve your question?
Sorry davidi1, I have not had time to get back here.

This is aspSmartUpload component. If you are uploading to a hosting company, be sure to find out what upload compoent they use.

'****html:

<html>
<head>
<title>Health Products</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="styles.css" type="text/css">
</head>

 <body bgcolor="#ffffff" text="#000000" link="#CC3300" vlink="#CC3300" alink="#999999" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
     <br><br>
      <table width="540" border="0" cellspacing="0" cellpadding="0" name="table_heading" align="center">
        <tr>
          <td bgcolor="#FFFFFF">
            <FORM METHOD="POST" NAME="myform" ENCTYPE="multipart/form-data" ACTION="processAssignmentOld.asp">
            <table width="540" border="0" cellspacing="2" cellpadding="2">
              <tr valign=top>
                <td class="body1"><div align="right">Program Title</div></td>
                <td><input name="ptitle" type="text" CLASS="ilong" size="48">&nbsp;(eg. LPN)</td>
              </tr>
              <tr valign=top>
                <td class="body1"><div align="right">Program Description</div></td>
                <td><input name="briefDescr" type="text" CLASS="ilong" size="48"></td>
              </tr>
              <tr valign=top>
                <td class="body1"><div align="right">File Name</div></td>
                <td><input name="filename" type="text" CLASS="ilong" size="48">&nbsp;(eg.assignment1)</td>
              </tr>
              <tr valign=top>
                   <TD align="right" CLASS="standardtext" width="0%" valign="top" nowrap><div style="padding-top: 3px">Attachment</div></TD>
                   <TD id="xattachement"><INPUT NAME="writeattach[]" TYPE="FILE" value="" CLASS="ilong" SIZE=40 style="width: 85%">&nbsp;<INPUT TYPE="button" NAME="xmorebutton" VALUE="More" onclick="addattachment(this)" CLASS="fr_button"><br>
                     <div id="xattachement1"></div>
                   </TD>
              </tr>
              <tr valign=top>
                <td class="body1" div align="right">Path</td>
                <td> <select name="FilePath" style="font-family: Verdana; font-size: 8pt; color:#FF0000">
                      <option value="_CNA">Save to CNA folder</option>
                      <option value="PN">Save to PN folder</option>
                      <option value="EKG">Save to EKG folder</option>
                      <option value="LPN">Save to LPN folder</option>
                      <option value="NT">Save to Nursing Tech folder</option>
                      <option value="HIV">Save to HIV/Aids folder</option>
                      <option value="Phlebotomy">Save to Phlebotomy folder</option>
                    </select><br>
                </td>
              </tr>
              <tr valign=top>
                <td class="body1"><img src="images/layout/spacer.gif" height="10" width="1"></td>
                <td></td>
              </tr>
              <tr valign=top>
             <td class="body1">&nbsp;</td>
             <td><input type=button name='Submit' value='Post your lecture' class='button' onClick="{document.myform.submit();}"></td>
            </tr>
           </table>
          </FORM>
         </td>
        </tr>
      </table>
  </body>
</html>

Open in new window


'*** The upload code

<%@ Language=VBScript%>
<%Response.buffer=true%>
<%
Set mySmartUpload = Server.CreateObject("aspSmartUpload.SmartUpload")
mySmartUpload.Upload
mySmartUpload.TotalMaxFileSize = 1000000
mySmartUpload.AllowedFilesList = "jpg,pdf,doc,ppt,txt,xls"
'Response.Write("Files allowed=" & mySmartUpload.AllowedFilesList & "<br>")

Set oRs=Server.CreateObject("ADODB.Recordset")
 Set conn = Server.CreateObject("ADODB.Connection")
 conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
 "DATA SOURCE=" & server.mappath("db/mydb.mdb")
 conn.Open

nOKCount = 0
nErrCount = 0
For Each myFile In mySmartUpload.Files
  If Not(myFile.IsMissing) Then
     'get the new filename
     Filename = myFile.fileName
     Filesize = myFile.Size
     contentType = myFile.ContentType
     'response.write Filename
     'response.end


  oRs.open "Select * from assignmentUpload where filename='" & Filename & "'",conn,1,3

 'store the jpeg file on the server
' Set FSO = Server.CreateObject("Scripting.FileSystemObject")
' FSO.CreateTextFile Server.MapPath(mySmartUpload.Form("FilePath") & "\" & Filename)
  myFile.SaveAs(Server.MapPath(mySmartUpload.Form("FilePath") & "\" & Filename))

  'then store rest of data into the db
  if oRs.eof then
   oRs.addnew
  end if
    oRs("filesize") = Filesize
    oRs("contentType") = contentType
    myFile.FileToField oRs.Fields("filedata")
    oRs("filename") = Filename
    oRs("programtitle") = mySmartUpload.form("ptitle")
    oRs("programDescription") = mySmartUpload.form("briefDescr")
    oRs("programPeriod") = mySmartUpload.form("progperiod")
    oRs.Update
    nOKCount = nOKCOunt + 1
    Response.Redirect "uploadSuccess.asp"
  End If
next

Set mySmartUpload = Nothing


%>
This is actual working copy.

Modify to suit your needs.

if you have problem, ask

Forgot to upload the component. Here it is. Unzip, read the ReadMe on how to register the dlls and you are good to go.

Well, I can't attach the files due to dlls not allowed but  you can download them here:

http://aspsmartupload.net/