need help - SP to move data

Hello
I am trying to create a stored procedure to move a ROW from Table1 to Table
based on a coditon.
Eg. I want to transfer an item with ITEMNO = '001202' FROM currentstock TO
old_stock table.
I want to be able to pass the value for ITEMNO from my code which calls the
SP.
Please help me
TIA
Mackok, I have done myself.
Thanks
"MackS" <mackS@.yahooa.com> wrote in message
news:OWiNi8H$FHA.228@.TK2MSFTNGP12.phx.gbl...
> Hello
> I am trying to create a stored procedure to move a ROW from Table1 to
> Table based on a coditon.
> Eg. I want to transfer an item with ITEMNO = '001202' FROM currentstock TO
> old_stock table.
> I want to be able to pass the value for ITEMNO from my code which calls
> the SP.
> Please help me
> TIA
> Mack
>

Need a trigger based on a row

Hello, everyone

I need to create a trigger to update a FLAG column by row modification. The table likes,

Col1 FLAG
a 0
b 0
c 0
d 0
e 0

If "a" in Col1 is changed, the "0" in same row (First row) should be changed to '1'. Other FLAG values should not be changed. The same rule for other row.

Anyhelp will be approciated.

Thanks a lot

ZYTIf col1 is a primary key, this is relatively easy. If you don't have a primary key, I don't know how to do it. Can you "fill in the blanks" a bit on your requirements?

-PatP|||Col1 doesn't have to be a primary key, but you do need a primary key. Put this in your trigger:

Update YourTable
set FLAG = 1
from YourTable
inner join Deleted on YourTable.PrimaryKey = Deleted.PrimaryKey
where YourTable.Col1 <> Deleted.Col1|||So what happens when the code that launches the trigger is something like:UPDATE yourtable SET col1 = 'a'All of the rows except for one change. Without a PK, things get ugly really fast from a SQL perspective, and I'd guess that the business rules would go berzerk!

-PatP|||All of the rows except for one change.-PatP

I think that is what he would want. All of the rows except one were modified, so set the flags on all but one record.

But yes, as I mentioned in my post, he is going to need a primary key on the table.

Need "refresh connection" or something ?

HI,

I have a problem with the data that I want to delete and insert new one on my SQL.
It will work if I just delete a row. And work well if I just insert a row.
But it will not work if I delete and insert at once on one procedure.

Here's the code :

Protected Sub RolesRadioButtonList_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles RolesRadioButtonList.SelectedIndexChanged
Session("CurrentRoleId") = Me.RolesRadioButtonList.SelectedValue
' Call Sub RemoveUsersInRoles
RemoveUsersInRoles()
' Call Sub AddNewUsersInRoles
AddNewUsersInRoles()
End Sub

Sub RemoveUsersInRoles()
' Create SQL database connection
Dim sqlConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.Text
' Delete that row with correct UserId
cmd.CommandText = "DELETE aspnet_UsersInRoles WHERE (UserId = '" & Session("CurrentUserId") & "')"
cmd.Connection = sqlConn
sqlConn.Open()
cmd.ExecuteNonQuery()
' Close SQL connecton
sqlConn.Close()
End Sub

Sub AddNewUsersInRoles()
' Create SQL database connection
Dim sqlConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")
sqlConn.Open()
' Create new row with new UserId
Dim sqlString As String = "INSERT INTO aspnet_UsersInRoles (UserId, RoleId) VALUES( '" & Session("CurrentUserId") & "','" & Session("CurrentRoleId") & "')"
Dim sqlComm As New SqlCommand(sqlString, sqlConn)
Dim sqlExec As Integer = sqlComm.ExecuteNonQuery
' Close SQL connection
sqlConn.Close()
End Sub

--------------

I can delete the row if I call 'RemoveUsersInRoles'
And I can insert new one if I call 'AddNewUsersInRoles'

But if 'RolesRadioButtonList_SelectedIndexChanged' is called, it will not work well.
If the row not exist, it will insert new row. And that what I want.
But If the row is exist, It wont delete that row and wont insert the new one. That's the problem.

Do I need some 'pause' or 'refresh connection' here ?

Thank You.

Why don't you create one procedure that checks if the row exists, then delete and insert if it does (or just amends/updates it), otherwise it just performs an insert?

|||

uh, Mike, thanks for your respon.

My mistake. I put this at my page load. It always reset the variable when the page is reload.

If Len(Session("CurrentRoleId")) Then
Me.RolesRadioButtonList.SelectedValue = Session("CurrentRoleId")
End If

And Mike, thanks for the advise : use update. I already use it.

And thank you all at that 2nd class, keep programming ...

navigation on subtotal

I have a matrix and a subtotal footer summing up a group of numbers. Each row has a primary key. I use the primary key to drill-through another report through "Navigation".

Unfortunately, the generated subtotal column also have a link but it has a primary key of whatever the first row is, which shouldn't have a primary key. So I can drill-through the subtotal but it returns the wrong result.

I have not been able to override the subtotal to specify a new link with different paramters.

