Update requires a valid UpdateCommand when passed DataRow collection with modified rows

I use an OracleCommandBuilder to build the commands. For some reason, I get the error "Update requires a valid UpdateCommand when passed DataRow collection with modified rows" The code looks like this: OracleDataAdapter adapter = new OracleDataAdapter( selectquery, connection ); OracleCommandBuilder builder = new OracleCommandBuilder( adapter); return dbAdapter.Update(ds); I thought that the parameters were automatically inserted from the dataset, and I see that an updatecommand is generated. So, what am I not getting here

Posted On: Wednesday 21st of November 2012 02:37:22 AM Total Views:  996
View Complete with Replies

Related Messages:

How to Read an open Excel File which is updated Continuosly   (397 Views)
How to Read an open Excel File which is updated Continuosly. I have to read data from an excel file which is updated Continuosly on another server and it remains open all the time. Please suggest me the right way to proceed.
How to update data returned using REF CURSOR   (221 Views)
Hi all, I am trying to update updated data in a gridview but the update button seem to do nothing as i retreive data using REF CURSOR. Let me describe the architecture of my application first. I'm trying to implement best practice whenever possible. I am following the data access tutorial published in , the only difference is that i have an Oracle (10g) database. So I split my application into three layers, data access, business logic, and presentation layer. I'm also writing all queries in an Oracle package. So here is the code i am using: Oracle Package: the CRUD functions. Notice the first procedure (READ)returns a REF Cursor /*===================TABLE:TF_REF_TYPES===============================*/ /*=======================================================================*/ PROCEDUREget_ref_type(ref_type_cursorOUTT_CURSOR)AS BEGIN OPENref_type_cursorFOR SELECTref_type_id,ref_type FROMtf_ref_types; ENDget_ref_type; PROCEDUREcreate_ref_type(p_ref_typeINVARCHAR2,p_created_byINVARCHAR2)AS BEGIN INSERTINTOtf_ref_types(ref_type,created_by,created_date) VALUES(p_ref_type,p_created_by,SYSDATE); ENDcreate_ref_type; PROCEDUREupdate_ref_type(p_ref_typeINVARCHAR2,p_ref_type_idINNUMBER,p_updated_byINVARCHAR2)AS BEGIN UPDATEtf_ref_types SETref_type=p_ref_type, updated_by=p_updated_by, updated_date=SYSDATE WHEREref_type_id=p_ref_type_id; ENDupdate_ref_type; PROCEDUREdelete_ref_type(p_ref_type_idINNUMBER)AS ref_type_countNUMBER; BEGIN --Verifythatnoreferencesareusingthereferencetype. SELECTCOUNT(ref_id)INTOref_type_count FROMtf_references WHEREref_type_id=p_ref_type_id; IFref_type_count>0THEN raise_application_error(-20018,'Therecordcannotbedeletedbecausethereistableincludingrelatedrecords.$'); ELSE DELETEFROMtf_ref_types WHEREref_type_id=p_ref_type_id; ENDIF; ENDdelete_ref_type; The I have xsd file that define a tableAdapter that uses the procedures defined above. Next I have a .cs file where my business logic will be and calls the function fromthe xsd file. And I am assuming that this is where i need to make the change to allow row updates.[System.ComponentModel.DataObject] public class ReferencesTypeBLL { private REF_TYPETableAdapter _refTable = null; protected REF_TYPETableAdapter Adapter { get { if (_refTable == null) _refTable = new REF_TYPETableAdapter(); return _refTable; } } [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)] public References.REF_TYPEDataTable GetReferenceTypes() { Object x; return Adapter.GetRefType(out x); } [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)] public References.REF_TYPEDataTable GetReferenceTypeByID(int typeID) { Object x; if (typeID == -1) return GetReferenceTypes(); else return Adapter.GetRefTypeByID(typeID, out x); } [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)] public bool AddReferenceType(string REF_TYPE) { int rowsAffected = Adapter.Insert(REF_TYPE, "Admin"); return rowsAffected == 1; } [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)] public bool UpdateReferenceType(int REF_TYPE_ID, string REF_TYPE) { References.REF_TYPEDataTable refType = GetReferenceTypeByID(REF_TYPE_ID); if (refType.Count == 0) return false; /*References.REF_TYPERow typeItem = refType[0]; typeItem.REF_TYPE_ID = typeID; typeItem.REF_TYPE = value;*/ int rowsAffected = Adapter.Update(REF_TYPE, REF_TYPE_ID, "Admin"); return rowsAffected == 1; } } Presentation Layer: In a nutshell, I am just trying to update records retrieved using REF CURSOR. Your help is very much appreciated. Please let me know if further details are required.Cheers,
update query problem   (231 Views)
dear friends when i m firing mysql update query in query analyzer window it updates the data in my table. but when i fire that table frm my stored procedure at that time it doesnt update my table data. it doesnt show any error shows only message 0 Rows affected. yes i want to say one thing that in my stored procedure i m passing2 flags 1-for insert 2-for update. in this stored procedure first i was using different table but then i have created one new table nd just changed this table name in stored procedure. can it create any problem nd with my old table it was wrking fine. i dont really understand what is the problem can anyone help me to figure this out oracle update   (209 Views)
, I'm trying to attempt my first oracle update in code. This is an update off of a gridview. Below is my code. When I run Iget this error: ORA-00911: invalid character. I checked the values of strPassW, txtSecurity and txtUserId and they are all valid. Can you tell me what is wrong
can i write my all SQL insert,update and delete statement in a dll file and how it will work   (575 Views)
hi Forum Members, i donot want to write my sql Insert,Update And Delete Statement in my codebehind page i want i write all my sql query in a file ad make it dll when my project load it first compile all dll file there is no need to sql statement execute for every page if it is possible then how if i want to pass my variables to that file how i will writ if any body have solution with small example then plz.send me thank you Subhakanta , Besides using stored procedures, you should move all your data access code into a separate class project. This will allow: Code re-useUnit testing of the codeYou will be able to check the code using FXCOP and StyleCop
How do i use objectdatasource to update records   (309 Views)
In context of MySqlDriverCS. I tried it with select statement and it works. If you have any sample C# code to show it would be helpful.I guess and Mysql is also hot future combination.
insert,update & delete problem for oracle db from vs.net2005 using c# & object data source   (311 Views)
how i can refer to the varchar2 data type from 2005 how i can update, insert & delete the database ENTRY How i can oracle stored procedure from vs.net2005how can i pass parameters
update into mysql   (208 Views)
hello, first read now I want to ask if someone can help me make a code to edit the data in my database and then update it. all I need is a little sample.
Oracle in VS 2005: update and concurrency access   (251 Views)
Hi I'm a newbie with environment. my aim is to create a website with an access to Oracle database. I can connect to Oracle database and see web page containing Gridview. But I cannot access to the menu enable update and enable delete. So VS consider the data as read only. More over, I cannot access to the advanced menu in the data source parmetrisation allowing to control concurrency acess to the database (since it's read only for the moment, it's not an important issue). Is is a problem with the provider configuration What I should do to enable edit and delete actions NB: if I choose the ODBC driver from MS, I can update the database.
is there a way update Blob image in the database?   (289 Views)
I am currently using Oracle database and need to update a row with Blob column. I know how to insert new row with Blob column - its explained here but I have no idea how to update a row that already exists. I tried something oike this, but it didnt work, because it wasnt possible to convert byte[] to System.Data.DataRow HttpPostedFile myPhoto = FileUpload.PostedFile; int photoSize = myPhoto.ContentLength; byte[] myData = new byte[photoSize]; myPhoto.InputStream.Read(myData, 0, photoSize); OracleDataAdapter adapter = new OracleDataAdapter("select * from webpages", Connection); OracleCommandBuilder oracleBuild = new OracleCommandBuilder(adapter); DataSet dbSet = new DataSet(); adapter.Fill(dbSet, "Webpages"); DataTable dbTable = dbSet.Tables["Webpages"]; DataRow[] dbRow = dbTable.Select("id_webpage=" + ViewState["IdUser"].ToString()); dbRow["user_photo"] = myData; // error here adapter.Update(dbSet, "Webpages"); Could please someone tell me where am I doing a mistake
batch update with As/400   (280 Views)
I'm trying to do batch update to AS/400 table through ADO. My code is as follows: DataSet dsChanges = oDs0.GetChanges(); // get the changed records from another dataset //prepare insert statement for AS/400 table"ABC" string sqlText2 = "insert into ABC" + " (TRXDATE, STRNBR, REGNBR, TRXNO)" + " values " + " (@TRXDATE, @STRNBR, @REGNBR, @TRXNO)"; OleDbDataAdapter oDa2 = new OleDbDataAdapter(); oDa2.InsertCommand = new OleDbCommand(); oDa2.InsertCommand.CommandText = sqlText2; oDa2.InsertCommand.Connection = oCon0; // set sql parameters oDa2.InsertCommand.Parameters.Add( du.OleCreateParameter( "@TRXDATE", "TRXDATE", OleDbType.Decimal, 6, 0) ); oDa2.InsertCommand.Parameters.Add( du.OleCreateParameter( "@STRNBR", "STRNBR", OleDbType.Decimal, 4, 0) ); oDa2.InsertCommand.Parameters.Add( du.OleCreateParameter( "@REGNBR", "REGNBR", OleDbType.Decimal, 3, 0) ); oDa2.InsertCommand.Parameters.Add( du.OleCreateParameter( "@TRXNO", "TRXNO", OleDbType.Decimal, 4, 0) ); // do the batch update oDa2.Update( dsChanges, "ABC" ); I try update all the records in dataset (dsChanges) into AS/400 table, but it always shows the following errors: "System.Data.OleDb.OleDbException: SQL0206: Column @TRXNOnot in specified tables. Cause . . . . . : @TRXNOis not a column of table *N in library *N. If the table is *N, @TRXNOis not a column of any table or view that can be referenced. Recovery . . . : Do one of the following and try the request again: -- Ensure that the column and table names are specified correctly in the statement. -- If this is a SELECT statement, ensure that all the required tables were named in the FROM clause. -- If the column was intended to be a correlated reference, qualify the column with the correct table designator." Is it something to do with the OLE driver OR I miss something. Is batch update well supported in OLE AS/400 driver A single update seems working normally but batch update doesn't work, why Any advice is very much appreciated.
table blocked with "... for update NOWAIT"   (307 Views)
, I have a table which is blocked because of experiments with select... for update nowait any ideas on how to delock the table
transaction problem in mysql for update for each row of gridview   (291 Views)
i am having a transaction related problem regarding mysql.. need some advice how to proceed !!! here is a gridview for my application and i am inserting or updating for each row.... like foreach(GridViewRowrowinGridPaymentSchedule.Rows) { if(row.RowType==DataControlRowType.DataRow) { if(payment.Update(PayDetails,0)) { success=true; } } } if(success==true) { lblmsg.Text="PaymentScheduleDetailsUpdated"; ClearFields(); } well now what i want is if any of the row update or insert failes ... it should revert all the changes that was already made into the database ... even a single row update .. into that perticular table ... the code which i have called forpayment.Update(PayDetails, 0) is : public Boolean Update(ProjectPaymentForm Payment, int flag) { MySqlConnection con = new MySqlConnection(Connection.GetConnectionString()); if (con.State == ConnectionState.Open) { con.Close(); } con.Open(); try { using (MySqlCommand cmd = new MySqlCommand("UPDATE payment_schedule_details SET" + " actual_date = ActualDate," + " actual_amount = ActualAmount," + " reason_for_delay = ReasonForDelay where payment_schedule_id=PayScheduleId" + " and milestone_desc=MileStone", con)) { //cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("PaymentScheduleID", Payment.ScheduleId); cmd.Parameters.AddWithValue("ActualDate", (Payment.ActualDate.ToString("yyyy-MM-dd") == null DBNull.Value.ToString() : Payment.ActualDate.ToString("yyyy-MM-dd"))); cmd.Parameters.AddWithValue("ActualAmount", (Payment.ActualAmount == null 0 : Payment.ActualAmount)); cmd.Parameters.AddWithValue("ReasonForDelay", Payment.ReasonForDelay); cmd.Parameters.AddWithValue("PayScheduleId", Payment.ScheduleId); cmd.Parameters.AddWithValue("MileStone", Payment.ProjectMileStone); cmd.Parameters.AddWithValue("flag", flag); if (cmd.ExecuteNonQuery() > 0) { con.Close(); return true; } } } catch (MySqlException ex) { throw ex; } return true; } can someone help me out how to proceed in this scenario how do i undo each of the updates made into the table payment schedule details ...
Conflict detection updates more than one row when I only want one!   (348 Views)
IhaveaMySqldatabasesetwithaprimarykeyof: cust_code Thisprimarykeycannotbechangedtoautoincrement,itmustbeassignedbyandcorrelatetoaseparateprogram! WhenIperformtheupdatecomandeveryrowthathasdatainanyfieldthatisthesameastheentryIamtryingtoupdateisupdatedinsteadofjusttheonethatthecustomercodecorrespondsto. Itseamsasifitisupdatinganyrowthathasduplicationsuchassamelastnames. Isuspectithassomethingtodowiththeconflictdetection,butIamnotsureifitcanbefixed. WhenIeliminatetheconflictdetectionforupdatingoneveryfieldbuttheprimarykey,itupdates(onlytheselectedentry)perfectly. However,thatreallydefeatsthepurposeofconflictdetection. Iwouldliketobeabletodetectandoverwriteconflictsonlywithinthecust_codethatisselectedforediting. Iwillneverbeabletoavoidduplicatenames,dollars,etc. Besidestheweb.configfile,theprogramisthreefiles:UpdatePrefCustomers.aspx prefcust_edit.aspx prefcust_insert.aspx UpdatePrefCustomers.aspxisusedtoaccesstheothertwoedit&insertpages. prefcust_insert.aspxdoesnothaveanyproblems,it'sonlyprefcust_edit.aspxthatisaproblem. Anyhelpwouldbegreatlyappreciated. Thefollowingistheaccesspage,UpdatePrefCustomers.aspx: PreferredCustomersatLawrencevilleHondaYamaha! functionConfirmOnDelete() { if(confirm("AreyousuretodeletethisPreferredCustomer")==true) returntrue; else returnfalse; } .style1{ text-align:center; margin-left:auto; margin-right:auto; } .style2{ text-align:center; font-size:60pt; font-family:Mistral; color:#0000FF; } h1{color:#003366;font-size:24px;margin:0px00px0; line-height:6px;} .style3{ text-align:center; font-size:x-large; } .style4{ text-align:center; margin-top:0; margin-bottom:0; } .style5{ text-align:center; font-size:60pt; font-family:Mistral; } LawrencevilleHondaYamaha PREFERREDCUSTOMERS Tosort,clickoncolumnheader. Delete This next one is the problem child! I cannot bold the code, so, lines 118 through 131are the linesthat makethe page work when I remove them. protected void DetailsView1_ModeChanging(Object sender, System.Web.UI.WebControls.DetailsViewModeEventArgs e) { if (e.CancelingEdit == true) { Response.Redirect("UpdatePrefCustomers.aspx"); } } protected void srcPrefCustomersEdit_Updated(object sender, SqlDataSourceStatusEventArgs e) { if (e.AffectedRows == 0) lblMessage.Text = "Could not update record because it has changed since you opened this page"; else Response.Redirect("UpdatePrefCustomers.aspx"); } Edit Preferred Customers The following is prefcust_insert.aspx which is not causing a problem: protected void DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e) { Response.Redirect("UpdatePrefCustomers.aspx"); } protected void DetailsView1_ModeChanging(Object sender, System.Web.UI.WebControls.DetailsViewModeEventArgs e) { if (e.CancelingEdit == true) { Response.Redirect("UpdatePrefCustomers.aspx"); } } Insert Preferred Customers
Cannot update the cursor mytable   (164 Views)
When I run my application inside the Visual web developer, everything works perfectly, but when i run it on internet explorer in this way: http://localhost/webapplication/default.aspx and try to update I get this error "Cannot update the cursor mytable, since it is read-only" Below is the code for updating: OleDbCommand cmd = new OleDbCommand("update mytable set status=) // it is a visual foxpro table cmd.Parameters.Add("@status", OleDbType.Char, 1).Value = "D"; cmd.ExecuteNonQuery(); // here is the error I don't know why it works when running in VWD but doesn't work in internet explorer on the SAME computer. Anyone can help
mysql connector auto update command specifies incorrect database need another way to update   (275 Views)
I use Typed Datasets for my DAL and class files for my BLL The Application uses multiple databases depending on what user logs in. The MSSQL autogenerated update method uses a sort of agnostic database variable [dbo]. so when the app passes in a different dabase name in its connection string it updates the correct database. However the MYSQL autogenerated code for the update command actually specifies the name of the dabase that the tableadapter is using when you create the typed dataset. so even if you pass in a different database in the connection still goes to the original database name. This is how I currently update rows in a database in my BLL using System; using System.Data; using System.Configuration; using System.Web; using VinWinBLL.DAL.CONTACTDAL.CompanyDALTableAdapters; using VinWinBLL.DAL.CONTACTDAL; namespace VinWinBLL.BLL.CONTACTBLL { /// /// Summary description for CompanyBLL /// public class CompanyBLL { private tblcompanyTableAdapter _tblcompanyAdapter = null;internal tblcompanyTableAdapter CompanyAdapter { get { if (_tblcompanyAdapter == null) _tblcompanyAdapter = new tblcompanyTableAdapter(); return _tblcompanyAdapter; } } public bool UpdateCompany(long companyid, string company, bool active) { CompanyDAL.tblcompanyDataTable companies = CompanyAdapter.GetCompanyDataByCompanyID(companyid.Value);if (companies.Count == 0) { return false; } CompanyDAL.tblcompanyRow comp = companies[0]; comp.Active = active; comp.Company = company; int rowsAffected = CompanyAdapter.Update(comp);return rowsAffected == 1; } I apparently need another way to handle this since the autogenerated code using teh mysql connector has this bug. I am thinkingof adding a query in the tableadapter to "Update" the databale. I do this with the insert and it works properly making use of the connection string databse name. Mainly because the sql statement doesnt specify the database name like the autogenerated methods do. For example the autogenerated insert this._adapter.InsertCommand.CommandText = "INSERT INTO `vinwin_base_dbo`.`tblcompany` (`Company`, `Active`) VALUES (@Company" + ", @Active)"; My added insert methods sql statement this._commandCollection[5].CommandText = "INSERT INTO tblcompany\r\n (Company, Active)\r\nVALUES (@Com" + "pany, @Active);\r\nSelect last_insert_id();\r\n"; Notice how my sql statement doesnt specify the database name. To access the insert I use the following in my BLL class file private tblcompanyTableAdapter _tblcompanyAdapter = null;internal tblcompanyTableAdapter CompanyAdapter { get { if (_tblcompanyAdapter == null) _tblcompanyAdapter = new tblcompanyTableAdapter(); return _tblcompanyAdapter; } } public long AddCompany(string company, bool active) { byte b_active = Convert.ToByte(active); long companyid = Convert.ToInt64(CompanyAdapter.InsertQuery(company, b_active)); return companyid; } So what I am wondering is if I create in teh table adapater an update method so that it looks like this this._commandCollection[6].CommandText = @"UPDATE tblcompany SET Company = @Company, Active = @Active WHERE (CompanyID = @Original_CompanyID) AND (@IsNull_Company = 1) AND (Company IS NULL) AND (Active = @Original_Active) OR (CompanyID = @Original_CompanyID) AND (Company = @Original_Company) AND (Active = @Original_Active)"; Problem is I am not sure how to handle this in the BLL. If I try to handle it in the same manner using the new "UpdateQuery" methodI get a build error No Overload for Method UpdateQuery takes 1 argument.
to Calculated Columns, then to update another column in same table in ADO.NET   (309 Views)
toget severalColumns, then pass those Columns into one function which will return a result, then using the result to update another column in same table in ADO.NET, How can I do this
update query   (281 Views)
HiI want to perform bulk update for the code below. can anybody suggest DECLARE tmp_shipping_code VARCHAR2(256); str_token VARCHAR2(256); INDEX_a NUMBER; up_str_value VARCHAR2(2000); tmp_ownerid VARCHAR2(255); tmp_stringval VARCHAR2(255); CURSOR o_cursor IS SELECT stringvalue, ownerid FROM PRODTEST_AVWHERE name = 'ShippingOptions' ; curs_row o_cursor%ROWTYPE; BEGINFOR curs_row IN o_cursorLOOPup_str_value :=''; tmp_shipping_code :=''; tmp_ownerid := curs_row.ownerid; INDEX_a :=1; tmp_stringval := REPLACE(curs_row.stringvalue, CHR(9), ','); str_token := get_token(tmp_stringval,INDEX_a,','); WHILE str_token IS NOT NULLLOOPSELECT MAX (uuid)--This is required because 'DeliverLocally has two uuidsINTO tmp_shipping_code FROM SHIPPINGMETHODWHERE name=str_token;up_str_value := up_str_value||tmp_shipping_code; INDEX_a := INDEX_a + 1; str_token := get_token(tmp_stringval,INDEX_a,','); IF str_token IS NOT NULLTHENup_str_value := up_str_value ||CHR(9); END IF; END LOOP;IF up_str_value IS NOT NULLTHENUPDATE PRODTEST_AV SET stringvalue= up_str_value WHERE name='ShippingOptions' AND ownerid = tmp_ownerid; END IF; COMMIT; END LOOP; END;
MySQL update problem   (284 Views)
Hi i'm trying to update my MySQL Database and i've tried 3 methods and it did work. Here are the codes.. 1st Method - Using the object datasets - connected via ODBC AkuDataTableAdapters.announcementTableAdapter announceAdapter = new AkuDataTableAdapters.announcementTableAdapter(); announceAdapter.UpdateAnnouncement(DateTime.Now, Server.HtmlEncode(FreeTextBox1.Text), announceID); 2nd Method - Using ODBC connecter String akudetaConnectionString = "Dsn=akudetaDB;database=akudeta;description=Located at web host;option=0;port=0;server=server; uid=pass"; using (OdbcConnection con = new OdbcConnection(akudetaConnectionString)) using (OdbcCommand cmd = new OdbcCommand("UPDATE announcement SET Message = WHERE AnnounceNo =", con)) { cmd.Parameters.Add("@Message", OdbcType.VarChar, 2000).Value = Server.HtmlEncode(FreeTextBox1.Text); cmd.Parameters.Add("@AnnounceNo", OdbcType.Int).Value = announceID; con.Open(); cmd.ExecuteNonQuery(); } 3rd method using .Net Connector String ConnString = " Database=akudeta;Data;"; ConnString += " User Id=akudetadb; Password=pass"; using (MySqlConnection con2 = new MySqlConnection(ConnString)) using (MySqlCommand cmd2 = new MySqlCommand("UPDATE announcement SET Message =Message WHERE AnnounceNo =AnnounceNo", con2)) { cmd2.Parameters.Add("Message", MySqlDbType.VarChar, 2000).Value = Server.HtmlEncode(FreeTextBox1.Text); cmd2.Parameters.Add("AnnounceNo", MySqlDbType.Int32).Value = announceID; con2.Open(); cmd2.ExecuteNonQuery(); } Apparently there are no errors when i put these codes in a try-catch method but my database is not updated.
Do I need to commit when executing ExecuteNonQuery to update?   (281 Views)
Will the single update query in example below automatically commit to Oracle database or I need to give that statement. 'myConnectionString' is an oledbconenction stringto an Oracle database. Dim UpdateStatement As String = "UPDATE Customers SET ContactTitle='Sales Manager' WHERE CustomerID = 'ALFKI'" Dim myConnection As New OleDbConnection(myConnectionString) Dim myCommand As New OleDbCommand(myExecuteQuery, myConnection) myCommand.Connection.Open() myCommand.ExecuteNonQuery() MyConnection.Close() I know in SQL Server 2000, each single update statement is automatically treated as a transaction, which means if it succeeds then updates are automatically committed, and if it fails then the table being updated is automatically rolled back.