asked on

Bianary INSERT!!!

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)
VALUES
('"&companynameInput2&"', '"&filetype&"', '"&filesubcategory&"', '"&fileName&"', '"&fileSize&"', '"&keywords&"', '"&fileData&"', '"&contentType&"')"

Any ideas???

arbert

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.....

mack1er

ASKER

I have access to the server and could create one, I just don't know about the code.

ASKER CERTIFIED SOLUTION

xmstr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mack1er

ASKER

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):

<%
' 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?????????

xmstr

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

arbert

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)

mack1er

ASKER

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.