Get back uniqueid?

[benji]

Hi,

I'm using VS2008, and the dataset designer to access my SQL Server 2005 DB.
I can insert a row into a table and then call the adapter to update. No
problem, except I want to get back the ID field of the newly created row.
I've tried the following:

DataSet1.CustomersRow myrow = myCustomersTable.NewCustomersRow();
myrow.field1 = "xyz"

myCustomersTable.AddCustomersRow(myrow);
myCustomersAdapter.Update(myCustomersTable);

Label1.Text = myrow.NonGroupChellengeID.ToString()

This returns -1. I can get the right value if I access the row directly on
the datatable after calling update, but as it stands I just access the last
row, and that might not be clean. ANy suggestions for this? I have to imagine
this is a common scenario?

Thanks...

-Ben

 

[Miha Markic]

You should check out the definition of myCustomersAdapter.InsertCommand - it
should have ;SELECT Scope_Identity() at the end or something like that.

 

[benji]

Hi Miha,

2 followup questions:

1) Would I not need a stored procedure for a compound SQL stamement?
2) After I have that in place, where would I retrieve the value in the data
access layer?

Thanks..

-Ben

You should check out the definition of myCustomersAdapter.InsertCommand - it
should have ;SELECT Scope_Identity() at the end or something like that.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi,

I'm using VS2008, and the dataset designer to access my SQL Server 2005
DB.
I can insert a row into a table and then call the adapter to update. No
problem, except I want to get back the ID field of the newly created row.
I've tried the following:

DataSet1.CustomersRow myrow = myCustomersTable.NewCustomersRow();
myrow.field1 = "xyz"

myCustomersTable.AddCustomersRow(myrow);
myCustomersAdapter.Update(myCustomersTable);

Label1.Text = myrow.NonGroupChellengeID.ToString()

This returns -1. I can get the right value if I access the row directly on
the datatable after calling update, but as it stands I just access the
last
row, and that might not be clean. ANy suggestions for this? I have to
imagine
this is a common scenario?

Thanks...

-Ben

 

[Miha Markic]

Hi Miha,

2 followup questions:

1) Would I not need a stored procedure for a compound SQL stamement?

No, you can have more than one statement in commandtext (depending on the
provider, sql can handle multiple statements)

2) After I have that in place, where would I retrieve the value in the
data
access layer?

Depends on the mappings associated with DataAdapter.

HTH
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Thanks..

-Ben

You should check out the definition of myCustomersAdapter.InsertCommand -
it
should have ;SELECT Scope_Identity() at the end or something like that.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi,

I'm using VS2008, and the dataset designer to access my SQL Server 2005
DB.
I can insert a row into a table and then call the adapter to update. No
problem, except I want to get back the ID field of the newly created
row.
I've tried the following:

DataSet1.CustomersRow myrow = myCustomersTable.NewCustomersRow();
myrow.field1 = "xyz"

myCustomersTable.AddCustomersRow(myrow);
myCustomersAdapter.Update(myCustomersTable);

Label1.Text = myrow.NonGroupChellengeID.ToString()

This returns -1. I can get the right value if I access the row directly
on
the datatable after calling update, but as it stands I just access the
last
row, and that might not be clean. ANy suggestions for this? I have to
imagine
this is a common scenario?

Thanks...

-Ben

 

[benji]

Hi Miha,

I added another query to the table, which was an insert query. I then
appended:

;SELECT Scope_Identity()

it compiled fine. It generated the expected insert method in the
tableadapter class, and the signature had it returning an int. However, the
int always returns 1 (probably indicating success). Could you go into more
detail regarding what you mean by " Depends on the mappings associated with
DataAdapter."? Thanks...

-Ben

Hi Miha,

2 followup questions:

1) Would I not need a stored procedure for a compound SQL stamement?

No, you can have more than one statement in commandtext (depending on the
provider, sql can handle multiple statements)

2) After I have that in place, where would I retrieve the value in the
data
access layer?

Depends on the mappings associated with DataAdapter.

HTH
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Thanks..

-Ben

You should check out the definition of myCustomersAdapter.InsertCommand -
it
should have ;SELECT Scope_Identity() at the end or something like that.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi,

I'm using VS2008, and the dataset designer to access my SQL Server 2005
DB.
I can insert a row into a table and then call the adapter to update. No
problem, except I want to get back the ID field of the newly created
row.
I've tried the following:

