Function Problem

[jotap]

Hi there...!

I have a problem when calling a function that returns a recordset value. Here's my problem:

sub main
...
call WriteCatalog(rs("productname"),..., GetName(rs("manufacturercode")),...)
...
end sub

sub WriteCatalog(prodname,...,manufacturer,...)
response.write "<table>...manufacturer...prodname...</table>"
end sub

function GetName(Code)
declare recordset
set recordset=connection.execute...

GetName=recordset(0).value

recordset.close
set recordset=nothing
end function

Now, i doesn't get an error but, if take off the function the code works well, if not the page isn't written. where do you think the problem may be?? i´ve also tried to call the function from the WriteCatalog sub but it also doesn't work.

thanks in advance

 

[PHV]

Why not posting the REAL code of your GetName function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jotap]

here's the function code:
Function GetName(Code)

Dim SQL
Dim RS

SLQ = "SELECT manufacturer.Name "
SLQ = SLQ & "FROM manufacturers "
SLQ = SLQ & "WHERE (((manufacturer.Code)= " & Code & " ));"

Set RS = Server.CreateObject("adoDB.recordset")
set RS = Conn.Execute(SLQ)

if err.number = 0 then
GetName= RS(0).Value
else
GetName= 0
err.Clear
end if

RS.close
set RS = Nothing

end function

 

[Sheco]

This is your pseudo-code right?

Please post the actual code.

 

[Sheco]

whoops, well disregard my post, i started it and then the phone rang and i didnt hit submit until i was off the phone...

 

[jotap]

ok the real code:

Function GetMarca(Codigo)

Dim SQLMarcas
Dim RSMarcas

SLQMarcas = "SELECT Marcas.Nome "
SLQMarcas = SLQMarcas & "FROM Marcas "
SLQMarcas = SLQMarcas & "WHERE (((Marcas.Cod)= " & Codigo & " ));"

Set RSMarcas = Server.CreateObject("adoDB.recordset")
set RSMarcas = Conn.Execute(SLQMarcas)

if err.number = 0 then
GetMarca = RSMarcas(0).Value
else
GetMarca = 0
err.Clear
end if

RSMarcas.close
set RSMarcas = Nothing

end function

 

[Sheco]

Is your table manufacturers or manufacturer ???


You've got it both ways.

Also you should check the recordset for EoF

 

[Sheco]

Marcas.Cod should this be Marcas.Code ?

What is the data type of this field? If it is character data you need quotes around it in the sql string.

 

[Sheco]

set RS = Conn.Execute(SLQ)

I dont see you open the connection, is this a global variable connection?

 

[Sheco]

Set RSMarcas = Server.CreateObject("adoDB.recordset")

This looks like ASP. Are you running this from a web server or as a .vbs file?

 

[Sheco]

Marcas.Nome

Should this be Marcas.Name ???

 

[Sheco]

You should put a check at teh top of the function to make sure that the input argument Codigo is not empty.

 

[PHV]

If Marcas.Cod isn't defined as numeric:
SLQMarcas = SLQMarcas & "WHERE Marcas.Cod=[tt]'" & Codigo & "'"[/tt]
Anyway:
Set RSMarcas = Conn.Execute(SLQMarcas)
If Not (RSMarcas.BOF Or RSMarcas.EOF) Then
GetMarca = RSMarcas(0).Value
Else
GetMarca = ""
End If


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jotap]

No, the field is correct (Marcas.Cod) and it's an auto_increment field. i've tried to use the code in a separate asp page and it work's fine. Here's the code working:
<!--#include file=Conn.asp-->
<%

Dim SQLMarcas
Dim RSMarcas
Dim Cod

Cod = 1

SLQMarcas = "SELECT Marcas.Nome "
SLQMarcas = SLQMarcas & "FROM Marcas "
SLQMarcas = SLQMarcas & "WHERE (((Marcas.Cod)= " & Cod & " ));"

Set RSMarcas = Server.CreateObject("adoDB.recordset")
set RSMarcas = Conn.Execute(SLQMarcas)

response.write RSMarcas(0).Value

RSMarcas.close
set RSMarcas = Nothing



%>

 

[PHV]

OK, try this:
SLQMarcas = SLQMarcas & "WHERE Marcas.Cod=" & CLng(Codigo)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jotap]

it still doesn't work(SLQMarcas = SLQMarcas & "WHERE Marcas.Cod=" & CLng(Codigo)

i've also tried to convert to string, integer...

 

[Sheco]

I see your working code has this:
<!--#include file=Conn.asp-->

Which brings me back to my question above... is Conn defined and opened in the one that doesnt work?

 

[jotap]

to sheco
Are you running this from a web server or as a .vbs file?
i'm running fro a web server.

I dont see you open the connection, is this a global variable connection?
it´s an include .asp file

 

[jotap]

Yes it is

 

[PHV]

Have you tried some debugging like:
Response.Write "<BR>Entered GetMarca with Codigo=/" & Codigo & "/<BR>"
as the 1rst instruction in the function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244