Null value in a combobox not accepted on SQL server

  • Thread starter jubiiab via AccessMonster.com
  • Start date
J

jubiiab via AccessMonster.com

In my form I have some comboboxes that have some data from tables. If I keep
my combobox empty the SQL server will not save the record. How can I force it
to save it? In my database table on SQL server I have check marked the Null
value but it don’t have any effect?

Please HELP ME!!!!
 
S

Stefan Hoffmann

jubiiab said:
In my form I have some comboboxes that have some data from tables. If I keep
my combobox empty the SQL server will not save the record. How can I force it
to save it? In my database table on SQL server I have check marked the Null
value but it don’t have any effect?
Click to expand...
Emtpy is not the same as NULL. So use the forms Before Update event to
check this value, e.g.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Trim("" & yourCombo.Value)) = 0 Then
Me![yourBoundField] = Null
End If

End Sub


mfG
--> stefan <--
 
J

jubiiab via AccessMonster.com

Hi

I did it but it still not working

Stefan said:
In my form I have some comboboxes that have some data from tables. If I keep
my combobox empty the SQL server will not save the record. How can I force it
to save it? In my database table on SQL server I have check marked the Null
value but it don’t have any effect?
Click to expand...
Emtpy is not the same as NULL. So use the forms Before Update event to
check this value, e.g.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Trim("" & yourCombo.Value)) = 0 Then
Me![yourBoundField] = Null
End If

End Sub

mfG
--> stefan <--
Click to expand...
 
S

Stefan Hoffmann

hi,
I did it but it still not working
Click to expand...
Do you get any error message?

Set a break point and check what happens in the Before Update event.



mfG
--> stefan <--
 
J

jubiiab via AccessMonster.com

When I make a new record and keep the comobox blank I get this Message:
ODBC – insert on a linked table ‘my_tablename’ failed

I know you told me to use before update and I did that but now I am using
before insert but it still not working:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Len(Trim("" & Combo80.Value)) = 0 Then
Me![AcceptBy] = Null

End If
End Sub
 
S

Stefan Hoffmann

hi,
When I make a new record and keep the comobox blank I get this Message:
ODBC – insert on a linked table ‘my_tablename’ failed
I know you told me to use before update and I did that but now I am using
before insert but it still not working:
Click to expand...
Hmm, seems to be the same issue. Check your table definition on the SQL
Server side carefully. I suspect there is another required field. Also
check if there is any trigger causing this error.


mfG
--> stefan <--
 
J

jubiiab via AccessMonster.com

But when I choose the name in the combobox it saves the record. I don’t need
to field out other fields?

I am really appreciate your help. thx
 
S

Stefan Hoffmann

hi,
But when I choose the name in the combobox it saves the record. I don’t need
to field out other fields?
Click to expand...
The the field is required and may not be null.


mfG
--> stefan <--
 
J

jubiiab via AccessMonster.com

But in Access mdb database/file it worked - why not on a SQL server???
 
J

jubiiab via AccessMonster.com

I just noticed that it writes "COLLATE SQL_Latin1_General_CP1_CI_AS NULL" on
every combobox. Should I remove that and how?



