Question How to retrive the ID of the recently saved record.

[rajhansh]

Hi,

is there any way to get the row ID for the recently saved record. Below is some details,

I am saving records in a table "Items" which is having ID column as a primary column and it's type is set to autonumber.

I need to get the value of this column (ID) for the record that was recently saved.
I am using dataset for saving records. Below is code snapshot that I am using for saving records,

dim sql as string="Select * from Items"
dim da as oledb.oledbdataadapter=new oledb.oledbdataadapter(sql,conn)

dim cb as oledb.oledbcommandbuilder = new oledb.oledbcommandbuilder (da)

if ds.haschanged then
   da.update(ds,"Items")
ds.acceptchanges

da.dispose

conn.close
conn.dispose

 

[rajhansh]

Hi,

anyone please help.

 

[rajhansh]

I am really in very big issue.
Can anyone please help me.

 

[ddivita]

Does this help:

If ds.HasChanges then
     call GetChanges(ds)
End If

Sub GetChanges(byval currentDS as dataset)

dim tempDataset as dataset = currentDS.GetChanges(DataRowState.Modified)

...iterate through the tempDataset to see what IDs were modified...
end sub

Is this what you were looking for?

end sub

 

[rajhansh]

Let me explain u a bit about the application.

The application needs to save records in two table (ITEM and ITEM_DESC).
ITEM have has primary key ID which is a autonumber.
The primary key for ITEM table is a foreign ket to ITEM_DESC table.

There is a command button "SAVE" which needs to save records in both tables.

The problem is that, when I am saving a record in ITEM table, I am unable to retrieve the primary key, so that I can store that in ITEM_DESC table.

I cann't use MAX(ID) as more than one users can be saving records in a instance.

Is there any way out to get the primary key of the record just saved?

 

[ddivita]

So, you want to insert data into the dataset object and get the newly created ID back. I would think this would be a function of a DataTable object. I know how to get it back in SQL and retrieve it via VB.net, but I am not sure about DataTable or DataSet objects.

Daniel

 

[ddivita]

Unless you are looking for a SQL solution, it is not clear to me, but here is some code:

CREATE PROCEDURE dbo.ins_StaticFolder(
                                  @projectID int,
                                  @sfName varchar(30),
                                  @sfDisplayName varchar(100),
                                  @sfDescription varchar(500),                                  
                                  @sfDateCreated datetime,
                                  @sfDateModified dateTime,
                                  @staticFolderID int output
                                  )
AS
BEGIN
	Insert Into dbo.StaticFolders(projectID,name,displayName,description,dateCreated,dateModified)
    VALUES(@projectID,@sfName,@sfDisplayName,@sfDescription,@sfDateCreated,@sfDateModified)
    
    Set @staticFolderID = (SELECT @@IDENTITY)
    
END

as you can see in the SP there is a variable @staticFolderID that is set as an OUTPUT varaible. This means that you can access the varaible outside of the SQL server. Then, in your code you would do something likt this....

...All of your conenction code here....
Dim sqlParms(0  to 6) as SQLParameter

sqlParms(0) = new SQLParameter("@projectID",INT)
sqlParms(0).value = projectIDValue
...........
sqlParms(6) = new sqlPArameter("@staticFolderID",INT)
sqlParms(6).Direction = Output

....Execute your SQL....

This way when you execute your SQL you can get access to that parapmeters value when it is compelted.

Daniel

 

[cjard]

If youre using TableAdapters, they have an option in the Advanced button of the TableAdapter Configuration Wizard (r-click the TA and choose Configure) called "refresh the dataset" -> read the text underneath it