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

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

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
PotentisFrog
Asked:
PotentisFrog
  • 13
  • 10
1 Solution
 
HuyBDCommented:
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
 
PotentisFrogAuthor Commented:
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
 
PotentisFrogAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
nmcdermaidCommented:
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
 
nmcdermaidCommented:
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
 
PotentisFrogAuthor Commented:
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
 
nmcdermaidCommented:
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
 
PotentisFrogAuthor Commented:
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
 
nmcdermaidCommented:
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
 
PotentisFrogAuthor Commented:
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
 
PotentisFrogAuthor Commented:
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
 
nmcdermaidCommented:
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
 
PotentisFrogAuthor Commented:
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
 
nmcdermaidCommented:
I've never ever seen a aliased field appear as its original field name... and why would it appear and no others? Strange.
0
 
PotentisFrogAuthor Commented:
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
 
nmcdermaidCommented:
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
 
nmcdermaidCommented:
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
 
PotentisFrogAuthor Commented:

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
 
PotentisFrogAuthor Commented:
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
 
PotentisFrogAuthor Commented:
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
 
PotentisFrogAuthor Commented:
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
 
nmcdermaidCommented:
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
 
nmcdermaidCommented:
Feel free to close the question and ask for a refund since you found the answer yourself.
0
 
PotentisFrogAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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