I, a non-developer, am trying to build some custom business software using SQL Server and .NET framework. I have tables in the server which self monitor to make sure data rules are followed using the instead of insert heading. the procedure checks if there will be any conflicts and if a user has permission to make a specific change and then either inserts the rows or rejects the transaction.
Well I'm just starting on the .net side of things, trying to insert data and all is well except that when I review the inserted rows, my ID, which is Identity, bigint, true zero (-9123... something) doesn't reflect the inserted ID but instead shows, -1, -2, -3... etc.
Does the instead of insert operation stop SQL Server from returning the updated rows? I'm lost here. I have about 20 tables set up like this and am limited on time to finish. Any help would be greatly appreciated.
Table Companies in SQL Server
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Companies]
(
[ID] [bigint] IDENTITY(-9223372036854775808,1) NOT NULL,
[Name] [nvarchar](200) NULL,
[MainPhone] [nvarchar](200) NULL,
[AltPhone] [nvarchar](200) NULL,
[BillingAddress] [nvarchar](500) NULL,
[ShippingAddress] [nvarchar](500) NULL,
[ModifiedBy] [bigint] NULL,
[LastModified] [datetime2](7) NULL,
CONSTRAINT [PK_Companies]
PRIMARY KEY CLUSTERED ([ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Companies]
ADD CONSTRAINT [DF_Companies_CompanyName]
DEFAULT (N'Not provided') FOR [Name]
GO
ALTER TABLE [dbo].[Companies]
ADD CONSTRAINT [DF_Companies_MainPhone]
DEFAULT (N'Not provided') FOR [MainPhone]
GO
ALTER TABLE [dbo].[Companies]
ADD CONSTRAINT [DF_Companies_AltPhone]
DEFAULT (N'Not provided') FOR [AltPhone]
GO
ALTER TABLE [dbo].[Companies]
ADD CONSTRAINT [DF_Companies_BillingAddress]
DEFAULT (N'Not provided') FOR [BillingAddress]
GO
ALTER TABLE [dbo].[Companies]
ADD CONSTRAINT [DF_Companies_ShippingAddress]
DEFAULT (N'Not provided') FOR [ShippingAddress]
GO
ALTER TABLE [dbo].[Companies]
ADD CONSTRAINT [DF_Companies_Editor]
DEFAULT ((0)) FOR [ModifiedBy]
GO
Instead of insert trigger on Companies table SQL Server:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Trig_Company_BeforeInsert]
ON [dbo].[Companies]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
--Check for duplicates entries in the rows being inserted
DECLARE @int1 int;
SELECT @int1 = COUNT(*)
FROM
(SELECT inserted.Name, COUNT(*) AS Duplicates
FROM inserted
GROUP BY Name
HAVING COUNT(*) > 1) AS sq;
IF (@int1 > 0)
BEGIN;
THROW 51000, 'Duplicate entries detected in rows to be inserted. Transaction was canceled.', 10;
ROLLBACK
END;
-- Make sure the new records don't already exist.
SELECT @int1 = COUNT(*)
FROM
(SELECT *
FROM Companies
WHERE Name IN (SELECT Name FROM inserted)) AS sq;
IF (@int1 > 0)
BEGIN;
THROW 51000, 'One or more of the new entries already exist. Transaction was canceled', 10;
ROLLBACK
END;
--Check if the current user exists.
IF (dbo.USER_CurrentUserIsRegistered() = 0)
BEGIN;
THROW 51000, 'Current user not registered. Transaction canceled.', 10;
ROLLBACK
END;
--Check if the current user is allowed to Make new companies
DECLARE @userid bigint
SET @userid = dbo.USER_GetCurrent()
DECLARE @canedit bit
SELECT @canedit = Users.EditCompanies
FROM Users
WHERE ID = @userid
IF @canedit = 0
BEGIN;
THROW 51000, 'Current user does not have permission.', 10;
ROLLBACK
END;
--if it passed all the checks then go ahead and record it - being sure to mark the current moment in time and user.
INSERT INTO Companies (AltPhone, BillingAddress, LastModified, MainPhone, ModifiedBy, [Name], ShippingAddress)
SELECT
AltPhone, BillingAddress, GETDATE(), MainPhone, @userid, [Name], ShippingAddress
FROM
inserted;
END
Class for keeping track of old and new ID from imported data:
Private Class ComboEntry
Property OldID As Integer
Property NewEntry As Object
Sub New()
End Sub
Sub New(_Oldid As Integer, _NewEntry As Object)
OldID = _Oldid
NewEntry = _NewEntry
End Sub
End Class
'comboentry objects for keeping track of old ID and new rows'
Dim ClientComboEntries As New List(Of ComboEntry)
'adapters'
Dim ADP_Companies As New JBADevDataSetTableAdapters.CompaniesTableAdapter
Dim TBL_Companies As New JBADevDataSet.CompaniesDataTable
My current import Code. Pausing execution and exploring state of rows in table shows the -1 ID'
Private Sub ImportClients()
'setup connection with old database table'
Dim oldClientAdp As New MTGJobBook2DataSetTableAdapters.EPI_ClientsTableAdapter
Dim OldClienttble As MTGJobBook2DataSet.EPI_ClientsDataTable = oldClientAdp.GetData()
Console.WriteLine("")
Console.WriteLine("Starting import")
Console.WriteLine(OldClienttble.Count.ToString & " Clients to be added.")
'initially fill the insert table'
ADP_Companies.Fill(tbl_Companies)
Dim Row_Companies As JBADevDataSet.CompaniesRow
'loop through each old company row and insert'
For Each r As MTGJobBook2DataSet.EPI_ClientsRow In OldClienttble.Rows
'Add a new row to the companies table for insert'
Row_Companies = tbl_Companies.NewCompaniesRow()
With Row_Companies
If r.IsPhoneNumber_AltNull Then
.AltPhone = "PhoneNotProvided"
Else
.AltPhone = r.PhoneNumber_Alt
End If
If r.IsAddress_BillingNull Then
.BillingAddress = "Billing address not provided"
Else
.BillingAddress = r.Address_Billing
End If
If r.IsPhoneNumber_MainNull Then
.MainPhone = "Main Phone not provided"
Else
.MainPhone = r.PhoneNumber_Main
End If
If r.IsNameNull Then
.Name = "Client ID " & r.ClientID.ToString
Else
.Name = r.Name
End If
If r.IsAddress_ShippingNull Then
.ShippingAddress = "Shipping Address not provided"
Else
.ShippingAddress = r.Address_Shipping
End If
End With
'add the row to the update table'
tbl_Companies.AddCompaniesRow(Row_Companies)
'Have to get the row from the table after insert in order' to retain reference to tbl_companies row object
ClientComboEntries.Add(New ComboEntry(r.ClientID, tbl_Companies.Rows(tbl_Companies.Rows.Count - 1)))
Next
Dim insertedcnt As Integer
Try
insertedcnt = ADP_Companies.Update(tbl_Companies)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Console.WriteLine(insertedcnt.ToString & " rows inserted")
If Not TBL_Companies.HasErrors Then
MessageBox.Show("success")
'add notes for each entry'
'add contacts'
Else
Console.WriteLine("Client insert failed.")
End If
Also, like I said, I'm a non-developer so constructive criticism about what I'm doing here in general is also welcome.
Thank you in advance for your time.
I've tried finding the answers by searching online but mostly it seems like the people having this issue are using stored procedures or referencing the wrong property when looking up the ID.
The reason why you were getting negative numbers is because your ID is defined as
[ID] [bigint] IDENTITY(-9223372036854775808,1) NOT NULL,
so it starts from that number. You probably want this instead
[ID] [bigint] IDENTITY(1,1) NOT NULL,
I'm not sure why you are using an INSTEAD OF trigger, but it's completely unnecessary. INSTEAD OF triggers have many problematic side effects, and you should avoid them if possible.
If you want to enforce uniqueness on Name, just use a proper UNIQUE constraint.
ALTER TABLE Companies
ADD CONSTRAINT uq_Name UNIQUE (Name);
To enforce user permissions, SQL Server has a complex system of database, schema, table and column level permissions for each user and role. You should use that instead.
Now, to return the results of the inserte data, do not use select top (1) as it is unreliable. Instead use OUTPUT. For example, if you have an IDENTITY column, you can get it back like this
INSERT YourTable (SomeColumn1, SomeColumn2)
OUTPUT inserted.IdColumn
VALUES (SomeVal1, SomeVal2);
If you really really wanted to use a trigger to enforce permissions, I would advise an AFTER trigger.
CREATE OR ALTER TRIGGER [dbo].[Trig_Company_BeforeInsert]
ON [dbo].[Companies]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
--Check if the current user exists.
IF (dbo.USER_CurrentUserIsRegistered() = 0)
BEGIN;
THROW 51000, 'Current user not registered. Transaction canceled.', 10;
END;
--Check if the current user is allowed to Make new companies
DECLARE @userid bigint
SET @userid = dbo.USER_GetCurrent()
IF (
SELECT u.EditCompanies
FROM Users u
WHERE u.ID = @userid)
BEGIN;
THROW 51000, 'Current user does not have permission.', 10;
END;
END
ROLLBACK is unnecessary, as THROW will automatically do that for you.