[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

problems uploading files  to SQL database

Posted on 2004-11-19
3
Medium Priority
?
436 Views
Last Modified: 2008-02-20
I am trying to use a script to uplad word docs to a database using a script I found on the net.  however I keep getting an error msg I can't get past

Microsoft Cursor Engine error '80040e21'

Multiple-step operation generated errors. Check each status value.

/webpublishing/publishing/db-file-to-binary.asp, line 96


The scipt is below

<%@ Language=VBScript %>
<% option explicit %>
<%Response.Expires = 0
Response.CacheControl = "no-cache"%>



<%
'Simple upload to database.
'Suitable for small files - up to 20% of physical server memory
'This sample works with any objDBConection - MDB (JetOLEDB, ODBC)
' MS SQL (MSDASQL/ODBC) etc.

Server.ScriptTimeout = 240
'Simple upload to database

'Create upload form
'Using Huge-ASP file upload
'Dim Form: Set Form = Server.CreateObject("ScriptUtils.ASPForm")
'Using Pure-ASP file upload
Dim Form: Set Form = New ASPForm %><!--#INCLUDE FILE="_upload.asp"--><%

Server.ScriptTimeout = 1000
Form.SizeLimit = 1024*1024'1MB

'was the Form successfully received?
Const fsCompletted  = 0

If Form.State = fsCompletted Then 'Completted

  'Open objDBConection to database
Dim objDBCon, objDBRSExample, RS
Dim      strConnectionString
Dim strSQLstmt
strConnectionString = "Provider=SQLOLEDB;Data Source=CVNT158;Initial Catalog=WebPublishing;Integrated Security=SSPI"
'Only opent the objDBConection at the point where you need it!
Set objDBCon = Server.CreateObject("ADODB.Connection")
objDBCon.CursorLocation = 3 'Read/write cursor
objDBCon.Open strConnectionString
Set RS = Server.CreateObject("ADODB.RecordSet")
'Ideally use stored procedure and the ADO Command object - speak to me if you need examples of these however
'to illustrate the objDBConection works ok...
strSQLstmt = "SELECT * FROM WebPublishing.dbo.upload"
RS.open strSQLstmt, objDBCon, 3 , 3

  RS.AddNew

    'Store extra form info.
    RS("Description") = Form("Description")
    RS("Title") = Form("Title")

    'Add file from source field 'SourceFile' to table field 'Data'
     RS("Data") = Form("SourceFile").ByteArray

            'Some OLEDB drivers (Oracle) requires AppendChunk method
            'Please use next line instead of 'RS("Data") = Form("SourceFile").ByteArray'
   'RS("Data").AppendChunk Form("SourceFile").ByteArray

            'One-block assigning/AppendChunk is suitable for small files
            '(<20% physical server memory). Plese see documentation to store
            '10th megabytes or more in database.

    'Store technical informations
    RS("ContentType") = Form("SourceFile").ContentType
    RS("SourceFileName") = Form("SourceFile").FileName
    RS("DataSize") = Form("SourceFile").Length

    RS("UploadDT") = Now()
  RS.Update
  RS.Close
  objDBCon.Close
      response.write "<Font color=green><br>File " & Form("SourceFile").FileName & " (" & Form("SourceFile").Length & "B) was stored to a database"
  response.write "<br>See Upload table in " & Server.MapPath("upload.mdb") & " database.<br><br></Font>"
ElseIf Form.State > 10 then
  Const fsSizeLimit = &HD
  Select case Form.State
            case fsSizeLimit: response.write  "<br><Font Color=red>Source form size (" & Form.TotalBytes & "B) exceeds form limit (" & Form.SizeLimit & "B)</Font><br>"
            case else response.write "<br><Font Color=red>Some form error.</Font><br>"
  end Select
End If'Form.State = 0 then

Function GetConnection()
  dim objDBCon, strConnectionString : Set objDBCon = Server.CreateObject("ADODB.Connection")
  GetConnection = "Provider=SQLOLEDB;Data Source=CVNT158;Initial Catalog=WebPublishing;Integrated Security=SSPI"
  set GetConnection = objDBCon
  objDBCon.CursorLocation = 3 'Read/write cursor
      

 
      
end function

function CreateUploadTable(Conn)
  'This function creates upload table for MSSQL 6.5/7.0/2000
  dim SQL
  SQL = SQL & "CREATE TABLE Upload ("
  SQL = SQL & "      UploadID int IDENTITY (1, 1) NOT NULL ,"
  SQL = SQL & "      UploadDT datetime NULL ,"
  SQL = SQL & "      RemoteIP char (15) NULL ,"
  SQL = SQL & "      ContentType char (64) NULL ,"
  SQL = SQL & "      SouceFileName varchar (255) NULL ,"
  SQL = SQL & "      Title varchar (255) NULL ,"
  SQL = SQL & "      Description text NULL ,"
  SQL = SQL & "      Data image NULL "
  SQL = SQL & ")"
  objDBCon.Execute SQL
end function

%>  

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
 <TITLE>ASP huge file upload sample.</TITLE>
 <STYLE TYPE="text/css"><!--TD      {font-family:Arial,Helvetica,sans-serif }TH      {font-family:Arial,Helvetica,sans-serif }TABLE      {font-size:10pt;font-family:Arial,Helvetica,sans-serif }--></STYLE>
 <meta name="robots" content="noindex,nofollow">
</HEAD>
<BODY BGColor=white>


<Div style=width:600>
<TABLE cellSpacing=0 cellPadding=0 width="100%" border=0>
 
  <TR>
    <TH noWrap align=left width="20%" bgColor=khaki>&nbsp;<A
      href="http://www.motobit.com/help/scptutl/upload.asp">Power ASP
      file upload</A> - upload to database, store file to binary data field&nbsp;</TH>
    <TD>&nbsp;</TD></TR></TABLE>
<TABLE cellSpacing=2 cellPadding=1 width="100%" bgColor=white border=0>
 
  <TR>
    <TD colSpan=2>
      <P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;This sample demontrates using
      of Huge-ASP file upload to store files in server-side database.
                  The sample ASP file is running with Microsoft.Jet.OLEDB (MDB) objDBConection, but you can use it
                  with any other SQL server or file drivers (MS SQL server, Oracle, MySQL, FoxPro driver, etc.).

                  </P>
<P>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;You can select source file, write some title and description of the file.
Title, description, file name, file size and file contents are stored in server-side database along with current upload time.
</P>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Form size limit is <%=Form.SizeLimit%>B (<%=Form.SizeLimit \ 1024 %>kB - you can change it, see source) .
  </TD></TR></TABLE>

<TABLE cellSpacing=1 cellPadding=3 bordercolor=silver bgcolor=GAINSBORO width="" border=1>
<form method=post ENCTYPE="multipart/form-data">
<TR>
 <TD>&nbsp;</TD>
 <TD Align=Right><input type="submit" Name="Action" value="Upload the file &gt;&gt;"></TD>
</TR>
<TR>
 <TD>File to upload</TD>
 <TD><input type="file" name="SourceFile"></TD>
</TR>
<TR>
 <TD>Title</TD>
 <TD><input size="60" name="Title" value="Title of the file."></TD>
</TR>
<TR>
 <TD>Description</TD>
 <TD><textarea cols="60" rows="8" name="Description">Type description of the file.</textarea></TD>
</TR>

</form></Table>

<HR COLOR=silver Size=1>
<CENTER>
<FONT SIZE=1>© 1996 – <%=year(date)%> Antonin Foller, <a href="http://www.motobit.com">Motobit Software</a>, e-mail <A href="mailto:help@pstruh.cz" >help@pstruh.cz</A>
<br>To monitor current running uploads/downloads, see <A Href="http://www.motobit.com/help/iistrace/iis-monitor.asp">IISTracer - IIS real-time monitor</A>.
</FONT>

</CENTER>
</Div>
</BODY></HTML>
0
Comment
Question by:garthpyper
3 Comments
 
LVL 4

Expert Comment

by:divt
ID: 12624382
Try removing this line:

objDBCon.CursorLocation = 3 'Read/write cursor
0
 

Author Comment

by:garthpyper
ID: 12624551
now it is not displaying an error, nor is it working
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 12636133
The GetChunk/AppendChunk methods have been largely deprecated in favor of the Stream object introduced with ADO v2.5.

Use this code (untested):

Sub AddImage(rs, ByVal FileName)
Dim stm

Set stm = Server.CreateObject("ADODB.Stream")
With stm
  .Type = adTypeBinary
  .Open
  .LoadFromFile FileName
 
  'Insert the binary object into the table.
  rs.AddNew
  rs.Fields("Data").Value = .Read
  rs.Update
  .Close
End With
Set stm = Nothing

End Sub

You would call this procedure as follows:

Dim rs

Set rs = Server.CreateObject("ADODB.Recordset")
With rs
   .ActiveConnection = MY_CONN_STRING
   .Source = "SELECT Data FROM WebPublishing.dbo.upload Where 1=0"
   .CursorType = adOpenForwardOnly
   .LockType = adLockOptimistic
   .Open ,,,, adCmdText
   AddImage rs, FileName
   .Close
End With
Set rs = Nothing

As you can see it is a lot simpler.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question