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 ....
kennethckyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.