November 27, 2007 at 8:03 am
When the end user do not select the value(which default Value is Select One) and I want to insert into database Sex as Null.
When the end user do not select the value (which default Value is Select One) and I want to update into database Sex as Null.
Here is the statment for update which is not work at all after I put the case.
the insert and update is not working.
CSex]=CASE WHEN @CSex = 'Select One' THEN NULL ELSE @CSex END
InsertCommand=" InsertCommand="INSERT INTO UserDataEntry.Children ([CName],SS,[CSex],CSS, WithMom ,[CDOB],NotApplyToRB ,[CareGiverName],RelationshipToCareGiver,ReasonForSeparation,Reunite, CSchool,Grade,SDoing,ListSchools, ADDADHD,Behavior,Physical,Developmental,Long,Hospitalizations,Emotional,Learning,Major,Special,Premature,Drugs,Medications , PDHS, CDHS,PCaregiver,LCustody,FatherName,ContactFather, SeparatedFromChild) VALUES (@CName, @SS, SELECT CASE @CSex WHEN @CSex='Select One' THEN Null Else @CSex ,@CSS,@WithMom, @CDOB,@NotApplyToRB,@CareGiverName, @RelationshipToCareGiver,@ReasonForSeparation,@Reunite,@CSchool,@Grade,@SDoing,@ListSchools, @ADDADHD,@Behavior,@Physical,@Developmental,@Long,@Hospitalizations,@Emotional,@Learning,@Major,@Special,@Premature,@Drugs,@Medications, @PDHS, @CDHS,@PCaregiver,@LCustody,@FatherName,@ContactFather,@SeparatedFromChild)"
UpdateCommand="UPDATE UserDataEntry.Children SET CSS=@CSS, [CDOB] = @CDOB, CSex]=CASE WHEN @CSex = 'Select One' THEN NULL ELSE @CSex END , [WithMom]=@WithMom, [NotApplyToRB] =@NotApplyToRB, [CareGiverName] =@CareGiverName, RelationshipToCareGiver=@RelationshipToCareGiver, ReasonForSeparation=@ReasonForSeparation, Reunite=@Reunite, CSchool=@CSchool,Grade=@Grade, SDoing=@SDoing,ListSchools=@ListSchools, ADDADHD=@ADDADHD,Behavior=@Behavior,Physical=@Physical,Developmental=@Developmental,Long=@Long,Hospitalizations=@Hospitalizations,Emotional=@Emotional,Learning=@Learning,Major=@Major, Special=@Special, Premature=@Premature,Drugs=@Drugs,Medications=@Medications,PDHS=@PDHS,CDHS=@CDHS,PCaregiver=@PCaregiver,LCustody=@LCustody,FatherName=@FatherName,ContactFather=@ContactFather, SeparatedFromChild=@SeparatedFromChild
WHERE ([SS] = @SS) and CName=@CName"
November 27, 2007 at 8:32 am
Well, I see a couple of things wrong here. First, you cannot have a 'SELECT' statement in a VALUES clause and second, you have no END on your CASE statement. To get this working correctly, either replace your VALUES clause with a SELECT or remove the CASE statement and replace with a variable. Perform the CASE prior to the insert and use the results in a variable in your VALUES clause.
November 27, 2007 at 8:50 am
I am not quite understand why you said.
Since the statement will use in the asp.net detail view data source. I have no way to set variable there.
It use data bound. update and insert statement. I tried to simple the query and test in sql 2005. It gave me error
Msg 102, Level 15, State 1, Procedure Test, Line 11
Incorrect syntax near '='.
I think the error must be what you mentioned in the post. Thx.
INSERT INTO UserDataEntry.Children ([CName],SS,[CSex]) VALUES (@CName, @SS, Case @CSex When @CSex='Select One' then null else @CSex end )
November 27, 2007 at 8:59 am
Try using NULLIF. Here's an example:
declare @table table (sex varchar(10))
declare @CSex varchar(20)
set @CSex = 'Select One'
insert into @table
values(NULLIF(@CSex,'Select One'))
set @CSex = 'Female'
insert into @table
values(NULLIF(@CSex,'Select One'))
select * from @table
November 27, 2007 at 9:05 am
I do not think I can set up in asp.net data control datasource for datasource like that. I am hoping I can find the one line code for that.
Thx.
November 27, 2007 at 9:10 am
Really, your best bet here would be to not default your value to 'Select One' but to default it to NULL instead or to use a stored procedure for your inserts so you are not stuck with the limitations that your app. appears to be putting on you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply