CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
--
Thanks in advance,
sck10I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
Thanks in advance,
sck10
I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
--
Thanks in advance,
sck10I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

carry over values to next page

hi, all.
is it possible to carry-over a subtotal of a page to the page header of the
next page?
we use ssrs 2005.
thx,
Carstenhi, all.
no idea? am i the one and only who needs a carry-forward value?
pls help.

Capture Return Value from Stored Procedure, Use Same in Code Behind Page

My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted. In my code behind file (VB) how would I capture the value "@.myERROR" then display in the label I have that a duplicate entry was attempted.

Stored Procedure
CREATE PROCEDURE dbo.usp_InsertNew
@.IDNumber nvarchar(25),
@.ID nvarchar(50),
@.LName varchar(50),
@.FName varchar(50)

AS

DECLARE @.myERROR int -- local @.@.ERROR
, @.myRowCount int --local @.@.rowcount
BEGIN
-- See if a contact with the same name and zip code exists
IF EXISTS (Select * FROM Info
WHERE ID = @.ID)

BEGIN
RETURN 1
END
ELSE
BEGIN TRAN

INSERT INTO Info(IDNumber, ID, LName,
FName) VALUES (@.IDNumber, @.ID, @.LName,
@.FName)
SELECT @.myERROR = @.@.ERROR, @.myRowCount = @.@.ROWCOUNT
If @.myERROR !=0 GOTO HANDLE_ERROR

COMMIT TRAN
RETURN 0

HANDLE_ERROR:
ROLLBACK TRAN
RETURN @.myERROR

END
GO

asp.net page
<asp:SqlDataSource ID="ContactDetailDS" runat="server" ConnectionString="<%$ ConnectionStrings:EssPerLisCS %>"
SelectCommand="SELECT * FROM TABLE_One"

UpdateCommand="UPDATE TABLE_One WHERE ID = @.ID"

InsertCommand="usp_InsertNew" InsertCommandType="StoredProcedure">

<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" />
</SelectParameters>

</asp:SqlDataSource>

You have to declare it as output parameter in your stored procedure

Stored Procedure
CREATE PROCEDURE dbo.usp_InsertNew
@.IDNumber nvarchar(25),
@.ID nvarchar(50),
@.LName varchar(50),
@.FName varchar(50),
@.myERROR int OUTPUT

and just read value of this parameter after you close connection in which you call you SP.

remember to set parameter type as INPUTOUTPUT or OUTPUT when you define parameter in you VB code.

|||

Hi,

I included "@.myERROR" in the stored procedure, but have no idea where or how to read its value. As far as closing the connection in which I call the sp; my sp is called within the SqlDataSource, so how would I read/write the Return Value.

Thank you.

Ayomide

Cant's shrink tempdb in sql2k5

Hi, tried to shrink 190gb tempdb but got following message:

DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.

Anyone know why? The tempdb is almost empty, just doesn't release free space.

If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.
http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

|||

Thanks for the info, but they don't address my issue.

The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.

|||I personally have not seen this issue.

As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||

hi rmiao,

is your tempdb files set to autogrow?

regards

jag

|||

first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Madhu

|||

Yes, but don't think it's related.

|||I like to know why as well, but didn't get any answer from Microsoft.|||

most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...

Madhu

|||

You could also look at :

http://msdn2.microsoft.com/en-gb/library/ms176029.aspx

this gives practical monitoring suggestions and links to other tempdb concept articles.

Cant's shrink tempdb in sql2k5

Hi, tried to shrink 190gb tempdb but got following message:

DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.

Anyone know why? The tempdb is almost empty, just doesn't release free space.

If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.
http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

|||

Thanks for the info, but they don't address my issue.

The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.

|||I personally have not seen this issue.

As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||

hi rmiao,

is your tempdb files set to autogrow?

regards

jag

|||

first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Madhu

|||

Yes, but don't think it's related.

|||I like to know why as well, but didn't get any answer from Microsoft.|||

most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...

Madhu

|||

You could also look at :

http://msdn2.microsoft.com/en-gb/library/ms176029.aspx

this gives practical monitoring suggestions and links to other tempdb concept articles.

Cant's shrink tempdb in sql2k5

Hi, tried to shrink 190gb tempdb but got following message:

DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.

Anyone know why? The tempdb is almost empty, just doesn't release free space.

