Atttachments on my form?

I have VB6.

Is there a way to store a file on my SQL server using my VB6 form?  For example we have to scan articles and then attach them to the VB6 form somehow.  Then the form will have to store in the SQL database.

Any guidance would be appreciated.

Who is Participating?

Commented:
Just rounding out the options -

I would not like to takle uploading files to a webserver with VB6.  VB6 is pretty old and whilst you can do it, it is not easy.  If you were in a .NET world then it would be a lot easier.

I still am suggesting that you only save paths to your files.  Yes people will play around with folders etc.  The way I do this is have a shared folder that the user does NOT have access to.  Only User_XY has acces to this folder.

In VB6 you can "log in" or impersonate a user.  So when access to a file is needed your use the below code for User_XY.  Get the file and deal with it. Then log out.

Option Explicit

Private Declare Function RevertToSelf Lib "advapi32.dll" () As Long

Private Declare Function LogonUser Lib "advapi32.dll" Alias "LogonUserA" (ByVal lpszUsername As String, ByVal lpszDomain As String, ByVal lpszPassword As String, ByVal dwLogonType As Long, ByVal dwLogonProvider As Long, phToken As Long) As Long

Private Declare Function ImpersonateLoggedOnUser Lib "advapi32.dll" (ByVal hToken As Long) As Long

Const LOGON32_LOGON_INTERACTIVE = 2
Const LOGON32_PROVIDER_DEFAULT = 0

Private Sub Command1_Click()
Dim strLine As String
Open "C:\path\to\secure\file.txt" For Input As #1
Line Input #1, strLine
Close #1
MsgBox strLine
Logoff

End Sub

Dim lngTokenHandle, lngLogonType, lngLogonProvider As Long
Dim blnResult As Boolean

lngLogonType = LOGON32_LOGON_INTERACTIVE
lngLogonProvider = LOGON32_PROVIDER_DEFAULT

blnResult = RevertToSelf()

blnResult = ImpersonateLoggedOnUser(lngTokenHandle)
End Sub

Public Sub Logoff()
Dim blnResult As Boolean

blnResult = RevertToSelf()
End Sub

0

Commented:
What you want is certainly possible.  I would like to suggest a much easier way.

Why not store these articles in a safe place somewhere and then store the path to this place in the database.

This way you are not storing various files in a database which will enlarge the size of the database and you will not have to deal with different file types.

On the form people will have a link to the file and be able to click on the link allowing the Operating System to chooes the appropriate program to view the file.
0

Commented:
I'd like to add that although I agree with darbid73 that it is easier to just store the file on the network and retain a path in the db, there is a caveat to be aware of with this approach.  I know, because I use this method in one of our databases and I've had this problem:  If the folder where the files are stored is easily available to users, they *will* change paths, filenames, add & delete files, all at will without regard to how it affects the database, no matter how many times you tell them, and then your paths stored in the db will be invalid and can't easily be fixed.  If you use this method, store the files where the users don't have easy access.  They still need some access to the files because otherwise they wouldn't be able to open the files later.  So you'd be relying on obfuscation, or a complex process of mapping to a network share within your app using an alternate user acct.  In the latter case, when the user needs to open the file, you may want to copy to the local temp folder and then disconnect your temp network mapping, just so they don't modify the original except through your managed process.

On the other hand, regarding yor idea, as darbid73 suggested, you can do it, but you need some special coding to be able to store BLOB's in any SQL database.  BLOB's are Binary Large OBjects.  And you're likely looking for how to use GetChuck and AppendChunk methods of ADO.

