[RESOLVED] File handling in webform

[knelson312]

One of the current projects I'm working on is converting an existing Access front end to a web app for a SQL Database. I wasn't involved in the original design of the, formerly, Access db and front end. I have the project nearly complete except for one remaining task, which involved links to part datasheets for a particular part.

The current db has a table with 8 columns "File1" through "File8". For each datasheet created for the part one of these columns is populated with the UNC path to the file, "\\<Server>\<Department>\Documents\<filename>.pdf". The Documents folder currently has over 16000 files stored there. The Access front end has a series of textboxes with associated file save, file open, and delete buttons for each. I could do the same think in the webform where I need to allow access but it doesn't seem very clean.

I've thought about importing the documents to the SQL db then use a fileupload method to maintain them, which I've used in apps for new databases but that seems like a daunting task with the existing db.

I'm wondering if someone might have an example of a way to use the existing file path data in the db and existing document folder to allow access to the documents and the user to manage them?

[NeedSomeAnswers]

I've thought about importing the documents to the SQL db then use a fileupload method to maintain them

Dont! by the sounds of things this is an internal web app, which means you have access to the file system so leave your files exactly where they are!

It will not only be faster to just open your documents from the file-system, it will also be a lot easier to manage.

To me your main issue seems to be display related, dynamically creating lots of textboxes and buttons to manage your files sounds quite inefficient.

Why not use a grid control or something like that to store all you documents in, and then have the document open on double clicking the grid item?

Which bring us to... What type of documents are you opening word / pdf / something else ???

[knelson312]

All the files are PDF. I've run into a couple of other hitches with the document links in the db. Some of those are inconsistencies with the path data, columns not populated in order. I'm leaning towards creating a SQL SP to pull the path then populating hyperlinks, either on the page or in a grid with Nav URL's to the path, cleaned up display text (rather than a long UNC path) and adding the target attribute to the links so the docs open in a new tab. I believe I can pass the appropriate data to the app easily enough. The remaining difficulty will be allowing the user to add new documents.

[NeedSomeAnswers]

The remaining difficulty will be allowing the user to add new documents.

Why what are your difficulties?

Are you not saving the path+filename to your documents in your db?

[knelson312]

Why what are your difficulties?

Are you not saving the path+filename to your documents in your db?

Yes, we are saving the UNC path to the file in the db, in one of the 8 File columns of a note table. My initial problem, although probably not a big deal to populate the page, is that in many cases the columns aren't populated in order (i.e. File1, File2, File4, File6 might be populated). If I just query the non-null columns that's not a problem and write the paths to hyperlinks in a grid. I'm just trying to wrap my head around how to deal with the random order of the populated columns when a user adds a new file to the db.

Currently, the Access front end has individual links, save buttons, delete button and open buttons for each of the eight populated on the page. As noted above, I'd prefer a cleaner approach so I just need to sort out the code to identify the first null column for the part then choose that first column to write the new path. I don't think it will be a big deal but I'm guessing it would be easier to just pass the path to SQL then let SQL pick the appropriate field to write it to.

I'm just not a SQL pro and will probably need to pass that SQL off to my DBA.

[NeedSomeAnswers]

I'm just trying to wrap my head around how to deal with the random order of the populated columns when a user adds a new file to the db.

I am not sure i understand, why is this a problem?

I am guessing here but do you mean that 1 user may fill in column 1,3,5 and then next user 2,4,6 ??

Currently, the Access front end has individual links, save buttons, delete button and open buttons for each of the eight populated on the page. As noted above, I'd prefer a cleaner approach

With the Grid approach i spoke about earlier you could for instance -

- Make the grid items editable, and get the Grid to save your edits as you leave the grid cell.
- Double Click the item to Open
- have a Right click menu to delete

You can even add Buttons to your Grid, see here

[knelson312]

I am guessing here but do you mean that 1 user may fill in column 1,3,5 and then next user 2,4,6 ??

This is no longer an issue. My DBA cleaned up the table. It turns out there were some larger issues with the data I hadn't noticed. She also provided a process to send update the new paths added by users.

Thanks for the input and suggestions.