Hello,
My insert results in two records - or better stated the temporary
record (with the temporary ID value of -1) stays as the stored
procedure returns the new record with its data base assigned ID value
Below is shown the ID field and one data field. Notice the last two
records shown.
1 bat
2 frog
3 wren
-1 cat < this is the lingering temporary record
4 cat < this is the actual new row
When I close and reopen the form the temporary record will go away.
(I use GUIDs for my Id's so am not familar with auto-increment
syntax...)
Below is my code. Can you tell me what I am missing?
Here is my insert stored procedure;
\\
CREATE PROCEDURE dbo.usp_102Phase_ins(
@Phase varchar(50),
@Ord tinyint,
@Hide bit
) AS
SET NOCOUNT OFF;
INSERT INTO lkp102Phase(
Phase,
Ord,
Hide
) VALUES (
@Phase,
@Ord,
@Hide
);
SELECT
pkPhaseId,
Phase,
Ord,
Hide
FROM lkp102Phase
WHERE
(pkPhaseID = @@Identity)
//
Below is the code behind my update button;
\\
Sub btnEndEdit_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnEndEdit.Click
'Save changes to to the local dataset
_bmb.EndCurrentEdit()
Call UpdateSource2(DALa.da102Phase, "lkp102Phase")
End sub
Protected Sub UpdateSource2(ByVal dA As SqlDataAdapter, _
ByVal tbl As String)
Dim dsDataChanges As New CLIP.dsTables
dsDataChanges = CType(_dataSet1.GetChanges, CLIP.dsTables)
If (Not (dsDataChanges) Is Nothing) Then
Try
dA.Update(dsDataChanges, tbl)
_dataSet1.Merge(dsDataChanges)
_dataSet1.AcceptChanges()
Catch ...
End Try
End If
End Sub
//
Here is the DataAccess code - Command parameter
\\
With cmd102Phase_Ins
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_102Phase_ins"
.Connection = sqlConn
With cmd102Phase_Ins.Parameters
.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, _
ParameterDirection.ReturnValue, False, CType(0, Byte), _
CType(0, Byte), "", DataRowVersion.Current, Nothing))
''.Add(New SqlParameter("@pkPhaseId", SqlDbType.Int, 4, "pkPhaseId"))
.Add(New SqlParameter("@Phase", SqlDbType.VarChar, 50, "Phase"))
.Add(New SqlParameter("@Ord", SqlDbType.TinyInt, 1, "Ord"))
.Add(New SqlParameter("@Hide", SqlDbType.Bit, 1, "Hide"))
End With
End With
//
Here is the table;
\\
CREATE TABLE [lkp102Phase] (
[pkPhaseId] [int] IDENTITY (1, 1) NOT NULL ,
[Phase] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Ord] [tinyint] NOT NULL CONSTRAINT [DF_lkp102Phase_ord] DEFAULT (0),
[Hide] [bit] NOT NULL CONSTRAINT [DF_lkp102Phase_hide] DEFAULT (0),
CONSTRAINT [PK_lkp102Phase] PRIMARY KEY CLUSTERED
(
[pkPhaseId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
//
What am I missing that I causes the temporary record to remain in the
datagrid?
Thank you,
dbuchanan