CREATE TABLE [dbo].[tbl_Maskine] (
[MaskineNr] [int] IDENTITY (102753, 1) NOT NULL ,
[MaskineNavn] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Serienr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasRumNr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasBruger] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasEjerforhold] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProducentNr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LeverandorNr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Indkobspris] [money] NULL ,
[IndkobsDato] [datetime] NULL ,
[MasDriftstatus] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServiceDato] [datetime] NULL ,
[SerType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasServStatus] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasNaesteService] [datetime] NULL ,
[Kalibrering] [datetime] NULL ,
[NaesteKalibrering] [datetime] NULL ,
[MasAfdnr] [int] NULL ,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RelationNr] [int] NULL ,
[SidstChangeBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SidstChangeByDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
J

jubiiab via AccessMonster.com

It's not only on combobox it write: COLLATE SQL_Latin1_General_CP1_CI_AS NULL

I just noticed that it writes "COLLATE SQL_Latin1_General_CP1_CI_AS NULL" on
every combobox. Should I remove that and how?

CREATE TABLE [dbo].[tbl_Maskine] (
[MaskineNr] [int] IDENTITY (102753, 1) NOT NULL ,
[MaskineNavn] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Serienr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasRumNr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasBruger] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasEjerforhold] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProducentNr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LeverandorNr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Indkobspris] [money] NULL ,
[IndkobsDato] [datetime] NULL ,
[MasDriftstatus] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServiceDato] [datetime] NULL ,
[SerType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasServStatus] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasNaesteService] [datetime] NULL ,
[Kalibrering] [datetime] NULL ,
[NaesteKalibrering] [datetime] NULL ,
[MasAfdnr] [int] NULL ,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RelationNr] [int] NULL ,
[SidstChangeBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SidstChangeByDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
hi,
Click to expand...
[quoted text clipped - 5 lines]
mfG
--> stefan <--
Click to expand...
Click to expand...
 
S

Stefan Hoffmann

hi,
CREATE TABLE [dbo].[tbl_Maskine] (
[MaskineNr] [int] IDENTITY (102753, 1) NOT NULL ,
[MaskineNavn] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [..]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Click to expand...
All fields accept NULL, so it should not be the problem.

Start the SQL Profiler and take a look at the SQL statement sent to the
SQL Server when the error occurs, does it show any problem?


mfG
--> stefan <--
 
J

jubiiab via AccessMonster.com

Hi

Sorry because of my late replay but I can only work with the SQL server when
I am at work.

“SQL Profiler†where do I find that?




Stefan said:
hi,
CREATE TABLE [dbo].[tbl_Maskine] (
[MaskineNr] [int] IDENTITY (102753, 1) NOT NULL ,
[MaskineNavn] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [..]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Click to expand...
All fields accept NULL, so it should not be the problem.

Start the SQL Profiler and take a look at the SQL statement sent to the
SQL Server when the error occurs, does it show any problem?

mfG
--> stefan <--
Click to expand...
 
J

jubiiab via AccessMonster.com

Are you asking for this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tbl_Budget_FK02]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tbl_Budget] DROP CONSTRAINT tbl_Budget_FK02
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tbl_Dokument_FK00]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tbl_Dokument] DROP CONSTRAINT tbl_Dokument_FK00
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tbl_FejlOnske_FK01]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tbl_FejlOnske] DROP CONSTRAINT tbl_FejlOnske_FK01
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[T_tbl_Maskine_ITrig]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[T_tbl_Maskine_ITrig]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[T_tbl_Maskine_DTrig]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[T_tbl_Maskine_DTrig]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[T_tbl_Maskine_UTrig]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[T_tbl_Maskine_UTrig]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tbl_Maskine]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Maskine]
GO

CREATE TABLE [dbo].[tbl_Maskine] (
[MaskineNr] [int] IDENTITY (102753, 1) NOT NULL ,
[MaskineNavn] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Serienr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasRumNr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasBruger] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasEjerforhold] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProducentNr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LeverandorNr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Indkobspris] [money] NULL ,
[IndkobsDato] [datetime] NULL ,
[MasDriftstatus] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServiceDato] [datetime] NULL ,
[SerType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasServStatus] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MasNaesteService] [datetime] NULL ,
[Kalibrering] [datetime] NULL ,
[NaesteKalibrering] [datetime] NULL ,
[MasAfdnr] [int] NULL ,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RelationNr] [int] NULL ,
[SidstChangeBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SidstChangeByDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_Maskine] ADD
CONSTRAINT [aaaaatbl_Maskine_PK] PRIMARY KEY NONCLUSTERED
(
[MaskineNr]
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER T_tbl_Maskine_ITrig ON dbo.tbl_Maskine FOR INSERT AS
SET NOCOUNT ON
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tbl_Leverandor' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_Leverandor, inserted WHERE (tbl_Leverandor.
LeverandorNr = inserted.LeverandorNr))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''tbl_Leverandor''.'
ROLLBACK TRANSACTION
END

/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tbl_MasAfdeling' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasAfdeling, inserted WHERE (tbl_MasAfdeling.
MasAfdNr = inserted.MasAfdnr))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''tbl_MasAfdeling''.'
ROLLBACK TRANSACTION
END

/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tbl_MasBruger' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasBruger, inserted WHERE (tbl_MasBruger.
MasBruger = inserted.MasBruger))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''tbl_MasBruger''.'
ROLLBACK TRANSACTION
END

/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tbl_MasDriftstatus' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasDriftstatus, inserted WHERE
(tbl_MasDriftstatus.MasDriftstatus = inserted.MasDriftstatus))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''tbl_MasDriftstatus''.'
ROLLBACK TRANSACTION
END

