asked
How to create a Data Entry form for SQL Server - (not web based)
I have just finished building a DB model that will require updates and inserts occasionally by a small group of internal customers, but the majority do not know how to write SQL statements AND I would like to have some modicum of control on the input. I have heard about creating InfoPath forms for data entry - The sql code is embedded into the form and handles the inserts and updates into the tables. Has anyone used this and how well does it actually work? How is the connection made to the SQL Server? Are there security issues that I should be aware of? Is there another way to do this (without a web front end OR Microsoft Access)? I would appreciate any input on this subject
Honestly your data manipulation should NOT be embedded in the application. You should use stored procedures and put your logic there. There are a zillion options for creating a front end and they all vary significantly. A winforms app, Access (I wouldn't use it but lots of people do), the list goes on and on. The biggest issue here is your question requires a massive amount of information for a proper answer. This is really well beyond the scope of an online forum.
@Bugmesh Just to appreciate how dangerous is this idea, imagine that you give this "small group of internal customers" access to the database tables via SSMS and give them enough permissions to right-click whatever table they want and invoke "Edit top 200 rows" from the popup menu. Once they are in, all they need to do is type away whatever the heck changes they want directly in the grid, and if editing 200 rows is not enough, just change the number of rows in the selection by changing the TOP 200 to TOP (some other number). That is all they really need to know about T-SQL. To prevent them from running into trouble while deleting the parent records referenced by other tables, just disable all foreign keys, and make some other modifications if needed, such as remove all unique indexes, etc.That would really feel like a very ill-advised idea would not it? The database is **not** a collection of some sort of two-dimensional arrays which are called tables, and any direct data manipulations should be avoided if at all possible.
19 People are following this question.
