JavaScript.CoolDev.Com Support Forums
Any questions related to JavaScript menu and JavaScript tree menu by Javascript.CoolDev.Com
| Author |
Message |
alex byrd
Guest
|
 Posted: Mon Jun 16, 2003 10:03 am Post subject: asp creation of nodes file from sql database |
 |
|
Hi
I've been trying to get the asp side of things to get the data from an sql database instead of a access database. the code below details the small change i have made but it won't get the data from our hosted sql server any ideas would be great - I can get it to work with the access database.
many thanks
Alex.
| Code: |
<%@language="VBScript"%>
<%
Response.Expires = 0
function QuoteAndCheckStr( str )
QuoteAndCheckStr = """" & str & """"
if QuoteAndCheckStr = """""" then QuoteAndCheckStr = "null"
end function
'procedure generates one node and recursively repeats for all children
sub DrawNode( Node )
dim rs, rsCildren
Dim NodeId, ParentID, Caption, Url, Target
'open node and read all fields to variables
set rs = dbConn.Execute("select * from dbo.treemenu where NodeID = " & Node )
NodeId = rs("NodeId")
ParentID = rs("ParentID")
Caption = QuoteAndCheckStr( rs("Caption") )
Url = QuoteAndCheckStr( rs("Url") )
Target = QuoteAndCheckStr( rs("Target") )
'write node to response
Response.Write(Space(Level * 4) & "[" & Caption & ", " & Url & ", " & Target)
rs.Close
set rs = nothing
'get all children
set rsCildren = dbConn.Execute("select * from dbo.treemenu where ParentID = " & Node )
if rsCildren.eof and rsCildren.bof then
'there is no children - close branch
Response.Write("]," & vbCrLf)
else
Level = Level + 1
Response.Write("," & vbCrLf)
'if children exists - call this sub recursively for each child
while not rsCildren.eof
call DrawNode( rsCildren("NodeId") )
rsCildren.MoveNext
wend
Level = Level - 1
Response.Write(Space(Level * 4) & "], " & vbCrLf)
end if
rsCildren.Close
set rsCildren = nothing
end sub
Dim dbConn, rsTop, Level
'Create and open connection to DB
'set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../private/tree2k.mdb") & ";Persist Security Info=False"
dbConn.Open "Provider=SQLOLEDB.1;Password=*********;Persist Security Info=True;User ID=*******;Initial Catalog=actiondb;Data Source=***.***.***.***;Network Library=DBMSSOCN"
Level = 1
'go thought first level
Response.Write("var TREE_NODES = [" & vbCrLf)
set rsTop = dbConn.Execute("select * from dbo.treemenu where ( ParentID = 0 or ParentID is null) and disabled <> true order by NodeID, ParentID")
while not rsTop.eof
DrawNode( rsTop("NodeId") )
rsTop.MoveNext
wend
Response.Write("];" & vbCrLf)
rsTop.Close
set rsTop = nothing
dbConn.Close
set dbConn = nothing
%>
|
|
|
| Back to top |
|
 |
AlexKunin
Developer
Joined: 03 Jan 2003
Posts: 1191
|
| Posted: Mon Jun 16, 2003 7:23 pm Post subject: |
|
|
| As I can see your code calls dbConn.Open twice, and call to CreateObject is commented out - could this be a reason? Also, you can try to write tiny WSH-script to try is your SQL server can be reached from the Web-server machine. But in general you are right: the only thing you have to change is connection string. |
|
| Back to top |
|
|
alex byrd
Guest
|
| Posted: Tue Jun 17, 2003 8:11 am Post subject: already rplied to this and it disapeared overnight |
|
|
I think i put the rem mark on the wrong bit of code i pasted in to my original message - but not in the test file i uploaded to test with. I just remmed out the access connection string.
Could you give me an example of a WSH Script to do this so I can test it.
many thanks
Alex,
I did reply to this last night but the message dissapered overnight - ? |
|
| Back to top |
|
|
AlexKunin
Developer
Joined: 03 Jan 2003
Posts: 1191
|
| Posted: Tue Jun 17, 2003 4:03 pm Post subject: |
|
|
I've tested your script here with remote MSSQL 2000, database "test", table "treemenu" - it works, but I had to somewhat modify source code.
1. I've removed prefix "dbo." in SQL queries.
2. It was complaining about "disabled <> true" - "Cannot find column `true'.", so I've removed this condition from WHERE clause (please make sure that your server really understands your SQL queries - try to execute them in Enterprise Manager).
3. Obviously, data source, login, and password fields in the conntection string were changed.
And about testing script.
| Code: | set dbConn = WScript.CreateObject("ADODB.Connection")
dbConn.Open "Provider=SQLOLEDB.1;Password=test;Persist Security Info=True;User ID=test;Initial Catalog=test;Data Source=l7-07;Network Library=DBMSSOCN"
set rsTop = dbConn.Execute("select * from treemenu")
while not rsTop.eof
WScript.Echo rsTop("NodeId") & ": " & rsTop("Caption")
rsTop.MoveNext
wend
rsTop.Close
set rsTop = nothing
dbConn.Close
set dbConn = nothing |
As you can see this is your ASP code with some parts removed. Don't forget to modify connection string. To execute the script in the "Command Prompt" window type "cscript test.vbs". It will show error messages or list tree nodes. |
|
| Back to top |
|
|
alex byrd
Guest
|
| Posted: Tue Jun 17, 2003 10:34 pm Post subject: thanks all working |
|
|
HI Alex
I put back in the "dbo" bits as my hosted server seems to need them and took out the bit in the sql statement (diabled <>true) as you sugessted.
I don't think i need to worry about the "disabled being not true" as the original data is built from an access database and the node won't ever be disabled.
All works fine
Many thanks for making this work for me - just getting my head around sql server and it's making my brain fry a little 
regards
Alex. |
|
| Back to top |
|
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
