ADO.NET Please Help ?

ADO.NET is great But I have a problem

I using Stored Procedures and UDFs to Insert and retrieve my data

I can recieve a variable Only from UDF or Stored Procedure

Put when the returned value is a set of records Whats the matter then ?

and very especially: I need to know How to navigate through this set of records Next,Previous,First and Last Programmatically without Binding to Controls

One of my Friends challenge me and Say :"Go and use Recordset as usual"

But I Persist on my opinion that ADO.NET is moore efficient !!!

But next Previous problem STOPS me

Please any help from any of you

sites links,material or code inVB if available please ..

Thanks for All

Many Thanks to Microsoft

I'm really lost as to what your problem is. If you get a dataset back from a stored proc, you can use for each to iterate through the rows, or if you want a prev/next/first/last paradigm, you can reference them by index.

|||

Thanks

But can you provide me with any example on how toreference them by index in Any Language please if you can

And Really Is The ADO.NET more efficient than Recordset ?

(To Speak to my friend)

Sorry for my bad english

thanks

|||

To be honest, ADO.NET is your only option in ASP.NET afaik.

DataSet dataSet = new DataSet();

SqlConnection connection = createConnection(true);

try

{

sqlCommand.Connection = connection;

SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);

dataAdapter.Fill(dataSet);

return dataSet;

}

finally

{

connection.Close();

}

Would be an example of getting a dataset back from the database.

I believe that dataSet.Rows[0] would be an example of grabbing a row by index, and dataSet.Rows.Count would tell you how many rows there are.

|||

thanks more sir

But Can I speak to you online

this is my msn :moh_omar52@.hotmail.com

I have little bit questions

If you can not O.K

Thnaks in all conditions

Mohamed Omar

|||

I would definately prefer that you ask further questions on this forum, so that others can help you if they have a better answer than me, and other users can benefit from my replies.

|||

O.K

To be honest, ADO.NET is your only option in ASP.NET afaik.

Really I do not understand this ?(Sorry)

and you speak about ADO.NET 2.0 ( I think that It has great improvments ?)

I mean that there is no implementations in .NET 2.0 that allow to me directly access the Last Or Next Records

Does Really Microsoft puts 70% of its resources to .NET ?

I am sorry If you are annoyed from me !!

|||

I'm not annoyed, if I was, I would stop answering :-)

ADO.NET is the database model for ASP.NET. If you want to use another model, it's not built in, you'd have to do a lot of work to use an inferior model ( inferior because ADO.NET, as a disconnected model, is perfect for web development ).

ADO.NET 2.0 has a lot of new stuff, I've not seen anything I consider an 'improvement' though.

No, you cannot directly access the last nor next records from the DB, because you download ALL The records from your query when you make it. You don't maintain a connection, so the iteration happens entirely in your code, not through a database connection. My code sample shows how to use the dataset you get from the database to step through records if you need to.

I have no idea of percentages, but yes, Microsoft obviously is pushing .NET significantly over and above other options (the only other option being VC++, VB6 is completely unsupported now ).

|||

OK.

my Final Questions

Do you See that .NET will be the future of development especially when Vista ships and in the coming 10 Years ?

If I to be obliged to call a Windows API OR Use Unmanaged dll from the .NET World,

Do you think that this will affect my application performance ? %

I really happy with all these answers and Thank you More

|||

I don't see .NET going anywhere. .NET is not fundamental to Vista ( that is, I don't believe Vista uses it ), but Vista is not going to kill it either, they coexist, but are not codependant. I don't know if I will be using C# in 10 years time, but I don't care. Today, it's a good option. I still use C++, and will move back to it if that's what a project needs, or if the world turns back that way. I doubt it though. New languages will pop up from time to time. WPF is a new framework, they will pop up as well. As a developer, you can expect to always be learning and using new things.

If you call a native API, it's possible that things will work faster :-)

Glad to help...

|||

Hi my friend

sorry When I spoke to you yesterday I do not test any code BUt

this propety is not found in the dataset

dataSet.Rows[0]

Is not found ?

|||