/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tbl_MasEjerforhold' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasEjerforhold, inserted WHERE
(tbl_MasEjerforhold.MasEjerforhold = inserted.MasEjerforhold))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''tbl_MasEjerforhold''.'
ROLLBACK TRANSACTION
END

/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tbl_MasProducent' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasProducent, inserted WHERE (tbl_MasProducent.
ProducentNr = inserted.ProducentNr))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''tbl_MasProducent''.'
ROLLBACK TRANSACTION
END

/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tbl_MasRumNr' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasRumNr, inserted WHERE (tbl_MasRumNr.MasRumNr
= inserted.MasRumNr))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''tbl_MasRumNr''.'
ROLLBACK TRANSACTION
END

/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tbl_MasServiceStatus' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasServiceStatus, inserted WHERE
(tbl_MasServiceStatus.MasServStatus = inserted.MasServStatus))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''tbl_MasServiceStatus''.'
ROLLBACK TRANSACTION
END

/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tbl_MasType' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasType, inserted WHERE (tbl_MasType.MasType =
inserted.MasType))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''tbl_MasType''.'
ROLLBACK TRANSACTION
END

/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tbl_ServiceType' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_ServiceType, inserted WHERE (tbl_ServiceType.
SerType = inserted.SerType))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''tbl_ServiceType''.'
ROLLBACK TRANSACTION
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER T_tbl_Maskine_DTrig ON dbo.tbl_Maskine FOR DELETE AS
SET NOCOUNT ON
/* * PREVENT DELETES IF DEPENDENT RECORDS IN 'tbl_Budget' */
IF (SELECT COUNT(*) FROM deleted, tbl_Budget WHERE (deleted.MaskineNr =
tbl_Budget.MaskineNr)) > 0
BEGIN
RAISERROR 44445 'The record can''t be deleted or changed. Since
related records exist in table ''tbl_Budget'', referential integrity rules
would be violated.'
ROLLBACK TRANSACTION
END

/* * PREVENT DELETES IF DEPENDENT RECORDS IN 'tbl_Dokument' */
IF (SELECT COUNT(*) FROM deleted, tbl_Dokument WHERE (deleted.MaskineNr =
tbl_Dokument.MaskineNr)) > 0
BEGIN
RAISERROR 44445 'The record can''t be deleted or changed. Since
related records exist in table ''tbl_Dokument'', referential integrity rules
would be violated.'
ROLLBACK TRANSACTION
END

