No errors and no output

bleutiger · February 22nd, 2005, 12:26 PM

Having an issue with the code below. I know that the table has data in it but the debug sql statement has no errors and no data is displayed.

Here is the code...this is the whole page minus includes for cookie and sql server connection....the output will be below it.

<%

deaID=request("deaID")
region=request("region")

sql = "SELECT DISTINCT RegionCodes.zipcode FROM RegionCodes WHERE RegionName='" & region & "'"

'debugging code
Response.Write "DEBUG: SQL is " & sql & " "

set getregioncodes = conn.execute(sql)

%>

Region is a state name "Houston" for instance. deaID will be used in the next part of the page so it is irrelevant at the moment.

All I get for outpu is this.....

DEBUG: SQL is SELECT DISTINCT RegionCodes.zipcode FROM RegionCodes WHERE RegionName='Houston'
--------------------------------------------------------------------------------

no data is displayed below and no errors. Little help please.

ChrisScott · February 22nd, 2005, 12:33 PM

What are you expecting?

As far as I can see, you are not attempting to add any further output to the page and your sql looks fine.

"getregioncodes" may well be a populated recordset - check for EOF and then try and write some field content to the page if you have any records.

HTH,

Chris

bleutiger · February 22nd, 2005, 12:46 PM

ok your write been looking at too much code so I changed the code to the following.

<%

deaID=request("deaID")
region=request("region")

sql = "SELECT DISTINCT RegionCodes.zipcode FROM RegionCodes WHERE RegionName='" & region & "'"

'debugging code
Response.Write "DEBUG: SQL is " & sql & " "

set getregioncodes = conn.execute(sql)

Response.Write "debug recordset is " & getregioncodes & " "

and now I get this error...

Type mismatch

DealershipRegions.asp, line 17

line 17 is Response.Write "debug recordset is " & getregioncodes & " "

now what?

bleutiger · February 22nd, 2005, 12:54 PM

typo corrected the code to the following...

sql = "SELECT DISTINCT RegionCodes.zipcode FROM RegionCodes WHERE RegionName='" & region & "'"

'debugging code
Response.Write "DEBUG: SQL is " & sql & " "

set getregioncodes = conn.execute(sql)
regioncodes= getregioncodes("zipcode")
Response.Write "debug recordset is " & regioncodes & " "

and I get this output.

DEBUG: SQL is SELECT DISTINCT RegionCodes.zipcode FROM RegionCodes WHERE RegionName='Houston'
--------------------------------------------------------------------------------

debug recordset is 77001

In order to see the rest I am going to have to loop it right? so how would I set up th e debug statement to see that. Because I don't need to loop the recordset to get all the data do I?

I am eventually going to take the data from this record set and insert it into a new table with the deaID and zipcode information.

Is my thinking correct here?

ChrisScott · February 22nd, 2005, 12:54 PM

The problem is that you're trying to write a recordset object to the page and Response.Write is expecting a string.
Try something like...

Code:

If Not getregioncodes.EOF Then
    Response.Write getregioncodes("zipcode").Value
End If

Cheers,

Chris

ChrisScott · February 22nd, 2005, 01:05 PM

Hehe - started posting before the page updated.

Yep, your thinking is bang on - you will need a loop, try this...

Code:

Response.Write DebugRs(getregioncodes)

...

Function DebugRs(pRs)
    Dim temp: temp = ""
    If Not pRs.EOF Then 
        temp = temp & "<table>"
        Dim field
        temp = temp & "<tr>"
        For Each field In pRs.Fields
            temp = temp & "<th>" & field.Name & "</th>"
        Next
        temp = temp & "</tr>"
        Do While Not pRs.EOF
            temp = temp & "<tr>"    
            For Each field In pRs.Fields
                temp = temp & "<td><nobr>" & field.Value & "</nobr></td>"
            Next
            temp = temp & "</tr>"
            pRs.MoveNext
        Loop
        temp = temp & "</table>"
    End If    
    DebugRs = temp
End Function

HTH,

Chris

bleutiger · February 22nd, 2005, 01:05 PM

Ok changed the code like you said...I feel stupid for not realizing that myself..like I said too much code today....it only Tuesday... anyway.

here is my output

DEBUG: SQL is SELECT RegionCodes.zipcode FROM RegionCodes WHERE RegionName='Houston'
--------------------------------------------------------------------------------

77001

I know that the table has at least 100 zipcodes with the RegionName as Houston.

So why is it only giving me one record?

I took out the Distinct part of the SQL statement to see if that was the reason...same results.

bleutiger · February 22nd, 2005, 01:07 PM

samething here started my reply without checking the post first...let me try your new reply and I 'll be bac...

bleutiger · February 22nd, 2005, 01:28 PM

Ok this worked...just out of curiosity why did you create a function for this...

I'm fairly new to asp but I am learning..what are the advantages to the function....beside reusability

ChrisScott · February 22nd, 2005, 01:38 PM

The function was one I already had kicking around - debugging recordsets is a fairly common task, so having a function like this can be handy.

Like you say, functions are reusable which is a big bonus, they can also make your code easier to understand and reduce server overhead - variables, objects etc that you only use within a function only last as long as the function is executing.

Cheers,

Chris