OWC Pivottable w/ webservice asp.net

  • Thread starter slowmotiongenius
  • Start date
S

slowmotiongenius

Hello-
I'm a bit stumped at this point. Many of your posts have been helpful
getting me as far as I am at this point, but I'm not sure how to
proceed.

What I have currently is an asp.net project going. I'm trying to read
data in from a ColdFusion webservice, and write it out to a pivot table
so the end user can manipulate the data as they see fit.

I'm altering the recent example Alvin Bruney has posted to his Amazon
page for the VS2005 issue to suit my needs. I have to read the data
into the codebehind, and then pass it from the server to the client
(I'm attempting this with a recordset at the moment) and it's failing.
I'm passing it by writing out vbscript at the moment, but you can see
in my code I've also attempted this with Javascript.

My code and the resultant source from the compiled page are below. They
are resulting in an error on line 5 of the source "Error: Object
required: 'ADODB'"
Is this a good way to pass a recordset from the codebehind to the
client? Is there another way to hook this data into the datasource of
the pivottable? I've read that a recordset is a vaild datasource.
Appreciate any help any of you can give me with this.

My code************************
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim CFCwebService As New pivotData.getPivotService
Dim arrString() As String
Dim strPivotTable As String

For i As Integer = 0 To 9
ReDim Preserve arrString(i)
arrString(i) = CFCwebService.getPivotData(i)

Next
strPivotTable = "<object
classid=""clsid:0002E552-0000-0000-C000-000000000046"" id=""Pivot1""
height=""300"" width=""600"" ></object>"
Response.Write(strPivotTable)
Response.Write(LoadData(arrString))
'Response.Write(vbCrLf + "<script language='javascript'>" +
vbCrLf + "loadData();" + vbCrLf + "</script>")
Response.Write(vbCrLf + "<script language='vbscript'>" + vbCrLf
+ "LoadData()" + vbCrLf + "</script>")
Response.Write(CustomizeSpreadSheet())
End Sub

Function LoadData(ByVal strPayload() As String)
Dim newRS As New ADODB.Recordset
Dim xmlRS As New ADODB.Recordset

newRS.Fields.Append("ticket_numbers",
ADODB.DataTypeEnum.adVarChar, 10)
With newRS
.Open()
For i As Integer = 0 To 9
.AddNew()
.Fields.Item("ticket_numbers").Value = strPayload(i)
Next
End With
newRS.MoveFirst()
newRS.Save(newStream, ADODB.PersistFormatEnum.adPersistXML)

Dim strConn As String

strConn = "provider=mspersist"
newStream.Position = 0
xmlRS.Open(newStream)

''''JAVASCRIPT TRY
''strScript.Append(vbCrLf + "<script language='javascript'>" +
vbCrLf + "function loadData(){")
''strScript.Append(vbCrLf + "if(document.all.Pivot1 != null){")
'strScript.Append(vbCrLf +
"document.all.Pivot1.ConnectionString = ""DataSource='mydata.xml'"";")
'.Append(xmlRS)
''strScript.Append(vbCrLf +
"document.all.Pivot1.ConnectionString = ""Provider=mspersist"";")
''strScript.Append(vbCrLf + "document.all.Pivot1.DataSource =
").Append(xmlRS).Append(";")
'strScript.Append(vbCrLf +
"document.all.Pivot1.ConnectionString =
""DataSource=").Append(xmlRS).Append(""";")
''strScript.Append(vbCrLf + "}" + vbCrLf + "}" + vbCrLf +
"</script>")
''''JAVASCRIPT TRY

''''VBSCRIPT TRY
strScript.Append(vbCrLf + "<script language='vbscript'>" +
vbCrLf + "Function loadData()")
strScript.Append(vbCrLf + "Pivot1.ConnectionString =
""Provider=mspersist""")
strScript.Append(vbCrLf + "Pivot1.DataSource = ").Append(xmlRS)
strScript.Append(vbCrLf + "End Function")
strScript.Append(vbCrLf + "</script>")
''''VBSCRIPT TRY

Return strScript.ToString
End Function
************************

