VB.NET - How do I return an AutoNumber from an Insert Statement using the DataGridView

[Phil Williams]

Hello,
I have a Grid in a VB.Net application which I want to return the Primary Key
which is an AutoNumber after I insert a record. The Primary Key Number that
I get back is not from SQL Express but from VB and it can be wrong.

I have a DataGridView that is bound to my table through my TableAdapter.
If I go to Insert a row and then cancel it ups the autonumber that I see in
VB. Then if Insert a row and save it, the records AutoNumber is not the same
as the SQL AutoNumber.

I have been trying to return the ID from SQL using @@IDENTITY but with no
success.

Any pointers would be much appreciated.
Phil

 

[Tiago Teixeira]

Hi Phill,

I'm very far to be an expert in this stuff, but I think you cannot have
a variavel (parameter name) with spaces, I mean you have "@Parent Alias"
in the insert statement, try "@Parent_Alias" or "@ParentAlias" or
whatever and compile it.

Rgds,

Tiago Teixeira

-----Original Message-----
From: Phil Williams [mailto[email protected]]
Posted At: terça-feira, 24 de Janeiro de 2006 21:46
Posted To: microsoft.public.dotnet.framework.adonet
Conversation: VB.NET - How do I return an AutoNumber from an Insert
Statement using the DataGridView
Subject: VB.NET - How do I return an AutoNumber from an Insert Statement
using the DataGridView

Hello,
I have a Grid in a VB.Net application which I want to return the Primary
Key
which is an AutoNumber after I insert a record. The Primary Key Number
that
I get back is not from SQL Express but from VB and it can be wrong.

I have a DataGridView that is bound to my table through my TableAdapter.
If I go to Insert a row and then cancel it ups the autonumber that I
see in
VB. Then if Insert a row and save it, the records AutoNumber is not the
same
as the SQL AutoNumber.

I have been trying to return the ID from SQL using @@IDENTITY but with
no
success.

Any pointers would be much appreciated.
Phil

 

[Phil Williams]

Thanks for replying but I have no spaces in my parameter name.
I have also found a way for it to work but it seems rather "round about".

In the SQL I return the @@IDENTITY but by default the ID is not a parameter
and when I add it to the parameters collection it keeps disapearing! I think
it does this whenever I go into the SQL or the parameters collection.
Also this way I have to pass each parameter to the SQL which means that I
have to get each item from the Grid. This is also painfull.

Is this the correct way to do this or is there an easier way that I am
missing????

Phil

 

[Bart Mermuys]

Hi,

Thanks for replying but I have no spaces in my parameter name.
I have also found a way for it to work but it seems rather "round about".

In the SQL I return the @@IDENTITY but by default the ID is not a
parameter and when I add it to the parameters collection it keeps
disapearing! I think it does this whenever I go into the SQL or the
parameters collection.
Also this way I have to pass each parameter to the SQL which means that I
have to get each item from the Grid. This is also painfull.

Is this the correct way to do this or is there an easier way that I am
missing????

You don't need to return the auto-key with a parameter, you can return a
single row resultset with the new key:

INSERT INTO table1( field1, field2 ) VALUE ( @field1, @field2 );
SELECT keyfield, field1, field2 FROM table1 WHERE keyfield =
SCOPE_IDENTITY();

With an insert query like that, once you perform a TableAdapter (or
DataAdapter) update it will replace the temporary keys with the new ones
(from db) in the DataSet/DataTable.

If you happen to use a TableAdapter, then you should be able to configure it
so that i will generate an insert command like the above. Inside DataSet
schema designer, click on the TableAdapter to select it, then right click on
your TableAdapter and choose configure. Then click on "Advanced Options..."
and check "Refresh the data table".

HTH,
Greetings

Phil

Hi Phill,

I'm very far to be an expert in this stuff, but I think you cannot have
a variavel (parameter name) with spaces, I mean you have "@Parent Alias"
in the insert statement, try "@Parent_Alias" or "@ParentAlias" or
whatever and compile it.

Rgds,

Tiago Teixeira

-----Original Message-----
From: Phil Williams [mailto[email protected]]
Posted At: terça-feira, 24 de Janeiro de 2006 21:46
Posted To: microsoft.public.dotnet.framework.adonet
Conversation: VB.NET - How do I return an AutoNumber from an Insert
Statement using the DataGridView
Subject: VB.NET - How do I return an AutoNumber from an Insert Statement
using the DataGridView

Hello,
I have a Grid in a VB.Net application which I want to return the Primary
Key
which is an AutoNumber after I insert a record. The Primary Key Number
that
I get back is not from SQL Express but from VB and it can be wrong.

I have a DataGridView that is bound to my table through my TableAdapter.
If I go to Insert a row and then cancel it ups the autonumber that I
see in
VB. Then if Insert a row and save it, the records AutoNumber is not the
same
as the SQL AutoNumber.

I have been trying to return the ID from SQL using @@IDENTITY but with
no
success.

Any pointers would be much appreciated.
Phil

 

[Phil Williams]

Hi Bart and thanks for replying,

I did see that and was trying to work with it but I still could not get the
ID returned from the TableAdapter.Insert method.

This is what I was doing
NewQuestionID = TableAdapterQuestion.Insert(1, "Question")
thinking that that would Insert my Question into my table and return me the
QuestionID, but it always returns 1.
My SQL Insert statement looks like
INSERT INTO tblQuestions( QuestionNumber, Question) VALUE ( @QuestionNumber,
@Question);

SELECT QuestionID, QuestionNumber, Question FROM Questions WHERE
QuestionID = SCOPE_IDENTITY();

However after the save the grid does not display the QuestionID that is
stored in the SQL Database but a VB generated (or disconected dataset
generated) QuestionID.

For example if I have my grid and the last ID is 10. If I then insert but
escape out of the insert without saving it the next time I inset the ID will
show as 12. If I then save that record it will commit the changes to SQL and
SQL will allocate the ID 11 to the record. HOWEVER the record displayed in
the grid will still be 12.

I must need to refresh the TableBindingSource or something. Not just that
but I also need to know what the ID of the newly created record is.

Hi,

Thanks for replying but I have no spaces in my parameter name.
I have also found a way for it to work but it seems rather "round about".

In the SQL I return the @@IDENTITY but by default the ID is not a
parameter and when I add it to the parameters collection it keeps
disapearing! I think it does this whenever I go into the SQL or the
parameters collection.
Also this way I have to pass each parameter to the SQL which means that I
have to get each item from the Grid. This is also painfull.

Is this the correct way to do this or is there an easier way that I am
missing????

You don't need to return the auto-key with a parameter, you can return a
single row resultset with the new key:

INSERT INTO table1( field1, field2 ) VALUE ( @field1, @field2 );
SELECT keyfield, field1, field2 FROM table1 WHERE keyfield =
SCOPE_IDENTITY();

With an insert query like that, once you perform a TableAdapter (or
DataAdapter) update it will replace the temporary keys with the new ones
(from db) in the DataSet/DataTable.

If you happen to use a TableAdapter, then you should be able to configure
it so that i will generate an insert command like the above. Inside
DataSet schema designer, click on the TableAdapter to select it, then
right click on your TableAdapter and choose configure. Then click on
"Advanced Options..." and check "Refresh the data table".

HTH,
Greetings

Phil

Hi Phill,

I'm very far to be an expert in this stuff, but I think you cannot have
a variavel (parameter name) with spaces, I mean you have "@Parent Alias"
in the insert statement, try "@Parent_Alias" or "@ParentAlias" or
whatever and compile it.

Rgds,

Tiago Teixeira

-----Original Message-----
From: Phil Williams [mailto[email protected]]
Posted At: terça-feira, 24 de Janeiro de 2006 21:46
Posted To: microsoft.public.dotnet.framework.adonet
Conversation: VB.NET - How do I return an AutoNumber from an Insert
Statement using the DataGridView
Subject: VB.NET - How do I return an AutoNumber from an Insert Statement
using the DataGridView

Hello,
I have a Grid in a VB.Net application which I want to return the Primary
Key
which is an AutoNumber after I insert a record. The Primary Key Number
that
I get back is not from SQL Express but from VB and it can be wrong.

I have a DataGridView that is bound to my table through my TableAdapter.
If I go to Insert a row and then cancel it ups the autonumber that I
see in
VB. Then if Insert a row and save it, the records AutoNumber is not the
same
as the SQL AutoNumber.

I have been trying to return the ID from SQL using @@IDENTITY but with
no
success.

Any pointers would be much appreciated.
Phil

 

[Bart Mermuys]

Hi,

Hi Bart and thanks for replying,

I did see that and was trying to work with it but I still could not get
the ID returned from the TableAdapter.Insert method.

This is what I was doing
NewQuestionID = TableAdapterQuestion.Insert(1, "Question")
thinking that that would Insert my Question into my table and return me
the QuestionID, but it always returns 1.

It returns the number of affected records... To be honest, haven't used
these direct methods often, mostly TableAdapter.Update, because of the
databinding. But it looks like you could let the regular insert command
alone and add another insert query & method that does return the autonumber.
Click on the TableAdapter in DataSet schema designer, and choose add query,
then choose insert query, use a query like below, finsih wizard and choose a
name for the method like InsertAuto. Then click on InsertAuto method
(inside the TableAdapter) and go to the properties, change ExecuteMode from
NonQuery to Scalar.

NewQuestionID = TableAdapterQuestion.InsertAuto(1, "Question")

Should now return the auto generated key.

My SQL Insert statement looks like
INSERT INTO tblQuestions( QuestionNumber, Question) VALUE (
@QuestionNumber, @Question);


However after the save the grid does not display the QuestionID that is
stored in the SQL Database but a VB generated (or disconected dataset
generated) QuestionID.

For example if I have my grid and the last ID is 10. If I then insert but
escape out of the insert without saving it the next time I inset the ID
will show as 12.

That's normal, working with temporary keys generated by the DataTable,
because the id DataColumn has AutoIncrement set to true.

If I then save that record it will commit the changes to SQL and SQL will
allocate the ID 11 to the record. HOWEVER the record displayed in the grid
will still be 12.

That isn't normal. With a query like above, it should replace all temporary
keys with the DB generated ones, *once* you call TableAdapter.Update. If it
doesn't work for you then i don't know why, maybe a stupid question, but the
ID column is an identity=true column ?

I must need to refresh the TableBindingSource or something. Not just that
but I also need to know what the ID of the newly created record is.

Most of the time, you can work fine with these temporary keys considering
that the real ones are retrieved correctly when TableAdapter.Update is
called.


HTH,
Greetings

Hi,

Thanks for replying but I have no spaces in my parameter name.
I have also found a way for it to work but it seems rather "round
about".

In the SQL I return the @@IDENTITY but by default the ID is not a
parameter and when I add it to the parameters collection it keeps
disapearing! I think it does this whenever I go into the SQL or the
parameters collection.
Also this way I have to pass each parameter to the SQL which means that
I have to get each item from the Grid. This is also painfull.

Is this the correct way to do this or is there an easier way that I am
missing????

You don't need to return the auto-key with a parameter, you can return a
single row resultset with the new key:

INSERT INTO table1( field1, field2 ) VALUE ( @field1, @field2 );
SELECT keyfield, field1, field2 FROM table1 WHERE keyfield =
SCOPE_IDENTITY();

With an insert query like that, once you perform a TableAdapter (or
DataAdapter) update it will replace the temporary keys with the new ones
(from db) in the DataSet/DataTable.

If you happen to use a TableAdapter, then you should be able to configure
it so that i will generate an insert command like the above. Inside
DataSet schema designer, click on the TableAdapter to select it, then
right click on your TableAdapter and choose configure. Then click on
"Advanced Options..." and check "Refresh the data table".

HTH,
Greetings

Phil



Hi Phill,

I'm very far to be an expert in this stuff, but I think you cannot have
a variavel (parameter name) with spaces, I mean you have "@Parent
Alias"
in the insert statement, try "@Parent_Alias" or "@ParentAlias" or
whatever and compile it.

Rgds,

Tiago Teixeira

-----Original Message-----
From: Phil Williams [mailto[email protected]]
Posted At: terça-feira, 24 de Janeiro de 2006 21:46
Posted To: microsoft.public.dotnet.framework.adonet
Conversation: VB.NET - How do I return an AutoNumber from an Insert
Statement using the DataGridView
Subject: VB.NET - How do I return an AutoNumber from an Insert
Statement
using the DataGridView

Hello,
I have a Grid in a VB.Net application which I want to return the
Primary
Key
which is an AutoNumber after I insert a record. The Primary Key Number
that
I get back is not from SQL Express but from VB and it can be wrong.

I have a DataGridView that is bound to my table through my
TableAdapter.
If I go to Insert a row and then cancel it ups the autonumber that I
see in
VB. Then if Insert a row and save it, the records AutoNumber is not the
same
as the SQL AutoNumber.

I have been trying to return the ID from SQL using @@IDENTITY but with
no
success.

Any pointers would be much appreciated.
Phil

 

[Phil Williams]

Thanks for that Bart,
I now can get the id passed back, but can you help me now with passing in
the values from my Grid to the TableAdapters Insert method.

Sometimes the INTEGER values in the grid may be NULL and I am getting a
"System.InvalidCastException" error!
The Field in the Table does accept NULL values.

Hi,

Hi Bart and thanks for replying,

I did see that and was trying to work with it but I still could not get
the ID returned from the TableAdapter.Insert method.

This is what I was doing
NewQuestionID = TableAdapterQuestion.Insert(1, "Question")
thinking that that would Insert my Question into my table and return me
the QuestionID, but it always returns 1.

It returns the number of affected records... To be honest, haven't used
these direct methods often, mostly TableAdapter.Update, because of the
databinding. But it looks like you could let the regular insert command
alone and add another insert query & method that does return the
autonumber. Click on the TableAdapter in DataSet schema designer, and
choose add query, then choose insert query, use a query like below, finsih
wizard and choose a name for the method like InsertAuto. Then click on
InsertAuto method (inside the TableAdapter) and go to the properties,
change ExecuteMode from NonQuery to Scalar.

NewQuestionID = TableAdapterQuestion.InsertAuto(1, "Question")

Should now return the auto generated key.

My SQL Insert statement looks like
INSERT INTO tblQuestions( QuestionNumber, Question) VALUE (
@QuestionNumber, @Question);


However after the save the grid does not display the QuestionID that is
stored in the SQL Database but a VB generated (or disconected dataset
generated) QuestionID.

For example if I have my grid and the last ID is 10. If I then insert but
escape out of the insert without saving it the next time I inset the ID
will show as 12.

That's normal, working with temporary keys generated by the DataTable,
because the id DataColumn has AutoIncrement set to true.

If I then save that record it will commit the changes to SQL and SQL will
allocate the ID 11 to the record. HOWEVER the record displayed in the
grid will still be 12.

That isn't normal. With a query like above, it should replace all
temporary keys with the DB generated ones, *once* you call
TableAdapter.Update. If it doesn't work for you then i don't know why,
maybe a stupid question, but the ID column is an identity=true column ?

I must need to refresh the TableBindingSource or something. Not just that
but I also need to know what the ID of the newly created record is.

