I need some advice from the guru's out there

[FrankT]

All,

I've got a problem I need to solve and I'm not sure what the best approach
is. I have a web application that is using basic authentication. We have a
requirement to ingest excel files (very small files) in a standard format,
via the web and error check the contents, then insert the record(s) from
excel into a sql server 2000 database (soon to upgrade to sql server 2005) if
the spreadsheet contains valid data. This has to be done via https and basic
authentication and there is no direct database access from the client machine
to our sql server 2000 database.

I've thought about the following solutions to the problem, but I would like
to leverage anything MS has incorporated into their latest and greatest
toolsets to minimize the amount of code to write.

1. Custom application that would upload the file to the web server, open the
excel file, error check it then insert the record into sql server (lot's of
custom code I think here).
2. Create a web service, and a custom excel interface to talk to the web
service including a button in the excel spreadsheet that would send the data
to the web service, error check it, then insert the record into sql server
(still custom code, but less because we have no file transfer).

Questions:
1. Is there some sort of excel 2003 to SQL Server 2005 ingest capability
that I am missing that I can leverage to acheive the goal?

2. Does anyone have any experience with this, and if so how did you solve
the problem?

3. Any 3rd party components to do this (I couldn't find any)? I've played
with the sql xml interface in sql server 2000 put how do I hook that into
excel?

Any help would be appreciated!!

FrankT

 

[Alvin Bruney [ASP.NET MVP]]

I'm afraid not, at least from where i sit. The only other thing that comes
to mind is to write an xml file after you check it for errors and then write
a database trigger to insert the contents into the database. You aren't
saving a lot of code by that approach but it is a good approach if you want
to decouple the database portion from the error checking portion of your
code.

Unfortunately, you will have to write custom code. I am not aware of a
Microsoft solution or a 3rd party solution for this.

--
Regards,
Alvin Bruney
[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
available at www.lulu.com/owc
_________________________