?
Solved

SQL Server SELECT statement, Recordset returned should be empty - but isn't

Posted on 2008-11-05
24
Medium Priority
?
614 Views
Last Modified: 2012-05-05
The attached code/function worked fine in when the database was Access.  After upsizing from Access to SQL Server, I'm getting an unexpected result, which is this:  If the sql statement finds no record of a given orderID, it still returns a recordset. I have found that although the sql requests only a single field (totalPayment), the Recordset returned contains two fields (totalPayment, orderID).  So in SQL Server, even if no match is found for a given orderID, a Recordset is returned where EOF = false, RS.Fields(0).value = NULL, and RS.Fields(1).value = <orderID>.

Is there a way to alter this behavior by SQL Server?  
function getTotalPayment(byval orderID)
	getTotalPayment = 0
	dim sql, RS
	sql="select totalPayment from qryTotalCostAndPayment where orderID=" & orderID
	' getRS uses existing db connection to retrieve RS
	set RS = getRS(sql, adLockOptimistic)
	if not RS.EOF then
		getTotalPayment = RS.Fields("totalPayment").value
	end if
end function

Open in new window

0
Comment
Question by:PotentisFrog
  • 13
  • 10
24 Comments
 
LVL 17

Expert Comment

by:HuyBD
ID: 22892182
try this
function getTotalPayment(byval orderID)
        getTotalPayment = 0
        dim sql, RS
        sql="select totalPayment from qryTotalCostAndPayment where orderID=" & orderID
        ' getRS uses existing db connection to retrieve RS
        set RS = getRS(sql, adLockOptimistic)
        if not RS.EOF then
                getTotalPayment = RS("totalPayment")
        end if
end function

Open in new window

0
 
LVL 1

Author Comment

by:PotentisFrog
ID: 22892228
Tested your modified function. I'm afraid that gives the same result.  The odd thing is that although I'm requesting a single field from the Select statement, the Recordset contains 2 fields, one null and one with the Where clause field. Is that normal?
0
 
LVL 1

Author Comment

by:PotentisFrog
ID: 22947442
So, has anyone seen this behavior before?  ConnectionObject.Execute(someSql)  returning a field from the where clause that was not requested in the SELECT clause. Is that normal? Didn't happen in Access, but seeing it now that we've upsized to SQL Server.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22947454
At a guess, try this:
sql="SET NOCOUNT ON; select totalPayment from qryTotalCostAndPayment ......
Calling a stored procedure with NOCOUNT OFF returns an extra confusing recordset. It doesn't exactly sound like your issue but its worth a try.
You can also try monitoring the SQL that is submitted to the DB using SQL Profiler. There might be a clue there.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22947459
Actually can you post the code for the getRS function. Thats where the problem will be. At a guess its creating the wrong kind of recordset or something like that.
0
 
LVL 1

Author Comment

by:PotentisFrog
ID: 23053456
Thanks for theinfo. Sorry for delay getting back to you.
getRS function code is attached.  
I had another thought. I'm guessing that there's a SQL Server command somewhere that lets you choose whether a recordset should include the column names as the first record. I've looked around a bit, but haven't found it yet.  Does that sound like a possible culprit?
(Also, fyi, "qryTotalCostAndPayment " is a view -- not a stored procedure. Not sure that matters, but...)

Function getRS(sql, lockType)
	on error resume next : if DEBUG_MODE then on error goto 0
	dim RS
	set RS = CreateObject("ADODB.Recordset")
	RS.CursorLocation = adUseClient
	RS.CursorType = adOpenStatic
	RS.LockType = lockType
	call RS.Open(sql, connObj)
	set getRS = RS
End Function 

Open in new window

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23053477
I'm guessing that there's a SQL Server command somewhere that lets you choose whether a recordset should include the column names as the first record
I'm 99% sure there isn't. There are lots of questions around about including headings in the dataaset but I've never come accross a switch that does it.
The getRS function looks fine which means its time to take a look at SQL Profiler.
SQL Profiler is a bit confusing but I often use it and slap my head as I see the problem starting me in my face.
SQL Profiler lets you onitor what is being submitted to the database.
So you turn on SQl Profiler, run your function, verify that it does that wierd behaviour. Then you go into Profiler and identify what its doing, in particualr finding the query that its running.
 
Oh and just in case, could you also paste the DDL for the qryTotalCostAndPayment view?
0
 
LVL 1

Author Comment