Commonly, images will be stored this way.  In the case of files, you should look at the population of your files to get a sampling of file sizes and be aware of the impact this may have on your db, not just for storage, but also backup and maintenance, both very critical points.  (Make sure you follow best practices with any SQL database, because it's not fun or quick if you have to recover from any kind of failure.)

If you go the folder\file route instead of embedding in the db, try to generalize in case your target storage folder has to be moved to a new server/share name.  One way to do this would be to create an INI file that stores the root path (i.e. "\\server\shareName\PathToFiles\") and concatenate that onto the path name you actually store in th db.  That way if the stored files are restored to a different server/path, you can just change the INI file, not the stored path names.

That being said, here are some suggestions for storing files in a SQL db.

1. Know that it's not trivial.

2. Keep the images in a different database than your transactional data, kind of like the old days where a memo field was stored in a different table.  It needed different care and feeding that traditional transactional data.  This also helps avoid potential issues with schema changes on the transactional data.

3. Here are some articles that discuss how to store BLOBs to SQL (I'm assuming you're using MSSQL?)

http://support.microsoft.com/kb/194975

http://www.databasejournal.com/features/mssql/article.php/3719221/Storing-Images-and-BLOB-files-in-SQL-Server.htm

http://www.databasejournal.com/features/mssql/article.php/3738276/Storing-Images-and-BLOB-files-in-SQL-Server-Part-4.htm

4. Search the Internet for "store BLOB sql using VB6" (without the quotes).  You'll find oodles of other articles.

Let us know how it goes.
0

Author Commented:
Can I store it onto a webserver and then save the link?
0

Commented:
If I understand your question, you're thinking of pushing the file to a server that hosts a website of some kind, and that web server would provide a fully qualified URL link that would allow someone to open that file, correct?

Option 1) In that case, you are talking about more than just copying a file on a network, unless you have read/write access to a folder on the web server, indepnedant of http access, maybe something like a shared folder on any typical Windows server or a Samba share on a Linux server.  If you have that in the form of read/write access to a folder on the web server (assuming it is internal on your network and you keep in mind security concerns), you can treat it like any file share.  (Actually, I have this concept setup for limited users on our network to upload files easily to our internal-only Intranet: they can copy and paste a file to a drive mapped to a specific folder on the Intranet server and separately create a link that points to that file.)

Option 2) If you are talking about Internet-based options, to do it programatically, you'd have to use something like ftp, and that can be implemented as well in VB6, but differently, of course, because you have to use ftp protocol, not something like the FileCopy comand in VB6.  And your web server would have to have FTP server functionality implemented as well.  Here's a VB6 example to upload a file to an FTP server: http://www.example-code.com/vb/ftp_upload.asp
Again in this case, you'd have to already know the URL path to the folder you are uploading the file and you could store that URL in your db.

Comments: If the concept of storing the files to a web server was to prevent anyone from messing with the folder structure, that would work.  However, it just adds a small level of complexity that you may or may not be able to take advantage of (i.e. re-use or expand to include, for instance, expiration dates for the files that should be deleted after their expiration, or any other meta data about the file, etc.)  One problem that pops into my mind about this concept is the number of files you'd be storing in any given folder.  And file naming.  If you have only one folder that you push all you files to, you have to make sure on upload that the filename is unique now and into the future.  Or you'd have to manage a folder structure on the FTP server.  Windows and Linus have different limitations for numbers of files you can store in any given folder, but neither allow duplicate file names.

Another way might be this: Assuming users don't need to access the saved files directly in Windows Explorer, go back to your basic idea (I always try to keep things simple if I can), and use a network share.  But to obfuscate the location of the folder easily, create a hidden share, one that has a $at the end of the share name, and give read/write permissions. In your code you can reference the share like "\\Server\Share$\Path1\FileA.txt" and the visible link in your UI might be "FileA.txt" or "\Path1\FileA.txt".  I'm not sure if you are planning on showing one file per screen, listing multiple files and/or folders, or creating your own folder navigation for subsets of the files, but you can just not show the "\\Server\Share\$" portion in what you display.

Hope that helps.
0

Commented:
Reviewing my message I noticed I typed "Linus", probably a Freudian slip.  I meant Linux, of course.
0

Commented:
darbid73's suggestion may be better than my hidden share idea.  It's a matter of how you plan to use it.

Good suggestion, darbid73.
0

Author Commented: