forcing RowState

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?

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

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.

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?

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

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?

note: ada.Update(dsTemp, "CustomerInformation"); returns 1

1. you need to use the Profiler
2. Ensure ContinueUpdateOnError is set to false.

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,

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....

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");

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();
}

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

do you want me to ask this as a new question?  i have no idea what is going on here....

 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();
          }

ok that worked.  I executed while running a trace and still no Update shows up...  any more ideas?

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?

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                  

question continued here: https://www.experts-exchange.com/questions/21368942/save-xml-populated-dataset-to-database.html