insert picture in SQL with VBA

[mhamedm2008]

hello.my english is not good.
i use the botton code for save image in SQL

Dim fDialog As Object
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
Dim varFile As Variant
Set Cn = New ADODB.Connection
Cn.Open "Provider=SQLOLEDB;data Source=.;" & _
"Initial Catalog=test;Trusted_Connection=Yes;"
Set rs = New ADODB.Recordset
rs.Open "Select * from Table_2", Cn, adOpenKeyset, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open

With fDialog
.AllowMultiSelect = False
.Title = "áØÝÇ ÊÕæíÑ ÑÇ ãÔÎÕ äãÇííÏ :"
.InitialFileName = ""

.Filters.Clear
.Filters.Add "picture file", "*.png;*.jpg;*.pdf"

If .Show = True Then
'Loop through each file selected and add it to our list box. '
For Each varFile In .SelectedItems
GetFileName = varFile
Next

Else
' MsgBox "You clicked Cancel in the file dialog box."
End If
End With
If GetFileName <> "" Then
mstream.LoadFromFile GetFileName
rs.Fields("logo").Value = mstream.Read
rs.Update
rs.Close
Cn.Close
MsgBox "ÊÕæíÑ ÐÎíÑå ÑÏíÏ"
End If

but i have a problem when my database more than 2000 record.
i think my problem in
rs.Open "Select * from Table_2", Cn, adOpenKeyset, adLockOptimisti

because select all record in Table_2
I decided to use bottom code

strSQL = "INSERT INTO Table_2(image_v,sabt,filetype_v,imageid) Values (mstream.Read,'" & [kodimage] & "','" & [file_type] & "','" & Me.Text8 & "');"
Cnxn.Execute strSQL

But I get an error with mstream.Read!!!!
very thank you

 

[JHB]

...
But I get an error with mstream.Read!!!!
very thank you

And what is the error, (show the error number and error description)?

 

[mhamedm2008]

picture of error is attachment

strSQL = "INSERT INTO Table_2(image_v,sabt,filetype_v,imageid) Values (mstream.Read,'" & [kodimage] & "','" & [file_type] & "','" & Me.Text8 & "');"
Cnxn.Execute strSQL

 

[JHB]

Your strSQL string does have some errors in it.
A major one is, you are not putting the values return from mstream.Read into it.
It has been a while since I've used a SQL-Server, but are you not able to connect directly to a table using ADODB.Recordset?
Else you can use a criteria so you don't need to fetch all records in Table_2!

rs.Open "Select * from Table_2 WHERE [SomeFieldName]=A_criteria", Cn, adOpenKeyset, adLockOptimistic

And in both cases add a new record.

rs.AddNew