Dynamic Operator Help

Hi,

I'm trying to build a form that will allow users to choose their own parameters for the Select statement in the SqlDataSource. These results would then be displayed back to a GridView control. The only problem I am having is figuring out how to allow them to choose the operator (=, <, >, <>, etc) from a dropdown list. Does anyone have any suggestions on how to do this with a SqlDataSource control? It is probably something simple and any help would be much appreciated.

Thanks

You would just build the statement dynamically and set the SqlDataSource.SelectCommand propertyhttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selectcommand.aspx

But, be advise that using dynamic SQL is frought with danger due to sql injection attacks. You will really need to scrub your input. Seehttp://en.wikipedia.org/wiki/SQL_Injection or just google "sql injection" for many, many articles on this topic

|||

Dbland thanks for the reply. I do understand that the select command will be built dynamically and this is what I have done. Here is the Data Source.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:UsersConnectionString %>"
ProviderName="<%$ ConnectionStrings:UsersConnectionString.ProviderName %>" SelectCommand="SELECT Username, Email, Active, BuildingNum, UserRole, UserID FROM UserInfo WHERE (BuildingNum = @.BuildingNum)">
<SelectParameters>
<asp:ControlParameter ControlID="BuildingDropDown" Name="BuildingNum" PropertyName="Text" />
</SelectParameters>

I would like to parameterize the operator to a dropdown control which would have the =, >, < values options. That way people can select any building less than, greater than or, equal to a building number.

So my question is how do I allow this part of the select statement, "=", to be determined and populated from a users form input. (BuildingNum = @.BuildingNum)">

Is this possible?

|||

In your code you will have to take the value of the drop down and build your statement, eg

string sql = .................

SqlDataSource.SelectCommand = sql;

I'm not exactly sure in what event you would do this but I'm sure there is one just before the SelectCommand gets executed

Is this what you were after?

|||

Can you give me or point me to an example of how to do this?

|||

Let's say you have a Submit button. When they click that you build up your sql statement and set the sqldatasource as follows:

SqlDataSource.SelectCommand = sql;

Or you can put it in one of the data datasource events, like the Selecting event which occurs just before the select is done (http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selecting.aspx). I'm not sure which event gives you the flexibility you want, you'll have to play read about them and play around with it.

|||

Thanks for all your help dbland. I took your advise and set the select command upon the button click event. It seems to work out well. Thanks again.

Dynamic Form Letters

I would like to design a 'mail merge' type of letter, whereby some of the paragraphs will be conditional on parameters. Can anyone direct me to example of mail merger letters using reporting services?

thanks

ken

Did you ever find out any info about this? I am needing to do the same thing.

Thanks,

Scott

Dynamic Form Letters

I would like to design a 'mail merge' type of letter, whereby some of the paragraphs will be conditional on parameters. Can anyone direct me to example of mail merger letters using reporting services?

thanks

ken

Did you ever find out any info about this? I am needing to do the same thing.

Thanks,

Scott

Dynamic Field DB Schema Brainstorming

Hello -
Have a project where we are going to build a form creation application. (ASP.NET). This will allow an administrator to build a form on the fly - this form will appear on the front end of the site.
This is a fairly common thing. Are there any resources out there as to where to start designing the DB schema? I'm not looking to reinvent the wheel. Here's the basic objects I'm seeing:
Tables
Forms
FormFieldNames
FormFieldTypes
FormFieldJS
Any tips on the right direction to go?
Thanks
Rob
Try the link below and right click to download the PPT slides for datamodeling. The key to data modeling is files and associations, that means you may have fifty files but you may only have three tables based on files association. The table relationship is determined by upper and lower bound cardinality and it is not complicated. There are six complete database catalogs in the book. Hope this helps.

http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html|||

Thanks for the resource, good stuff - organized nicely. I was looking specifically to my example, so I didn't plow through an ERD of what's already out there, thankx.

|||A relational database sounds like a completely inapropiate tool to build this application you're looking to build. When you are using a RDBMS, you need to know what data you are modeling. You can't just try to make it up on the fly.
You need to consider other solutions, technologies, tools. XML may be suited formatted for this -- the administrator would define an XML schema (possibly through your tool) and then build a form to input data to create XML documents that conform to the schema.
No less, any time I see anyone try to do this in a RDBMS, it falls apart in less than a year -- if it ever makes it out of development.|||

Sounds like you're both directing me the same way - a relatively small DB schema that holds the forms, the fields, and descriptive text for the fields, etc. The actual field types, values, javascript, etc... should be in a bunch of files that describe each fieldID, most likely in XML.

Thank you for that. We were thinking about compiling a bunch of .JS files for each field, and then one large.js file for the form aggregate fields. But XML would make more sense for the fields' descriptive data I believe, no?

dynamic audit trigger

Hi. I'm trying to create a dynamic audit trigger for a rather large datbase.
the trigger should be able to dynamically read the field names form the
deleted tabel and check each column for updates to store them to a table (i
only want to store fieldnames and values that changed)
the spilified table looks like:
date --the date of the update
field --the updated column
value --the inserted value
I can read column names, from deleted and check for updates by comparing to
inserted. the problem i have is the retrieval of the columns value.
How do i fetch the value of one single column from the deleted table if i
have stored the columns name in a variable as i cannot access deleted via
sp_execute?
this does not work:
select @.fieldName = name from syscolumns where colid=@.field and...
declare @.sql nvarchar(4000);
declare @.params nvarchar(4000);
set @.sql = 'select @.val = cast('+@.fieldname+' as nvarchar(3000)) from
deleted';
set @.params = '@.val nvarchar(3000) OUTPUT';
exec sp_executesql @.sql, @.params, @.val=@.value OUTPUT;
anyone any idea'
thanks so farhow did you check for the updates by comparing with the inserted table.
Can you paste the script for that?|||Hi I think i solved the problem storing the values of deleted to a temporary
table. not quite what i wanted but it works.
here's the script i use anyway
CREATE TRIGGER dbo.mytable_trigger ON dbo... FOR UPDATE
AS
DECLARE @.bit INT,
@.field INT,
@.char INT,
@.fieldName NVARCHAR(50),
@.modificationDate DATETIME,
@.contextId BIGINT,
@.value NVARCHAR(3000),
@.value2 NVARCHAR(3000),
@.maxCols INT
SET @.field = 0;
--the modification date is used to indicate date and time the update occured
SET @.modificationDate = GETDATE();
--DROP THE TEMPORARY TABLE WHICH STORES THE VALUES FROM deleted IF IT EXISTS
IF EXISTS(SELECT id FROM sysobjects WHERE NAME='tmpDel') DROP TABLE tmpDel;
--STORE VALUES FROM updated COLS in a temporary table
SELECT * INTO tmpDel FROM deleted;
--iterate throug all columns of the source table and check the values
against the deleted table to
--find the cols updated
--store the cols to a history table using a stored procedure
WHILE @.field < (SELECT MAX(colid) FROM syscolumns WHERE id = (SELECT id FROM
sysobjects WHERE name = 'mytable')) BEGIN
--DEBUG OUTPUT
--print 'field: '+cast(@.field as nvarchar(50));
--calculate the field id's (for details refer to:
http://msdn2.microsoft.com/de-de/library/ms186329.aspx)
SET @.field=@.field+1;
SET @.bit = (@.field-1)%8+1;
SET @.bit = power(2, @.bit-1);
SET @.char = ((@.field - 1) / 8) + 1
--check if a column has been updated and perform the compare operation here
IF substring(COLUMNS_UPDATED(),@.char, 1) & @.bit > 0 BEGIN
SELECT @.field, name FROM syscolumns WHERE colid=@.field AND id = (SELECT id
FROM sysobjects WHERE name = 'mytable')
-- select the ucrrent fieldName
select @.fieldName = name FROM syscolumns WHERE colid=@.field AND id =
(SELECT id FROM sysobjects WHERE name = 'mytable');
--DEBUG OUTPUT
--print 'fieldname: '+@.fieldName
SELECT @.contextId = id FROM deleted;
--DEBUG OUTPUT
--print 'contextid: "'+cast(@.contextId as nvarchar(10));
--as a variable cannot be used to select a col by its name stored in the
variable
--use dynamic sql here.
--the field to select is written by a variable to an sqlString
--within the sql string a variable called @.val is definded as an output
parameter
--to avoid type problems all selected values are casted to nvarchar
--via sp_executesql the statement is executed and the variable assigned.
DECLARE @.sql NVARCHAR(4000);
DECLARE @.params NVARCHAR(4000);
--select the actual value for the field
SET @.sql = 'select @.val = cast('+@.fieldname+' as nvarchar(3000)) from
mytable';
SET @.params = '@.val nvarchar(3000) OUTPUT';
EXEC sp_executesql @.sql, @.params, @.val=@.value OUTPUT;
--select the fields old value
SET @.sql = 'select @.val = cast('+@.fieldname+' as nvarchar(3000)) from
tmpDel';
SET @.params = '@.val nvarchar(3000) OUTPUT';
EXEC sp_executesql @.sql, @.params, @.val=@.value2 OUTPUT;
--if the values do not match the column has been updated
IF(@.value <> @.value2) BEGIN
--calll stored proc to save values to history
EXEC dbo.HistorySave4Table 'dbo.[mytable-History]', @.modificationDate,
@.contextId, null, @.fieldName, @.value2;
END
end
end
--DROP THE TEMPORARY TABLE WHICH STORES THE VALUES FROM deleted IF IT EXISTS
IF EXISTS(SELECT id from sysobjects where name='tmpDel') drop table tmpDel;
"Omnibuzz" wrote:

> how did you check for the updates by comparing with the inserted table.
> Can you paste the script for that?
>