|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
VB.NET - How do I return an AutoNumber from an Insert Statement using the DataGridViewHello,
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 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:Phillandsa***@hotmail.com] 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 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 "Tiago Teixeira" <teixeira1***@gmail.com> wrote in message news:000101c62137$c4fe8410$830aa8c0@portatiltt...Show quote > 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:Phillandsa***@hotmail.com] > 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 > > Hi,
Show quote "Phil Williams" <Phillandsa***@hotmail.com> wrote in message You don't need to return the auto-key with a parameter, you can return a news:%23R9lgIUIGHA.1388@TK2MSFTNGP11.phx.gbl... > 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???? 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 Show quote > > Phil > > > > "Tiago Teixeira" <teixeira1***@gmail.com> wrote in message > news:000101c62137$c4fe8410$830aa8c0@portatiltt... >> 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:Phillandsa***@hotmail.com] >> 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 >> >> > > 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 However after the save the grid does not display the QuestionID that is > QuestionID = SCOPE_IDENTITY(); 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. Show quote "Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message news:Ov5VeaaIGHA.2912@tk2msftngp13.phx.gbl... > Hi, > > "Phil Williams" <Phillandsa***@hotmail.com> wrote in message > news:%23R9lgIUIGHA.1388@TK2MSFTNGP11.phx.gbl... >> 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 >> >> >> >> "Tiago Teixeira" <teixeira1***@gmail.com> wrote in message >> news:000101c62137$c4fe8410$830aa8c0@portatiltt... >>> 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:Phillandsa***@hotmail.com] >>> 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 >>> >>> >> >> > > Hi,
"Phil Williams" <Phillandsa***@hotmail.com> wrote in message It returns the number of affected records... To be honest, haven't used news:OzdnGHfIGHA.3896@TK2MSFTNGP15.phx.gbl... > 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. 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. Show quote > My SQL Insert statement looks like That's normal, working with temporary keys generated by the DataTable, > 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. 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 That isn't normal. With a query like above, it should replace all temporary > allocate the ID 11 to the record. HOWEVER the record displayed in the grid > will still be 12. 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 ? > Most of the time, you can work fine with these temporary keys considering > 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. that the real ones are retrieved correctly when TableAdapter.Update is called. HTH, Greetings Show quote > > > > > > "Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message > news:Ov5VeaaIGHA.2912@tk2msftngp13.phx.gbl... >> Hi, >> >> "Phil Williams" <Phillandsa***@hotmail.com> wrote in message >> news:%23R9lgIUIGHA.1388@TK2MSFTNGP11.phx.gbl... >>> 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 >>> >>> >>> >>> "Tiago Teixeira" <teixeira1***@gmail.com> wrote in message >>> news:000101c62137$c4fe8410$830aa8c0@portatiltt... >>>> 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:Phillandsa***@hotmail.com] >>>> 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 >>>> >>>> >>> >>> >> >> > > 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. Show quote "Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message news:%23TGdqugIGHA.3984@TK2MSFTNGP14.phx.gbl... > Hi, > > "Phil Williams" <Phillandsa***@hotmail.com> wrote in message > news:OzdnGHfIGHA.3896@TK2MSFTNGP15.phx.gbl... >> 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 > >> >> >> >> >> >> "Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message >> news:Ov5VeaaIGHA.2912@tk2msftngp13.phx.gbl... >>> Hi, >>> >>> "Phil Williams" <Phillandsa***@hotmail.com> wrote in message >>> news:%23R9lgIUIGHA.1388@TK2MSFTNGP11.phx.gbl... >>>> 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 >>>> >>>> >>>> >>>> "Tiago Teixeira" <teixeira1***@gmail.com> wrote in message >>>> news:000101c62137$c4fe8410$830aa8c0@portatiltt... >>>>> 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:Phillandsa***@hotmail.com] >>>>> 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 >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > Hi,
"Phil Williams" <Phillandsa***@hotmail.com> wrote in message See reply to your other thread above this one.news:uC01ZchIGHA.3936@TK2MSFTNGP12.phx.gbl... > 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. Greetings Show quote > > > > "Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message > news:%23TGdqugIGHA.3984@TK2MSFTNGP14.phx.gbl... >> Hi, >> >> "Phil Williams" <Phillandsa***@hotmail.com> wrote in message >> news:OzdnGHfIGHA.3896@TK2MSFTNGP15.phx.gbl... >>> 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 >> >>> >>> >>> >>> >>> >>> "Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message >>> news:Ov5VeaaIGHA.2912@tk2msftngp13.phx.gbl... >>>> Hi, >>>> >>>> "Phil Williams" <Phillandsa***@hotmail.com> wrote in message >>>> news:%23R9lgIUIGHA.1388@TK2MSFTNGP11.phx.gbl... >>>>> 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 >>>>> >>>>> >>>>> >>>>> "Tiago Teixeira" <teixeira1***@gmail.com> wrote in message >>>>> news:000101c62137$c4fe8410$830aa8c0@portatiltt... >>>>>> 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:Phillandsa***@hotmail.com] >>>>>> 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 >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics