Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2258
  • Last Modified:

ASPSmartUpload file.FileToField problem!!!!!!

HI could you tell me what is the problem with my code below?

****************************************
<%
Set uPBinaryFile = Server.CreateObject("aspSmartUpload.SmartUpload")
uPBinaryFile.Upload

set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open = "Driver={mySQL ODBC 3.51 Driver};Server=kenneth;database=ecep;uid=root;pws=123456"

set rs = Server.CreateObject("ADODB.Recordset")
sql = "select image,filename,imagetype from mainpage_layout"
Set rs.ActiveConnection = dbConn
rs.Source = sql
rs.LockType = 3
rs.Open
For each file In uPBinaryFile.Files
            If not file.IsMissing Then      
      rs.AddNew            
            file.FileToField rs.Fields("image") <--- LINE 19
            rs("FileName") = file.filename
            rs("ImageType") = file.ContentType                                                
            rs.update
            intCount = intCount + 1
      end if
Next
%>
<%
rs.Close
dbConn.Close
Set rs = Nothing
Set dbConn = Nothing
%>
******************************************

Error Mesg :
ADODB.Field error '800a0c93'
Operation is not allowed in this context
uploadsave.asp, line 19

LINE 19 IS "file.FileToField rs.Fields("image")"

I'm using MySQL. FYI, Access and Ms SQL 2000 are working and it only happened when i use MySQL

image datatype  = "longblob"

If I comment line 19 it work correctly

Please advice ....
0
kennethcky
Asked:
kennethcky
  • 3
1 Solution
 
Programming_GalCommented:
1) If you are using an ADODB.Recordset to UPDATE / INSERT (e.g. using the AddNew method), use a direct SQL statement instead

A quick note: NEVER store a recordset in the session object. For a discussion of this see:
 
http://www.microsoft.com/mind/1198/ado/ado.htm 
http://support.microsoft.com/?kbid=176056

Most data access tasks can be implemented by using the execute method of the connection object. Why would we want to do this? Well, for one, there is extra overhead in using a recordset object for UPDATE and INSERT functionality. This is because the provider has to translate your code into an equivalent T-SQL statement anyway (the database itself has no knowledge of "addNew" and similar methods). Also, there are many dangerous locks associated with recordsets... most of which are not necessary (especially when performing an INSERT or UPDATE). Your goal should be to get in, tweak your data, and get out as quickly as possible. Using direct statements is the quickest way to do this, since there is much less overhead and no locks associated with your activity.
 
Another benefit of using an INSERT or UPDATE statement is that it is much easier to debug. You can change conn.execute(sql) to response.write(sql) and immediately see why your statement is throwing an error. With a multi-line transaction using a recordset object, it is translated to an INSERT or UPDATE statement (inefficient!) on the DB side, so there is no straightforward way to trap errors at the code level.
 
To use the connection object, simply design a transact-SQL statement for the action you want to use and implement it like so:
 
<%
    sql = "INSERT INTO <table> (fields) VALUES (values)"
    set conn = Server.CreateObject("ADODB.Connection")
    conn.open "<connection string>"
    conn.execute sql, , 129
    ' 129 is the constant for adExecuteNoRecords + adCmdText
    ...
%>
0
 
Programming_GalCommented:

<%
    sql = "EXEC SP_doSomething @param1=1, @param2='" & var & "'"
    set conn = Server.CreateObject("ADODB.Connection")
    conn.open "<connection string>"
    set rs = conn.execute(sql)
    ...
%>
0
 
Programming_GalCommented:
2) If you are trying to binaryWrite image data to an ASP page that also has plain text content, consider retrieving this data in a separate file and pointing to it via <IMG SRC>. You can also see http://support.microsoft.com/?kbid=276488 for ways to use ADODB.Stream to return binary content from a database, without using the GetChunk method.
0
 
JimboDSCommented:
When using aspSmartUpload with the same type of setup as initially stated by 'kennethcky', I found I needed to set the cursor location to client in order to upload images and files to MySQL.  I set this in the connection object and all seemed to work fine.

objConn.CursorLocation = 3      'adUseClient

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now