If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.
http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

|||

Thanks for the info, but they don't address my issue.

The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.

|||I personally have not seen this issue.

As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||

hi rmiao,

is your tempdb files set to autogrow?

regards

jag

|||

first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Madhu

|||

Yes, but don't think it's related.

|||I like to know why as well, but didn't get any answer from Microsoft.|||

most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...

Madhu

|||

You could also look at :

http://msdn2.microsoft.com/en-gb/library/ms176029.aspx

this gives practical monitoring suggestions and links to other tempdb concept articles.

Cant's shrink tempdb in sql2k5

Hi, tried to shrink 190gb tempdb but got following message:

DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.

Anyone know why? The tempdb is almost empty, just doesn't release free space.

If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.
http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

|||

Thanks for the info, but they don't address my issue.

The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.

|||I personally have not seen this issue.

As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||

hi rmiao,

is your tempdb files set to autogrow?

regards

jag

|||

first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Madhu

|||

Yes, but don't think it's related.

|||I like to know why as well, but didn't get any answer from Microsoft.|||

most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...

Madhu

|||

You could also look at :

http://msdn2.microsoft.com/en-gb/library/ms176029.aspx

this gives practical monitoring suggestions and links to other tempdb concept articles.

Cant update a record using a DetailsView and SqlDataSource

Hi,

I'm trying to create a registration page that I've divided into multiple pages (first page for basic details, next page for address, etc.). I insert the record in the first page, and update it in the other pages. I pass the newly created ID to the other pages using the Page.PreviousPage property.

In the second page, I have the SqlDataSource configured as "SELECT * FROM [Table] WHERE ID = ?", and the UpdateCommand is "UPDATE ... WHERE ID = ?".

In Page_Load, I am updating the SelectCommand to "SELECT ... WHERE ID = " & intID, and the UpdateCommand similarly. The I do a dtlsvw.Databind()

But when I go to the next page (the newly created ID is being passed properly), the update doesn't do anything. The new record doesn't contain the values in the detailsview. Can somebody help me out?

Thanks,

Wild Thing

Does your ASP page looks like in this example from .Net help:

This section contains two code examples. The first code example demonstrates how to set theUpdateCommand property of theSqlDataSource control and update data in a Microsoft SQL Server database using theGridView control. The second code example demonstrates how to update data in an ODBC database using theGridView control.

The following code example demonstrates how to set theUpdateCommand property of theSqlDataSource control and update data in a SQL Server database using theGridView control. TheGridView automatically populates theUpdateParameters collection, inferring the parameters from theBoundField objects, and calls theUpdate method when theUpdate link on the editableGridView is selected. This example also includes some post-processing: after a record is updated, a notification e-mail message is sent.

<%@.Page Language="VB" %><%@.Import Namespace="System.Web.Mail" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><SCRIPT runat="server"> Sub OnDSUpdatedHandler(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs) If e.AffectedRows > 0 Then ' Perform any additional processing, ' such as setting a status label after the operation. Label1.Text = Request.LogonUserIdentity.Name & _ " changed user information successfully!" Else Label1.Text = "No data updated!" End If End Sub 'OnDSUpdatedHandler</SCRIPT><HTML> <BODY> <FORM runat="server"> <asp:SqlDataSource id="SqlDataSource1" runat="server" DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:MyNorthwind%>" SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees" UpdateCommand="Update Employees SET FirstName=@.FirstName,LastName=@.LastName,Title=@.Title WHERE EmployeeID=@.EmployeeID" OnUpdated="OnDSUpdatedHandler"> </asp:SqlDataSource> <asp:GridView id="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID" AutoGenerateEditButton="True" DataSourceID="SqlDataSource1"> <columns> <asp:BoundField HeaderText="First Name" DataField="FirstName" /> <asp:BoundField HeaderText="Last Name" DataField="LastName" /> <asp:BoundField HeaderText="Title" DataField="Title" /> </columns> </asp:GridView> <asp:Label id="Label1" runat="server"> </asp:Label> </FORM> </BODY></HTML>

|||

Hi,

Thanks for your help! I'll go through the code you've attached.

I managed to solve the problem by binding the detailsview to the data source from scratch, and asking it to retrieve the ID parameter for the select statement from the Session Field. I don't know if it's the best alternative, but it's working!

Thanks!

Wild Thing