Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nimesh69
Starting Member
30 Posts |
 Posted - 2001-12-20 : 09:02:04
|
| Our app is using ASP/COM+VB-6/Stored proc-SQL2000.In our application one one of the form we have several fields to be saved to the database. These fields are dynamic in nature and will differ from role to role. This means, if I login as Role= X, I may get 40fields and if I login as Role=Y then I may get 30 fields which might be altogether different from what Role-X got.In such a case at the time of saving I do not know which fields am I going to save to the database. I'm submitting entire html form and sending it to COM. When a form is received in COM, I have two options, either I run a loop on number of fields available on form and save one field at a time i.e. in a loop form and execute insert statement which saves data to the backend table. The second option is I form a string delimited with some special characters and send this string to the stored proc as parameter.The first option gives me the flexibility as to I can have as many numer of fields as I want and each field having as much long values as I want. Where as to implement the second option, I'll need to put some restrictions as to number of fields and value size in each of the fields or else string lenght will exceed.I plan to use varchar(8000) as a input parameter type for each string sent to stored proc. also, I'll have to fix how many params am I going to support.I'll highly appreciate if you can suggest any better options.Well I've just thought of one but have a question on it.In SQL2000 a new datatype "table" has been introduced using which we can define a variable of type Table.Can I use this table datatype as an input param? I'm quite sure the answer will be Yes but the problem is, how do I send it as an input parameter from VB-COM.Excuse me as the question looks VB question and am asking in SQL forum. But I also needed advise from someone who can suggest alternatives to what I've thought.Thanks!- NimeshNimesh |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-20 : 17:14:34
|
| I'm not sure either option is quite right. It seems to me that if, based on Role, certain fields will be retrieved (presumably from different tables) then on update, you could determine, again based on Role, which fields you have that your are updating, and call the appropriate sprocs to do that.-------------------It's a SQL thing... |
 |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-22 : 00:07:07
|
Nimesh, how many maximum fields your form can have , with 8000 as input param you can take 800 fields easily(if i take one field name as 10 characters long) and IMHO 800 fields in a form are more then enough. using Dynamic sql this approach would be simpler to use.quote:
Where as to implement the second option, I'll need to put some restrictions as to number of fields and value size in each of the fields or else string lenght will exceed.I plan to use varchar(8000) as a input parameter type for each string sent to stored proc. also, I'll have to fix how many params am I going to support.
Another option will be maitaining a seperate table with rights to every user. when a user logs in by checking his/her rights you know how many fields the user is entitled to see, that will make it very easy.otherwise as Ajarn Suggest you can keep creates Roles in Sql server and work accordingly-------------------------"Graz's Baby is my Master:)"Edited by - Nazim on 12/22/2001 00:08:20 |
|
|
|
|
|
|
|
|
Table variable as Input param to Stored Proc???