run time error 80040e21

[SaRa Ahmad]

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

     '''



     pdffile.Close

 Set pdffile = Nothing
    rs2.Update
    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

[Reverend Jim]

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?

[SaRa Ahmad]

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

[Klahr_R]

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

   rs2.Edit

[Reverend Jim]

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

[SaRa Ahmad]

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.

[Reverend Jim]

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
mstream.Open()
mstream.LoadFromFile(filename)
rs.Fields("contents").Value = mstream.Read

'update the database record with the new data

rs.Update()

rs.Close()
cn.Close()

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

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

[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;")
cn.Execute(query)
cn.Close()

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

[SaRa Ahmad]

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

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

[Reverend Jim]

Can you post your database structure (field definitions, etc)?

[SaRa Ahmad]

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

what i should do to solve it

[SaRa Ahmad]

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

[Goutami_1]

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,
Thanks

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


        ConnectDB


    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
        Next
    End With
End Sub