dataSet.Tables(0).Rows(0), or dataSet.Tables[0].Rows[0] ( first VB, then C# )

The dataset has a collection of tables, each table has a collection of rows.

ADO Recordset from SQL Task issue

Dear Folks,
I have a Foreach Loop that enumerates a set of files from an ADO recordset variable which is populated by a preceding SQL task. The query from the task that populates the recordset returns about 200 rows with one varchar field(a file path). The loop is long running, and so far it errors on the connection string populated by the enumeration variable after about an hour. The timeout for the SQL task is set to zero. Could it be the source recordset variable timing out, or could it be that the recordset is too large?
Thanks,
Chris

If it is related to the number of records you could start by limiting the result to 10 records, and add 10 records until you see the problem again.

Also, do you have the latest service pack? Here's a post concerning an issue prior to the service pack.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=277765&SiteID=1

|||

200 rows is not too large for a recordset.

I am confused. the sql task populates the recordset and the for each loop enumerates the results. I assume that the sql task is outside (before) the loop. so, which is taking a long time? executing the sql command or running over the recordset in the loop?

|||Thanks

for the replies, and yes, the SQL Task populates the ADO recordset

variable before the loop. The loop enumerates the results for about an

hour before it errors out. The error is the same every time, reporting

that the connection string has failed to have been acquired. The

variable for the connection string is populated by enumerating the

recordset. If i change the loop's collection enumerator to a directory,

the loop runs fine.
Thanks for your help,
Chris
|||I'm on a SQL Server 2k5 x86 ,SP1, windows 2000 platform if that helps.
Thanks,
Chris
|||Also, It's definitely not the recordset itself, as I've been able to use the same ADO variable result set to enumerate loops with fewer tasks\are shorter running. Any chance this is a memory leak?
|||

Well, what are you doing with the rows from the recordset? I think the enumerator gives you a fields collection.

in other words, what is in the loop

|||The (one field per) rows from the recordset enumerate the collection, a directory of Access databases. Inside the Foreach loop are several data flow tasks. A variable is mapped from the returned row and helps populate a connection string expression for an Access connection manager. The source connection for each data flow task uses the connection manager. It works great for about an hour when i use the recordset as a collection enumerator, and the loop does not error out if I use the directory as a collection enumerator.
Thanks,
Chris
|||

This is odd. there certainly could be a bug somewhere in the recordset object itself (in the interest of full disclosure, I wrote the code for the ADO Recordset object back in '96 :) )

If you don't mind doing some experiments for me, I would ask you to try to separate the recordset usage from the pipeline usage as a way to try to narrow down the problem. it sounds like you have done this a bit.

instead of a dataflow task, put a script task in the loop. extract the value of the connection string and write it out somewhere so that you can see the values and make sure they all look rational. If this loop completes without error, then add some long delay (like a 5 minute wait) into the script task. this will tell us if their is some purely time related issue with the recordset (doubtful, but who knows could be a garbage collector kicking in and dropping references to the com objects of the recordset? crazy i know).

You say you can use a non-recordset list of the same exact connections and get this to succeed? that makes me think that this is not an issue with the access driver, but who knows. Exactly what error are you getting when it fails?

These two things just don't seem related.

ADO errors after changing SP to use local variable

Changed stored procedure

[dbo].[spLogonName @.pNewLogonName varchar(60) AS

SELECT * FROM .dbo.tblUser Where vcLogonName = @.pNewLogonName

to

[dbo].[spLogonName @.pNewLogonName varchar(60) AS

DECLARE @.Local_pNewLogonName varchar(60)

SET @.Local_pNewLogonName = @.pNewLogonName

SELECT * FROM .dbo.tblUser Where vcLogonName = @.Local_pNewLogonName

and started getting this error on the web page.

ADODB.Recordset error '800a0cb3'

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

Does anyone know why this is happening? Nothing on the site has changed. If I change the sp back the errors go away. I'm trying to use local variables in all SP to avoid the slowness that can happen when using the parameter varibles.

Hi,

I guess you've changed the stored proc to avoid parameter sniffing and make sure the execution plan is stable? :-)

Could you try adding SET NOCOUNT ON to the beginning of the procedure? I suspect you're getting an additional DONE token from the SET statement and multiple recordsets. You could probably test that by calling NextRecordset method of the recordset.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello and thanks for the response.

Yes, I'm trying to change the sp to avoid sniffing.

I added SET NOCOUNT ON to the beginning of the procedure but it did not help. I removed the local variable stuff and left the NOCOUNT statement in and it doesn't like it either. It seems like it doesn't like any statements in the sp except the SELECT.

Here is more information if it helps.

Set query = Session_LogonNameQuery(NewLogonName)
Set oRecordSet = New ADODB.Recordset
Set oRecordSet = RetrieveDataRS(myconn, query, adOpenForwardOnly, adLockOptimistic, 1)

Even though I have adLockOptimistic selected the recordset has adLockReadOnly set.

Any help would be appreciated.