/* * PREVENT DELETES IF DEPENDENT RECORDS IN 'tbl_FejlOnske' */
IF (SELECT COUNT(*) FROM deleted, tbl_FejlOnske WHERE (deleted.MaskineNr =
tbl_FejlOnske.MaskineNr)) > 0
BEGIN
RAISERROR 44445 'The record can''t be deleted or changed. Since
related records exist in table ''tbl_FejlOnske'', referential integrity rules
would be violated.'
ROLLBACK TRANSACTION
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER T_tbl_Maskine_UTrig ON dbo.tbl_Maskine FOR UPDATE AS
SET NOCOUNT ON
/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tbl_Leverandor' */
IF UPDATE(LeverandorNr)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_Leverandor, inserted WHERE
(tbl_Leverandor.LeverandorNr = inserted.LeverandorNr))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
''tbl_Leverandor''.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tbl_MasAfdeling' */
IF UPDATE(MasAfdnr)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasAfdeling, inserted WHERE
(tbl_MasAfdeling.MasAfdNr = inserted.MasAfdnr))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
''tbl_MasAfdeling''.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tbl_MasBruger' */
IF UPDATE(MasBruger)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasBruger, inserted WHERE (tbl_MasBruger.
MasBruger = inserted.MasBruger))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
''tbl_MasBruger''.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tbl_MasDriftstatus' */
IF UPDATE(MasDriftstatus)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasDriftstatus, inserted WHERE
(tbl_MasDriftstatus.MasDriftstatus = inserted.MasDriftstatus))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
''tbl_MasDriftstatus''.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tbl_MasEjerforhold' */
IF UPDATE(MasEjerforhold)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasEjerforhold, inserted WHERE
(tbl_MasEjerforhold.MasEjerforhold = inserted.MasEjerforhold))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
''tbl_MasEjerforhold''.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF DEPENDENT RECORDS IN 'tbl_Budget' */
IF UPDATE(MaskineNr)
BEGIN
IF (SELECT COUNT(*) FROM deleted, tbl_Budget WHERE (deleted.MaskineNr
= tbl_Budget.MaskineNr)) > 0
BEGIN
RAISERROR 44446 'The record can''t be deleted or changed.
Since related records exist in table ''tbl_Budget'', referential integrity
rules would be violated.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF DEPENDENT RECORDS IN 'tbl_Dokument' */
IF UPDATE(MaskineNr)
BEGIN
IF (SELECT COUNT(*) FROM deleted, tbl_Dokument WHERE (deleted.
MaskineNr = tbl_Dokument.MaskineNr)) > 0
BEGIN
RAISERROR 44446 'The record can''t be deleted or changed.
Since related records exist in table ''tbl_Dokument'', referential integrity
rules would be violated.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF DEPENDENT RECORDS IN 'tbl_FejlOnske' */
IF UPDATE(MaskineNr)
BEGIN
IF (SELECT COUNT(*) FROM deleted, tbl_FejlOnske WHERE (deleted.
MaskineNr = tbl_FejlOnske.MaskineNr)) > 0
BEGIN
RAISERROR 44446 'The record can''t be deleted or changed.
Since related records exist in table ''tbl_FejlOnske'', referential integrity
rules would be violated.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tbl_MasProducent' */
IF UPDATE(ProducentNr)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasProducent, inserted WHERE
(tbl_MasProducent.ProducentNr = inserted.ProducentNr))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
''tbl_MasProducent''.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tbl_MasRumNr' */
IF UPDATE(MasRumNr)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasRumNr, inserted WHERE (tbl_MasRumNr.
MasRumNr = inserted.MasRumNr))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
''tbl_MasRumNr''.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tbl_MasServiceStatus' */
IF UPDATE(MasServStatus)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasServiceStatus, inserted WHERE
(tbl_MasServiceStatus.MasServStatus = inserted.MasServStatus))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
''tbl_MasServiceStatus''.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tbl_MasType' */
IF UPDATE(MasType)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_MasType, inserted WHERE (tbl_MasType.
MasType = inserted.MasType))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table ''tbl_MasType''.
'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tbl_ServiceType' */
IF UPDATE(SerType)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_ServiceType, inserted WHERE
(tbl_ServiceType.SerType = inserted.SerType))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
''tbl_ServiceType''.'
ROLLBACK TRANSACTION
END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO





Hi

Sorry because of my late replay but I can only work with the SQL server when
I am at work.

“SQL Profiler†where do I find that?
hi,
Click to expand...
[quoted text clipped - 10 lines]
mfG
--> stefan <--
Click to expand...
Click to expand...
 
S

Stefan Hoffmann

hi,
“SQL Profiler†where do I find that?
Click to expand...
Either in the SQL Server start menu group or under the Tools menu in the
SQL Server Management Studio.


mfG
--> stefan <--
 
S

Stefan Hoffmann

hi,
CREATE TRIGGER T_tbl_Maskine_ITrig ON dbo.tbl_Maskine FOR INSERT AS
SET NOCOUNT ON
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tbl_Leverandor' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tbl_Leverandor, inserted WHERE (tbl_Leverandor.
LeverandorNr = inserted.LeverandorNr))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''tbl_Leverandor''.'
ROLLBACK TRANSACTION
END
[..]
Click to expand...
Are these conditions all met, when you try to insert your record? If
not, your ODBC error messages simply means, that you don't have
specified the correct dependend values.

btw, why don't use this system declarative referential integrity:

http://en.wikipedia.org/wiki/Declarative_Referential_Integrity


mfG
--> stefan <--
 
J

jubiiab via AccessMonster.com

Stefan said:
“SQL Profiler†where do I find that?
Either in the SQL Server start menu group or under the Tools menu in the
SQL Server Management Studio.

mfG
--> stefan <--
Click to expand...

sorry mate cant find it :(
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A problem occurred running while microsoft access was communicating with OLE server or ActiveX Control. 0
Outlook Desktop (latest) stopped syncing IMAP accpounts from selfowned Mail server. 0
Optionbutton Not working 3
Combobox syncing 3
Cascading Combobox problem 10
Combobox looking for data in a table and write to another 2
Combobox default value 4
case statement in a combobox 5

Top