Hi,
I have come across a requirement where i need to send data from dashboard to database.
What is the way to achieve this? this is the first time I am working on this kind of requirement, so any information will be useful.
Thanks,
Krishna.
kvaranas (BOB member since 2013-02-19)
Setup an XML data connection which connects to a php, aspx, or other script in your favourite programming language. So, for example, set its URL to “www.website.com/script.php”
Turn on “Send Data” and send whatever data you want to add to the database to this script
The script itself will be pretty simple. In short:
a) Load the XML file from the http request (i.e., read the file that Xcelsius sent)
b) Parse the XML file into a string
c) Connect to your database and insert the string into it
gaffika (BOB member since 2012-08-10)
Thanks for your prompt reply
Do you have any document which details this workflow or a working script and a model?
That will help me a lot.
Krishna.
kvaranas (BOB member since 2013-02-19)
If I remember right, Debbie wrote a big and clear post (using .aspx) a few months ago in this forum; you should try searching for that.
gaffika (BOB member since 2012-08-10)
I did for getting data out of databases with aspx, but not for writing back to the database - that’s beyond my capabilities!
But this question has been asked several times in the last 12 months and I’m almost certain somebody has covered it. This isn’t a particularly busy forum so it shouldn’t be too hard to find the posts.
Debbie
Debbie 
(BOB member since 2005-03-01)
Hi Debbie
Sorry to hijack this thread, but could you post or send me directly (contact details in my profile) a more comprehensive example of the aspx file with prompts? I’m looking to use a very similar solution for a client, so any help would be much appreciated.
Many thanks
cdavies (BOB member since 2005-01-28)
for writeback the concept I used a few years back was:
I use a parameterised store procedure to insert the parameters into my database table.
I then have a derrived table based on the SPROC in my universe. Calling any element from the derrived table passes the SPROC parameters up from the database.
3.1 users can also use TaaWS if you can get past the really poor documentation and the fact it’s in Labs.
Ozzy_BOBJ 
(BOB member since 2013-03-20)
There’s a script on this forum. I pasted the whole thing a while back. Doesn’t have prompts though - I wouldn’t know how to do that. I just pass through user-selected values from xcelsius. And I’ve never done a write-back to a database.
Debbie
Debbie (BOB member since 2005-03-01)
Here’s the code with the variables bit added. No idea whether this is the best or most efficient way of doing stuff but it works for me…
The array passed in must be whatever you set as the output of your xcelsius connection.
Obviously in a real example you’d use avals(0) and avals(1) in the SQL:
"where something = '" & avals(0) & "' "Note the single quotes concatenated either side.
Debbie
<% Response.CacheControl = "no-cache" %>
<% Response.Expires = -1 %>
<%@ Page Language="VB" aspcompat=true Debug="true" validateRequest="false" %>
<%
'----get user value output from xcelsius if required
Dim sXMLString
dim item as String
For Each item In Request.Form
sXMLString = sXMLString & item & " = " & Request.Form(item)
Next
Dim oXML as Object
Dim oNodeList as Object
Dim oNode as Object
oXML = Server.CreateObject( "Microsoft.XMLDOM" )
oXML.async = False
oXML.validateOnParse = False
oXML.loadXML(sXMLString)
dim sPath as String
sPath = "/data/variable/row/column"
oNodeList = oXML.documentElement.selectNodes(sPath)
dim i, aVals(1) '2 items passed out from xcelsius into a 2D array (0,1)
i=0
For Each oNode in oNodeList
With oNode
aVals(i)=.text
i=i+1
End With
Next
'----create a database connection & open it - oracle & sql server connection strings
dim oCon as object
oCon = Server.CreateObject("ADODB.Connection")
oCon.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=database;User Id=yyy;Password=zzz;"
'oCon.ConnectionString = "Provider=SQLOLEDB;Data Source=path;Initial Catalog=database;User Id=yyy;Password=zzz; "
oCon.Open
'----create a recordset & open it
dim oRst as object
oRst = Server.CreateObject("ADODB.RecordSet")
oRst.ActiveConnection = oCon
dim sSQL as String
sSQL = sSQL & " select sysdate from dual "
oRst.Open(sSQL)
'----output results as xml
dim str as string
str = oRst.GetString(,,"</row><row>","</row></column><column><row>","&nbsp;")
dim sXML as string
sXML = "<data>" + chr(13)
sXML = sXML + " <variable name=" + chr(34) + "Range0" + chr(34) + ">" + chr(13)
sXML = sXML + str + Chr(13)
sXML = sXML + " </variable>" + chr(13)
sXML = sXML + "</data>"+ chr(13)
Response.Write(sXML)
'----close down
oRst.close
oRst = Nothing
oCon.close
oCon = Nothing
%>Debbie (BOB member since 2005-03-01)
Thanks Debbie,
How are you passing user based selections from Xcelsius? As a value in your SQL where clause?
cdavies (BOB member since 2005-01-28)
Yes. By building the SQL as a string. In its simplest form
dim sSQL as String
sSQL = sSQL & " select columns from tables "
sSQL = sSQL & " where table.column = '" & avals(0) & "' and ... "
oRst.Open(sSQL)
Where avals(0) is one of the array values passed out from xcelsius. Sometimes I’ll process avals(0) further and end up with another variable in the SQL instead.
Sometimes even the FROM statement is a variable, so I might have:
dim sSQL as String
sSQL = sSQL & " select columns from " & tab_var & " "
sSQL = sSQL & " where table.column = '" & avals(0) & "' and ... "
oRst.Open(sSQL) where tab_var could be any one of 3 or 4 different aggregate tables depending on what grain of data I’m looking at.
Debbie
Debbie (BOB member since 2005-03-01)
Good stuff, thanks!
cdavies (BOB member since 2005-01-28)