How to insert a record with a stored procecure

[dbuchanan]

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

 

[dbuchanan]

Hello,

I discovered that it depends on how the update is called. Following are
two examples #1 works and #2 does not

1.) The data adapter directly updates the dataset
\\
DALa.da101PortSize.Update(_dataset1, "lkp101PortSize")
//

2.) A special dataset is created and populated with the records that
canged in the dataset. Then the update is made from that special
dataset. Then the changes are merged and accepted back into the
dataset.
\\
UpdateSource2(DALa.da101PortSize, "lkp101PortSize")

Protected Sub UpdateSource2(ByVal dA As SqlDataAdapter, ByVal tbl As
String)
Dim dsDataChanges As New CLIP.dsTables

dsDataChanges = CType(_dataSet1.GetChanges, CLIP.dsTables)
dA.Update(dsDataChanges, tbl)
_dataSet1.Merge(dsDataChanges)
_dataSet1.AcceptChanges()

End Sub
//

Perhaps there is a way to handle those changes so that the temporaty
record is removed. Does anyone understand how to do that?

Thank you,
dbuchanan

 

[Bart Mermuys]

Hi,

Hello,

I discovered that it depends on how the update is called. Following are
two examples #1 works and #2 does not

1.) The data adapter directly updates the dataset
\\
DALa.da101PortSize.Update(_dataset1, "lkp101PortSize")
//

2.) A special dataset is created and populated with the records that
canged in the dataset. Then the update is made from that special
dataset. Then the changes are merged and accepted back into the
dataset.
\\
UpdateSource2(DALa.da101PortSize, "lkp101PortSize")

Protected Sub UpdateSource2(ByVal dA As SqlDataAdapter, ByVal tbl As
String)
Dim dsDataChanges As New CLIP.dsTables

dsDataChanges = CType(_dataSet1.GetChanges, CLIP.dsTables)
dA.Update(dsDataChanges, tbl)
_dataSet1.Merge(dsDataChanges)
_dataSet1.AcceptChanges()

End Sub
//

Perhaps there is a way to handle those changes so that the temporaty
record is removed. Does anyone understand how to do that?

See http://support.microsoft.com/default.aspx?scid=kb;en-us;313540 .

If you want to understand what is going wrong, then you need to know the
steps the DataAdapter performs when it updates the DataBase and also that
each row has three values for each field (old, new & temp).

This is how the row looks when it comes from GetChanges:
[ RowState = Inserted, NewKey = -1 ]

Once the adapter inserts the row in the DataBase then AcceptChanges is
called on that row, so the row looks like:
[ RowState = Unchanged, OldKey = -1, NewKey = -1 ]

Then the adapter fetches the new key:
[ RowState = Modified, OldKey = -1, NewKey = 5 ]

But now it goes wrong, the DataAdapter will call AcceptChanges again:
[ RowState = Unchanged, OldKey = 5, NewKey = 5 ]

At this point you *lost* the old key and mergings fails with duplicate rows
since keys are used to match.

The solution is to add an event to DataAdapter.RowUpdated (not Updating) and
then set Action to skipcurrentrow for inserted rows, this will make it so
that the second AcceptChanges is not called, so your row keeps the old and
new key and it can merge. After the merging you have to call
AccceptChanges.


HTH,
Greetings