Solved

Atttachments on my form?

Posted on 2010-09-13
8
163 Views
Last Modified: 2012-06-27
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.

 
0
Comment
Question by:al4629740
  • 4
  • 2
  • 2
8 Comments
 
LVL 19

Expert Comment

by:darbid73
ID: 33669826
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
 
LVL 11

Expert Comment

by:kbirecki
ID: 33671067
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 Comment

by:al4629740
ID: 33688511
Can I store it onto a webserver and then save the link?
0
 
LVL 11

Assisted Solution

by:kbirecki
kbirecki earned 200 total points
ID: 33690694
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 11

Expert Comment

by:kbirecki
ID: 33690707
Reviewing my message I noticed I typed "Linus", probably a Freudian slip.  I meant Linux, of course.
0
 
LVL 19

Accepted Solution

by:
darbid73 earned 300 total points
ID: 33690927
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

   Logon "username", "password", "domain"  'I used computer name if no domain.

   Open "C:\path\to\secure\file.txt" For Input As #1

   Line Input #1, strLine

   Close #1

   MsgBox strLine

   Logoff

 

End Sub

 

 

Public Sub Logon(ByVal strAdminUser As String, ByVal strAdminPassword As String, ByVal strAdminDomain As String)

 

  Dim lngTokenHandle, lngLogonType, lngLogonProvider As Long

 Dim blnResult As Boolean

 

  lngLogonType = LOGON32_LOGON_INTERACTIVE

 lngLogonProvider = LOGON32_PROVIDER_DEFAULT

 

  blnResult = RevertToSelf()

 

  blnResult = LogonUser(strAdminUser, strAdminDomain, strAdminPassword, lngLogonType, lngLogonProvider, lngTokenHandle)

 

 blnResult = ImpersonateLoggedOnUser(lngTokenHandle)

End Sub

 

Public Sub Logoff()

 Dim blnResult As Boolean

 

  blnResult = RevertToSelf()

End Sub

Open in new window

0
 
LVL 11

Expert Comment

by:kbirecki
ID: 33691059
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 Closing Comment

by:al4629740
ID: 33775366
thank you guys.  sorry about the delay in grading.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now