• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

SqlCommandBuilder autogenerated Update statement

Hi,

I'm working with a DataSet, SqlDataAdapter and SqlCommandBuilder.  I'm using the SqlCommandBuilder to auto generate my update statement.  The DataSet I'm working with has a primary key.

Now, why doesn't the autogenerated SQL statement just use a where statmen like this:

WHERE PK = @PK

instead of this HUGE where statmenet:

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



is there any way i can force it to use the PK in the where?? as you can see I h ave a ton of columns, so I don't want to manually write out the update statement, but i will if i have to.  because of the complexity of this where statement, i am getting an exception "UpdateCommand affected 0 of the expected 1 records"

thx - Trevor
0
trevorhartman
Asked:
trevorhartman
  • 5
2 Solutions
 
trevorhartmanAuthor Commented:
hmm its a concurrency issue.  do you think i need to refresh my original datasource, modify (by doing a merge), then update?
0
 
dharmesh_amityCommented:
This is because in you datatable you have not added constraint for primary key. DataSet primary key for the datatable in the dataset, then it will know to use primary key.

DataColumn[] keys = new DataColumn[1];
keys[0] = ds.Tables["tablename"].Columns["primarykeycolumnname"];

ds.Tables["tablename"].PrimaryKey = keys;

If you have more than one column making a primary key create an array of DataColumn of that size.
0
 
laotzi2000Commented:
It's better this way if you do not want to use transaction.

If when you want to update the database, another guy have modified it, then your update will fail.
That's a good thing because it avoids possible inconistent state of the database.

Think about this scenario:
Both A and B share an account with a balance of 1000.
A want to deposit 100 and B want to deposit 200.

1. A read the balance 1000
2. B read the balance 1000
3. A compute balance should be 1100
4. B compute balance should be 1200
5. B write 1200 to the balance
6. A write 1100 to the balance

So in the end, the balance becomes 1100, but instead it should be 1300
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.

 
trevorhartmanAuthor Commented:
ok, i understand, and i want to go ahead and overwrite anyway in this case..

now, i'm reading schema in from an xsd, so do I still have to explicity set the PrimaryKey??  it is specified in the xsd.. it just doesn't seem to be working.  i am running a trace on sql server, and this is the sql i get when i change the Last_name field:

sp_executesql N'UPDATE [customers] SET [last_name] = @p1 WHERE (([cust_id] = @p2) AND ((@p3 = 1 AND [last_name] IS NULL) OR ([last_name] = @p4)) AND ((@p5 = 1 AND [first_name] IS NULL) OR ([first_name] = @p6)) AND ((@p7 = 1 AND [middle_initial] IS NULL) OR ([middle_initial] = @p8)) AND ((@p9 = 1 AND [other_cust_id] IS NULL) OR ([other_cust_id] = @p10)) AND ((@p11 = 1 AND [name_prefix] IS NULL) OR ([name_prefix] = @p12)) AND ((@p13 = 1 AND [name_suffix] IS NULL) OR ([name_suffix] = @p14)) AND ((@p15 = 1 AND [address_1] IS NULL) OR ([address_1] = @p16)) AND ((@p17 = 1 AND [address_2] IS NULL) OR ([address_2] = @p18)) AND ((@p19 = 1 AND [city] IS NULL) OR ([city] = @p20)) AND ((@p21 = 1 AND [state] IS NULL) OR ([state] = @p22)) AND ((@p23 = 1 AND [zip] IS NULL) OR ([zip] = @p24)) AND ((@p25 = 1 AND [birth_month] IS NULL) OR ([birth_month] = @p26)) AND ((@p27 = 1 AND [birth_day] IS NULL) OR ([birth_day] = @p28)) AND ((@p29 = 1 AND [birth_year] IS NULL) OR ([birth_year] = @p30)) AND ((@p31 = 1 AND [home_phone] IS NULL) OR ([home_phone] = @p32)) AND ((@p33 = 1 AND [work_phone] IS NULL) OR ([work_phone] = @p34)) AND ((@p35 = 1 AND [email_address] IS NULL) OR ([email_address] = @p36)) AND ((@p37 = 1 AND [fax] IS NULL) OR ([fax] = @p38)) AND ((@p39 = 1 AND [cust_type] IS NULL) OR ([cust_type] = @p40)) AND ([cust_group] = @p41) AND ([cust_status] = @p42) AND ((@p43 = 1 AND [status_reason] IS NULL) OR ([status_reason] = @p44)) AND ((@p45 = 1 AND [status_date] IS NULL) OR ([status_date] = @p46)) AND ((@p47 = 1 AND [mail_status] IS NULL) OR ([mail_status] = @p48)) AND ((@p49 = 1 AND [mail_status_date] IS NULL) OR ([mail_status_date] = @p50)) AND ((@p51 = 1 AND [cac_status] IS NULL) OR ([cac_status] = @p52)) AND ((@p53 = 1 AND [cac_status_date] IS NULL) OR ([cac_status_date] = @p54)) AND ((@p55 = 1 AND [nsf_status] IS NULL) OR ([nsf_status] = @p56)) AND ((@p57 = 1 AND [nsf_status_date] IS NULL) OR ([nsf_status_date] = @p58)) AND ((@p59 = 1 AND [card_status] IS NULL) OR ([card_status] = @p60)) AND ((@p61 = 1 AND [card_type] IS NULL) OR ([card_type] = @p62)) AND ((@p63 = 1 AND [card_issued] IS NULL) OR ([card_issued] = @p64)) AND ((@p65 = 1 AND [entry_date] IS NULL) OR ([entry_date] = @p66)) AND ((@p67 = 1 AND [entry_source] IS NULL) OR ([entry_source] = @p68)) AND ((@p69 = 1 AND [entry_empl] IS NULL) OR ([entry_empl] = @p70)) AND ((@p71 = 1 AND [update_date] IS NULL) OR ([update_date] = @p72)) AND ((@p73 = 1 AND [update_source] IS NULL) OR ([update_source] = @p74)) AND ((@p75 = 1 AND [update_empl] IS NULL) OR ([update_empl] = @p76)) AND ((@p77 = 1 AND [plays] IS NULL) OR ([plays] = @p78)) AND ((@p79 = 1 AND [primary_location] IS NULL) OR ([primary_location] = @p80)) AND ((@p81 = 1 AND [accum_promo] IS NULL) OR ([accum_promo] = @p82)) AND ((@p83 = 1 AND [accum_payout] IS NULL) OR ([accum_payout] = @p84)) AND ((@p85 = 1 AND [accum_visits] IS NULL) OR ([accum_visits] = @p86)) AND ((@p87 = 1 AND [year_promo] IS NULL) OR ([year_promo] = @p88)) AND ((@p89 = 1 AND [year_payout] IS NULL) OR ([year_payout] = @p90)) AND ((@p91 = 1 AND [year_visits] IS NULL) OR ([year_visits] = @p92)) AND ((@p93 = 1 AND [rating] IS NULL) OR ([rating] = @p94)) AND ((@p95 = 1 AND [points1_accum] IS NULL) OR ([points1_accum] = @p96)) AND ((@p97 = 1 AND [points1_used] IS NULL) OR ([points1_used] = @p98)) AND ((@p99 = 1 AND [points1_update] IS NULL) OR ([points1_update] = @p100)) AND ((@p101 = 1 AND [points2_accum] IS NULL) OR ([points2_accum] = @p102)) AND ((@p103 = 1 AND [points2_used] IS NULL) OR ([points2_used] = @p104)) AND ((@p105 = 1 AND [points2_update] IS NULL) OR ([points2_update] = @p106)) AND ((@p107 = 1 AND [potential_rating] IS NULL) OR ([potential_rating] = @p108)) AND ((@p109 = 1 AND [verify_date] IS NULL) OR ([verify_date] = @p110)) AND ((@p111 = 1 AND [cust_ethnic] IS NULL) OR ([cust_ethnic] = @p112)) AND ((@p113 = 1 AND [time_pref] IS NULL) OR ([time_pref] = @p114)) AND ((@p115 = 1 AND [cust_gender] IS NULL) OR ([cust_gender] = @p116)) AND ((@p117 = 1 AND [credit_card] IS NULL) OR ([credit_card] = @p118)) AND ((@p119 = 1 AND [credit_card_type] IS NULL) OR ([credit_card_type] = @p120)) AND ((@p121 = 1 AND [credit_card_expire] IS NULL) OR ([credit_card_expire] = @p122)) AND ((@p123 = 1 AND [drivers_license] IS NULL) OR ([drivers_license] = @p124)) AND ((@p125 = 1 AND [check_limit] IS NULL) OR ([check_limit] = @p126)) AND ((@p127 = 1 AND [drivers_license_state] IS NULL) OR ([drivers_license_state] = @p128)) AND ((@p129 = 1 AND [mj_money] IS NULL) OR ([mj_money] = @p130)) AND ((@p131 = 1 AND [mj_code] IS NULL) OR ([mj_code] = @p132)) AND ((@p133 = 1 AND [player_id] IS NULL) OR ([player_id] = @p134)))'

still getting the massive where statement


one thing:  how does sqlcommandbuilder even know that the table has a PK if it doesn't even know which DS it will be updating?  this is how i'm doing it..


// setup a connection
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);

as of now, builder doesn't know anything about the ds...

only later, when i call ada.Update(ds, "customers") does it actually know.. is that right?  man this is a nightmare
0
 
trevorhartmanAuthor Commented:
let me explain what i'm doing...

1.  create a dataset and save it in Application[cust_id + "ds"]
2.  send dataset as xml to other app to be modified
3.  accept xml from other app, and bring it into a new dataset
     DataSet newDs = new DataSet();
     newDs.ReadXml(Request.InputStream);

// now i need to update it, so pull the existing ds out of application
    DataSet ds = (DataSet);
// read in xmlschema and merge
    ds.ReadXmlSchema("customers.xsd");
    ds.Merge(newDs);

// setup the conn, ada, and builder

    ada.Update(ds, "customers");


is there a better way to accomplish this??  i think the problem isnot even with concurrency.. the code is not right, because the DB is not being updated anywhere else.
0
 
trevorhartmanAuthor Commented:
i figured out why 0 records updated.. it's sending the original value of the column in the sql, instead of the modified value.. i have no clue why.. testing
0
 
trevorhartmanAuthor Commented:
well i figured out how to make it update.. i had some weird settings going on my DS..  it's still using the massive WHERE, but it actually works now
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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