.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

asp.net vs asp dataadapter vs recordset

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :ASP.Net


One of my colleagues has done this in vbscript. His code within the recordset is

  1. "SELECT DISTINCT (ProductNumber), Doors, Fuel, Type FROM view WHERE Height between 3.0 and 4.0"

How can he be calling the column Height when it is not declared as a selected column?

I am trying to do this in C#, and I can't - if it goes to the dataapater it fails because it wants the column Height to be declared.

More info:

I have 2 tables in SQL. One has information about the Product, the other has the Heights. One productnumber can have many heights. An inner join joins them and I save this to a view. This means I may have mulitple parts with the same ProductNumber in the view. (EG, if ProductNumber abc123 has 3 heights, it will appear 3 times in the view).

My web page has a 'refine your search' option where it lists all available heights as hyperlinks and counts how many options there are. EG, it shows:

height 1 (5 availabe)
height 2 (3 available)
height 4 (3 availabe)

Underneath this, it shows the products in a datalist.

View Complete Post

More Related Resource Links

Creating a recordset using VBA in Excel with SQL Server Compact

I have create a SQL Server Compact database from VBA in Excel.  So far so good.  Now I want to access the database via a recordset.  here is my code.        Set recSet = New ADODB.Recordset        recSet.LockType = adLockBatchOptimistic     recSet.CursorType = adOpenKeyset     Call recSet.Open("SELECT * FROM SPAR", dbConn)   When I run this I get and error in Call recSet.Open("SELECT * FROM SPAR", dbConn):Errors Occurred, [,,,,]  I have search many forums about his problem, but I still can't see where the error is from.  I am using Excel 2003, and SQL Server Compact 3.5   Regards, Peter

Counting Values in a recordset

A developer in our group is generating a report that returns the following data:   Date FirstName LastName Count  Dinner1 Dinner2 10/16/2010 John   Doe    2 PrimeRib Salmon 10/16/2010 Cletus  Judd    2   Salmon Salmon 10/16/2010 Some   One    2 PrimeRib PrimeRib ...   She needs to get a count for totals in dinner 1 and dinner 2 as follows: Dinner 1 Dinner 2 Salmon 1 Salmon 2 PrimeRib 2 PrimeRib 1 Is there a function in SSRS that can do this.  We can do this in T-SQL if necessary, but it would be more work.  Any suggestions are appreciated.        

Parent and child object insert through dataadapter

Hi,I have Parent(order)and child(Orderdetail) object how to  insert through dataadapter.Update() method in single shot.

How to insert SQL2008 date data type using ADODB.Recordset and Native Client?



I am working with a classic ASP application that we are trying to keep hobbling along while it gets rewritten.  The underlying database has been upgraded many times and is currently running on SQL Server 2008.

Recently a column of table was updated from a smalldatetime (with a check constraint to ensure it didn't have any time values) to simply the date type, but the code now does not work.  I was originally using SQLOLEDB, and was confident that treating the date as a string would be find with our client.  Unfortunately it does not, nor does it work using the SQLNCLI10 client.

This is the stripped down code:

Set adoConnection = aspServer.CreateObject("ADODB.Connection")
adoConnection.Open "Provider=SQLNCLI10"  ' Details Removed
Set adoRS = aspServer.CreateObject("ADODB.Recordset")
adoRS.CursorLocation = adUseClient
adoRS.Open "SELECT * FROM TestTable WHERE id = 0", adoConnection, adOpenKeyset, adLockOptimistic, adCmdText
adoRS("datecolumn") = "09/20/2010"

On the update line, I get the following error:

Microsoft SQL Server Native Client 10.0 error '80040e07'
Error convertin

How to check if ADO recordset object populated by SQL Exec task


Hey all,


Is there a function I can use in the precedence constraint editor to check if a Exec SQL task populated an ADO recordset object?


Basically I have a select a,b,c from metadata_table where key = param and enabled = 1 and the output gets saved as an recordset object

I want the constraint to check that the object was populated before continuing and error out if not (the package should never execute with 0 rows being returned)

i've tried using 

!isnull(User::ListOfFiles) and Len(User::ListOfFiles)>0 but both return true even when 0 rows are returned

I'd rather not add a @@rowcount output parameter to the Exec SQL task

Does the object have any methods I can use in the expression bit of the constraint editor that'll check if it has any rows? Or are there any Count-like functions that'll work on it? I'm not much of a .net coder so can't think of anything else


edit: nvm I just realised I need to use a script task to set the error message anyway so I can load the object there and do a Rows.Count

call to stored procedure occasionally returns empty recordset



I have a devil of an intermittent problem that I have so far found nothing to help me solve.

I have a web application/module in Dotnetnuke that requires data for a simple form from another SQL server DB on a different server.  The app uses a connection string in the web.config like this:

<add name="CompanyUpdate" connectionString="Data Source=mail.mydatabase.be;Initial Catalog=MA_Online;Persist Security Info=True;User ID=Gus;Password=xxxxxx" providerName="System.Data.SqlClient" />

The application calls a stored proc and most of the time data is returned as expected. There should always be a row returned from the SP.  However, if the form is idle for a while or the module has only just loaded the call to the stored proc returns as empty recordset. A 'table not found' error occurs.

There is no connection issue otherwise there would be a connection error instead of an empty recordset right?. But I cannot work out why sometimes no data is returned. I can go onto the server and run management studio and run the sp over and again and always get data.

I don't have control over the target DB but the person who does thinks it's something to do with connection pooling. I tried turning this off in the connection string and found that I got an empty recordset every time the sp was called wit

Error shows up in RecordSet Field's Value with status being OK


I used ADODB to run an MDX query against a powerpivot cube. The MDX query contains a calculated MDX member as well as a calculated DAX member. This is not allowed/supported by the provider and I did expect an error. However the error showed up as a value inside the RecordSet Field's value (with the RecordSet Field's status being 0 - meaning no error occured). To be more precise, this is what shows up in the value field of the recordset:

ERROR - CALCULATION ABORTED: Query (7,3) The expression invokes an object that was defined by an MDX calculated member or script. Such objects are not supported in DAX expressions

I thought this should really be translated into a field status code or something else, not in the value field itself. This prevented me from doing proper error detection/handling.

Is this a bug or some known issue when dealing with ADODB?



Recordset Destination Component Error in SQL Agent


I am using the Recordset Destination Component to populate an Object Variable, and then using a For Each Component to loop over that Variable.  The package executes perfectly if I run it from within BIDS, however, when I execute as a Job Step within the SQL Server Agent, I get the following error:

Description: Component "component "Recordset Destination" (34)" could not be created and returned error code 0x80070005. Make sure that the component is registered correctly.  Code: 0xC0048021  Recordset Destination [34]     Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "Recordset Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0"  Code: 0xC0047017

I have tried executing in the Job Step every way I can think of...as command line from file system, from within SQL Server, as 32-bit, 64-bit, Windows Auth, SQL Server Auth...

Any Ideas?



Microsoft Visual Studio 2005
Version 8.0.50727.42  (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727 SP1

Installed Edition: IDE Standard

OLE DB Destination SQL command - how can I select from recordset variable?


How do I insert records from connection1 into a a table with the same name on connection2 where the records in connection2 do not already exist?

The approach I'm trying is this: I have an OLE DB Source that selects records from connection1 (successfully). The flow into a recordset destination in the Data Flow. But I can't figure out how to select from this recordset using the FROM CLAUSE of a SQL Command in the OLE DB Destination component. Can someone point me to an example of this?

It seems like this would be a common task but I can't find any examples.

Timeout on dataadapter.Fill on strongly typed dataset


Hi friends,

               Error message shown "timeout expired.the timeout period elapsed prior to completion of the operation or the server is not responding "

                on filling the strongly typed dataset(ie. dataadapter.Fill(dataset.tablename)).  plz help me




ADO Recordset server-side forward-only, read-only cursor asynchronously cache all records?


We are using ADO, and currently create our recordsets using adUseClient and adOpenStatic since that way we can guarantee all the results will be loaded from the server and cached into the client memory without holding locks open on the server. 

However there are great performance benefits to adUseServer and adOpenForwardOnly! I have tried using adAsyncFetch and adAsyncExecute to load things in the background when necessary.

Unfortunately the trouble is that despite the value that I've set the CacheSize to, when the recordset is opened it does not load all of the records into memory. This is a concern since some operations on each record in the client application may take a while or even block waiting on user input. While this occurs, this holds locks on the tables in SQL Server until we read through all the records.

I have tried setting CacheSize to see if it would load everything all at once, but to no avail.

Since all the pieces seem to be here, I don't quite understand why the server-side cursor can't simply load everything into memory in the background the same way the (static) client-side cursor does.

I've tried some of the recordset's dynamic properties (like Maximum Open Records, which is readonly) to no avail either.

I know ADO is a bit dated now, but I would be overjoyed if anyone could guide me to a solution

ADO RecordSet Update Fails (Insufficient key column information for updating...)


Hi, I'd like to update one field in a recordset but I'm getting this error:

*** insufficient key column information for updating or refreshing" ***

My program consists in a .VBS file (VbScript), and the code goes as follows:

connectionString = "Provider=SQLNCLI; DataSource=dbABC;Initial Catalog=whateverCat;Integrated Security=SSPI"

set objConn = CreateObject( "ADODB.Connection" )
set objRs = CreateObject( "ADODB.RecordSet" ) objConn.ConnectionTimeout = 0
objRS.CommandTimeout = 0
objRS.CursorLocation = 3 ' adUseClient
objRs.LockType = 3 ' adLockOptimistic
objRs.CursorType = 2 'adOpenDynamic
objConn.Open ConnectionString
objRs.Open "Select ComputerID, ComputerName From ComputerTable", objConn
do until objRs.eof
objRs( "ComputerName" ).Value = "NewComputerName"
objRs.Update ' ****Fails here

I don't need that the changes made in the RecordSet to be propagated into the DB table, so perhaps here is my problem: I do only have read access to the tables. Could this be the problem? Thanks.

Package using Recordset variable showing weird behavior



I am using a recordset variable in my package... say.. User::VarRecordset(Scope: Package)

I am first filling the User::VarRecordset using a Recordset Destination. Then, I am using this Variable in a For Each Loop..

The package works fine in BIDS. Even on Making a File system Deployment, It works on My machine, and some others too.

But on Some machines, the Execution Fails, showing an Error, Package Variable "User::VarRecordset" not Found in List of Variables..

The execution results do not show any error while putting data into "User::VarRecordset" . But FOR EACH Loop Fails with that Error..


Unable to find any reason, why it works well on some machines and Fails on Some.

My Machine is a 32 bit one...


Any clue or help to this requested...


values from one dataadapter to multiple tables in a dataset through foreach loop



 How can i fill the values from one dataadapter to multiple tables in a dataset...ie,the adapter is fetching values through foreach loop...i want to take that values into different tables of one dataset....How to do this...Hope anyone will help me....

Thanks in advance

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend