VB中上传下载文件到SQL数据库
编写人:左丘文
2015-4-11
近期在修改一个VB编写的系统时,想给画面增加一个上传文件到数据库,并可以下载查看的功能,今天在这里,我想与大家一起分享代码,在此做个小结,以供参考。有兴趣的同学,可以一同探讨与学习一下,否则就略过吧。
1、 首先要保存文件到数据库,我们需要使用流对象保存,所以第一时间先在数据库中增加
一个image的字段(注意:Access中的photo字段类型为OLE对象.SqlServer中的photo字段类型为Image),用于存储文件。
2、 下面再在画面增加用于上传及下载的CommandButton及一个commondialog:
1) 数据库连接处理
1 Private Sub Form_Load()
2 ' 数据库连接字符串
3 Connstring= " Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False " & _
4 " ;Data Source=d:\csdn_vb\database\保存图片\access图片\img.mdb "
5
6 ‘下面的语句是连接sqlserver数据库的.
7 ‘Connstring= " Provider=SQLOLEDB.1;Persist Security Info=True; " & _
8 ‘ " User ID=sa;Password=;Initial Catalog=test;Data Source=yang "
9 Set Conn = New ADODB.Connection
10 Conn.Open Connstring
11 End Sub
2 ' 数据库连接字符串
3 Connstring= " Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False " & _
4 " ;Data Source=d:\csdn_vb\database\保存图片\access图片\img.mdb "
5
6 ‘下面的语句是连接sqlserver数据库的.
7 ‘Connstring= " Provider=SQLOLEDB.1;Persist Security Info=True; " & _
8 ‘ " User ID=sa;Password=;Initial Catalog=test;Data Source=yang "
9 Set Conn = New ADODB.Connection
10 Conn.Open Connstring
11 End Sub
2) 上传文件处理
1 Private Sub cmdUpload_Click()
2 ** 引用 Microsoft ActiveX Data Objects 2.5 Library 及以上版本
3 ‘ 2.5版本以下不支持Stream对象
4
5 On Error GoTo handleErr
6 Dim rs As ADODB.Recordset
7 Dim mstream As ADODB.Stream
8
9 ' 保存文件到数据库中
10 Set rs = New ADODB.Recordset
11 With rs
12 .ActiveConnection = Conn
13 .LockType = adLockOptimistic
14 .CursorLocation = adUseClient
15 .CursorType = adOpenKeyset
16 .Open " SELECT * from SR WHERE SRNUM=' " & txtSRNUM.Text & " ' "
17 End With
18
19
20
21 CommonDialog1.Filter = " Pictures (*.PDF;*.pdf)|*.PDF;*.pdf "
22 CommonDialog1.ShowOpen
23 If CommonDialog1.filename = "" Then Exit Sub
24
25 If (rs.RecordCount = 1) Then
26
27
28 ' 读取文件到内容
29 Set mstream = New ADODB.Stream
30 With mstream
31 .Type = adTypeBinary ' 二进制模式
32 .Open
33 .LoadFromFile CommonDialog1.filename
34 End With
35
36 rs.Fields( " FileName ").Value = CommonDialog1.FileTitle
37 rs.Fields( " FileUploadTime ").Value = Format(Now, " YYYY-MM-DD hh:mm ")
38 rs.Fields( " FileNameContent ") = mstream.Read
39 rs.update
40 ' 完成后关闭对象
41 mstream.Close
42
43 End If
44 rs.Close
45 Set rs = Nothing
46 txtFileName.Text = CommonDialog1.FileTitle
47
48 Exit Sub
49
50 handleErr:
51 MsgBox ERR.Description
52 End Sub
2 ** 引用 Microsoft ActiveX Data Objects 2.5 Library 及以上版本
3 ‘ 2.5版本以下不支持Stream对象
4
5 On Error GoTo handleErr
6 Dim rs As ADODB.Recordset
7 Dim mstream As ADODB.Stream
8
9 ' 保存文件到数据库中
10 Set rs = New ADODB.Recordset
11 With rs
12 .ActiveConnection = Conn
13 .LockType = adLockOptimistic
14 .CursorLocation = adUseClient
15 .CursorType = adOpenKeyset
16 .Open " SELECT * from SR WHERE SRNUM=' " & txtSRNUM.Text & " ' "
17 End With
18
19
20
21 CommonDialog1.Filter = " Pictures (*.PDF;*.pdf)|*.PDF;*.pdf "
22 CommonDialog1.ShowOpen
23 If CommonDialog1.filename = "" Then Exit Sub
24
25 If (rs.RecordCount = 1) Then
26
27
28 ' 读取文件到内容
29 Set mstream = New ADODB.Stream
30 With mstream
31 .Type = adTypeBinary ' 二进制模式
32 .Open
33 .LoadFromFile CommonDialog1.filename
34 End With
35
36 rs.Fields( " FileName ").Value = CommonDialog1.FileTitle
37 rs.Fields( " FileUploadTime ").Value = Format(Now, " YYYY-MM-DD hh:mm ")
38 rs.Fields( " FileNameContent ") = mstream.Read
39 rs.update
40 ' 完成后关闭对象
41 mstream.Close
42
43 End If
44 rs.Close
45 Set rs = Nothing
46 txtFileName.Text = CommonDialog1.FileTitle
47
48 Exit Sub
49
50 handleErr:
51 MsgBox ERR.Description
52 End Sub
3) 下载文件处理
1 Private Sub cmdDownload_Click()
2 On Error GoTo handleErr
3 Dim rs As ADODB.Recordset
4 Dim mstream As ADODB.Stream
5
6 Set rs = New ADODB.Recordset
7 With rs
8 .ActiveConnection = ConGamma
9 .LockType = adLockOptimistic
10 .CursorLocation = adUseClient
11 .CursorType = adOpenKeyset
12 .Open " SELECT * from SR WHERE SRNUM=' " & txtSRNUM.Text & " ' "
13 End With
14 If (rs.RecordCount = 1) Then
15 If (rs( " FileNameContent ").ActualSize > 1) Then ‘判断是否为空
16 ' 保存到文件
17 Set mstream = New ADODB.Stream
18 With mstream
19 .Mode = adModeReadWrite
20 .Type = adTypeBinary
21 .Open
22 .Write rs( " FileNameContent ")
23 .SaveToFile " C:\8D.PDF " ‘‘这里注意了,如果当前目录下存在8D.PDF,会报一个文件写入失败的错误.
24 End With
25
26 ' 关闭对象
27 rs.Close
28 mstream.Close
29
30
31 End If
32 End If
33
34 Exit Sub
35 handleErr:
36 MsgBox ERR.Description
37 End Sub
2 On Error GoTo handleErr
3 Dim rs As ADODB.Recordset
4 Dim mstream As ADODB.Stream
5
6 Set rs = New ADODB.Recordset
7 With rs
8 .ActiveConnection = ConGamma
9 .LockType = adLockOptimistic
10 .CursorLocation = adUseClient
11 .CursorType = adOpenKeyset
12 .Open " SELECT * from SR WHERE SRNUM=' " & txtSRNUM.Text & " ' "
13 End With
14 If (rs.RecordCount = 1) Then
15 If (rs( " FileNameContent ").ActualSize > 1) Then ‘判断是否为空
16 ' 保存到文件
17 Set mstream = New ADODB.Stream
18 With mstream
19 .Mode = adModeReadWrite
20 .Type = adTypeBinary
21 .Open
22 .Write rs( " FileNameContent ")
23 .SaveToFile " C:\8D.PDF " ‘‘这里注意了,如果当前目录下存在8D.PDF,会报一个文件写入失败的错误.
24 End With
25
26 ' 关闭对象
27 rs.Close
28 mstream.Close
29
30
31 End If
32 End If
33
34 Exit Sub
35 handleErr:
36 MsgBox ERR.Description
37 End Sub
具体就需要各位好好的去自已去调试了。
3、有关更多的技术分享,大家可以加入我们的技术群。
欢迎加入技术分享群:238916811