The source of the compile page comes up with:
****************************
<object classid="clsid:0002E552-0000-0000-C000-000000000046"
id="Pivot1" height="300" width="600" ></object>
<script language='vbscript'>
Function loadData()
Pivot1.ConnectionString = "Provider=mspersist"
Pivot1.DataSource = ADODB.RecordsetClass
response.write("loaddata has been called")
End Function
</script>
<script language='vbscript'>
LoadData()
</script>
<script language='javascript'>
if(document.all.Pivot1 != null){
document.all.Pivot1.ActiveView.FilterAxis.label.visible = true
document.all.Pivot1.ActiveView.RowAxis.label.visible = true
document.all.Pivot1.ActiveView.ColumnAxis.label.visible = true
document.all.Pivot1.ActiveView.titlebar.visible = true
}
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head><title>
Untitled Page
***************************
 
A

Alvin Bruney [MVP]

firstly, make sure that the object is registered on the client machine. you
may run into problems if the client does not have the control in the
registry. However, in your case, since it is on your development machine,
that doesn't seem to be the case but you should rule it out by writing a
simple test case in an empty case to see if you can interact with the
control without using the owc.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
 
S

slowmotiongenius

Yes. the object is registered on my machine. the main issue I am having
is passing the recordset from the aspx.vb to the aspx.

What are the valid datasources of a pivottable? XML file, adodb
recordset, data source control, any others? I'd like to use a stream if
possible.

You're seeing what I'm attempting to accomplish here, correct? Any
ideas? Think it will be possible?

firstly, make sure that the object is registered on the client machine. you
may run into problems if the client does not have the control in the
registry. However, in your case, since it is on your development machine,
that doesn't seem to be the case but you should rule it out by writing a
simple test case in an empty case to see if you can interact with the
control without using the owc.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------


Hello-
I'm a bit stumped at this point. Many of your posts have been helpful
getting me as far as I am at this point, but I'm not sure how to
proceed.

What I have currently is an asp.net project going. I'm trying to read
data in from a ColdFusion webservice, and write it out to a pivot table
so the end user can manipulate the data as they see fit.

I'm altering the recent example Alvin Bruney has posted to his Amazon
page for the VS2005 issue to suit my needs. I have to read the data
into the codebehind, and then pass it from the server to the client
(I'm attempting this with a recordset at the moment) and it's failing.
I'm passing it by writing out vbscript at the moment, but you can see
in my code I've also attempted this with Javascript.

My code and the resultant source from the compiled page are below. They
are resulting in an error on line 5 of the source "Error: Object
required: 'ADODB'"
Is this a good way to pass a recordset from the codebehind to the
client? Is there another way to hook this data into the datasource of
the pivottable? I've read that a recordset is a vaild datasource.
Appreciate any help any of you can give me with this.

My code************************
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim CFCwebService As New pivotData.getPivotService
Dim arrString() As String
Dim strPivotTable As String

For i As Integer = 0 To 9
ReDim Preserve arrString(i)
arrString(i) = CFCwebService.getPivotData(i)

Next
strPivotTable = "<object
classid=""clsid:0002E552-0000-0000-C000-000000000046"" id=""Pivot1""
height=""300"" width=""600"" ></object>"
Response.Write(strPivotTable)
Response.Write(LoadData(arrString))
'Response.Write(vbCrLf + "<script language='javascript'>" +
vbCrLf + "loadData();" + vbCrLf + "</script>")
Response.Write(vbCrLf + "<script language='vbscript'>" + vbCrLf
+ "LoadData()" + vbCrLf + "</script>")
Response.Write(CustomizeSpreadSheet())
End Sub

Function LoadData(ByVal strPayload() As String)
Dim newRS As New ADODB.Recordset
Dim xmlRS As New ADODB.Recordset

newRS.Fields.Append("ticket_numbers",
ADODB.DataTypeEnum.adVarChar, 10)
With newRS
.Open()
For i As Integer = 0 To 9
.AddNew()
.Fields.Item("ticket_numbers").Value = strPayload(i)
Next
End With
newRS.MoveFirst()
newRS.Save(newStream, ADODB.PersistFormatEnum.adPersistXML)

Dim strConn As String

strConn = "provider=mspersist"
newStream.Position = 0
xmlRS.Open(newStream)

