Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 670
  • Last Modified:

forcing RowState

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
trevorhartman
Asked:
trevorhartman
  • 13
  • 6
1 Solution
 
trevorhartmanAuthor Commented:
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
 
HavaganCommented:
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
 
trevorhartmanAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
b1xml2Commented:
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
 
trevorhartmanAuthor Commented:
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
 
b1xml2Commented:
yes, open up SQL Profiler, create a new trace, and connect to your sql server and run the filter.

0
 
trevorhartmanAuthor Commented:
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
 
trevorhartmanAuthor Commented:
note: ada.Update(dsTemp, "CustomerInformation"); returns 1
0
 
b1xml2Commented:
1. you need to use the Profiler
2. Ensure ContinueUpdateOnError is set to false.
0
 
b1xml2Commented:
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
 
trevorhartmanAuthor Commented:
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
 
trevorhartmanAuthor Commented:
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
 
b1xml2Commented:
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
 
trevorhartmanAuthor Commented:
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
 
trevorhartmanAuthor Commented:
do you want me to ask this as a new question?  i have no idea what is going on here....
0
 
b1xml2Commented:

 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
 
trevorhartmanAuthor Commented:
ok that worked.  I executed while running a trace and still no Update shows up...  any more ideas?
0
 
trevorhartmanAuthor Commented:
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
 
trevorhartmanAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 13
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now