|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DataAdapter Update Does Nothingwithout error but when I check the database there are no changes. Is there some step I'm missing? Here is my code: OracleConnection con = new OracleConnection(this.MyConnectionString); OracleDataAdapter da = new OracleDataAdapter(); da.SelectCommand = new OracleCommand("select * from " + this.DBOwner + "MyTable where valid_status = 1 and MyTable_id = " + work.ID.ToString(),con); OracleCommandBuilder cb = new OracleCommandBuilder(da); DataSet ds = new DataSet(); da.Fill(ds, "MyTable"); DataRow newRow = ds.Tables[0].NewRow(); newRow["xxxxxxx"] = work.property1; newRow["xxxxxx"] = work.property2; newRow["xxxxxxxxxx"] = work.property3; newRow["xxxxx"] = work.property4; newRow["xxxxx"] = work.property5; ds.Tables[0].Rows.Add(newRow); da.Update(ds,"MyTable"); -- -Demetri Before the Update method, check and see if
a) Any of the RowStates are "Added". ( ds.Tables[0].Rows.Add(newRow); --> Should take care of that I think). b) DataAdapter.InsertCommand.CommandText is indeed what you expect. c) BTW - Also you can trace the commands going to the DB - if the above two fail, try and trace the command. Your problem is within the above three. :) - Sahil Malik [MVP] http://codebetter.com/blogs/sahil.malik/ "Demetri" <Deme***@discussions.microsoft.com> wrote in message work.ID.ToString(),con);news:4077EA24-7C4B-407C-9D12-CDF22534317E@microsoft.com... > I am attempting to insert a new row into the database. It is executing > without error but when I check the database there are no changes. Is there > some step I'm missing? > > Here is my code: > > OracleConnection con = new OracleConnection(this.MyConnectionString); > > OracleDataAdapter da = new OracleDataAdapter(); > da.SelectCommand = new OracleCommand("select * from " + this.DBOwner + > "MyTable where valid_status = 1 and MyTable_id = " + Show quoteHide quote > OracleCommandBuilder cb = new OracleCommandBuilder(da); > > DataSet ds = new DataSet(); > > da.Fill(ds, "MyTable"); > > DataRow newRow = ds.Tables[0].NewRow(); > > newRow["xxxxxxx"] = work.property1; > newRow["xxxxxx"] = work.property2; > newRow["xxxxxxxxxx"] = work.property3; > newRow["xxxxx"] = work.property4; > newRow["xxxxx"] = work.property5; > > ds.Tables[0].Rows.Add(newRow); > > da.Update(ds,"MyTable"); > > -- > -Demetri I put a breakpoint on the da.Update line of code and checked the
InsertCommand property of the dataAdapter in a watch window and low and behold....look what the window showed: da.InsertCommand <undefined value> System.Data.OracleClient.OracleCommand Why isn't the command being built? Show quoteHide quote "Sahil Malik [MVP]" wrote: > Before the Update method, check and see if > > a) Any of the RowStates are "Added". ( ds.Tables[0].Rows.Add(newRow); --> > Should take care of that I think). > b) DataAdapter.InsertCommand.CommandText is indeed what you expect. > c) BTW - Also you can trace the commands going to the DB - if the above two > fail, try and trace the command. > > Your problem is within the above three. :) > > - Sahil Malik [MVP] > http://codebetter.com/blogs/sahil.malik/ > > > > "Demetri" <Deme***@discussions.microsoft.com> wrote in message > news:4077EA24-7C4B-407C-9D12-CDF22534317E@microsoft.com... > > I am attempting to insert a new row into the database. It is executing > > without error but when I check the database there are no changes. Is there > > some step I'm missing? > > > > Here is my code: > > > > OracleConnection con = new OracleConnection(this.MyConnectionString); > > > > OracleDataAdapter da = new OracleDataAdapter(); > > da.SelectCommand = new OracleCommand("select * from " + this.DBOwner + > > "MyTable where valid_status = 1 and MyTable_id = " + > work.ID.ToString(),con); > > OracleCommandBuilder cb = new OracleCommandBuilder(da); > > > > DataSet ds = new DataSet(); > > > > da.Fill(ds, "MyTable"); > > > > DataRow newRow = ds.Tables[0].NewRow(); > > > > newRow["xxxxxxx"] = work.property1; > > newRow["xxxxxx"] = work.property2; > > newRow["xxxxxxxxxx"] = work.property3; > > newRow["xxxxx"] = work.property4; > > newRow["xxxxx"] = work.property5; > > > > ds.Tables[0].Rows.Add(newRow); > > > > da.Update(ds,"MyTable"); > > > > -- > > -Demetri > > > Okay so now that we zeroed in on the reason. :)
Better not rely on the autobuilding of the command. Better specify your own. - Sahil Malik [MVP] http://codebetter.com/blogs/sahil.malik/ "Demetri" <Deme***@discussions.microsoft.com> wrote in message System.Data.OracleClient.OracleCommandnews:FC969587-FE1A-448D-80EE-ED2513342C98@microsoft.com... > I put a breakpoint on the da.Update line of code and checked the > InsertCommand property of the dataAdapter in a watch window and low and > behold....look what the window showed: > > da.InsertCommand <undefined value> > ds.Tables[0].Rows.Add(newRow); -->> Why isn't the command being built? > > "Sahil Malik [MVP]" wrote: > > > Before the Update method, check and see if > > > > a) Any of the RowStates are "Added". ( Show quoteHide quote > > Should take care of that I think). > > b) DataAdapter.InsertCommand.CommandText is indeed what you expect. > > c) BTW - Also you can trace the commands going to the DB - if the above two > > fail, try and trace the command. > > > > Your problem is within the above three. :) > > > > - Sahil Malik [MVP] > > http://codebetter.com/blogs/sahil.malik/ > > > > > > > > "Demetri" <Deme***@discussions.microsoft.com> wrote in message > > news:4077EA24-7C4B-407C-9D12-CDF22534317E@microsoft.com... > > > I am attempting to insert a new row into the database. It is executing > > > without error but when I check the database there are no changes. Is there > > > some step I'm missing? > > > > > > Here is my code: > > > > > > OracleConnection con = new OracleConnection(this.MyConnectionString); > > > > > > OracleDataAdapter da = new OracleDataAdapter(); > > > da.SelectCommand = new OracleCommand("select * from " + this.DBOwner + > > > "MyTable where valid_status = 1 and MyTable_id = " + > > work.ID.ToString(),con); > > > OracleCommandBuilder cb = new OracleCommandBuilder(da); > > > > > > DataSet ds = new DataSet(); > > > > > > da.Fill(ds, "MyTable"); > > > > > > DataRow newRow = ds.Tables[0].NewRow(); > > > > > > newRow["xxxxxxx"] = work.property1; > > > newRow["xxxxxx"] = work.property2; > > > newRow["xxxxxxxxxx"] = work.property3; > > > newRow["xxxxx"] = work.property4; > > > newRow["xxxxx"] = work.property5; > > > > > > ds.Tables[0].Rows.Add(newRow); > > > > > > da.Update(ds,"MyTable"); > > > > > > -- > > > -Demetri > > > > > > That is because it is getting its value from the commandbuilder. This is
the way it is supposed to be. Show quoteHide quote "Demetri" <Deme***@discussions.microsoft.com> wrote in message news:FC969587-FE1A-448D-80EE-ED2513342C98@microsoft.com... >I put a breakpoint on the da.Update line of code and checked the > InsertCommand property of the dataAdapter in a watch window and low and > behold....look what the window showed: > > da.InsertCommand <undefined value> > System.Data.OracleClient.OracleCommand > > Why isn't the command being built? > > "Sahil Malik [MVP]" wrote: > >> Before the Update method, check and see if >> >> a) Any of the RowStates are "Added". ( ds.Tables[0].Rows.Add(newRow); --> >> Should take care of that I think). >> b) DataAdapter.InsertCommand.CommandText is indeed what you expect. >> c) BTW - Also you can trace the commands going to the DB - if the above >> two >> fail, try and trace the command. >> >> Your problem is within the above three. :) >> >> - Sahil Malik [MVP] >> http://codebetter.com/blogs/sahil.malik/ >> >> >> >> "Demetri" <Deme***@discussions.microsoft.com> wrote in message >> news:4077EA24-7C4B-407C-9D12-CDF22534317E@microsoft.com... >> > I am attempting to insert a new row into the database. It is executing >> > without error but when I check the database there are no changes. Is >> > there >> > some step I'm missing? >> > >> > Here is my code: >> > >> > OracleConnection con = new OracleConnection(this.MyConnectionString); >> > >> > OracleDataAdapter da = new OracleDataAdapter(); >> > da.SelectCommand = new OracleCommand("select * from " + this.DBOwner + >> > "MyTable where valid_status = 1 and MyTable_id = " + >> work.ID.ToString(),con); >> > OracleCommandBuilder cb = new OracleCommandBuilder(da); >> > >> > DataSet ds = new DataSet(); >> > >> > da.Fill(ds, "MyTable"); >> > >> > DataRow newRow = ds.Tables[0].NewRow(); >> > >> > newRow["xxxxxxx"] = work.property1; >> > newRow["xxxxxx"] = work.property2; >> > newRow["xxxxxxxxxx"] = work.property3; >> > newRow["xxxxx"] = work.property4; >> > newRow["xxxxx"] = work.property5; >> > >> > ds.Tables[0].Rows.Add(newRow); >> > >> > da.Update(ds,"MyTable"); >> > >> > -- >> > -Demetri >> >> >> I"m chiming in late but Sahil's my homie so I think he'll let it go.
Anyway, noticed you were using a commandbuilder- do you have a key on the table? No key equals Yuck, in general, and when using anything that autogenerates commands in particular. A good test is to try to drag a dataadapter and see if you can configure it - if all the commands are generated. If they aren't, then you can be sure that your commandbuilder stuff is going to cause you some drama. Cheers, Bill "Demetri" <Deme***@discussions.microsoft.com> wrote in message work.ID.ToString(),con);news:4077EA24-7C4B-407C-9D12-CDF22534317E@microsoft.com... > I am attempting to insert a new row into the database. It is executing > without error but when I check the database there are no changes. Is there > some step I'm missing? > > Here is my code: > > OracleConnection con = new OracleConnection(this.MyConnectionString); > > OracleDataAdapter da = new OracleDataAdapter(); > da.SelectCommand = new OracleCommand("select * from " + this.DBOwner + > "MyTable where valid_status = 1 and MyTable_id = " + Show quoteHide quote > OracleCommandBuilder cb = new OracleCommandBuilder(da); > > DataSet ds = new DataSet(); > > da.Fill(ds, "MyTable"); > > DataRow newRow = ds.Tables[0].NewRow(); > > newRow["xxxxxxx"] = work.property1; > newRow["xxxxxx"] = work.property2; > newRow["xxxxxxxxxx"] = work.property3; > newRow["xxxxx"] = work.property4; > newRow["xxxxx"] = work.property5; > > ds.Tables[0].Rows.Add(newRow); > > da.Update(ds,"MyTable"); > > -- > -Demetri Hey dude,
Thats a good answer. I shudda mentioned that muhself !! :) But either way, I'm not too big a fan of CommandBuilder - atleast not in 1.1. In 2.0 it has enhancements that let you specify the ConflictDetection methodology, so the command generated isn't so ugly. With Oracle though, if you really wanted to detect conflicts reliably, you'd have no choice but to check for all columns (CommandBuilder's default option) - and that is hella inefficient. - Sahil Malik [MVP] http://codebetter.com/blogs/sahil.malik/ Show quoteHide quote "W.G. Ryan eMVP" <WilliamR***@gmail.com> wrote in message news:udA9f6DQFHA.1528@TK2MSFTNGP09.phx.gbl... > I"m chiming in late but Sahil's my homie so I think he'll let it go. > Anyway, noticed you were using a commandbuilder- do you have a key on the > table? No key equals Yuck, in general, and when using anything that > autogenerates commands in particular. > > A good test is to try to drag a dataadapter and see if you can configure > it - if all the commands are generated. If they aren't, then you can be sure > that your commandbuilder stuff is going to cause you some drama. > > Cheers, > > Bill > > -- > W.G. Ryan, MVP > > www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com > "Demetri" <Deme***@discussions.microsoft.com> wrote in message > news:4077EA24-7C4B-407C-9D12-CDF22534317E@microsoft.com... > > I am attempting to insert a new row into the database. It is executing > > without error but when I check the database there are no changes. Is there > > some step I'm missing? > > > > Here is my code: > > > > OracleConnection con = new OracleConnection(this.MyConnectionString); > > > > OracleDataAdapter da = new OracleDataAdapter(); > > da.SelectCommand = new OracleCommand("select * from " + this.DBOwner + > > "MyTable where valid_status = 1 and MyTable_id = " + > work.ID.ToString(),con); > > OracleCommandBuilder cb = new OracleCommandBuilder(da); > > > > DataSet ds = new DataSet(); > > > > da.Fill(ds, "MyTable"); > > > > DataRow newRow = ds.Tables[0].NewRow(); > > > > newRow["xxxxxxx"] = work.property1; > > newRow["xxxxxx"] = work.property2; > > newRow["xxxxxxxxxx"] = work.property3; > > newRow["xxxxx"] = work.property4; > > newRow["xxxxx"] = work.property5; > > > > ds.Tables[0].Rows.Add(newRow); > > > > da.Update(ds,"MyTable"); > > > > -- > > -Demetri > > I agree, CommandBuilder is generally not a good idea. You can almost always
create SQL that is more efficient on your own. However, I have seen it be useful to create the SQL and then copy and paste it into your own SQL statement. In other words, use the CommmandBuilder just to get you started with your SQL statement. Anyway, these fine gentlemen have already chimed in with very good comments so I will be quiet now ;-) -- John Papa Show quoteHide quote "Sahil Malik [MVP]" wrote: > Hey dude, > > Thats a good answer. I shudda mentioned that muhself !! :) > > But either way, I'm not too big a fan of CommandBuilder - atleast not in > 1.1. In 2.0 it has enhancements that let you specify the ConflictDetection > methodology, so the command generated isn't so ugly. > > With Oracle though, if you really wanted to detect conflicts reliably, you'd > have no choice but to check for all columns (CommandBuilder's default > option) - and that is hella inefficient. > > - Sahil Malik [MVP] > http://codebetter.com/blogs/sahil.malik/ > > > > "W.G. Ryan eMVP" <WilliamR***@gmail.com> wrote in message > news:udA9f6DQFHA.1528@TK2MSFTNGP09.phx.gbl... > > I"m chiming in late but Sahil's my homie so I think he'll let it go. > > Anyway, noticed you were using a commandbuilder- do you have a key on the > > table? No key equals Yuck, in general, and when using anything that > > autogenerates commands in particular. > > > > A good test is to try to drag a dataadapter and see if you can configure > > it - if all the commands are generated. If they aren't, then you can be > sure > > that your commandbuilder stuff is going to cause you some drama. > > > > Cheers, > > > > Bill > > > > -- > > W.G. Ryan, MVP > > > > www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com > > "Demetri" <Deme***@discussions.microsoft.com> wrote in message > > news:4077EA24-7C4B-407C-9D12-CDF22534317E@microsoft.com... > > > I am attempting to insert a new row into the database. It is executing > > > without error but when I check the database there are no changes. Is > there > > > some step I'm missing? > > > > > > Here is my code: > > > > > > OracleConnection con = new OracleConnection(this.MyConnectionString); > > > > > > OracleDataAdapter da = new OracleDataAdapter(); > > > da.SelectCommand = new OracleCommand("select * from " + this.DBOwner + > > > "MyTable where valid_status = 1 and MyTable_id = " + > > work.ID.ToString(),con); > > > OracleCommandBuilder cb = new OracleCommandBuilder(da); > > > > > > DataSet ds = new DataSet(); > > > > > > da.Fill(ds, "MyTable"); > > > > > > DataRow newRow = ds.Tables[0].NewRow(); > > > > > > newRow["xxxxxxx"] = work.property1; > > > newRow["xxxxxx"] = work.property2; > > > newRow["xxxxxxxxxx"] = work.property3; > > > newRow["xxxxx"] = work.property4; > > > newRow["xxxxx"] = work.property5; > > > > > > ds.Tables[0].Rows.Add(newRow); > > > > > > da.Update(ds,"MyTable"); > > > > > > -- > > > -Demetri > > > > > > >  
Other interesting topics
ADO error "There is already an open DataReader associated with this Connection"
SQLHelper.ExecuteReader - Connection Close Can't Read Excel File (OleDb) w/ ASP.NET Impersonation BLOB fields and Microsoft Access DataRelation Filling multiple tables Oracle Data Providers (Connection Pooling & Transactions) Importing Excel data to Access Simple asynchronous method Making my own strongly typed dataset |
|||||||||||||||||||||||
