Typed DATASET - ID of last row inserted

Question

Hi all

I have a SQL Table in winform and a form to insert new record.

Until now I used Untyped Dataset and the thinks worked well

'DAL
Imports System.Data.SqlClient
Imports Libreria

Public Class DALClienti
    Private Property DAL_DS_Clienti As DataSet
    Private Property DAL_DT_Clienti As DataTable
	Private Property DAL_DA_Clienti As SqlDataAdapter


    Public Sub ClientiSaveRowDB(ByVal ds As DataSet, ByVal Nrow As Integer, ByVal op As String)
		DAL_DS_Clienti = ds
        DAL_DT_Clienti = DAL_DS_Clienti.Tables("AnaClienti")
        Dim row As DataRow = DAL_DT_Clienti.Rows(Nrow)
        Dim QueryI = <sql>
					INSERT INTO  Anagrafiche.[AnaClienti]
						([ClienteCodice]
						,[ClienteRagSoc]
						,[ClienteCitta]
						,[ClienteProvincia]
						,[ClienteDataCreazione]
						,[ClienteImporto])
					VALUES ( @Codice, @RagSoc ,@Citta, @Provincia, @DataCreazione, @Importo );
					SELECT ClienteID FROM Anagrafiche.[AnaClienti] 
					WHERE (ClienteID = (SCOPE_IDENTITY()))
					</sql>.Value


        Try
			Using CONN As New SqlConnection With {.ConnectionString = VG.FullCS}
				Using CMD As New SqlCommand With {.Connection = CONN, .CommandType =
				CommandType.Text}
                    ' INSERT COMMAND ---------------------------------	
                    If op = "I" Then

                        CMD.CommandText = QueryI
                        CMD.Parameters.AddWithValue("@Codice", row.Field(Of String)("ClienteCodice"))
                        CMD.Parameters.AddWithValue("@RagSoc", row.Field(Of String)("ClienteRagSoc"))
                        CMD.Parameters.AddWithValue("@Citta", row.Field(Of String)("ClienteCitta"))
                        CMD.Parameters.AddWithValue("@Provincia", row.Field(Of String)("ClienteProvincia"))
                        CMD.Parameters.AddWithValue("@DataCreazione", row.Field(Of DateTime)("ClienteDataCreazione"))
                        CMD.Parameters.AddWithValue("@Importo", row.Field(Of Decimal)("ClienteImporto"))
                    End If
                    CONN.Open()
                    CMD.ExecuteNonQuery()
                    CONN.Close()
                    MessageBox.Show(DAL_DT_Clienti.Rows(Nrow).Item("ClienteID").ToString)
                End Using
			End Using
		Catch sqlex As SqlException
            '.....
            '.....
        End Try
	End Sub

End Class

And with the messagebox I checked the ClienteID (Primary Key autoincrement) of last row inserted and I got the right number

Now I started to use Typed Dataset created by Designer. I keep to use DataAdapter instead of Table adapter. So I use this code to insert new row

Imports System.Data.SqlClient
Imports Libreria


Public Class DALClienti

    Private DAL_DS As DataSetClienti         'TypedDataset created by Designer
    Private DAL_DA As SqlDataAdapter
    Public Sub ClientiSaveRowDB(ByVal ds As DataSetClienti, ByVal Nrow As Integer, ByVal op As String)
        DAL_DS = ds
        Dim row = DAL_DS.AnaClienti.Item(Nrow)
        Dim QueryI = <sql>
					INSERT INTO  Anagrafiche.[AnaClienti]
						([ClienteCodice]
						,[ClienteRagSoc]
						,[ClienteCitta]
						,[ClienteProvincia]
						,[ClienteDataCreazione]
						,[ClienteImporto])
					VALUES ( @Codice, @RagSoc ,@Citta, @Provincia, @DataCreazione, @Importo );
					SELECT ClienteID FROM Anagrafiche.[AnaClienti] 
					WHERE (ClienteID = (SCOPE_IDENTITY()))
					</sql>.Value


        Try
            Using CONN As New SqlConnection With {.ConnectionString = VG.FullCS}
                Using CMD As New SqlCommand With {.Connection = CONN, .CommandType =
                CommandType.Text}
                    ' INSERT COMMAND ---------------------------------	
                    If op = "I" Then
                        CMD.CommandText = QueryI
                       
                        CMD.Parameters.AddWithValue("@Codice", row.ClienteCodice)
                        CMD.Parameters.AddWithValue("@RagSoc", row.ClienteRagSoc)
                        CMD.Parameters.AddWithValue("@Citta", row.ClienteCitta)
                        CMD.Parameters.AddWithValue("@Provincia", row.ClienteProvincia)
                        CMD.Parameters.AddWithValue("@DataCreazione", row.ClienteDataCreazione)
                        CMD.Parameters.AddWithValue("@Importo", row.ClienteImporto)
                    End If
                    CONN.Open()
                    CMD.ExecuteNonQuery()
                    CONN.Close()
                    MessageBox.Show(row.ClienteID.ToString)
                End Using
            End Using
        Catch sqlex As SqlException
            '......
            '......
        End Try
    End Sub
End Class

The Row is well inserted in DB,  but the problem is that now I don't get the last ClienteID for the inserted row.

It gives me the value of -1

The Query is the same. I just used Typed Dataset and table row property to specify parameters

So, how to do to get the last ClienteID 

Thanks for help

Answer 1

The base logic is to use ExecuteScalar

While the long path is;

Both need ExecuteScalar to get the value as ExecuteNonQuery is not capable of that.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Answer 2

Try this query

        Dim QueryI As String = <sql>
            					INSERT INTO  Anagrafiche.[AnaClienti]
            						([ClienteCodice]
            						,[ClienteRagSoc]
            						,[ClienteCitta]
            						,[ClienteProvincia]
            						,[ClienteDataCreazione]
            						,[ClienteImporto])
            					VALUES ( @Codice, @RagSoc ,@Citta, @Provincia, @DataCreazione, @Importo );
                                SELECT SCOPE_IDENTITY() as last_ID;
					</sql>.Value

And then instead of ExecuteNonQuery do this

        Dim id As Integer = CMD.ExecuteScalar

---

"Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it."

- from former MSDN User JohnWein

SerialPort Info

Multics - An OS ahead of its time.

Answer 3

You could always issue a Select statement on getting the Max(ID) number. I don't know how that would work out for you if the DAL is used in a multi user environment.  

Answer 4

It is OK, I get the last ID,  but the problem is that I need the last ID into table ClientiID 

DAL_DS.AnaClienti.Item(Nrow).ClienteID

In fact this table is the datasource of Bindingsource to which a DataGridView is bound

Answer 5

ok Karen but how to have the last ID into ClientiID field of DataTable

I mean 

DAL_DS.AnaClienti.Item(Nrow).ClienteID

that is the dataSOurce of Binding source to which a DGV is boud.

For now in the DGV I still see a -1 value for the new row

Answer 6

I try to explain better the problem

I used a Typed dataset = DAL_DS and a DataAdapter to fill a DataTable = DAL_DS.AnaClienti

When I want to add a new row in database I first add a new row to DataTable.

using a Dim ROW1 =DAL_DS.AnaClienti.NewAnaClientiRow

specifying all fiELDSs excpet row.ClientiID (primaryKey autoincrement in DB)

After adding the new row in DAL_DS.AnaClienti the ROW1.ClientiID = -1

Then I try to add the Row to DB by the INSERT statement and I use your suggestion (ExecuteScalar) that gives me the last DB Table ID

The problem is that  the ROW1.ClientiID has  always value = -1 and is not updated with the last ID used in the  DB Table 

Answer 7

ok Karen but how to have the last ID into ClientiID field of DataTable

I mean 

DAL_DS.AnaClienti.Item(Nrow).ClienteID

that is the dataSOurce of Binding source to which a DGV is boud.

For now in the DGV I still see a -1 value for the new row

You set the value from ExecuteScalar into the new row yourself.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Answer 8

I try to explain better the problem

I used a Typed dataset = DAL_DS and a DataAdapter to fill a DataTable = DAL_DS.AnaClienti

When I want to add a new row in database I first add a new row to DataTable.

using a Dim ROW1 =DAL_DS.AnaClienti.NewAnaClientiRow

specifying all fiELDSs excpet row.ClientiID (primaryKey autoincrement in DB)

After adding the new row in DAL_DS.AnaClienti the ROW1.ClientiID = -1

Then I try to add the Row to DB by the INSERT statement and I use your suggestion (ExecuteScalar) that gives me the last DB Table ID

The problem is that  the ROW1.ClientiID has  always value = -1 and is not updated with the last ID used in the  DB Table 

See if this helps

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Answer 9

So you suggest me first to add row to DB then add row to DataTable with the ID get from ExecuteScalar

Is it Right ?

Answer 10

So you suggest me first to add row to DB then add row to DataTable with the ID get from ExecuteScalar

Is it Right ?

Yes that is correct. If you successfully add a row to the database table then add a row to the DataTable.

The following is SQL-Server example, see BindingNavigatorAddNewItem

https://code.msdn.microsoft.com/Windows-forms-detail-view-89372f21/sourcecode?fileId=169490&pathId=1454738890

I do the same as the above with MS-Access, see AddNewRow_Click.

https://code.msdn.microsoft.com/CRUD-data-operations-for-4783d8dd/sourcecode?fileId=185192&pathId=1226622064

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator