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 |
|
asdoye111
Starting Member
16 Posts |
 Posted - 2009-11-16 : 11:21:27
|
| Say I have an web page that allows users to create a sql statement using 5 parameters: Order, Logical Operator, Field, Comparison Operator and value.Users can Add, Edit and Delete these parameters on the site. Each time a parameter is added, a new row is created in a SQL Table. So an example would look like this:Order LogOper Field CompOper Value 1 Title Code = ASD 2 AND Product Code = 1111 3 AND Billing Code = ABC 4 AND Fiscal Year = 2009 5 AND Other Code < 1234 when Edit is clicked, and a parameter is changed, ie ORDER, the appropriate column/row is updated in SQL. Row #1 can never have a Logical Operator.The problem I'm trying to solve is this... When a user changes the ORDER of #3 to #1, i want #1 to move to #2 spot, and #2 to move to #3 spot, etc.Currently the code interacting with SQL looks similar to this:cmd.CommandText = "Update qryWheres SET"cmd.CommandText &= " whrOrder=" & whrOrder.ToStringcmd.CommandText &= " ,whrFieldID=" & whrFieldID.ToStringcmd.CommandText &= " ,whrComOpID=" & whrComOpID.ToStringcmd.CommandText &= " ,whrValue='" & whrValue & "'"when they editing of a parameter is done and saved, the above code is used and the SQL Table is updated. The problem I'm running into is when it gets saved, it doesn't change the orders of the other rows. so i end up getting something looking like the table below.Order LogOper Field CompOper Value 1 Title Code = ASD 2 AND Product Code = 1111 2 AND Billing Code = ABC 3 AND Fiscal Year = 2009 5 AND Other Code < 1234 Order LogOper Field CompOper Value 1 Title Code = ASD 1 Product Code = 1111 2 AND Billing Code = ABC 3 AND Fiscal Year = 2009 5 AND Other Code < 1234 the first table would technically still work, but the second table would give an error because the SQL code would not have the additional LogOperator(AND/OR) to make the select statement work.Anybody ever had any experience doing something similar to this? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-16 : 12:22:34
|
| one way to make this easy is to hardcode "WHERE 1=1"then ALL of your user defined parameters have a logical operator.so the final query looks like:WHERE 1 = 1And Title Code = 'ASD'AND Product Code = 1111 etc. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-16 : 12:23:53
|
| by the way, while it'd be easy to solve your ordering problem, who cares what order they're in? |
|
|
|
asdoye111
Starting Member
16 Posts |
Posted - 2009-11-16 : 12:34:59
|
| it doesn't matter what order they are in really, but it's more of a cosmetic issue. if there are 5 pieces to the puzzle, i want to see the order 1,2,3,4,5... not 1,2,2,3,5.and there is a ton of possibilities. If you move 5 to 1, 1 to 5, or anything in between they'll have to adjust accordingly to always end at 1,2,3,4,5 with 1 never having a logical operator and the rest always having some sort of logical operator. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-16 : 12:42:38
|
| http://www.webdeveloper.com/forum/showpost.php?p=804445&postcount=4 |
|
|
|
asdoye111
Starting Member
16 Posts |
Posted - 2009-11-16 : 12:51:29
|
| thanks russell. i think that is going to help alot. i'll let you know, if i end up figuring it all out. |
|
|
|
|
|
|
|
|
Update ORDER Problem