Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Professional
|
ASP.NET 2.0 Professional If you are an experienced ASP.NET programmer, this is the forum for your 2.0 questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 23rd, 2010, 12:11 AM
Friend of Wrox
 
Join Date: Jun 2007
Posts: 477
Thanks: 10
Thanked 19 Times in 18 Posts
Default ADO.NET nonqueries return a DataTable?

According to the documentation I've read, when you execute a SQL nonquery, like an UPDATE or an INSERT statement, ADO.NET returns a DataTable with the affected row(s). However, when I try to recover the primary key of the affected row or display the DataTable using a GridView, it appears to be an empty DataTable even though I can confirm on the DB side that the UPDATE / INSERT was successful.

I would really like to get my hands on the primary key so I can store it in a variable and use it to execute additional administration functions by passing that primary key into other commands. However, without the primary key I can't identify the row that was affected. Is there a way to do this, or is the information I read incorrect.
__________________
-------------------------

Whatever you can do or dream you can, begin it. Boldness has genius, power and magic in it. Begin it now.
-Johann von Goethe

When Two Hearts Race... Both Win.
-Dove Chocolate Wrapper

Chroniclemaster1, Founder of www.EarthChronicle.com
A Growing History of our Planet, by our Planet, for our Planet.
 
Old April 23rd, 2010, 09:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Hi there...

I though that my head was rigth, so I went to MSDN to check it...

http://msdn.microsoft.com/en-us/libr...enonquery.aspx

ExecutenonQuery returns the number of affected rows.

Where did you read that info???

And yes, you can do it, that page tell you a method (using an SP that has an out parameters), also a trigger would do the trick... That's depend on what you need...
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
The Following User Says Thank You to gbianchi For This Useful Post:
chroniclemaster1 (April 23rd, 2010)
 
Old April 23rd, 2010, 02:22 PM
Friend of Wrox
 
Join Date: Jun 2007
Posts: 477
Thanks: 10
Thanked 19 Times in 18 Posts
Default

I read it on one of my late night dives through Google articles, forums, and MSDN which last until my eyes bleed. I can't even find it in my bookmarked pages, so it's possible that's what I found and just misread it, though it wouldn't be the first time I'd found contradictory material on MSDN either. ;) OK, so based on what you said...

I haven't written out parameters or triggers before, so bear with me for a second. From what I can tell, it looks like the trigger would be tied to the database field. Anything that happened to that field would trigger further action. The out parameter would be tied to a particular procedure. So one procedure with an out parameter would act on a field and return a record ID (for example) and I could use that to perform another SQL statement. I could then write a different procedure with no output parameter and even if I act on the same database field(s), nothing else would happen.

With an out parameter feeding back a record ID for me, I could call the same stored procedure from different pieces of code and execute different SQL statements based on the application logic requirements.

Does that sound correct to you?

PS Most of my SQL is written into my DAL logic because my database technology has changed several times recently and may change again. Therefore, I've avoided stored procedures on this application, but out parameters look like they are tied to Stored Procedures. If I want to use out parameters, I'll have to bite the bullet and write those functions into the DB, correct? Or is there a way to use out parameters with CommandType.Text?
__________________
-------------------------

Whatever you can do or dream you can, begin it. Boldness has genius, power and magic in it. Begin it now.
-Johann von Goethe

When Two Hearts Race... Both Win.
-Dove Chocolate Wrapper

Chroniclemaster1, Founder of www.EarthChronicle.com
A Growing History of our Planet, by our Planet, for our Planet.

Last edited by chroniclemaster1; April 23rd, 2010 at 02:36 PM..
 
Old April 23rd, 2010, 03:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

I loved more the non edited version of the post :p

Anyway, yes, with SP you are tied to the database.. sort of, since migrating from a db to another implies migrate all the data, that could include SP.
You can't use out parameters on a text command, as far as I know, I never try pass more than a query, but maybe you can...

Another way is start thinking in workarounds. Since you know which rows you are editing, wouldn´t be crazy to just execute a select with the same where clause as in your last query...

Triggers are attached to tables, no fields. With a trigger you can store the key values of the last updated row in a temp table for example for use later.
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
The Following User Says Thank You to gbianchi For This Useful Post:
chroniclemaster1 (April 26th, 2010)
 
Old April 26th, 2010, 12:04 PM
Friend of Wrox
 
Join Date: Jun 2007
Posts: 477
Thanks: 10
Thanked 19 Times in 18 Posts
Default

Quote:
Originally Posted by gbianchi View Post
I loved more the non edited version of the post :p
:) Funny, people usually tell me I type too much. ;)

Quote:
Originally Posted by gbianchi View Post
You can't use out parameters on a text command, as far as I know, I never try pass more than a query, but maybe you can...

Another way is start thinking in workarounds. Since you know which rows you are editing, wouldn´t be crazy to just execute a select with the same where clause as in your last query...

Triggers are attached to tables, no fields. With a trigger you can store the key values of the last updated row in a temp table for example for use later.
Interesting, I hadn't thought about that technique for triggers. That might be a work around I play with. I was hoping for a uniform solution, but I had considered identical WHERE clauses to manage UPDATE statements. As long as I test that only one row is affected, it should be fine.

That still leaves me needing a different approach to handle INSERTs. I think I'll start with an out parameter from an SP. I agree with you, if there's a way to do it from text, I haven't found it. It does make sense and there should not be too many of them. If INSERTs are the only thing I have to port to a different DB I'll count myself lucky. Talk about workarounds, it will also get around an awkward interview answer I have to give when asked about SPs. "Well, I don't use SPs, but I'm familiar with them." If I use SPs to get my out parameter I can simply answer "Yes." If I don't like how it works, I can try the trigger solution you suggested.

Thanks!
__________________
-------------------------

Whatever you can do or dream you can, begin it. Boldness has genius, power and magic in it. Begin it now.
-Johann von Goethe

When Two Hearts Race... Both Win.
-Dove Chocolate Wrapper

Chroniclemaster1, Founder of www.EarthChronicle.com
A Growing History of our Planet, by our Planet, for our Planet.
 
Old May 2nd, 2010, 06:05 PM
Friend of Wrox
 
Join Date: Jun 2007
Posts: 477
Thanks: 10
Thanked 19 Times in 18 Posts
Default

Follow up: Thanks Gonzalo. This worked beautifully.

Before running an UPDATE, I'm performing a SELECT on the WHERE clause I want, to see if it returns one unique record. If it does, I then execute the UPDATE statement (and any thing else I want to run) by calling the record ID returned in the initial select.

For INSERT statements, I'm writing them as stored procedures with an output parameter. I've been able to retrieve the parameter and execute further application logic based on the record id.
__________________
-------------------------

Whatever you can do or dream you can, begin it. Boldness has genius, power and magic in it. Begin it now.
-Johann von Goethe

When Two Hearts Race... Both Win.
-Dove Chocolate Wrapper

Chroniclemaster1, Founder of www.EarthChronicle.com
A Growing History of our Planet, by our Planet, for our Planet.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Return datatable from a webmethod josemanjusha ASP.NET 2.0 Basics 0 September 21st, 2006 07:52 AM
COM .NET Interop - How to return a DataTable from jhpe VS.NET 2002/2003 1 July 19th, 2005 01:29 PM
Return DataSet / DataTable from Stored Procedure ashu_from_india ADO.NET 3 March 20th, 2005 05:39 AM
Return DataTable from Web Method Function kasie ASP.NET 1.0 and 1.1 Basics 2 February 11th, 2004 06:23 AM
ADO .NET return params bmains ADO.NET 2 October 16th, 2003 07:03 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.