Most of the time, you can work fine with these temporary keys considering
that the real ones are retrieved correctly when TableAdapter.Update is
called.


HTH,
Greetings

Hi,

Thanks for replying but I have no spaces in my parameter name.
I have also found a way for it to work but it seems rather "round
about".

In the SQL I return the @@IDENTITY but by default the ID is not a
parameter and when I add it to the parameters collection it keeps
disapearing! I think it does this whenever I go into the SQL or the
parameters collection.
Also this way I have to pass each parameter to the SQL which means that
I have to get each item from the Grid. This is also painfull.

Is this the correct way to do this or is there an easier way that I am
missing????

You don't need to return the auto-key with a parameter, you can return a
single row resultset with the new key:

INSERT INTO table1( field1, field2 ) VALUE ( @field1, @field2 );
SELECT keyfield, field1, field2 FROM table1 WHERE keyfield =
SCOPE_IDENTITY();

With an insert query like that, once you perform a TableAdapter (or
DataAdapter) update it will replace the temporary keys with the new ones
(from db) in the DataSet/DataTable.

If you happen to use a TableAdapter, then you should be able to
configure it so that i will generate an insert command like the above.
Inside DataSet schema designer, click on the TableAdapter to select it,
then right click on your TableAdapter and choose configure. Then click
on "Advanced Options..." and check "Refresh the data table".

HTH,
Greetings


Phil



Hi Phill,

I'm very far to be an expert in this stuff, but I think you cannot
have
a variavel (parameter name) with spaces, I mean you have "@Parent
Alias"
in the insert statement, try "@Parent_Alias" or "@ParentAlias" or
whatever and compile it.

Rgds,

Tiago Teixeira

-----Original Message-----
From: Phil Williams [mailto[email protected]]
Posted At: terça-feira, 24 de Janeiro de 2006 21:46
Posted To: microsoft.public.dotnet.framework.adonet
Conversation: VB.NET - How do I return an AutoNumber from an Insert
Statement using the DataGridView
Subject: VB.NET - How do I return an AutoNumber from an Insert
Statement
using the DataGridView

Hello,
I have a Grid in a VB.Net application which I want to return the
Primary
Key
which is an AutoNumber after I insert a record. The Primary Key Number
that
I get back is not from SQL Express but from VB and it can be wrong.

I have a DataGridView that is bound to my table through my
TableAdapter.
If I go to Insert a row and then cancel it ups the autonumber that I
see in
VB. Then if Insert a row and save it, the records AutoNumber is not
the
same
as the SQL AutoNumber.

I have been trying to return the ID from SQL using @@IDENTITY but with
no
success.

Any pointers would be much appreciated.
Phil

 

[Bart Mermuys]

Hi,

Thanks for that Bart,
I now can get the id passed back, but can you help me now with passing in
the values from my Grid to the TableAdapters Insert method.

Sometimes the INTEGER values in the grid may be NULL and I am getting a
"System.InvalidCastException" error!
The Field in the Table does accept NULL values.

See reply to your other thread above this one.

Greetings

Hi,

Hi Bart and thanks for replying,

I did see that and was trying to work with it but I still could not get
the ID returned from the TableAdapter.Insert method.

This is what I was doing
NewQuestionID = TableAdapterQuestion.Insert(1, "Question")
thinking that that would Insert my Question into my table and return me
the QuestionID, but it always returns 1.

It returns the number of affected records... To be honest, haven't used
these direct methods often, mostly TableAdapter.Update, because of the
databinding. But it looks like you could let the regular insert command
alone and add another insert query & method that does return the
autonumber. Click on the TableAdapter in DataSet schema designer, and
choose add query, then choose insert query, use a query like below,
finsih wizard and choose a name for the method like InsertAuto. Then
click on InsertAuto method (inside the TableAdapter) and go to the
properties, change ExecuteMode from NonQuery to Scalar.