''''JAVASCRIPT TRY
''strScript.Append(vbCrLf + "<script language='javascript'>" +
vbCrLf + "function loadData(){")
''strScript.Append(vbCrLf + "if(document.all.Pivot1 != null){")
'strScript.Append(vbCrLf +
"document.all.Pivot1.ConnectionString = ""DataSource='mydata.xml'"";")
'.Append(xmlRS)
''strScript.Append(vbCrLf +
"document.all.Pivot1.ConnectionString = ""Provider=mspersist"";")
''strScript.Append(vbCrLf + "document.all.Pivot1.DataSource =
").Append(xmlRS).Append(";")
'strScript.Append(vbCrLf +
"document.all.Pivot1.ConnectionString =
""DataSource=").Append(xmlRS).Append(""";")
''strScript.Append(vbCrLf + "}" + vbCrLf + "}" + vbCrLf +
"</script>")
''''JAVASCRIPT TRY

''''VBSCRIPT TRY
strScript.Append(vbCrLf + "<script language='vbscript'>" +
vbCrLf + "Function loadData()")
strScript.Append(vbCrLf + "Pivot1.ConnectionString =
""Provider=mspersist""")
strScript.Append(vbCrLf + "Pivot1.DataSource = ").Append(xmlRS)
strScript.Append(vbCrLf + "End Function")
strScript.Append(vbCrLf + "</script>")
''''VBSCRIPT TRY

Return strScript.ToString
End Function
************************

The source of the compile page comes up with:
****************************
<object classid="clsid:0002E552-0000-0000-C000-000000000046"
id="Pivot1" height="300" width="600" ></object>
<script language='vbscript'>
Function loadData()
Pivot1.ConnectionString = "Provider=mspersist"
Pivot1.DataSource = ADODB.RecordsetClass
response.write("loaddata has been called")
End Function
</script>
<script language='vbscript'>
LoadData()
</script>
<script language='javascript'>
if(document.all.Pivot1 != null){
document.all.Pivot1.ActiveView.FilterAxis.label.visible = true
document.all.Pivot1.ActiveView.RowAxis.label.visible = true
document.all.Pivot1.ActiveView.ColumnAxis.label.visible = true
document.all.Pivot1.ActiveView.titlebar.visible = true
}
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head><title>
Untitled Page
***************************
Click to expand...
Click to expand...
 
A

Alvin Bruney [MVP]

This should be working for you, I believe you may have other issues so
here's how to trouble shoot it.
Put this in a new project with an empty aspx page and run it, it should work
without script error. Once you get that to work, translate it so that it is
streamed from the codebehind. That should work as well. If it doesn't, at
least you can debug the script in IE to see what's failing.

Dim oConn, oRS
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")
oConn.Open sConn
oRS.Open sMDX, oConn, 3

'Set the recordset as the datasource for the pivot table
Set pivotTable.DataSource = oRS

pivotTable.Refresh

'Close the recordset and connection
oRS.Close
oConn.Close


--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------


Yes. the object is registered on my machine. the main issue I am having
is passing the recordset from the aspx.vb to the aspx.

What are the valid datasources of a pivottable? XML file, adodb
recordset, data source control, any others? I'd like to use a stream if
possible.

You're seeing what I'm attempting to accomplish here, correct? Any
ideas? Think it will be possible?

firstly, make sure that the object is registered on the client machine.
you
may run into problems if the client does not have the control in the
registry. However, in your case, since it is on your development machine,
that doesn't seem to be the case but you should rule it out by writing a
simple test case in an empty case to see if you can interact with the
control without using the owc.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------


Hello-
I'm a bit stumped at this point. Many of your posts have been helpful
getting me as far as I am at this point, but I'm not sure how to
proceed.

What I have currently is an asp.net project going. I'm trying to read
data in from a ColdFusion webservice, and write it out to a pivot table
so the end user can manipulate the data as they see fit.

