?
Solved

forcing RowState

Posted on 2005-03-24
20
Medium Priority
?
667 Views
Last Modified: 2012-06-21
Hi,

I need to update my database based on an xml stream sent to asp.net from another application.  this is how it works:

1.  asp.net generates a dataset and sends the xml to a seperate director application (dataset.GetXml())
2.  director app modifies xml, then sends it back to asp.net.
3.  asp.net reads xml into a new dataset, then needs to update the database.

this is what i'm doing for step 3:

SqlConnection cn = Global.GetConnection();
// provide the adapter with a select so the sqlcommandbuilder can do everything else
SqlDataAdapter ada = new SqlDataAdapter("SELECT * FROM Customers", cn);
SqlCommandBuilder builder = new SqlCommandBuilder(ada);

// load in the xml that was sent from director application, along with the Schema
DataSet dsTemp = new DataSet();
dsTemp.ReadXmlSchema(Server.MapPath("Customer.xsd"));
dsTemp.ReadXml(Request.InputStream);

// save changes to database
ada.Update(dsTemp, "CustomerInformation");

I get this error:

Violation of PRIMARY KEY constraint 'aaaaacustomers_PK'. Cannot insert duplicate key in object 'customers'. The statement has been terminated.

It's obvious that when i call ada.Update, it's trying to do an Insert.  How do I make it do an update instead?  the dataset only contains one table, CustomerInformation which only contains one row.  The RowState of that row is "Added" when it needs to be "Modified" in order to work.

please advise :)

Thanks-Trevor
0
Comment
Question by:trevorhartman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 6
20 Comments
 
LVL 8

Author Comment

by:trevorhartman
ID: 13626863
i found DataRow.SetModified() in the .net 2.0 framework, but if i try it in visual studio .net 2003, it of course doesn't let me compile.  is there any way to make visual studio use the 2.0 framework?
0
 
LVL 6

Expert Comment

by:Havagan
ID: 13626963
You can try having your original data in one dataset and then taking the second dataset and then merge the two datasets. This should change the values (and thus the rowstate to modified) in the original dataset.

Paul
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13627450
i can't very well use my original dataset.  it is built on a seperate request, and there is no session since the asp.net isn't accessed thru a browser.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 23

Accepted Solution

by:
b1xml2 earned 2000 total points
ID: 13628283
to make it do an Update, you have to manipulate the RowState property.

in ADO.NET 1.x
==========
// to set RowState to DataRowState.Current
dsTemp.Tables[0].AcceptChanges();

// to set RowState to DataRowState.Modified
dsTemp.Tables[0].Rows[0][0] = dsTemp.Tables[0].Rows[0][0];

ada.Update(dsTemp.Tables[0]);
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13631579
bit of a hack, but it works :)


my rowstate is now set to Modified, but after I call ada.Update(dsTemp.Tables[0]); it doesn't actually change anything in the DB?

is there any way i can see the actual SQL that it's executing?
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13631615
yes, open up SQL Profiler, create a new trace, and connect to your sql server and run the filter.

0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13631672
i get an error when i try that, i don't think i have access:

"The operation could not be completed because your security context could not be impersonated."


can't I just catch the RowUpdated event?  i tried but i'm not getting anything:

ada.RowUpdated += new SqlRowUpdatedEventHandler(RowUpdatedHandler);

            private void RowUpdatedHandler(object sender, SqlRowUpdatedEventArgs e)
            {
                  Response.Write("UPDATED :: ");
                  Response.Write(e.Command);
            }

nothing ever prints out though, so the event never even fires?
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13631759
note: ada.Update(dsTemp, "CustomerInformation"); returns 1
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13631799
1. you need to use the Profiler
2. Ensure ContinueUpdateOnError is set to false.
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13631806
also, since you are using the Update method,
make sure the parameters have the correct SourceColumn name which matches the row's column to the parameter!. Ensure you got the right sql statement..etc,
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13631854
i've never used the profiler before so i really don't know what i'm doing.  i got on the actual server and ran a trace and there's all kinds of activity going on, i don't really know what is what.

i set ContinueUpdateOnError to false


if i output builder.GetUpdateCommand().CommandText i get this huge statement:

UPDATE [customers] SET [cust_id] = @p1, [last_name] = @p2, [first_name] = @p3, [middle_initial] = @p4, [other_cust_id] = @p5, [name_prefix] = @p6, [name_suffix] = @p7, [address_1] = @p8, [address_2] = @p9, [city] = @p10, [state] = @p11, [zip] = @p12, [birth_month] = @p13, [birth_day] = @p14, [birth_year] = @p15, [home_phone] = @p16, [work_phone] = @p17, [email_address] = @p18, [fax] = @p19, [cust_type] = @p20, [cust_group] = @p21, [cust_status] = @p22, [status_reason] = @p23, [status_date] = @p24, [mail_status] = @p25, [mail_status_date] = @p26, [cac_status] = @p27, [cac_status_date] = @p28, [nsf_status] = @p29, [nsf_status_date] = @p30, [card_status] = @p31, [card_type] = @p32, [card_issued] = @p33, [entry_date] = @p34, [entry_source] = @p35, [entry_empl] = @p36, [update_date] = @p37, [update_source] = @p38, [update_empl] = @p39, [preferences] = @p40, [comments] = @p41, [plays] = @p42, [primary_location] = @p43, [accum_promo] = @p44, [accum_payout] = @p45, [accum_visits] = @p46, [year_promo] = @p47, [year_payout] = @p48, [year_visits] = @p49, [rating] = @p50, [points1_accum] = @p51, [points1_used] = @p52, [points1_update] = @p53, [points2_accum] = @p54, [points2_used] = @p55, [points2_update] = @p56, [potential_rating] = @p57, [verify_date] = @p58, [cust_ethnic] = @p59, [time_pref] = @p60, [cust_gender] = @p61, [credit_card] = @p62, [credit_card_type] = @p63, [credit_card_expire] = @p64, [drivers_license] = @p65, [check_limit] = @p66, [drivers_license_state] = @p67, [mj_money] = @p68, [mj_code] = @p69, [player_id] = @p70 WHERE (([cust_id] = @p71) AND ((@p72 = 1 AND [last_name] IS NULL) OR ([last_name] = @p73)) AND ((@p74 = 1 AND [first_name] IS NULL) OR ([first_name] = @p75)) AND ((@p76 = 1 AND [middle_initial] IS NULL) OR ([middle_initial] = @p77)) AND ((@p78 = 1 AND [other_cust_id] IS NULL) OR ([other_cust_id] = @p79)) AND ((@p80 = 1 AND [name_prefix] IS NULL) OR ([name_prefix] = @p81)) AND ((@p82 = 1 AND [name_suffix] IS NULL) OR ([name_suffix] = @p83)) AND ((@p84 = 1 AND [address_1] IS NULL) OR ([address_1] = @p85)) AND ((@p86 = 1 AND [address_2] IS NULL) OR ([address_2] = @p87)) AND ((@p88 = 1 AND [city] IS NULL) OR ([city] = @p89)) AND ((@p90 = 1 AND [state] IS NULL) OR ([state] = @p91)) AND ((@p92 = 1 AND [zip] IS NULL) OR ([zip] = @p93)) AND ((@p94 = 1 AND [birth_month] IS NULL) OR ([birth_month] = @p95)) AND ((@p96 = 1 AND [birth_day] IS NULL) OR ([birth_day] = @p97)) AND ((@p98 = 1 AND [birth_year] IS NULL) OR ([birth_year] = @p99)) AND ((@p100 = 1 AND [home_phone] IS NULL) OR ([home_phone] = @p101)) AND ((@p102 = 1 AND [work_phone] IS NULL) OR ([work_phone] = @p103)) AND ((@p104 = 1 AND [email_address] IS NULL) OR ([email_address] = @p105)) AND ((@p106 = 1 AND [fax] IS NULL) OR ([fax] = @p107)) AND ((@p108 = 1 AND [cust_type] IS NULL) OR ([cust_type] = @p109)) AND ([cust_group] = @p110) AND ([cust_status] = @p111) AND ((@p112 = 1 AND [status_reason] IS NULL) OR ([status_reason] = @p113)) AND ((@p114 = 1 AND [status_date] IS NULL) OR ([status_date] = @p115)) AND ((@p116 = 1 AND [mail_status] IS NULL) OR ([mail_status] = @p117)) AND ((@p118 = 1 AND [mail_status_date] IS NULL) OR ([mail_status_date] = @p119)) AND ((@p120 = 1 AND [cac_status] IS NULL) OR ([cac_status] = @p121)) AND ((@p122 = 1 AND [cac_status_date] IS NULL) OR ([cac_status_date] = @p123)) AND ((@p124 = 1 AND [nsf_status] IS NULL) OR ([nsf_status] = @p125)) AND ((@p126 = 1 AND [nsf_status_date] IS NULL) OR ([nsf_status_date] = @p127)) AND ((@p128 = 1 AND [card_status] IS NULL) OR ([card_status] = @p129)) AND ((@p130 = 1 AND [card_type] IS NULL) OR ([card_type] = @p131)) AND ((@p132 = 1 AND [card_issued] IS NULL) OR ([card_issued] = @p133)) AND ((@p134 = 1 AND [entry_date] IS NULL) OR ([entry_date] = @p135)) AND ((@p136 = 1 AND [entry_source] IS NULL) OR ([entry_source] = @p137)) AND ((@p138 = 1 AND [entry_empl] IS NULL) OR ([entry_empl] = @p139)) AND ((@p140 = 1 AND [update_date] IS NULL) OR ([update_date] = @p141)) AND ((@p142 = 1 AND [update_source] IS NULL) OR ([update_source] = @p143)) AND ((@p144 = 1 AND [update_empl] IS NULL) OR ([update_empl] = @p145)) AND ((@p146 = 1 AND [plays] IS NULL) OR ([plays] = @p147)) AND ((@p148 = 1 AND [primary_location] IS NULL) OR ([primary_location] = @p149)) AND ((@p150 = 1 AND [accum_promo] IS NULL) OR ([accum_promo] = @p151)) AND ((@p152 = 1 AND [accum_payout] IS NULL) OR ([accum_payout] = @p153)) AND ((@p154 = 1 AND [accum_visits] IS NULL) OR ([accum_visits] = @p155)) AND ((@p156 = 1 AND [year_promo] IS NULL) OR ([year_promo] = @p157)) AND ((@p158 = 1 AND [year_payout] IS NULL) OR ([year_payout] = @p159)) AND ((@p160 = 1 AND [year_visits] IS NULL) OR ([year_visits] = @p161)) AND ((@p162 = 1 AND [rating] IS NULL) OR ([rating] = @p163)) AND ((@p164 = 1 AND [points1_accum] IS NULL) OR ([points1_accum] = @p165)) AND ((@p166 = 1 AND [points1_used] IS NULL) OR ([points1_used] = @p167)) AND ((@p168 = 1 AND [points1_update] IS NULL) OR ([points1_update] = @p169)) AND ((@p170 = 1 AND [points2_accum] IS NULL) OR ([points2_accum] = @p171)) AND ((@p172 = 1 AND [points2_used] IS NULL) OR ([points2_used] = @p173)) AND ((@p174 = 1 AND [points2_update] IS NULL) OR ([points2_update] = @p175)) AND ((@p176 = 1 AND [potential_rating] IS NULL) OR ([potential_rating] = @p177)) AND ((@p178 = 1 AND [verify_date] IS NULL) OR ([verify_date] = @p179)) AND ((@p180 = 1 AND [cust_ethnic] IS NULL) OR ([cust_ethnic] = @p181)) AND ((@p182 = 1 AND [time_pref] IS NULL) OR ([time_pref] = @p183)) AND ((@p184 = 1 AND [cust_gender] IS NULL) OR ([cust_gender] = @p185)) AND ((@p186 = 1 AND [credit_card] IS NULL) OR ([credit_card] = @p187)) AND ((@p188 = 1 AND [credit_card_type] IS NULL) OR ([credit_card_type] = @p189)) AND ((@p190 = 1 AND [credit_card_expire] IS NULL) OR ([credit_card_expire] = @p191)) AND ((@p192 = 1 AND [drivers_license] IS NULL) OR ([drivers_license] = @p193)) AND ((@p194 = 1 AND [check_limit] IS NULL) OR ([check_limit] = @p195)) AND ((@p196 = 1 AND [drivers_license_state] IS NULL) OR ([drivers_license_state] = @p197)) AND ((@p198 = 1 AND [mj_money] IS NULL) OR ([mj_money] = @p199)) AND ((@p200 = 1 AND [mj_code] IS NULL) OR ([mj_code] = @p201)) AND ((@p202 = 1 AND [player_id] IS NULL) OR ([player_id] = @p203)))

i don't know why the WHERE has to be so huge when i have a simple primary key....
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13631892
ok while doing trace i ran my select in the db manually:

SELECT     *
FROM         customers
WHERE     (cust_id = '00000006500')

and it showed up in the trace.  
then i ran the page but there is no update or anything that shows up..

is there anything wrong with my code?

int rows = ada.Update(dsTemp, "CustomerInformation");
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13631897
look inside your profiler for the Update [customer] statement...
and then copy and paste the statement into Query Analyzer and then execute...

also do the following:
try
{
cn.Open();
SqlTransaction tx = cn.BeginTransaction;
...
da.ContinueOnUpdateError = false;
...
da.UpdateCommand = builder.GetUpdateCommand();
da.UpdateCommand.Transaction = tx;
...
da.Update(dsTemp.Tables[0]);
tx.Commit();
}
catch (Exception e)
{
 MessageBox.Show(e.Message);
}
finally
{
 cn.Close();
}
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13631991
ok. an exception is being thrown. here's the code:

// load in the xml that was sent from director, along with the Schema
            DataSet dsTemp = new DataSet();
            dsTemp.ReadXmlSchema(Server.MapPath("Customer.xsd"));
            //dsTemp.ReadXml(Request.InputStream);
            dsTemp.ReadXml(Server.MapPath("dsTemp.txt"));

            SqlConnection cn = Global.GetConnection();
            try
            {
                  cn.Open();
                  SqlTransaction tx = cn.BeginTransaction();

                  // provide the adapter with a select so the sqlcommandbuilder can do everything else
                  SqlDataAdapter ada = new SqlDataAdapter("SELECT * FROM customers", cn);
                  SqlCommandBuilder builder = new SqlCommandBuilder(ada);

                  // accept changes
                  dsTemp.Tables["CustomerInformation"].AcceptChanges();
                  // set rowstate to modified so that ada.Update will cause an UPDATE in the DB
                  dsTemp.Tables["CustomerInformation"].Rows[0][0] = dsTemp.Tables["CustomerInformation"].Rows[0][0];

                  // check rowstate
                  Response.Write(dsTemp.Tables[0].Rows[0].RowState + "<br>");
                  
                  ada.ContinueUpdateOnError = false;
                  ada.UpdateCommand = builder.GetUpdateCommand();
                  ada.UpdateCommand.Transaction = tx;

                  // save changes to database
                  int rows = ada.Update(dsTemp, "CustomerInformation");

                  tx.Commit();

                  Response.Write("<br>" + rows.ToString());

                  // write to file so i can check the results
                  dsTemp.WriteXml(Server.MapPath("dsTemp.txt"));
            }
            catch (Exception ex)
            {
                  Response.Write("exception occured: " + ex.Message);
            }
            finally
            {
                  cn.Close();
            }