DataSet1.CustomersRow myrow = myCustomersTable.NewCustomersRow();
myrow.field1 = "xyz"

myCustomersTable.AddCustomersRow(myrow);
myCustomersAdapter.Update(myCustomersTable);

Label1.Text = myrow.NonGroupChellengeID.ToString()

This returns -1. I can get the right value if I access the row directly
on
the datatable after calling update, but as it stands I just access the
last
row, and that might not be clean. ANy suggestions for this? I have to
imagine
this is a common scenario?

Thanks...

-Ben

 

[Miha Markic]

Here is the idea

a) you'd need a mapping between database column and your column, i.e. (Id
<=> Id):
adapter.TableMapping.ColumnMappings.Add("Id", "Id");
b) you'd append this statement after the insert:
SELECT Id = SCOPE_IDENTITY()

BTW all this code is generated for you, if you drag & drop a table from
Server Explorer to dataset designer - you could try and see what code it
generates.

 

[benji]

Hi Miha,

I already have this mapping in place for the uniqueid column. When I do a
fill, I can access the value for every row through the generated dataset
class. But it seems I can't access it through my generated Query, despite
appending
;SELECT ID = Scope_Identity()
The method generated simply returns an int for success or failure of
insertion. I'm not sure how having the UniqueID mapped into the table helps
in this way?

Here is the idea

a) you'd need a mapping between database column and your column, i.e. (Id
<=> Id):
adapter.TableMapping.ColumnMappings.Add("Id", "Id");
b) you'd append this statement after the insert:
SELECT Id = SCOPE_IDENTITY()

BTW all this code is generated for you, if you drag & drop a table from
Server Explorer to dataset designer - you could try and see what code it
generates.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi Miha,

I added another query to the table, which was an insert query. I then
appended:

;SELECT Scope_Identity()

it compiled fine. It generated the expected insert method in the
tableadapter class, and the signature had it returning an int. However,
the
int always returns 1 (probably indicating success). Could you go into more
detail regarding what you mean by " Depends on the mappings associated
with
DataAdapter."? Thanks...

 

[benji]

Hi Miha,

I found the solution. After creating the query, I had to go to its
properties and change "ExecuteMode" from nonquery to scalar.

Best,

-Ben

Hi Miha,

I already have this mapping in place for the uniqueid column. When I do a
fill, I can access the value for every row through the generated dataset
class. But it seems I can't access it through my generated Query, despite
appending
;SELECT ID = Scope_Identity()
The method generated simply returns an int for success or failure of
insertion. I'm not sure how having the UniqueID mapped into the table helps
in this way?

Here is the idea

a) you'd need a mapping between database column and your column, i.e. (Id
<=> Id):
adapter.TableMapping.ColumnMappings.Add("Id", "Id");
b) you'd append this statement after the insert:
SELECT Id = SCOPE_IDENTITY()

BTW all this code is generated for you, if you drag & drop a table from
Server Explorer to dataset designer - you could try and see what code it
generates.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi Miha,

I added another query to the table, which was an insert query. I then
appended:

;SELECT Scope_Identity()

it compiled fine. It generated the expected insert method in the
tableadapter class, and the signature had it returning an int. However,
the
int always returns 1 (probably indicating success). Could you go into more
detail regarding what you mean by " Depends on the mappings associated
with
DataAdapter."? Thanks...

 

[Cor Ligthert[MVP]]

Benji,

Are you sure you are talking about a Global Unique ID or are you just
talking about an integer that is set by SQL automaticly to the next
available free number accoording its seed (I am almost sure you are talking
about the latter)

As you use a GUID, then set it in advance in your program, its is globaly
unique so there should not be any problem.

Cor

 

[benji]

Hi Cor,

You are right, I want the integer set by SQL. Everything appears to be
functioning, what in my code suggests I'm using a GUID?

Thanks...

-Ben

 

[Cor Ligthert[MVP]]

You are right, I want the integer set by SQL. Everything appears to be
functioning, what in my code suggests I'm using a GUID?

Nothing, only your message, have a look at the subject you have used.

That is why I have asked this before we are giving you answers about that
while you are using an automatic seeded integer.

Which is given back in your datatable by the update while using the
SQLClient adapters (when using the right insert SQL script), but not with
OleDB. (Be aware that using a execute scalar with the scope identity will
give you only back the ID of the latest inserted row).

For the rest see Miha's messages about the mappings.

Cor