by:PotentisFrog
ID: 23053492
I'm not familiar with SQL Profiler. Very new to SQL Server. Is it a tool in SQL Server Management Studio? (I'm using SQL Server Management Studio Express.)
Also, what is "DDL"? (Sorry.) You mean the sql code used to create that view?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23053503
Oops, sorry, Express doesn't come with Profiler. I notice there is a free one here, but I can't vouch for it:
http://sqlprofiler.googlepages.com/
and sorry, yep, DDL means data definition language. You can go into Management Studio and right click and 'script' out the view definition. Thats the DDL.
0
 
LVL 1

Author Comment

by:PotentisFrog
ID: 23053572
Thanks. I'll look into that profiler.
Meanwhile here's the DDL.  (You asked for it.)  I can post the DDL for orders and qryOrderTotalPayment if you want.  

USE [mydgfg]
GO
/****** Object:  View [dbo].[qryTotalCostAndPayment]    Script Date: 11/27/2008 22:31:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[qryTotalCostAndPayment]
AS
SELECT orders.id AS orderID, (orders.totalMerch + orders.totalTax + orders.totalShipping) AS totalCost, qryOrderTotalPayment.totalPayment AS totalPayment
FROM orders LEFT JOIN qryOrderTotalPayment ON (orders.id=qryOrderTotalPayment.orderID)

Open in new window

0
 
LVL 1

Author Comment

by:PotentisFrog
ID: 23057122
Well -- odd.  If I copy the SQL statement run by the function and run it directly in SQL Express, the result shows a single field (with a value of NULL).  This is as it should be.
When I alter the function to display some info about the resulting recordset, I get different info.  Here's the statement:
   "select totalPayment from qryTotalCostAndPayment where orderID=269"
As I said, SQL Management Studio Express shows the correct result (or what I expect, anyway), which is a single null field in the result.
The asp page that calls the function, however, shows two fields in the result set. (See my altered function in the attached code snippet.)
It shows:
   Num Fields returned: 2
   totalPayment
   id
So, why does the RS returned by the asp code add the id field? Very strange. Maybe this is no longer a database question...
(I have yet to figure out the profiler tool.)

function getTotalPayment(byval orderID)
	getTotalPayment = 0
	dim sql, RS
	sql="select totalPayment from qryTotalCostAndPayment where orderID=" & orderID
	set RS = getRS(sql, adLockOptimistic)
	if not RS.EOF then
		getTotalPayment = RS.Fields("totalPayment").value
	end if
	Response.Write("Num Fields returned: " & cstr(RS.Fields.Count))
	dim f
	for each f in RS.Fields
		Response.Write(f.name)
	next
end function

Open in new window

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23057739
Interestingly there's no field called 'id' in the view. Its very weird. It does sound like more of a programming question. I do more db stuff than programming these days. If you spoke to a programmer they might know straight off.
At this page http://msdn.microsoft.com/en-us/library/ms676603(VS.85).aspx, I found this info:
If you attempt to reference a nonexistent field by name, a new Field object will be appended to the Fields collection with a Status of adFieldPendingInsert. When you call Update, ADO will create a new field in your data source if allowed by your provider.
.. but you're not referencingthe field.
I'm really grasping at straws here, but what happens if you run the code below (check the fields collection before you check for EOF)
 
Rregarding it returning records even though there is no order_id... are you sure there is no id against the orders table? As you have an outer join in your view, it will return a record even if there is no order_detail.

function getTotalPayment(byval orderID)
	getTotalPayment = 0
	dim sql, RS
	sql="select totalPayment from qryTotalCostAndPayment where orderID=" & orderID
	set RS = getRS(sql, adLockOptimistic)
 
	Response.Write("Num Fields returned: " & cstr(RS.Fields.Count))
	dim f
	for each f in RS.Fields
		Response.Write(f.name)
	next
 
	if not RS.EOF then
		getTotalPayment = RS.Fields("totalPayment").value
	end if
end function

Open in new window

0
 
LVL 1

Author Comment

by:PotentisFrog
ID: 23058604
But there is a field in the view defined by "orders.id AS orderID".  That must be where the id field is coming from (and it contains the correct value). It just makes no sense to me that it should exist in the asp call, but not in the direct SQL Express query using the same sql.  The asp function is accessing the same database.
Definitely weird.
I'll do some more testing in asp, and see if the profiler can tell me anything.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23061234
I've never ever seen a aliased field appear as its original field name... and why would it appear and no others? Strange.
0
 
LVL 1

Author Comment

by:PotentisFrog
ID: 23061455
If you have SQL Server (Express) and want to try it yourself, here are some files.  Remove the .txt extensions, and edit as needed.  I don't know why the create View sql has so much extra info.
In SQL Server Management Studio, use the attached files to create 2 tables and 1 view.  You don't even need to add any data.  Then run the asp page (editing your connection string as needed).
See if you end up showing two fields.

CreateOrdersTable.sql.txt
CreatePaymentsTable.sql.txt
CreateTotalsView.sql.txt
testRSFunction.asp.txt
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23062602
Haven't tested it yet, but the supplied view is called viewTotalCostAndPayment whereas everything above refers to qryTotalCostAndPayment
I'm guessing its just an oversight but just checking.....
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23062658
I'm afraid to say this is what I get (see picture)
I am running SQL 64 bit Enterprise but I seriously doubt that that is the difference.
I'm pretty much stumped.
I get the expected outcome with empty tables, data only in orders, and matching data in payments.

Screen.jpg
0
 
LVL 1

Author Comment

by:PotentisFrog
ID: 23064284

I changed the table/view names for clarity in the test files.  
When I run the Profiler, it shows the SQL statement exactly as entered. Don't know if there's anything else to check there.  
So what's different?  Hmmm.  My machine is running the following:
 - SQL Server 2005 Express
 - IIS V5.1
 - ADO connectionObject.Version = 2.8
 - Not sure how to get VBScript version.
I tried the same test on Network Solutions server and got your result. (FieldCount=1). Their system also shows ADO Version 2.8, but I don't know what version of IIS, and, of course, they're not using SQL Server EXPRESS.
Could it possibly be a difference between Express and 'real' SQL Server?  Sheesh.  
0
 
LVL 1

Author Comment

by:PotentisFrog
ID: 23064495
Looks like the SQL Server Express version must be the difference.
I finally realized I could connect directly to my SQL Server database on Network Solutions' server directly from my local asp pages. I edited the connection string. (see code snippet).  That was the only change in my local asp.
So, running my code, on my machine (my IIS, my ADO, my VBScript) ... but connecting to SQL Server 2005 (Enterprise, I assume) on Network Solutions, I get ....
... FieldCount: 1
Thoughts?

'Local connection:
'getConnectionStr = "Driver={SQL Native Client};Server=DELLDIMENSION\SQLEXPRESS;Database=testdb;Uid=xxxxxxxx;Pwd=xxxxxxxx!"
'Remote ip connection:
getConnectionStr = "PROVIDER=SQLOLEDB;DATA SOURCE=xxx.xxx.xxx.xxx,1433;UID=xxxxxxxx;PWD=xxxxxxxx;DATABASE=testdb;Encrypt=yes;"

Open in new window

0
 
LVL 1

Author Comment

by:PotentisFrog
ID: 23064703
AHA!
The difference is NOT the sql server version.  The difference is the connection string, regardless of whether you connect to a remote db or not, regardless of whether it's Express or not.  
Try your test again, but use the first example, below ("Driver=..." instead of "PROVIDER=..."), and see what you get!

getConnectionStr = "Driver={SQL Native Client};Server=DELLDIMENSION\SQLEXPRESS;Database=dbName;Uid=userID;Pwd=yourPassword;"
 
Result: FieldCount = 2
 
----------------------------------
getConnectionStr = "PROVIDER=SQLOLEDB;Server=DELLDIMENSION\SQLEXPRESS;UID=userID;PWD=yourPassword;DATABASE=dbName;"
 
Result: FieldCount = 1

Open in new window

0
 
LVL 1

Author Comment

by:PotentisFrog
ID: 23065179
I've simplified the test.  (see attached txt/asp file)  No need for multiple tables, or views.  Just a simple table with 2 fields. One field in the SELECT clause, the other field in the WHERE clause.
PROVIDER (ole db) connection string returns 1 field.
DRIVER (odbc) connection string returns 2 fields.

TestRSFunction.txt
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23066346
Bizzare - thats very different behaviour but I can't find anything on the web about it. MS recommends using the latest driver of course (SQL Native Client), but its behaviour is very different. Very strange.
Well we both learnt something here! (Plus I got to polish up my ASP skills)
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23066348
Feel free to close the question and ask for a refund since you found the answer yourself.
0
 
LVL 1

Accepted Solution

by:
PotentisFrog earned 0 total points
ID: 23101766
I gave this question the wrong title, and I see no way to edit it.  I added a separate question with a better title and gave a solution there, too.  Thanks for your help.
Just to restate the solution here, I changed the  connection string
from
  Driver={SQL Native Client}; ....  (odbc)
to
  PROVIDER=SQLOLEDB; .... (oledb)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question