Get back uniqueid?

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

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/

"benji" <benji (AT) discussions (DOT) microsoft.com> wrote

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

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


"Miha Markic" wrote:

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/

"benji" <benji (AT) discussions (DOT) microsoft.com> wrote in message
news:0C418196-09A0-45F5-897D-0A9EFC0A4C87 (AT) microsoft (DOT) com...
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" <benji (AT) discussions (DOT) microsoft.com> wrote

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


"Miha Markic" wrote:

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/

"benji" <benji (AT) discussions (DOT) microsoft.com> wrote in message
news:0C418196-09A0-45F5-897D-0A9EFC0A4C87 (AT) microsoft (DOT) com...
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

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



"Miha Markic" wrote:

"benji" <benji (AT) discussions (DOT) microsoft.com> wrote in message
news:0D4D5A82-0271-4E4E-AADC-282B662E4129 (AT) microsoft (DOT) com...
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


"Miha Markic" wrote:

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/

"benji" <benji (AT) discussions (DOT) microsoft.com> wrote in message
news:0C418196-09A0-45F5-897D-0A9EFC0A4C87 (AT) microsoft (DOT) com...
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

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/

"benji" <benji (AT) discussions (DOT) microsoft.com> wrote

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...

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?

"Miha Markic" wrote:

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/

"benji" <benji (AT) discussions (DOT) microsoft.com> wrote in message
news:1F0FCA65-3644-4A5A-9DC8-7654D2A23BC9 (AT) microsoft (DOT) com...
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...

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

"benji" wrote:

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?

"Miha Markic" wrote:

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/

"benji" <benji (AT) discussions (DOT) microsoft.com> wrote in message
news:1F0FCA65-3644-4A5A-9DC8-7654D2A23BC9 (AT) microsoft (DOT) com...
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,

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" <benji (AT) discussions (DOT) microsoft.com> schreef in bericht
news:0C418196-09A0-45F5-897D-0A9EFC0A4C87 (AT) microsoft (DOT) com...

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

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]" wrote:

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" <benji (AT) discussions (DOT) microsoft.com> schreef in bericht
news:0C418196-09A0-45F5-897D-0A9EFC0A4C87 (AT) microsoft (DOT) com...
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