2. ASPRunnerPro General questions
  3. Checking another db from listpage
This topic is locked

Checking another db from listpage

 		12/2/2009 11:04:17 AM
ASPRunnerPro General questions
Z
 zion4ever author

Hello all,
I have a question. I have a list page that lists network configuration info. One field is called "Hostname". This field holds the name of a domaincomputer, e.g. "Client225".

The info is loaded from the database correctly. However, I have added a column in front of the EDIT and SHOW links on the listpage. This Column has the header "SHOW STATUS". I want this column to response.write a status check from another database on another server.
I have experimented with adding a custom snippet of asp code in the visual editor on the list page:
Set my_conn = createobject("ADODB.Connection")

myconn.open = "DRIVER={MySQL ODBC 5.1 Driver};"

& "SERVER=SecondDBServer;"

& "DATABASE=AnotherTable;"

& "UID=MyUser;PWD=MyPassword; OPTION=35;"
DBstrSQL="select DISTINCT h.status from HOSTS_INTERFACES as h WHERE h.NAME='" & SelectedHost & "';"

set Frs = my_conn.Execute(DBstrSQL)
Do while Not Frs.EOF

Response.write Frs("Status")

Frs.MoveNext

Loop

Next
The problem is that I do not know what to fill in for the variable SelectedHost. This should be a string equalling the Hostname that is retrieved from the first db and that is shown on the listpage. I'm not sure how i can get a handle to the hostname for that new row, which clearly changes each time a record is retrieved from db and displayed by aspr 6.1
I also tried using data("Hostname") under LISTPAGE: Before record processed and storing it in a session variable, delimited by a comma. I then tried parsing each entry in the array (using Split) from the custom asp snippet on the listpage, but no luck. There must be a better way.
Thank you in advance
Hans
Z
 		zion4ever author 12/3/2009

I made some progress. I placed a custom ASP snippet in the view page of the record. The code for this asp snippet is:

response.write session("Nstatus")
Now in the View page: Before Display page, I have placed the following code:

session("Nstatus") = ""
' Place event code here.

' Use "Add Action" button to add code snippets.

Dim SelectedHost

SelectedHost = values("Host")
Set my_conn = createobject("ADODB.Connection")

myconn.open = "DRIVER={MySQL ODBC 5.1 Driver};"

& "SERVER=XXXXX;"

& "DATABASE=MyDB;"

& "UID=XXX;PWD=XXX; OPTION=35;"
DBstrSQL="select status from HOSTS_INTERFACES WHERE NAME='" & SelectedHost & "';"

set Frs = my_conn.Execute(DBstrSQL)
If not Frs.eof Then

session("Nstatus") = frs("Status")

end i
This workaround displays the status on the VIEW page. However, the LISt page would be best for me.
Ideally I would place the above connectionstring somewhere central and only execute DBstrSQL for the record.

Anyone? Please.
J
 		Jane 12/3/2009

Hans,
I recommend you to use List page: After record processed event for this purpose. Field value is in the data("FieldName").

Then assign this value to ther new variable:

record("Nstatus") = session("Nstatus")



and use {$Nstatus} on the Visual Editor tab in your new column.