Case - When - Then - Else?

Thanks in advance for your help!
I have been using very basic SQL statements to return recordsets to my ASP. One that someone gave me a few months ago was the case statement. Until now, I have been using
Case
When this Then that
When day Then night
When wet Then Dry End as whatever.
This has been fine because everything has been this, day or wet. Is there a way to do a Case Else that would capture the very few exceptions that fall outside the norm?

Thanks,
LeeLee,

Why not just attach 'else' to your case expression...

case when 1=1 then 1
else 0 end

--
-oj
http://www.rac4sql.net

Originally posted by clinel
Thanks in advance for your help!
I have been using very basic SQL statements to return recordsets to my ASP. One that someone gave me a few months ago was the case statement. Until now, I have been using
Case
When this Then that
When day Then night
When wet Then Dry End as whatever.
This has been fine because everything has been this, day or wet. Is there a way to do a Case Else that would capture the very few exceptions that fall outside the norm?

Thanks,
Lee|||Thanks for not starting the reply with HI STUPID as I was unaware that it was as simple as that!
Again Thanks,
Lee|||Yep. See below...

Case
When this Then that
When day Then night
When wet Then Dry
Else Foo
End as whatever.

Originally posted by clinel
Thanks in advance for your help!
I have been using very basic SQL statements to return recordsets to my ASP. One that someone gave me a few months ago was the case statement. Until now, I have been using
Case
When this Then that
When day Then night
When wet Then Dry End as whatever.
This has been fine because everything has been this, day or wet. Is there a way to do a Case Else that would capture the very few exceptions that fall outside the norm?

Thanks,
Lee|||Sorry for the repeat post; somehow I had not seen the responses already in the browser. Very confused...

Regards,

Hugh

Originally posted by hmscott
Yep. See below...

Case
When this Then that
When day Then night
When wet Then Dry
Else Foo
End as whatever.

Cascade set to null on delete.

Hi all,

I am using SQL Server 2000 and am trying to perform a basic delete query on a table called ClientType. The only child table of this is called Client. The relationship between the tables has a cascade action of cascade update and when I try to perform the delete operation, I get the error "DELETE statement conflicted with COLUMN REFERENCE constraint". The foreign key field accepts nulls and has a defauilt value of null. Now, am I being completely dense or shouldn't the cascade update set the foreign key values to null?

Regards,

Stephen.

Could you please send over the DDL for the table and the constraints, thanks.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de|||

Here you go:
...

CREATE TABLE [dbo].[ClientType] (
[ClientTypeId] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectVersion] [int] NULL ,
[Type] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Client] (
[ClientId] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectVersion] [int] NULL ,
[ClientTypeId] [int] NULL ,
[ReferenceNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forename] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Initial] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOfBirth] [datetime] NULL ,
[DateRegistered] [datetime] NULL ,
[Address1] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address3] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[County] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Postcode] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone3] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMail] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URL] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Position] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Overview] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InternalOverview] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CV] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HoldingCompanyName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClientType] WITH NOCHECK ADD
CONSTRAINT [PK_ClientType] PRIMARY KEY CLUSTERED
(
[ClientTypeId]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientId]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClientType] ADD
CONSTRAINT [DF_ClientType_ObjectVersion] DEFAULT (0) FOR [ObjectVersion]
GO

ALTER TABLE [dbo].[Client] ADD
CONSTRAINT [DF_Client_ObjectVersion] DEFAULT (0) FOR [ObjectVersion],
CONSTRAINT [DF_Client_ClientTypeId] DEFAULT (null) FOR [ClientTypeId]
GO

ALTER TABLE [dbo].[Client] ADD
CONSTRAINT [FK_Client_ClientType] FOREIGN KEY
(
[ClientTypeId]
) REFERENCES [dbo].[ClientType] (
[ClientTypeId]
) ON UPDATE CASCADE
GO

...

Regards,

Stephen.

|||OK, you just defined the Cascade on the update, if you delete a row and the child table contains rows for that parent row and you did not define a cascade delete on the parent table, this error message will come up.

You wil have to add: ON DELETE CASCADE

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the response. I do understand that adding cascade delete would get rid of the error but will that not result in the child rows being deleted? I only want the foreign keys to be set to null, not have the entire related record dropped from the table.

Regards,

Stephen.

|||OK, then you will have to use the ON DELETE SET NULL.

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de|||OK. I gave that a go, but I just get an error "incorrect syntax near the keyword 'SET'".|||

Sorry, I re-read your first post and saw that you are using SQL Server 2k. SET NULL is a new feature for SQL 2k5. In SQL Server 2000 you probably would use no constraint in that case and do the work with triggers, in that case an update trigger.

Sorry for confusing you :-)

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||

OK. I'll look into that.

Thanks for your help. Its much appreciated.

Regards,

Stephen.

Carriage Return In View

Is it possible to return one field in a view that has something similar to
vbCrLf in Visual Basic? I want to return it as a formatted envelope
address. Thanks.
Daviduse char(13) as your column value
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"David C" wrote:

> Is it possible to return one field in a view that has something similar to
> vbCrLf in Visual Basic? I want to return it as a formatted envelope
> address. Thanks.
> David
>
>|||Thanks. Actually, I had to use ... + Char(13) + Char(10)
David
"mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
news:2CEEA448-B43B-457C-BE12-D0547A4F3AD4@.microsoft.com...
> use char(13) as your column value
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Database change management for SQL Server
>
> "David C" wrote:
>

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