Thread: Storing Dynamic .NET Controls Data

This is a bit of a complicated one and I'm not sure how to approach it.

I want to implement a system for dynamically generating controls in an ASP.NET page. I have the basic logic in my head about creating the controls, the main issue is in storing the data that's entered in the controls.

If I explain a bit more. Let's say, for example, a page was generated that contained a Date type control, a text type control and an integer valued dropdown type control. When a user browsers to the page, they might have the following controls:-

Code:

Date box <asp:textbox id="1" runat="server" style="text-align:center" maxlength="10" /> <calendar control here />
Text box <asp:textbox id="2" runat="server" />
Select box with integers as values <asp:dropdownlist id="3" runat="server />

If a user enters data into each box, I then want to store these values in a db table.

What I already have is a table that will contain the objects to be displayed and I can then relate the data table to these objects.

What I'm not sure about is how to setup the data holding table? I'm thinking something like:-
dataref INT :- PK field
objref INT :- ref from object table
data ??? :- field holding the data.

I'm not sure if I should just have one field to hold the data and make it a VARCHAR field and then convert the data in code into whatever data type it should be?
Or should I create a DATE field, a VARCHAR field and an INT field and then use whichever one I need depending on the obj type data being saved?

I hope that makes sense.

Any thoughts?

i'd do a single field and convert it. but that's just my personal preference.

Originally Posted by mehere

i'd do a single field and convert it. but that's just my personal preference.

I'm not brilliant with SQL, but can't you convert data from one type to another in an SQL query? Is it CAST or something?

I'm thinking if I'd need to do any queries based on, for example, DATE data. If it was stored in a VARCHAR field, I wouldn't be able to use any DATE functions on the value in an SQL query, would I?

So, how would I only query the data where a date value is held, for example?

And if I used CAST to convert to a DATE type, how would I only CAST the DATE type date?

Do you see what I mean?

i hear ya on that ... if this was SQL Server, I would just use case statements and the cast or convert. i'm not sure if MySQL has stuff like that. i just started a PHP/MySQL class and we haven't gotten that far. RF needs to answer this question. or don for that matter.

Originally Posted by micky

If i knew the types of possible inputs which user can have, i wud definitely have different fields in db, like here......... date, text and integer.
But for this i have to be sure about the max no. of inputs so as to make respective fields in db.

As i have low skills, it wud be easy for me to read data from db.

Yeah, that's kind of what I was thinking micky.

I will be controlling the types of controls that can be created; DATE, TEXT etc. so I wonder if it would be better to create seperate fields. Thinking about it a bit more, I wonder if I should just have DATE data and NON-DATE data. The only thing I thinking of is if I needed to run a date based query on the table. But then I also wonder if I might need to run an INT based query....Hmmm

Originally Posted by mehere

i hear ya on that ... if this was SQL Server, I would just use case statements and the cast or convert. i'm not sure if MySQL has stuff like that. i just started a PHP/MySQL class and we haven't gotten that far. RF needs to answer this question. or don for that matter.

Yeah, MySQL does have CAST and CONVERT. But, if I only had one data field, wouldn't it cause an error if I, for example, used a CAST function on a text or integer value?

How would I filter it to only CAST the date type?

Ahhh..hang on. I'd know that from the object type ref, wouldn't I....Hmmmm...Perhaps it would work with only one field then...

i figured one of those fields would tell you the type. that's why i went with the CASE statement.

something like this

Code:

SELECT field1, field2,
CASE
     WHEN field1 = 1 THEN cast(field3 AS datetime)
     WHEN field1 = 2 THEN cast(field3 AS int)
     ELSE field3
END AS data
FROM tbl_TableName

I tried using the code above with a dat value, but it returns NULL on the date, not the date value.

Code:

SELECT
CASE phases_objects.objtype
    WHEN 'date' THEN CAST(phases_objects_data.objdata AS DATE)
    WHEN 'text' THEN CAST(phases_objects_data.objdata AS CHAR)
    ELSE 'nothing'
END AS obj_data
FROM phases_objects_data
INNER JOIN phases_objects ON phases_objects.objref=phases_objects_data.objref
WHERE phases_objects_data.phaseref=3;

If I change the WHEN 'date' line to:-

Code:

WHEN 'date' THEN phases_objects_data.objdata

It returns the correct value. The objdata field is a VARCHAR (255) field.

Any ideas?

It's OK. I think I got it.

I had to convert the string date field data into the format yyyy/mm/dd

It was being stored as dd/mm/yyyy