none
Typed DATASET - ID of last row inserted RRS feed

  • 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

    Tuesday, June 12, 2018 12:45 PM

Answers

All replies

  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, June 12, 2018 2:09 PM
    Moderator
  • 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.

    Tuesday, June 12, 2018 2:10 PM
  • 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.  
    Tuesday, June 12, 2018 2:11 PM
  • 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

    Tuesday, June 12, 2018 2:59 PM
  • 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

    Tuesday, June 12, 2018 3:04 PM
  • 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 

    Tuesday, June 12, 2018 5:01 PM
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, June 12, 2018 5:42 PM
    Moderator
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, June 12, 2018 5:49 PM
    Moderator
  • So you suggest me first to add row to DB then add row to DataTable with the ID get from ExecuteScalar

    Is it Right ?


    Tuesday, June 12, 2018 8:28 PM
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Claudio111 Wednesday, June 13, 2018 7:13 AM
    Tuesday, June 12, 2018 9:42 PM
    Moderator