|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to insert rows into tables with Autonumber PKey by using DataSet and DataAdapter?I have a strongly typed DataTable that represent a table with database with a primary key. I am trying to use the DataTable to add a new row to the database table by using a DataAdapter. Since the primary key is autonumber, I have to leave the primary key column blank, so my insert stored procedure can return the key to the DataAdapter and DataAdapter assign the key value back to the DataTable column through the output parameter. I assume that above process is standard way to insert rows by using typed DataTables. The problem is that I cannot insert a new row with blank primary key into the typed DataTable. I am sure that I am missing a small point. How can I add a new row without primary key value into the column? Any help would be appreciated, Alan Hi Alan,
I think there might be something wrong with the DataAdapter.InsertCommand property. Could you post your insert command here? I assume you have specified the primary key value as null in the insert command, while it has to be left untouched, and let the SQL server add value itself. Kevin Yu ======= "This posting is provided "AS IS" with no warranties, and confers no rights." Hi Kevin,
> I think there might be something wrong with the DataAdapter.InsertCommand The problem happens before I use DataAdapter> property. I need to create a new DataRow with NULL primary key and add it to DataTable then ask the Adapter.Update to persist it. The problem is that I cannot add such DataRow to the DataTable because the in the DataTable's schema, the Key column is "Allow Null=False" In other words, you must have the PK value to be able to create the DataRow and add it to the DataTable. I don't have the PK value untill I call the stored procedure. Thanks for help Alan Show quote "Kevin Yu [MSFT]" <v-k***@online.microsoft.com> wrote in message news:EK5mBZKHGHA.3944@TK2MSFTNGXA02.phx.gbl... > Hi Alan, > > I think there might be something wrong with the DataAdapter.InsertCommand > property. Could you post your insert command here? I assume you have > specified the primary key value as null in the insert command, while it > has > to be left untouched, and let the SQL server add value itself. > > Kevin Yu > ======= > "This posting is provided "AS IS" with no warranties, and confers no > rights." > Hi,
Show quote "A.M" <alanalan@newsgroup.nospam> wrote in message Ussually if the PK is a numeric identity column (eg. int) then you use a news:uDdyZGLHGHA.3176@TK2MSFTNGP12.phx.gbl... > Hi Kevin, > >> I think there might be something wrong with the DataAdapter.InsertCommand >> property. > The problem happens before I use DataAdapter > > I need to create a new DataRow with NULL primary key and add it to > DataTable then ask the Adapter.Update to persist it. > The problem is that I cannot add such DataRow to the DataTable because the > in the DataTable's schema, the Key column is "Allow Null=False" > > In other words, you must have the PK value to be able to create the > DataRow and add it to the DataTable. I don't have the PK value untill I > call the stored procedure. temporary key by setting the PK DataColumn to AutoIncrement and AutoIncrementStep to -1. Then when DataAdapter.Update is called, the temporary keys are replaced with the real one. Offcourse for this to work, you must not include the pk column name inside the "INSERT INTO" statement. It's commonly done this way. HTH, Greetings Show quote > > Thanks for help > Alan > > > "Kevin Yu [MSFT]" <v-k***@online.microsoft.com> wrote in message > news:EK5mBZKHGHA.3944@TK2MSFTNGXA02.phx.gbl... >> Hi Alan, >> >> I think there might be something wrong with the DataAdapter.InsertCommand >> property. Could you post your insert command here? I assume you have >> specified the primary key value as null in the insert command, while it >> has >> to be left untouched, and let the SQL server add value itself. >> >> Kevin Yu >> ======= >> "This posting is provided "AS IS" with no warranties, and confers no >> rights." >> > > Thanks Bart, Your post solved my problem!
Thanks alot Show quote "Bart Mermuys" <bmermuys.nospam@hotmail.com> wrote in message news:eA65iDPHGHA.1628@TK2MSFTNGP12.phx.gbl... > Hi, > > "A.M" <alanalan@newsgroup.nospam> wrote in message > news:uDdyZGLHGHA.3176@TK2MSFTNGP12.phx.gbl... >> Hi Kevin, >> >>> I think there might be something wrong with the >>> DataAdapter.InsertCommand property. >> The problem happens before I use DataAdapter >> >> I need to create a new DataRow with NULL primary key and add it to >> DataTable then ask the Adapter.Update to persist it. >> The problem is that I cannot add such DataRow to the DataTable because >> the in the DataTable's schema, the Key column is "Allow Null=False" >> >> In other words, you must have the PK value to be able to create the >> DataRow and add it to the DataTable. I don't have the PK value untill I >> call the stored procedure. > > Ussually if the PK is a numeric identity column (eg. int) then you use a > temporary key by setting the PK DataColumn to AutoIncrement and > AutoIncrementStep to -1. > > Then when DataAdapter.Update is called, the temporary keys are replaced > with the real one. > > Offcourse for this to work, you must not include the pk column name inside > the "INSERT INTO" statement. > > It's commonly done this way. > > HTH, > Greetings > > >> >> Thanks for help >> Alan >> >> >> "Kevin Yu [MSFT]" <v-k***@online.microsoft.com> wrote in message >> news:EK5mBZKHGHA.3944@TK2MSFTNGXA02.phx.gbl... >>> Hi Alan, >>> >>> I think there might be something wrong with the >>> DataAdapter.InsertCommand >>> property. Could you post your insert command here? I assume you have >>> specified the primary key value as null in the insert command, while it >>> has >>> to be left untouched, and let the SQL server add value itself. >>> >>> Kevin Yu >>> ======= >>> "This posting is provided "AS IS" with no warranties, and confers no >>> rights." >>> >> >> > > Hi, I have de same problem,I think, can you help me?
I have a store procedure to insert rows, nad I use a tableadapter to execute the store procedure, but when I run my application I see the row added but when I return to my database the row don't exist. My code to execute the store procedure is: dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter prensa = new Prensas.dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter(); int i = prensa.Update(ID, this.txtNombre.Text, int.Parse(txtNumero.Text)); My store rpocedure code is : ALTER PROCEDURE spu_Prensa ( @ID int=0, @sNombre varchar(15), @bNumero int =0 ) AS if @ID=0 begin insert into tPrensa (sNombre,bNumero) values (@sNombre,@bNumero) end else begin update tPrensa set sNombre=@sNombre, bNumero=@bNumero where ID=@ID end RETURN And I use Id as int autoincrement too. I hope you can help me! Regards. Hi Diana,
The 1st thing that I see in you SP code is it doesn't return the new PK value to the DataAdapter Have a look at this article. It explains everything except that you must set AutoNumberIncrementStep -1 http://www.codeproject.com/cs/database/relationaladonet.asp HTH Show quote "Diana Estrada" <DianaEstr***@discussions.microsoft.com> wrote in message news:AFCD57C7-480E-43F7-811D-B8FD07FCEF9F@microsoft.com... > Hi, I have de same problem,I think, can you help me? > I have a store procedure to insert rows, nad I use a tableadapter to > execute > the store procedure, but when I run my application I see the row added but > when I return to my database the row don't exist. My code to execute the > store procedure is: > > dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter prensa = new > Prensas.dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter(); > int i = prensa.Update(ID, this.txtNombre.Text, int.Parse(txtNumero.Text)); > > My store rpocedure code is : > ALTER PROCEDURE spu_Prensa > > ( > @ID int=0, > @sNombre varchar(15), > @bNumero int =0 > ) > > AS > if @ID=0 > begin > insert into tPrensa (sNombre,bNumero) > values (@sNombre,@bNumero) > end > else > begin > update tPrensa > set sNombre=@sNombre, bNumero=@bNumero > where ID=@ID > end > RETURN > > And I use Id as int autoincrement too. > I hope you can help me! > Regards. > > Sorry, but I try it and I can´t , now I change using commands, but when I
close the application and reun again the rows aren´t there, in the data base, now my code is: SqlConnection sqlConnection1 = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbPrensas2006.mdf;Integrated Security=True;User Instance=True"); SqlCommand cmd = new SqlCommand(); Object returnValue; cmd.CommandText = "spu_Prensa"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = sqlConnection1; cmd.Parameters.Add("@ID",SqlDbType.Int).Value=ID; cmd.Parameters.Add("@sNombre",SqlDbType.VarChar ,15).Value = txtNombre.Text; cmd.Parameters.Add("@bNumero",SqlDbType.Int).Value = int.Parse(txtNumero.Text); sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); this.Close(); Help with some idea please!! Thanks a lot Regards!! Hi,
"Diana Estrada" <DianaEstr***@discussions.microsoft.com> wrote in message The "|DataDirectory|" alias is what's causing the problem, you should not news:A219EDA3-B253-4A10-A6AB-A6A35522D632@microsoft.com... > Sorry, but I try it and I can´t , now I change using commands, but when I > close the application and reun again the rows aren´t there, in the data > base, > now my code is: > SqlConnection sqlConnection1 = new SqlConnection(@"Data > Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbPrensas2006.mdf;Integrated > Security=True;User Instance=True"); use it, it doesn't work well within an IDE env, use an absolute path to the DB file instead. The result of using "|DataDirectory|" and a DB file in your vs project is that each time your app starts it will copy the DB from your project directory into the \bin\debug folder and use that one, next time your app starts it gets overwritten and all changes are lost. Can you make a simple fresh project ? Create a new project and add a new Data Source (Data Source window), when you create the Data Source, the wizard will ask if you want to copy the DB inside your project then it's important to say "No". Next drag a DataTable from the Data Sources window to the Form and you end up with a complete data entry Form. Now run it and see if updates work. If you have chosen to copy the DB into your project, then the connectionstring will use "|DataDirectory|" inside .app.config, if you have chosen NOT to copy the DB into your project then the connectionstring within app.config will use an absolute path (which works better). I hope this makes it clear. HTH, Greetings Show quote > SqlCommand cmd = new SqlCommand(); > Object returnValue; > > cmd.CommandText = "spu_Prensa"; > cmd.CommandType = CommandType.StoredProcedure; > cmd.Connection = sqlConnection1; > cmd.Parameters.Add("@ID",SqlDbType.Int).Value=ID; > cmd.Parameters.Add("@sNombre",SqlDbType.VarChar ,15).Value = > txtNombre.Text; > cmd.Parameters.Add("@bNumero",SqlDbType.Int).Value = > int.Parse(txtNumero.Text); > sqlConnection1.Open(); > cmd.ExecuteNonQuery(); > > sqlConnection1.Close(); > this.Close(); > > Help with some idea please!! > Thanks a lot > Regards!! > > Hi, thanks a lot!! I found the error, Bart your idea was excellent, the
problem was that my dataset make a copy in debug directory and allways when I run my application this overwrite, I don´t need to make a new dataset only I look the mdf file properties inside VS2005 and it have a Copy to output directory property, and I had copy always and now I change it to Copy if newer and it solve my problem. Realy, thanks a lot for your help!!! I love you!!! jejeje See you. Regards Hi Diana,
Have you used profiler to track what is the SQL command that database receives? If you issue the same command within query analyzer, do you have rows in the table? The reason that I asked is that I think it is not your c# code issue and it might be database side issue. Alan Show quote "Diana Estrada" <DianaEstr***@discussions.microsoft.com> wrote in message news:A219EDA3-B253-4A10-A6AB-A6A35522D632@microsoft.com... > Sorry, but I try it and I can´t , now I change using commands, but when I > close the application and reun again the rows aren´t there, in the data > base, > now my code is: > SqlConnection sqlConnection1 = new SqlConnection(@"Data > Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbPrensas2006.mdf;Integrated > Security=True;User Instance=True"); > SqlCommand cmd = new SqlCommand(); > Object returnValue; > > cmd.CommandText = "spu_Prensa"; > cmd.CommandType = CommandType.StoredProcedure; > cmd.Connection = sqlConnection1; > cmd.Parameters.Add("@ID",SqlDbType.Int).Value=ID; > cmd.Parameters.Add("@sNombre",SqlDbType.VarChar ,15).Value = > txtNombre.Text; > cmd.Parameters.Add("@bNumero",SqlDbType.Int).Value = > int.Parse(txtNumero.Text); > sqlConnection1.Open(); > cmd.ExecuteNonQuery(); > > sqlConnection1.Close(); > this.Close(); > > Help with some idea please!! > Thanks a lot > Regards!! > > Hi,
"Diana Estrada" <DianaEstr***@discussions.microsoft.com> wrote in message What's i ? If it is 1 then the update succeeded and the row is stored inside news:AFCD57C7-480E-43F7-811D-B8FD07FCEF9F@microsoft.com... > Hi, I have de same problem,I think, can you help me? > I have a store procedure to insert rows, nad I use a tableadapter to > execute > the store procedure, but when I run my application I see the row added but > when I return to my database the row don't exist. My code to execute the > store procedure is: > > dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter prensa = new > Prensas.dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter(); > int i = prensa.Update(ID, this.txtNombre.Text, int.Parse(txtNumero.Text)); the DB. The reason you don't see it might be caused by a new feature in VS2005: storing your DB file inside your project ( Solution Explorer ). The simpliest workaround is not to choose "copy the DB into project" when you configure a DataSource or DataConnection. You can still read/modify the DB using Database Explorer window. (see also http://tinyurl.com/deuzx ) Show quote > I'm not entirely sure, but don't you need to use different sp's for insert, > My store rpocedure code is : > ALTER PROCEDURE spu_Prensa > > ( > @ID int=0, > @sNombre varchar(15), > @bNumero int =0 > ) > > AS > if @ID=0 > begin > insert into tPrensa (sNombre,bNumero) > values (@sNombre,@bNumero) > end > else > begin > update tPrensa > set sNombre=@sNombre, bNumero=@bNumero > where ID=@ID > end > RETURN update and delete commands so that the TableAdapter correctly works when updating DataTable too... ALTER PROCEDURE spu_PrensaInsert ( @sNombre varchar(15), @bNumero int ) AS ÏNSERT INTO tPrensa (sNombre,bNumero) VALUES (@sNombre,@bNumero); SELECT * FROM tPrense WHERE ID=SCOPE_IDENTITY(); RETURN Use this sp for the insert command on the TableAdapter and then use: dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter prensa = new Prensas.dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter(); ' direct insert int i = prensa.Insert(this.txtNombre.Text, int.Parse(txtNumero.Text)); HTH, Greetings Show quote > > And I use Id as int autoincrement too. > I hope you can help me! > Regards. > >  |
|||||||||||||||||||||||
Other interesting topics