Difference between version

vovan

2008-02-16 17:29:40 UTC

I found the way to fix it:
I replaced Sopa.JobID AS JobID with Sopa.JobID AS SopaJobID
It works now, but I still do not understand why it did not work with the
previous syntax.

Thank you
vovan

Post by vovan
I have SELECT statement in VB6 application. It is used to populate
ADODB.Recordset.
It works fine when I connect to SQL 2000 and SQL 2005 databases. It works
fine as well if I run it inside of Mangement Studio or Enterprise Manager
for all 3 instances of the database (original MDF file from SQL 2000 was
copied and attached to SQL 2005 and 2005 Express)
When I run VB6 application against SQL Express database the recordset
contains NULL in one of the fields. No NULL value in all other cases.
What is the difference? Why it might happen?
Although it might not help to understand what's going on there I decided
strSoldToSQL = "SELECT Account.AccountName + ' (ID = ' +
CAST(Account.AccountID AS varchar(5)) + ')' AS SoldTo, " & _
"AccountName AS CustomerForQB, Sopa.JobID AS JobID, Sopa.JobName,
Account.AccountID, " & _
"COALESCE (QBAccountCustomer.ListID, '') AS SoldToQBListID, " & _
"COALESCE (dbo.QBSopaJob.ListID, '') AS SopaQBListID " & _
"FROM Ordr RIGHT OUTER JOIN OrderDetail ON " & _
"Ordr.OrderID = OrderDetail.OrderID RIGHT OUTER JOIN " & _
"[Partial] ON OrderDetail.OrderDetailID = [Partial].OrderDetailID "
& _
"LEFT OUTER JOIN Sopa LEFT OUTER JOIN QBSopaJob ON " & _
"Sopa.JobID = QBSopaJob.JobID ON Ordr.JobID = Sopa.JobID " & _
"LEFT OUTER JOIN Account LEFT OUTER JOIN QBAccountCustomer ON " & _
"Account.AccountID = QBAccountCustomer.AccountID ON " & _
"Sopa.LowBidderID = Account.AccountID " & _
"WHERE Partial.PartialID = " & lngPartialID
Set rsSoldTo = New ADODB.Recordset
rsSoldTo.Open strSoldToSQL, objConn, adOpenStatic, adLockOptimistic
The field Sopa.JobID contains NULL
Thank you
vovan