求 VBA 上传文件到 sqlserver 中的示例

bigfeng

求 VBA 上传文件到 sqlserver 中的示例 也不要多，就两个字段，一个是文件名，一个是图像文件，在excel中选择图像文件，然后上传到sqlserver中，不是通过共享，而是存储在数据库里，求上传和下载显示代码

bigfeng

自己顶上去

bigfeng

再顶一次。别沉了

xyxcc177

1. '引用ADODB
   
2. Dim cn As New ADODB.Connection
   
3. Dim rs As New ADODB.Recordset
   
4. Dim trm As New ADODB.Stream
   
5. Dim strcn As String, sql As String
   
6. strcn = "provider=sqloledb;Data source=xxxx;uid=sa;pwd=xx;database=xxx"
   
7. cn.Open strcn
   
8. Dim imgFile As String
   
9. imgFile = "D:\1.png"
   
10. With trm
    
11. .Type = adTypeBinary
    
12. .Open
    
13. .LoadFromFile imgFile
    
14. End With
    
15. sql = "select * from test1"
    
16. rs.Open sql, cn, adOpenKeyset, adLockOptimistic
    
17. With rs
    
18. .AddNew
    
19. .Fields("pname") = imgFile
    
20. .Fields("pValue") = trm.Read
    
21. .Update
    
22. End With
    
23. rs.Close
    
24. cn.Close

复制代码

bigfeng

不管有没有用，先谢一下，这是写入的，读出的呢？

bigfeng

xyxcc177 发表于 2022-12-23 12:30

谢谢，实测有用，求读取代码

xyxcc177

bigfeng 发表于 2022-12-23 15:02
谢谢，实测有用，求读取代码

1. Sub ReadPicture()
   
2. '引用ADODB
   
3. Dim cn As New ADODB.Connection
   
4. Dim rs As New ADODB.Recordset
   
5. Dim trm As New ADODB.Stream
   
6. Dim strcn As String, sql As String
   
7. strcn = "provider=sqloledb;Data source=xxxx;uid=sa;pwd=xx;database=xxx"
   
8. cn.Open strcn
   
9. 
   
10. Dim imgFile As String
    
11. imgFile = "D:\tmp.png"
    
12. 
    
13. sql = "select * from test1"
    
14. rs.Open sql, cn, , , adCmdText
    
15. If Not rs.EOF Then
    
16.   With trm
    
17.   .Type = adTypeBinary
    
18.   .Open
    
19.   .Write rs!pvalue
    
20.   .SaveToFile imgFile, adSaveCreateOverWrite
    
21.   End With
    
22. End If
    
23. rs.Close
    
24. cn.Close
    
25. 
    
26. End Sub
    

复制代码