I'm altering the recent example Alvin Bruney has posted to his Amazon
page for the VS2005 issue to suit my needs. I have to read the data
into the codebehind, and then pass it from the server to the client
(I'm attempting this with a recordset at the moment) and it's failing.
I'm passing it by writing out vbscript at the moment, but you can see
in my code I've also attempted this with Javascript.

My code and the resultant source from the compiled page are below. They
are resulting in an error on line 5 of the source "Error: Object
required: 'ADODB'"
Is this a good way to pass a recordset from the codebehind to the
client? Is there another way to hook this data into the datasource of
the pivottable? I've read that a recordset is a vaild datasource.
Appreciate any help any of you can give me with this.

My code************************
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim CFCwebService As New pivotData.getPivotService
Dim arrString() As String
Dim strPivotTable As String

For i As Integer = 0 To 9
ReDim Preserve arrString(i)
arrString(i) = CFCwebService.getPivotData(i)

Next
strPivotTable = "<object
classid=""clsid:0002E552-0000-0000-C000-000000000046"" id=""Pivot1""
height=""300"" width=""600"" ></object>"
Response.Write(strPivotTable)
Response.Write(LoadData(arrString))
'Response.Write(vbCrLf + "<script language='javascript'>" +
vbCrLf + "loadData();" + vbCrLf + "</script>")
Response.Write(vbCrLf + "<script language='vbscript'>" + vbCrLf
+ "LoadData()" + vbCrLf + "</script>")
Response.Write(CustomizeSpreadSheet())
End Sub

Function LoadData(ByVal strPayload() As String)
Dim newRS As New ADODB.Recordset
Dim xmlRS As New ADODB.Recordset

newRS.Fields.Append("ticket_numbers",
ADODB.DataTypeEnum.adVarChar, 10)
With newRS
.Open()
For i As Integer = 0 To 9
.AddNew()
.Fields.Item("ticket_numbers").Value = strPayload(i)
Next
End With
newRS.MoveFirst()
newRS.Save(newStream, ADODB.PersistFormatEnum.adPersistXML)

Dim strConn As String

strConn = "provider=mspersist"
newStream.Position = 0
xmlRS.Open(newStream)

''''JAVASCRIPT TRY
''strScript.Append(vbCrLf + "<script language='javascript'>" +
vbCrLf + "function loadData(){")
''strScript.Append(vbCrLf + "if(document.all.Pivot1 != null){")
'strScript.Append(vbCrLf +
"document.all.Pivot1.ConnectionString = ""DataSource='mydata.xml'"";")
'.Append(xmlRS)
''strScript.Append(vbCrLf +
"document.all.Pivot1.ConnectionString = ""Provider=mspersist"";")
''strScript.Append(vbCrLf + "document.all.Pivot1.DataSource =
").Append(xmlRS).Append(";")
'strScript.Append(vbCrLf +
"document.all.Pivot1.ConnectionString =
""DataSource=").Append(xmlRS).Append(""";")
''strScript.Append(vbCrLf + "}" + vbCrLf + "}" + vbCrLf +
"</script>")
''''JAVASCRIPT TRY

''''VBSCRIPT TRY
strScript.Append(vbCrLf + "<script language='vbscript'>" +
vbCrLf + "Function loadData()")
strScript.Append(vbCrLf + "Pivot1.ConnectionString =
""Provider=mspersist""")
strScript.Append(vbCrLf + "Pivot1.DataSource = ").Append(xmlRS)
strScript.Append(vbCrLf + "End Function")
strScript.Append(vbCrLf + "</script>")
''''VBSCRIPT TRY

Return strScript.ToString
End Function
************************

The source of the compile page comes up with:
****************************
<object classid="clsid:0002E552-0000-0000-C000-000000000046"
id="Pivot1" height="300" width="600" ></object>
<script language='vbscript'>
Function loadData()
Pivot1.ConnectionString = "Provider=mspersist"
Pivot1.DataSource = ADODB.RecordsetClass
response.write("loaddata has been called")
End Function
</script>
<script language='vbscript'>
LoadData()
</script>
<script language='javascript'>
if(document.all.Pivot1 != null){
document.all.Pivot1.ActiveView.FilterAxis.label.visible = true
document.all.Pivot1.ActiveView.RowAxis.label.visible = true
document.all.Pivot1.ActiveView.ColumnAxis.label.visible = true
document.all.Pivot1.ActiveView.titlebar.visible = true
}
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head><title>
Untitled Page
***************************
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

XML FILE FORMAT 0
owc11 connectionstring error with olap - help 1
Problem binding PivotControl with XML data island recordset 0
Java "drop down" menus covered up by OWC objects 0
XML data source queried twice 1
DataSource Control + Read XML Data from Stream 2
FYI: Freezepanes sample ASP code for the OWC Spreadsheet 0
Chart Performance Problem with Large Data Set 2

Top