NewQuestionID = TableAdapterQuestion.InsertAuto(1, "Question")

Should now return the auto generated key.

My SQL Insert statement looks like
INSERT INTO tblQuestions( QuestionNumber, Question) VALUE (
@QuestionNumber, @Question);
SELECT QuestionID, QuestionNumber, Question FROM Questions WHERE
QuestionID = SCOPE_IDENTITY();


However after the save the grid does not display the QuestionID that is
stored in the SQL Database but a VB generated (or disconected dataset
generated) QuestionID.

For example if I have my grid and the last ID is 10. If I then insert
but escape out of the insert without saving it the next time I inset the
ID will show as 12.

That's normal, working with temporary keys generated by the DataTable,
because the id DataColumn has AutoIncrement set to true.

If I then save that record it will commit the changes to SQL and SQL
will allocate the ID 11 to the record. HOWEVER the record displayed in
the grid will still be 12.

That isn't normal. With a query like above, it should replace all
temporary keys with the DB generated ones, *once* you call
TableAdapter.Update. If it doesn't work for you then i don't know why,
maybe a stupid question, but the ID column is an identity=true column ?

I must need to refresh the TableBindingSource or something. Not just
that but I also need to know what the ID of the newly created record is.

Most of the time, you can work fine with these temporary keys considering
that the real ones are retrieved correctly when TableAdapter.Update is
called.


HTH,
Greetings

Hi,

Thanks for replying but I have no spaces in my parameter name.
I have also found a way for it to work but it seems rather "round
about".

In the SQL I return the @@IDENTITY but by default the ID is not a
parameter and when I add it to the parameters collection it keeps
disapearing! I think it does this whenever I go into the SQL or the
parameters collection.
Also this way I have to pass each parameter to the SQL which means
that I have to get each item from the Grid. This is also painfull.

Is this the correct way to do this or is there an easier way that I am
missing????

You don't need to return the auto-key with a parameter, you can return
a single row resultset with the new key:

INSERT INTO table1( field1, field2 ) VALUE ( @field1, @field2 );
SELECT keyfield, field1, field2 FROM table1 WHERE keyfield =
SCOPE_IDENTITY();

With an insert query like that, once you perform a TableAdapter (or
DataAdapter) update it will replace the temporary keys with the new
ones (from db) in the DataSet/DataTable.

If you happen to use a TableAdapter, then you should be able to
configure it so that i will generate an insert command like the above.
Inside DataSet schema designer, click on the TableAdapter to select it,
then right click on your TableAdapter and choose configure. Then click
on "Advanced Options..." and check "Refresh the data table".

HTH,
Greetings


Phil



Hi Phill,

I'm very far to be an expert in this stuff, but I think you cannot
have
a variavel (parameter name) with spaces, I mean you have "@Parent
Alias"
in the insert statement, try "@Parent_Alias" or "@ParentAlias" or
whatever and compile it.

Rgds,

Tiago Teixeira

-----Original Message-----
From: Phil Williams [mailto[email protected]]
Posted At: terça-feira, 24 de Janeiro de 2006 21:46
Posted To: microsoft.public.dotnet.framework.adonet
Conversation: VB.NET - How do I return an AutoNumber from an Insert
Statement using the DataGridView
Subject: VB.NET - How do I return an AutoNumber from an Insert
Statement
using the DataGridView

Hello,
I have a Grid in a VB.Net application which I want to return the
Primary
Key
which is an AutoNumber after I insert a record. The Primary Key
Number
that
I get back is not from SQL Express but from VB and it can be wrong.

I have a DataGridView that is bound to my table through my
TableAdapter.
If I go to Insert a row and then cancel it ups the autonumber that I
see in
VB. Then if Insert a row and save it, the records AutoNumber is not
the
same
as the SQL AutoNumber.

I have been trying to return the ID from SQL using @@IDENTITY but
with
no
success.

Any pointers would be much appreciated.
Phil