and here's the output:

Modified
exception occured: ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

still no UPDATE in the trace
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13633509
do you want me to ask this as a new question?  i have no idea what is going on here....
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13634703

 SqlConnection cn = Global.GetConnection();
          try
          {
               cn.Open();
             
               // provide the adapter with a select so the sqlcommandbuilder can do everything else
               SqlDataAdapter ada = new SqlDataAdapter("SELECT * FROM customers", cn);
               SqlCommandBuilder builder = new SqlCommandBuilder(ada);

               // accept changes
               dsTemp.Tables["CustomerInformation"].AcceptChanges();
               // set rowstate to modified so that ada.Update will cause an UPDATE in the DB
               dsTemp.Tables["CustomerInformation"].Rows[0][0] = dsTemp.Tables["CustomerInformation"].Rows[0][0];

               // check rowstate
               Response.Write(dsTemp.Tables[0].Rows[0].RowState + "<br>");
               
               ada.ContinueUpdateOnError = false;
               ada.UpdateCommand = builder.GetUpdateCommand();
               SqlTransaction tx = cn.BeginTransaction();
               ada.UpdateCommand.Transaction = tx;

               // save changes to database
               int rows = ada.Update(dsTemp, "CustomerInformation");

               tx.Commit();

               Response.Write("<br>" + rows.ToString());

               // write to file so i can check the results
               dsTemp.WriteXml(Server.MapPath("dsTemp.txt"));
          }
          catch (Exception ex)
          {
               Response.Write("exception occured: " + ex.Message);
          }
          finally
          {
               cn.Close();
          }

0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13646827
ok that worked.  I executed while running a trace and still no Update shows up...  any more ideas?
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13646843
i don't know why, but i don't think it's even trying an update.  remember i tried catching the RowUpdated event, but it never occured?
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 13647486
btw, i've been figuring out this trace stuff, and i now i have a good trace setup with filters in place to only trace .Net SqlClient Data Provider transactions.  when i execute the aspx with my update code, i get these events in the trace:

EventClass            TextData
===================================================                  
Audit Login                                          
ExistingConnection                                    
SQL:BatchCompleted      SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT * FROM customers SET FMTONLY OFF; SET NO_BROWSETABLE OFF;            
SQL:BatchCompleted      SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION            
SQL:BatchCompleted      COMMIT TRANSACTION            
SQL:BatchCompleted      sp_reset_connection            
Audit Logout                  
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question