SQL query not working in excel 1

[gregmosu]

I can run this query in sql server's analyzer and it runs just fine, but when the query runs in excel half the fields are blank.. not NULL, just an empty string.

SELECT c.candidateName AS exp1, instat.status AS exp2, instat.comment AS exp3, s.state AS exp4, n.note AS exp5, avail.description AS exp6,
t.description AS exp7
FROM Candidate c LEFT OUTER JOIN
InterviewStatus instat ON c.id = instat.candidateId LEFT OUTER JOIN
States s ON s.id = c.stateId LEFT OUTER JOIN
Note n ON n.candId = c.id LEFT OUTER JOIN
Availability avail ON avail.id = c.availabilityId LEFT OUTER JOIN
Title t ON t.id = c.titleId


Here is the code to write the values to the cells...

    Do While rs.EOF = False
        If Not IsNull(rs("exp1")) Then
            Range("A" & cellCount).Select
            ActiveCell.Value = rs("exp1")
        End If
        If Not IsNull(rs("exp2")) Then
            Range("G" & cellCount).Select
            ActiveCell.Value = rs("exp2")
        End If
        If Not IsNull(rs("exp3")) Then
            Range("H" & cellCount).Select
            ActiveCell.Value = rs("exp3")
        End If
        If Not IsNull(rs("exp4")) Then
            Range("B" & cellCount).Select
            ActiveCell.Value = rs("exp4")
        End If
        If Not IsNull(rs("exp5")) Then
            Range("J" & cellCount).Select
            ActiveCell.Value = rs("exp5")
        End If
        If Not IsNull(rs("exp6")) Then
            Range("F" & cellCount).Select
            ActiveCell.Value = rs("exp6")
        End If
        If Not IsNull(rs("exp7")) Then
            Range("C" & cellCount).Select
            ActiveCell.Value = rs("exp7")
        End If
   Loop

The values that wont show up are exp7, exp6, exp5. The rest show up perfectly.

Thanks,
Greg

 

[xlbo]

Any reason you are not just using the COPYFROMRECORDSET method ??

Your code never seems to move through the recordset - there is no "MoveNext" call that I can see so you would just be dumping the same records over and over if I read your code right...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

If you have a field (column) that contains BOTH TEXT & NUMBERS -- big problem!

You have no numbers!

You'll have to use an iif or case statement in your Select clause to FORCE Excel to NOT get confused.

For instance, you could append a [Space] to each numeric value.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[xlbo]

Skip - nice - didn't think of that - could certainly be the case....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

I've been SQL-bit in Excel before ;-)

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[gregmosu]

Not 100% sure what you mean.. what if I take out the exp# from my select statement and use rs(0)..(7) to pull in the fields?

Also, there is a movenext() I had a bunch of error trapping junk below the ifs that I left out when cutting a pasting. The movenext() must have been left out w/that stuff.

 

[xlbo]

Just been messing with using SQL queries within workbooks rather than vlookups etc - probably the area of excel I've been playing with most over the last year or so...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[gregmosu]

Ok, I see what you're saying now. ADO is getting hung up over the datatype. So did you use the IsNumeric function to evaluate it, or something else???

Thanks,
Greg

 

[xlbo]

I think you will need to convert all data to strings (as it is pretty difficult to coerce a string into a number !!)
Once that is done, in excel you can transform your "string numbers" into "true numbers"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[gregmosu]

Found two examples on the web, but they dont work...

1)
CASE WHEN IsNumeric(n.note) = 1 THEN n.note || ' '
ELSE n.note END

2)
Select CAST(n.note AS TEXT) AS exp1

both run, but produce nothing.

Am I at least on the right track...?

 

[gregmosu]

The columns that were being omitted were the columns of type 'TEXT' in the sql server. Since I cant find a way to get excel to accept these columns, I went into sql server and changed them all to varchars. Thanks for pointing out the problem Skip.