Answered by:
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
Tuesday, June 12, 2018 12:45 PM
Answers
-
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
I do the same as the above with MS-Access, see AddNewRow_Click.
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
- Marked as answer by Claudio111 Wednesday, June 13, 2018 7:13 AM
Tuesday, June 12, 2018 9:42 PM
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
- Proposed as answer by Cherry BuMicrosoft contingent staff Wednesday, June 13, 2018 2:54 AM
Tuesday, June 12, 2018 2:09 PM -
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
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
Tuesday, June 12, 2018 5:42 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
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
Tuesday, June 12, 2018 5:49 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 ?
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
I do the same as the above with MS-Access, see AddNewRow_Click.
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
- Marked as answer by Claudio111 Wednesday, June 13, 2018 7:13 AM
Tuesday, June 12, 2018 9:42 PM