Difference between version

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 to
give the thext of my statement:

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

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

"vovan" wrote in message

>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
> to give the thext of my statement:
>
> 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
>
>