Click here to Skip to main content
15,748,615 members
Please Sign up or sign in to vote.
4.67/5 (6 votes)
See more:
Hi All

I am developing an n-tier application using (vs2010) and have been able to access my data through a dataset.

Everything works very well until I have to submit some data to the database.

This is my function in the DataManager:

Public Function SaveReceipt(ByRef NewReceipt As ZHRISBLL.ExamsRegisterDataSet) As Boolean
        Dim saved = False
        If NewReceipt.HasChanges AndAlso Not NewReceipt.HasErrors Then
            Dim Manager As New ZHRISDAL.ExamsRegisterDataSetTableAdapters.TableAdapterManager

            Manager.InsertReceiptTableAdapter = New ZHRISDAL.ExamsRegisterDataSetTableAdapters.InsertReceiptTableAdapter
            Manager.BackupDataSetBeforeUpdate = True
            saved = (Manager.UpdateAll(NewReceipt) > 0)
        End If
        Return saved
    End Function

The above code is supposed to handle saving data to the database table. Other functions are saving successfully

I want to save to a table with an IDENTITY column that generates the Primary Key for the entry, and then my table adapter has to extract the generated PK and send it back to the client. (I am using the SCOPE_IDENTITY SQL function and have been able to see the value returned in the Dataset.TableAdapter Preview Data facility in Data Source Design) However I do not see the new record inserted into the database, but if I insert a record into the database table manually the autogenerated PK is higher than the one returned in VS2010 Dataset Designer TableAdapter preview.

To try and insert the newly created data from the client side I have the following code handling the click event from the client form:

Private Sub btnSaveAndPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveAndPrint.Click

       Using proxy As New ZHRISService.ServiceClient

           Dim changes = CType(Me.ExamsRegisterDataSet.GetChanges, ZHRISBLL.ExamsRegisterDataSet)

           If changes IsNot Nothing Then
               If proxy.SaveReceipt(changes) Then

                   Dim addedrows = From row In Me.ExamsRegisterDataSet.Receipts _
                                   Where row.RowState = DataRowState.Added
                   For Each row In addedrows.ToArray


                   MsgBox("Receipt Generated")
                   MsgBox("Receipt not Generated")
               End If

           End If
       End Using

   End Sub

I have the following code in the Service and IService respectively


<OperationContract()> _
    Function SaveReceipt(ByRef NewReceipt As ExamsRegisterDataSet) As Boolean


Public Function SaveReceipt(ByRef NewReceipt As ZHRISBLL.ExamsRegisterDataSet) As Boolean Implements IService.SaveReceipt
        Dim Manager As New ZHRISDataUpdateManager
        Return (Manager.SaveReceipt(NewReceipt))
    End Function

When I run my code I do not get any errors but the message "Receipt Not Generated".

Could I get help on the following in summary:

1. The Function that saves to the database and returns the transaction's generated Primary Key.
2. The correct Code for my client form's click event so that they will be able to use the newly generated PK from the database for further operations on their form.

Take note I am really new to but a fast learner, any help is appreciated even if it's criticism on my approach and code.

Updated 10-Jun-11 4:40am
DaveAuld 10-Jun-11 10:40am    
edit: removed extra formatting tags

1 solution

If I were you I would create a stored procedure in the database which returns the identity of the saved data (@@identity).

You will be able to test the sp separately and then all you need to do is pass in the parameters (hte data you want to save).

This will simplify your code and be more efficient while also being easier to test.
Share this answer
SimbarasheM 15-Jun-11 2:58am    

@@IDENTITY has it's inherent problems in multi tier applications and moreso multi-user applications. I prefer SCOPE_IDENTITY for the reason that it returns the primary key created for the transaction owned by the current insert statement. While @@Identity works well with single user database applications and for MS Access I don't see it working well for my application. Check This Article for more.

If you follow This Other Question. You will discover how I am working on a solution for an N-Tier VB.NET application using SCOPE_IDENTITY.

Thanks for your response


This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900