Anybody can help would be great!

Thanks.

In the place where you define the drillthrough action with its parameters, you have to use the InScope(...) function to determine if your are in a subtotal or not.

Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp

With InScope you can determine the current scope of a matrix cell. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

-- Robert

|||Thanks for the suggestion. But I have two dynamic rows, one for SubTotal and one for Grand Total.

<DynamicRows>
<Grouping Name="GrandTotal">
....
<DynamicRows>
<Grouping Name="SubTotal">
...

I am having a hard time to link it to a correct report as it appears that GrandTotal is in scope of SubTotal. I am not sure to put that in the omit node as well.

If you can give me some instructions that would be great.

Thanks.

Jason
|||there are two subtotals, one is the rightmost column and the other is the bottom row. How can I disable the navigation for these two subtotals and still have navigation "In Cell"? Thanks if you can help or point out some resouces that I can go check.|||

Use the InScope function as described earlier in this thread.

For the cells, you would generate the desired hyperlink or drillthrough link. For subtotal scope you would just return Nothing (i.e. VB equivalent of null) in the IIF arguments. If a hyperlink expression evaluates to Nothing for a particular cell, the hyperlink action will be removed entirely.

-- Robert

|||

Hi, Robert,

The problem is still not solved. I still want the subtotal number shows on rightmost column and bottom row. The way that you told me, I will get empty column and row on the end. Let me try an example here:

co1 col2 col3 subtotal

row1 3 5 7 15

row2 1 2 3 6

subtotal 4 7 10 21

3, 5, 7, 1, 2, and 3, will have a hyperlink to jump to another report show detailed page, but I wanto 4, 7, 10, 15, 6, and 21 to show the number but disable the hyplerlinks.

Thanks!

Henry

|||

Yes, that should work fine.

For the matrix cell, you use an expression that shows calculates the value (e.g. =Sum(Fields!A.Value)).

Then, on that textbox add a navigation action. Only for the hyperlink expression you would use the complex expression using IIF calls and InScope calls.

Hope this clarifies it.

|||

Hi, Robert,

It works now. Thanks!

The key point: "sum(fields!a.value)" in Matrix cell and complex expression using iif and inscope in navigation action.

Henry

|||

hi,

can u please send me the query you used.

and please also gimme a explanation if possible.

Thanks in advance,

Vinu

navigation on subtotal

I have a matrix and a subtotal footer summing up a group of numbers. Each row has a primary key. I use the primary key to drill-through another report through "Navigation".

Unfortunately, the generated subtotal column also have a link but it has a primary key of whatever the first row is, which shouldn't have a primary key. So I can drill-through the subtotal but it returns the wrong result.

I have not been able to override the subtotal to specify a new link with different paramters.

Anybody can help would be great!

Thanks.

In the place where you define the drillthrough action with its parameters, you have to use the InScope(...) function to determine if your are in a subtotal or not.

Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp

With InScope you can determine the current scope of a matrix cell. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

-- Robert

|||Thanks for the suggestion. But I have two dynamic rows, one for SubTotal and one for Grand Total.

<DynamicRows>
<Grouping Name="GrandTotal">
....
<DynamicRows>
<Grouping Name="SubTotal">
...

I am having a hard time to link it to a correct report as it appears that GrandTotal is in scope of SubTotal. I am not sure to put that in the omit node as well.

If you can give me some instructions that would be great.

Thanks.

Jason|||there are two subtotals, one is the rightmost column and the other is the bottom row. How can I disable the navigation for these two subtotals and still have navigation "In Cell"? Thanks if you can help or point out some resouces that I can go check.|||

Use the InScope function as described earlier in this thread.

For the cells, you would generate the desired hyperlink or drillthrough link. For subtotal scope you would just return Nothing (i.e. VB equivalent of null) in the IIF arguments. If a hyperlink expression evaluates to Nothing for a particular cell, the hyperlink action will be removed entirely.

-- Robert

|||

Hi, Robert,

The problem is still not solved. I still want the subtotal number shows on rightmost column and bottom row. The way that you told me, I will get empty column and row on the end. Let me try an example here:

co1 col2 col3 subtotal

row1 3 5 7 15

row2 1 2 3 6

subtotal 4 7 10 21

3, 5, 7, 1, 2, and 3, will have a hyperlink to jump to another report show detailed page, but I wanto 4, 7, 10, 15, 6, and 21 to show the number but disable the hyplerlinks.

Thanks!

Henry

|||

Yes, that should work fine.

For the matrix cell, you use an expression that shows calculates the value (e.g. =Sum(Fields!A.Value)).

Then, on that textbox add a navigation action. Only for the hyperlink expression you would use the complex expression using IIF calls and InScope calls.

Hope this clarifies it.

|||

Hi, Robert,

It works now. Thanks!

The key point: "sum(fields!a.value)" in Matrix cell and complex expression using iif and inscope in navigation action.

Henry

|||

hi,

can u please send me the query you used.

and please also gimme a explanation if possible.

Thanks in advance,

Vinu