Hi,
If anyone could throw some light here, it is greatly appreciated. The situaion is as follows...
I have a table that has columns with NULL allowed. Then in the program I am creating a DataAdapter and its InsertCommand is set to a SqlCommand whose CommandText is a SP to insert values into this table. Both Table definiton and SP defitnion is given below. I am inserting records through binding with controls on a form. Then using AddNew I am initiating a new process. When I am finishing the edit using EndEdit (without adding anything on the two textboxes which are binded to these NULL columns), I am getting the error that NULL is not allowed for the column. I have set the Binding on the controls (TextBox) to OnProperyChnaged. When executing the same SP in SQL server, it is working without giving any values for these NULL columns. Why then DataAdapter not recognisning the table constraints/definition in the seerver?
Thanks in advance..
USE [Library]
GO
/****** Object: Table [Friends].[Addresses] Script Date: 08/06/2009 12:38:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Friends].[Addresses](
[AddressId] [dbo].[UUniqueNumber] IDENTITY(1000000001,1) NOT NULL,
[AddressTyp] [dbo].[UOne] NOT NULL,
[AddressLn1] [dbo].[UName] NOT NULL,
[AddressLn2] [dbo].[UName] NOT NULL,
[AddressLn3] [dbo].[UName] NOT NULL,
[AddressLn4] [dbo].[UName] NOT NULL,
[LocationId] [dbo].[ULongKey] NOT NULL,
[Landmark] [dbo].[ULongName] NULL,
[Directions] [dbo].[UComment] NULL,
[InsertedTs] [dbo].[UTimeStamp] NOT NULL,
[InsertedUsr] [dbo].[ULongKey] NOT NULL,
[UpdatedTs] [dbo].[UTimeStamp] NOT NULL,
[UpdatedUsr] [dbo].[ULongKey] NOT NULL,
CONSTRAINT [PK_Addresses] PRIMARY KEY CLUSTERED
(
[AddressId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Friends].[Addresses] ADD DEFAULT (N'') FOR [AddressLn1]
GO
ALTER TABLE [Friends].[Addresses] ADD DEFAULT (N'') FOR [AddressLn2]
GO
ALTER TABLE [Friends].[Addresses] ADD DEFAULT (N'') FOR [AddressLn3]
GO
ALTER TABLE [Friends].[Addresses] ADD DEFAULT (N'') FOR [AddressLn4]
GO
ALTER TABLE [Friends].[Addresses] ADD DEFAULT (N'') FOR [Landmark]
GO
ALTER TABLE [Friends].[Addresses] ADD DEFAULT (N'') FOR [Directions]
GO
USE [Library]
GO
/****** Object: StoredProcedure [Friends].[uspAddAddress] Script Date: 08/06/2009 12:38:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [Friends].[uspAddAddress]
(
@FriendId dbo.ULongKey ,
@AddressTyp dbo.UOne ,
@AddressLn1 dbo.UName,
@AddressLn2 dbo.UName,
@AddressLn3 dbo.UName = '',
@AddressLn4 dbo.UName = '',
@LocationId dbo.ULongKey ,
@LandMark dbo.ULongName = '',
@Directions dbo.UComment = '',
@InsertedUsr dbo.ULongKey
)
AS
BEGIN
declare @ErrorNumber int;
declare @timestamp as datetime;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
exec @timestamp = ufTimestamp;
begin try
insert into Addresses
(
AddressTyp ,
AddressLn1 ,
AddressLn2 ,
AddressLn3 ,
AddressLn4 ,
LocationId ,
LandMark ,
Directions ,
InsertedUsr,
Insertedts ,
UpdatedUsr,
Updatedts
)
Values
(
@AddressTyp ,
@AddressLn1 ,
@AddressLn2 ,
@AddressLn3 ,
@AddressLn4 ,
@LocationId ,
@LandMark ,
@Directions ,
@InsertedUsr ,
@timestamp ,
@InsertedUsr ,
@timestamp
);
end try
begin catch
--select ERROR_NUMBER() as ErrorNumber;
--Return @@Error;
if @@Error = 2627
-- begin
-- if @Day <> 99
-- set @day = @day + 1
-- goto start
-- end
raiserror(50005,10,1)
end catch;
--return @@error;
END
GO
___________________________________________________
Thanks
anandnairv - Always learning something...
index > Windows Forms Data Controls and Databinding > DataAdapter not retrieving NULL information from Table