VBupload the downloaded file to the SQL database
Written by: Zho Chuwen
2015-4-11
Recent changes in a VB write the system, want to add an upload file to the screen to the database, and can download the function of viewing, today here, I would like to share the code with you, here to do a summary, for reference. Interested students, you can explore and study together, or skip it.
1. first, to save the file to the database, we need to save with Stream object, so the first time in the database to add
aImageThe fields(Note: Accessin thePhotofield type isOLEObject. SQL Serverin thePhotofield type isImage), which is used to store files.
2, commandbutton and a commondialog :
1) database connection processing
1Private Sub Form_Load ()
2'Database connection String
3connstring="provider=microsoft.jet.oledb.4.0; Persist Security Info=false"& _
4";D ata source=d:\csdn_vb\database\ save picture \access picture \img.mdb"
5
6' The following statement is connected to the SQL Server database.
7' Connstring="Provider=SQLOLEDB.1; Persist Security info=true;"& _
8‘"User Id=sa; Password=;initial Catalog=test;data Source=yang"
9Set Conn = New ADODB. Connection
TenConn.Open connstring
OneEnd Sub
2) upload file processing
1Private Sub Cmdupload_click ()
2* * Reference to Microsoft ActiveX Data Objects2.5Library and above version
3‘2Stream objects are not supported under. 5 version
4
5On Error GoTo Handleerr
6Dim rs as ADODB. Recordset
7Dim Mstream as ADODB. Stream
8
9'Save the file to the database
Ten Set rs = New ADODB. Recordset
OneWith RS
A. ActiveConnection = Conn
-. LockType = adLockOptimistic
-. CursorLocation = adUseClient
the. CursorType = adOpenKeyset
-. Open"SELECT * from SR WHERE srnum= '"& Txtsrnum.text &"'"
-End with
-
+
-
+Commondialog1.filter ="Pictures (*. Pdf;*.pdf) |*. Pdf;*.pdf"
ACommondialog1.showopen
atIf Commondialog1.filename =""Then Exit Sub
-
-If (Rs. RecordCount =1) Then
-
-
-'read file to content
inSet Mstream = New ADODB. Stream
-With Mstream
to. Type = adTypeBinary'Binary mode
+. Open
-. LoadFromFile Commondialog1.filename
theEnd with
*
$Rs. Fields ("FileName"). Value = Commondialog1.filetitle
Panax NotoginsengRs. Fields ("Fileuploadtime"). Value = Format (now,"YYYY-MM-DD hh:mm")
-Rs. Fields ("filenamecontent") = Mstream. Read
theRs.update
+ 'Close objects when finished
AMstream. Close
the
+End If
-Rs. Close
$Set rs = Nothing
$Txtfilename.text = Commondialog1.filetitle
-
-Exit Sub
the
-Handleerr:
WuyiMsgBox ERR. Description
theEnd SubView Code
3) download file processing
1Private Sub Cmddownload_click ()
2On Error GoTo Handleerr
3Dim rs as ADODB. Recordset
4Dim Mstream as ADODB. Stream
5
6Set rs = New ADODB. Recordset
7With RS
8. ActiveConnection = Congamma
9. LockType = adLockOptimistic
Ten. CursorLocation = adUseClient
One. CursorType = adOpenKeyset
A. Open"SELECT * from SR WHERE srnum= '"& Txtsrnum.text &"'"
-End with
-If (Rs. RecordCount =1) Then
theIf (RS ("filenamecontent"). ActualSize >1Then ' Determine if it is empty
-'Save to file
-Set Mstream = New ADODB. Stream
-With Mstream
+. Mode = adModeReadWrite
-. Type = adTypeBinary
+. Open
A. Write RS ("filenamecontent")
at. SaveToFile"C:\8D. PDF"Note here that if 8d.pdf is present in the current directory, a file write failure error will be reported.
-End with
-
-'Close Object
-Rs. Close
-Mstream. Close
in
-
toEnd If
+End If
-
theExit Sub
*Handleerr:
$MsgBox ERR. Description
Panax NotoginsengEnd SubView Code
specifically, you need to go to your own debugging.
3 , for more technology sharing, you can join our technology group.
Welcome to join the technology sharing group: 238916811
vb upload download file to SQL database
