Set rs2 = New ADODB.Recordset
 rs2.Open "select * from company_name", db, adOpenKeyset, adLockBatchOptimistic
 If pdfpath <> "" Then
     Set pdffile = New ADODB.Stream
     pdffile.Type = adTypeBinary
     pdffile.LoadFromFile pdfpath
     rs2.Fields("pdf") = pdffile.Read
     rs2!co_no = Val(co_no.Text)
     rs2!co_name = co_name.Text



 Set pdffile = Nothing
    Set rs2 = Nothing
    MsgBox "Saved"
       End If

       my error is Multiple - step OLEDB generated errors, check each  oledb status value , if available no work was done 

       any help plzzz
3 Years
Discussion Span
Last Post by Goutami_1

That can be caused by a bad connection string. What is your connection string and what kind of database are you connecting to? What line is throwing the error?

Edited by Reverend Jim

db.ConnectionString = "Provider=SQLOLEDB;Persist Security Info=TRUE" & _
                             ";Password=xxx" & _
                            ";User ID=sa ;Initial Catalog=xxx ;Data Source=xxxxx,1433"

this is my connection string and i used sql server 2008 

the error in line : 9 :  rs2.Fields("pdf") = pdffile.Read 

pdf data type : binary

It appears you are trying to change data in the record set with out and edit method
In the line before the error add


Why not just run an UPDATE query against the table? Why bother updating the recordset? What exactly are you trying to do?


what i am trying to do is to save a pdf file from a user computer to sql database , but i still facing the error.


I tried this and it works

Dim filename As String = "D:\test.pdf"

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")

'first insert a record that will hold the data

cn.Execute("insert into PDFStore (filename) values('" & filename & "')")

'now select just that record from the table

rs.Open("Select * from pdfstore where filename = '" & filename & "'", cn, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic)

'load the binary data field with the PDF data

Dim mstream As New ADODB.Stream
mstream.Type = StreamTypeEnum.adTypeBinary
rs.Fields("contents").Value = mstream.Read

'update the database record with the new data



I've never had to work with binary data so while this method works, it may be a tad clumsy. If I can find a way to do this in one step (rather than insert, fetch, modify, update) I'll post it later.

My test table was

    filename        varchar(256)   primary key
    Contents        varbinary(MAX) null

Edited by Reverend Jim


OK. I have an easier way. Try

Dim filename As String = "D:\test.pdf"

Dim cn As New ADODB.Connection

Dim query As String = "INSERT INTO PDFStore (filename,Contents) " _
                    & "SELECT '" & filename & "', * FROM " _
                    & "OPENROWSET(BULK '" & filename & "', SINGLE_BLOB) RS"

cn.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")

The RS at the end of the OPENROWSET line is just a placeholder, like assigning a temporary alias to a table to minimize typing. It can be any valid alias name. You could even make it the same as the tablename for clarity. My example use ADO but because all the gory details are in the query (and are handled by the DBMS) it should also work with OleDB and SqlClient (which would allow you to use parameterized queries).

Edited by Reverend Jim


i am trying the code but still have an error

Dim filename As String
filename = "D:\submit.pdf"
Dim cn As New ADODB.Connection
Dim query As String
query = "INSERT INTO PDFStore (filename,Contents) " _
                    & "SELECT '" & filename & "', * FROM " _
                    & "OPENROWSET(BULK '" & filename & "', SINGLE_BLOB) RS"
 cn.Open ("Driver={SQL Server};Server=xxxxx;Database=xxxx;Trusted_Connection=yes;")
 cn.Execute query

 my error is : 

 SQL Server implicit conversion from datatype varchar to varbinary is not allowed  use the CONVERT function to run this query. 

 the error in ths line : cn.Execute(query)

 any help plz i need to solve it..

i defined table like what you are doing
filename varbinary(250)
Contents varbinary(MAX) ??

what i should do to solve it


okay now the code working 100% i was the data type of filename was varbinary and i change it to varchar , but now how i can open the file or view it ??


Hi Im new to VBA plz any one help me (im getting run-time error :80040e21 odbc driver does not support the requested properties, and it is pointing to this line "rs.Open strSql, oConn, 3, 4" ) Can any one help me on this.
That will be a great help,

Private Sub InsertData()
Dim rs As ADODB.Recordset
Dim rowCursor As Integer
Dim Book1
Dim QueryTimeout As Integer

Dim strSql As Variant
Set rs = New ADODB.Recordset


    With Sheet1
        For rowCursor = 2 To 20

            strSql = "INSERT INTO Activities (SiNo, MilestoneName, Tasks, DurationDays, Precedence, StartDate, EndDate) " & _
                "VALUES(" & esc(.Cells(rowCursor, 1)) & ",'" & esc(.Cells(rowCursor, 2)) & "','" & esc(.Cells(rowCursor, 3)) & "'," & esc(.Cells(rowCursor, 4)) & "," & esc(.Cells(rowCursor, 5)) & ",'" & esc(.Cells(rowCursor, 6)) & "','" & esc(.Cells(rowCursor, 7)) & "')"

                 MsgBox strSql
                 QueryTimeout = 0
         rs.Open strSql, oConn, 3, 4
    End With
End Sub

Edited by Goutami_1

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.