Hello,

How can I use excel as the front end and allow users to add/edit/brows
records in sql server? I'm using .net with c# and would like to kno
how the front end should look like.

I'm pretty sure how to do the add/edit for an individual record, bu
NOT sure how users should query the database for existing records.
Should I just start by querying the database and add a hyperlink withi
cells? or is there a better way to achieve this. I would prefer we
like functionality, however, Excel doesn't clean up properly when usin
it through a browser. I've even tried releasing all objects, but G
doesn't take them away.

Anyway, I appreciate your help.

Thanks,



--
Message posted from http://www.ExcelForum.com

The great thing about Excel is that it gives the user a lot of
flexibility with data. This is its raison d'etre. Conversely, it can
be extremely frustrating for the user if this flexibility is
restricted by an application.

This flexibility causes problems in situations like yours when you
need to retain some control over the data so you can act on any
changes made. There are worksheet ActiveX/COM events which can be
trapped but I've yet to find a good way of using them to trap all the
things a user might try to do with database derived data.

The best approach I found is to perform a before and after comparison.
And be prepared for pretty much anything e.g. the user could have
changed the column heading, the column's ordinal position, deleted the
column completely, added columns, changed the column from raw values
to a complex array formula which only recalculates properly when the
workbook is opened in the native Excel app, etc.

The alternative is to use some kind of data grid control on a form
over which you can have complete control, which effectively means not
using Excel.

--

rishineedsajob wrote in message ...
Hello,

How can I use excel as the front end and allow users to add/edit/browse
records in sql server? I'm using .net with c# and would like to know
how the front end should look like.

I'm pretty sure how to do the add/edit for an individual record, but
NOT sure how users should query the database for existing records.
Should I just start by querying the database and add a hyperlink within
cells? or is there a better way to achieve this. I would prefer web
like functionality, however, Excel doesn't clean up properly when using
it through a browser. I've even tried releasing all objects, but GC
doesn't take them away.

Anyway, I appreciate your help.

Thanks,




---
Message posted from http://www.ExcelForum.com/

thanks for your reply, onedaywhen.

The datagrid/webform solution is not an option. I already built
prototype using that model, however the customer was not happy with th
webforms usability.

my plan is to generate/validate the excel sheet using th
ThisWorkbook_Open() and ThisWorkbook_BeforeClose() events. It look
like that should be pretty straightforward.

My problem: How can users see the exisiting entries in the database?
This will be the starting point of the application. Users will see
list of exisiting entries, and from here, they can edit/delete entrie
or create new ones.

In the web prototype, I just provided a datagrid of existing records
and added hyperlinks to edit existing entries. How can I do the sam
in Excel. Should I build a windows form that displays a datagrid?
then launch excel to edit/add new entries?

or

Can I just show them a list of entries in an existing sheet?

Any help is appreciated.

Thanks,

-

--
Message posted from http://www.ExcelForum.com

If you propose to use the ThisWorkbook_Open() and
ThisWorkbook_BeforeClose() events then you are basically doing the
'before and after' approach I recommended.

If you are using the worksheet itself to display data then I'm not
surprised your client requested this. Users love Excel and are very
comfortable with it.

You could lock the cells that protect the entire row which contain
your column headings, unlock the rest of the worksheet cells then
protect the worksheet. This would prevent come of the 'funnies' e.g.
users inserting or deleting columns. However, be warned this would
prevent the users from doing thing like atuofilter and sort, which may
be one of the client's reasons for using Excel in the first place.

To amend data in a row the user could, well, just change the data in
the cells. To add a row of data they could just type on the next
available row or, if the sheet is unprotected, insert a row where
needed. Excel is very intuitive and even occasional users are familiar
with these basic tasks.

If you do lock the sheet and want to provide a more 'controlled' way
of editing adding records, e.g. using a windows form for an individual
row, then your approach sounds good: populate the worksheet with all
the rows and add a hyperlink within one cell on each row to show the
form (hyperlinks are easy to use when automating Excel and have the
added benefit of giving you your web-type look). I'd suggest you show
the rows on the worksheet rather than a grid on a form because that
would make it even less like an Excel app and probably not be
acceptable to your client.

--

rishineedsajob wrote in message ...
thanks for your reply, onedaywhen.

The datagrid/webform solution is not an option. I already built a
prototype using that model, however the customer was not happy with the
webforms usability.

my plan is to generate/validate the excel sheet using the
ThisWorkbook_Open() and ThisWorkbook_BeforeClose() events. It looks
like that should be pretty straightforward.

My problem: How can users see the exisiting entries in the database?
This will be the starting point of the application. Users will see a
list of exisiting entries, and from here, they can edit/delete entries
or create new ones.

In the web prototype, I just provided a datagrid of existing records,
and added hyperlinks to edit existing entries. How can I do the same
in Excel. Should I build a windows form that displays a datagrid?
then launch excel to edit/add new entries?

or

Can I just show them a list of entries in an existing sheet?

Any help is appreciated.

Thanks,

-r


---
Message posted from http://www.ExcelForum.com/