We help IT Professionals succeed at work.

Bianary INSERT!!!

mack1er asked
Last Modified: 2008-02-01
Hello all,

I have to insert a binary file along with user entered data from a form into a SQL db. I HAVE to use an insert statement, I cannot use rs.Addnew like all the rest of the crud I've found on the web says. I am not able to use the aapendchunk statement the way I would like to. I would like to use rs("FileData").AppendChunk = fileData but I cannot, I NEED to use an insert statement... The field name is FileData, my variable to handle the file is called file data.

Here is my SQL statement:

sql = "INSERT INTO Files (companyname, filetype, filesubcategory, filename, filesize, keywords, filedata, filecontent)
('"&companynameInput2&"', '"&filetype&"', '"&filesubcategory&"', '"&fileName&"', '"&fileSize&"', '"&keywords&"', '"&fileData&"', '"&contentType&"')"

Any ideas???
Watch Question

Top Expert 2004

You really can't do it that simply....Can you create a stored proc--you can pass the binary type to the proc and basically do a simple insert that way.....


I have access to the server and could create one, I just don't know about the code.
This one is on us!
(Get your first solution completely free - no credit card required)


Nope, sorry that won't work. I keep getting:
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.

Can't use rs.addnew. Here is how I connect to my db (using a com object):
<!-- METADATA TYPE="TypeLib" UUID="{00000205-0000-0010-8000-00AA006D2EA4}" -->
  ' Open database connection
set objConn = CreateObject("Shared.Database")
set cmdText = objConn.Connect("documents")

Dim rs
dim sql

sql= "files"
Set rs = Server.CreateObject("ADODB.Recordset")
         cmdText.CommandText = sql
rs.open cmdtext

Addnew here bombs...

ANY IDEAS?????????

Is your com object just for connectivity to the database or does it provide a full range of methods for SQL data manipulation?
Top Expert 2004

This is the syntax for calling a proc with a blob:

strSPName = "dbo.Mydatabase.Update_Users"
   With cmdSQLServer
    .Connection = cnSQLServer
    .CommandText = strSPName  
    .CommandType = CommandType.StoredProcedure
  End With
 prmSQLParameter = cmdSQLServer.Parameters.Add
("@pkuserid", txtPKUserID.Text)

prmSQLParameter = cmdSQLServer.Parameters.Add
("@firstname", txtFirstName.Text)
prmSQLParameter = cmdSQLServer.Parameters.Add
("@middlename", txtMiddleName.Text)
              prmSQLParameter =
cmdSQLServer.Parameters.Add("@Photo", SqlDbType.Binary, aUserImage)


The com object allows for connection to the db and using it i have been able to select, insert, and delete so far. Just no luck with the binary file.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.