Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in

[multichild]

As you will see from the code below I'm using a load of case statements to provide my users with options to display contracts in different ways.

This one works fine:

    case "AllContractsTerminated"://complete but not tested with pageing
                $sql = "SELECT COUNT(*) FROM Intranet WHERE (Contract_Status='Terminated')"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber' FROM Intranet WHERE (Contract_Status='Terminated'))   
select * from LIMIT WHERE {limit clause} AND (Contract_Status='Terminated') Order by Contract_Number";   
        break; 

But this one below causes an error

    case "ByCountryA-Z"://complete
                $sql = "SELECT COUNT(*) FROM Intranet";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Country ASC) AS 'RowNumber' FROM Intranet)   
select * from LIMIT WHERE {limit clause} Order by Country ASC";   
        break; 

This is the error:

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in \\DATASTORE101\CSFWEBDATA$\csfintranet\contracts.php on line 720

Which relates to this line of code:

while($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))           
{ 

What I have noticed is that if I change (ORDER BY Country ASC) which is text to (ORDER BY ID ASC) which is a number, it works fine then, which is the reason I think why the first case example and the second one doesnt.

[multichild]

Maybe I should have added the data types which back this up.

The country database data type is 'text' and the contract_number data type is 'nvarchar(8)'.

[cpradio]

Where is your definition for $stmt? I need to see that line.

[multichild]

Hi cpradio,

Here it is, sorry should have thought and added it.

$stmt = sqlsrv_query($conn, $query);

[cpradio]

$stmt = sqlsrv_query($conn, $query);
var_dump($stmt);

What doe the above output?

[multichild]

Right I see, it outputs.

bool(false)

[cpradio]

Okay, the fact that it is bool(false) means either your $conn is bad or your $query variable is bad.

So now change the var_dump to this

var_dump($conn, $query);

[multichild]

I dont think its the $conn is it, as all the other case work if you change the order by the a number value.

I'm probably miles off.

Here is what it outputted.

resource(2) of type (SQL Server Connection) string(173) "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Country ASC) AS 'RowNumber' FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 1 AND 25 Order by Country ASC"

[cpradio]

Try running

WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Country ASC) AS 'RowNumber' FROM Intranet) 
select * from LIMIT WHERE RowNumber BETWEEN 1 AND 25 Order by Country ASC

In SQL Server (SSMS) to see what it says. I am almost 90% certain that your issue is doing an order by on a text column type (assuming you are using text and not varchar or nvarchar).

[multichild]

I ran it through sql server management studios debugging and it came back with an error that basically it coulsnt connect with the ip address, must be somethng to do with that i am using vpn.

But your exactly right with your comment. The data type field the country is using is 'text' type, and the others are mostly 'nvarchar'.

[cpradio]

Is there a reason why text was used? text is primarily for large amounts of data (up to a couple of gigs), I highly doubt you need to store that much data for country.

[cpradio]

The url below also agrees, that text cannot be used in sorting.
http://twogeeks.mindchronicles.com.dnpserver.com/?p=7

[multichild]

Umm, well what happened was they where originally using nvarchar(100), but I had a problem with drop downs not working because there was too much space after the word, so I changed them to text and they worked.

Do you know what I mean. Basically I needed to not have the space generated after the word for the drop downs to work for the admin user.

[cpradio]

To my knowledge, unless you write the spaces, nvarchar or varchar will not force a padding of 100 characters on your input. So if you state, store 'test' in country when it was nvarchar or varchar type, it should only store 'test', not 'test_______________________________' (assume 96 spaces)

char on the other hand or nchar WOULD pad all values to 100 characters, so 'test' would become 'test_______________________________' (assume 96 spaces)

[cpradio]

One thing you could do is use the trim() function in php when writing the drop down values (it will remove all trailing spaces).

[multichild]

thank you cpradio, will give the first one a go and if its does create the trailing space will use trimm.

Thanks again.

[cpradio]

Now, one more thing, when you switch the column type to nvarchar or varchar, since it has the padding right now you may need to run an update query to get rid of the padding. Then all new values from that point on, SHOULD NOT be padded when using nvarchar or varchar (unless your INSERT has the value already padded)

UPDATE Intranet SET country = RTRIM(LTRIM(country))