Do lots of COUNTs

Hello :)

I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?

SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULLsome ddl and sample data would help...read the hint sticky at the top of the forum...but I'll give it a shot

bit, would you like a result set of many rows or a single row

Also, why don't you use a sproc?|||Apologies:

CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)

Sample data attached.

I'd like the results as a single row with 6 columns: one for each of the queries.

I'm not using a sproc because I'm lazy and haven't got round to taking it out of my ASP and putting it into one yet. And I don't know how to put all those SQL queries into one proc.|||Something like

select
sum(case when(id like'123%')then 1 else 0 end) Count1
,sum(case when([name] like'sys%')then 1 else 0 end) Count2
from sysobjects|||Do you want something like this....BTW I am not sure...

CREATE PROCEDURE CountTot

AS

SELECT
(SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years') as Totq3,
(SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL) as Totq4,
(SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy')as Totq5,
(SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes') as Totq6,
(SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes') as ToTq7,
(SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz

That's exactly what I want, thankyou :) Now to try and figure out how it works :confused: :D|||Was that not equivalent to what I had posted ? :(|||Was that not equivalent to what I had posted ? :(nope, quite different :)

Do I need to change connection string if I upgrage SQL server from 2000 to 2005?

Do I need to change connection string if I upgrage SQL server from 2000 to 2005 in ASP.NET 2.0?Not necessarily. Seehttp://www.connectionstrings.com/ for examples of connection strings for each SQL Server.

Do I need a Primary Key?

I am programming a site in ASP. I am used to using Access which forces you
to have a Primary Key. However, I am learning to use SQL which does not
seem to force you to have a Primary Key. Do I really need one? Please let
me know and why. Thanks!!Techniclly speaking you don't need one, but I would consider any table
without a primary key poor design.
The main reason you need one is that the primary key guarantees you'll have
a column in your table that you can use to uniquely identify each record.
Without a primary key you could potentialy end up with multiple identical
records in your table which you're not able to identify individualy using a
select, update or delete statement.
HTH
Karl Gram
http://www.gramonline.com
"michaaal" <res0gyio@.verizon.net> wrote in message
news:O6$6#0iDEHA.3784@.TK2MSFTNGP10.phx.gbl...
> I am programming a site in ASP. I am used to using Access which forces
you
> to have a Primary Key. However, I am learning to use SQL which does not
> seem to force you to have a Primary Key. Do I really need one? Please
let
> me know and why. Thanks!!
>|||Hi,
To add on to old post,
1. Enforce uniqueness for values entered in specified columns
2. Will not allow nulls.
3. If you define a primary key for a table in your database, you can relate
that table to other tables, thus reducing the need for redundant data.
This will allow you to have Parent child relation ship with out writing
code.
4. This will allow you to do Cascading (Refer boks inline)
Always for a better database modelling we should enforce Primary key /
Foregn key concept.
Thanks
Hari
MCDBA
"Karl Gram" <NOSPAMkarl@.gramonline.nl> wrote in message
news:#FrwxMkDEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Techniclly speaking you don't need one, but I would consider any table
> without a primary key poor design.
> The main reason you need one is that the primary key guarantees you'll
have
> a column in your table that you can use to uniquely identify each record.
> Without a primary key you could potentialy end up with multiple identical
> records in your table which you're not able to identify individualy using
a
> select, update or delete statement.
> --
> HTH
> Karl Gram
> http://www.gramonline.com
> "michaaal" <res0gyio@.verizon.net> wrote in message
> news:O6$6#0iDEHA.3784@.TK2MSFTNGP10.phx.gbl...
> you
> let
>|||> 3. If you define a primary key for a table in your database, you can
relate
> that table to other tables, thus reducing the need for redundant data.
> This will allow you to have Parent child relation ship with out
writing
> code.
The above statement brings on another question I had... Do I really
WANT to do this type of thing on the SQL server level? Or do I
want to do this type of thing in my code. My first inclination is to do
it in the code, however, I have not really sat down and researched the
possible speed differences. Any comments on this? Thank you!|||"michaaal" <res0gyio@.verizon.net> wrote in message
news:eRB$WFlDEHA.3980@.TK2MSFTNGP09.phx.gbl...
> relate
> writing
> The above statement brings on another question I had... Do I really
> WANT to do this type of thing on the SQL server level? Or do I
> want to do this type of thing in my code. My first inclination is to do
> it in the code, however, I have not really sat down and researched the
> possible speed differences. Any comments on this? Thank you!
Enforcing constraints in the code means that they will only be enforced in
your code. If someone uses Access or similar to access your database
directly they can by-pass all your constraints and wreak havoc.
It is also (IMHO) easier to document and troubleshoot. The constraints are
there as part of your table definition. All your data-centric info is in
one place.
As for speed, if you have the contsraints in SQL Server the optimizer and
can make informed decisions on how best to optimize the queries. Otherwise
in the code you will have to decide how to join the data, which is either
going to be very complicated, or not the best method in every circumstance.
Finally, and this is more a judgement on my programming skills than yours,
constraints work pretty much the way it says on the box. If you are
hand-coding all this, then bugs can creep in, you may not foresee every
eventuality, etc.
So my vote is for data-centric rules to be in the data tier.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||No, you should always enforce constraints at the DATA level. Enforcing them
in the code means that your data can become corrupt by someone simply
bypassing your application (e.g. running an insert/update/delete from Query
Analyzer).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"michaaal" <res0gyio@.verizon.net> wrote in message
news:eRB$WFlDEHA.3980@.TK2MSFTNGP09.phx.gbl...
> relate
> writing
> The above statement brings on another question I had... Do I really
> WANT to do this type of thing on the SQL server level? Or do I
> want to do this type of thing in my code. My first inclination is to do
> it in the code, however, I have not really sat down and researched the
> possible speed differences. Any comments on this? Thank you!
>
>|||RE/
>No, you should always enforce constraints at the DATA level. Enforcing the
m
>in the code means that your data can become corrupt by someone simply
>bypassing your application (e.g. running an insert/update/delete from Query
>Analyzer).
Do you prefer to enforce RI via triggers or the other way?
"Other way" because I don't know enough go spell it out...Converted a few MS
Access DBs and wound up with triggers - so that's all I know. MSDN Univers
al
coming soon - so I guess I'll get the option to go either way via MS Visio's
DB
design tool...
--
PeteCresswell|||No, triggers can be pretty poor for performance, depending on other
circumstances. I prefer traditional primary/foreign key relationships, then
violations are stopped in their tracks rather than after the fact.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"(Pete Cresswell)" <x@.y.z> wrote in message
news:k2op50heohj4o6j7tkgp05o7gk61hf1eue@.
4ax.com...
> RE/
> Do you prefer to enforce RI via triggers or the other way?
> "Other way" because I don't know enough go spell it out...Converted a few
> MS
> Access DBs and wound up with triggers - so that's all I know. MSDN
> Universal
> coming soon - so I guess I'll get the option to go either way via MS
> Visio's DB
> design tool...
> --
> PeteCresswell

DNS problems in ASP

I hope that I'm right here.
We have an intranet-site in ASP where, till now the data was in an
Access database and we connected to the data via DNS.
Now we would like to change the data to a SQL-server.
For me, it seems logic that logic that everything should work after
creating a new DNS to the new data.
It doesn't!
None of the tables where found, ... After some search I found that I
need to prefix the tables with the owner.
I suppose that I do something wrong here and that there is a solution
for this.
Also I keep a local copy of the site to develop new pages where I also
would like to keep the data in Access so that I don't work with the
real data.
Many thanks in advance,
Filip
What tool did you use to migrate from Access to SQL Server?
You mention that you need to preface the tables with their owner.
Does that mean that a table within Access called tblData is now:
owner.tblData
or
ownertblData
If tables were created with the incorrect name you can change the table
names pretty easily. Look up sp_rename within Books Online
http://msdn2.microsoft.com/en-us/library/ms203721.aspx
If the owner of the table is not dbo and is some database user that you have
within SQL Server you should change the owner to dbo.
Look up sp_changeobjectowner within Books Online.
Once the owner is changed to dbo you should not have to specify the owner of
the table when you retrieve data from the tables.
Now that you are using SQL Server you might want to look into using stored
procedures to perform all your data access (CRUD - create, read, update,
delete)...but lets take things one step at a time.
Keith Kratochvil
"FilMar" <FilMar@.gmail.com> wrote in message
news:1159948972.268238.108090@.c28g2000cwb.googlegr oups.com...
>I hope that I'm right here.
> We have an intranet-site in ASP where, till now the data was in an
> Access database and we connected to the data via DNS.
> Now we would like to change the data to a SQL-server.
> For me, it seems logic that logic that everything should work after
> creating a new DNS to the new data.
> It doesn't!
> None of the tables where found, ... After some search I found that I
> need to prefix the tables with the owner.
> I suppose that I do something wrong here and that there is a solution
> for this.
> Also I keep a local copy of the site to develop new pages where I also
> would like to keep the data in Access so that I don't work with the
> real data.
> Many thanks in advance,
> Filip
>
|||Thanks Keith, that does the trick.
In Access they become owner_tblData but there you can rename them
without problems. In ASP they need to be [owner].[tbl_Data] if there
not owned by dbo but that is now solved with your solution.
BTW: we did the migration with the upsize wizard of Access as it seems
it does the job far better than the import/export of SQL-server.
Many thanks,
Filip
Keith Kratochvil schreef:
[vbcol=seagreen]
> What tool did you use to migrate from Access to SQL Server?
> You mention that you need to preface the tables with their owner.
> Does that mean that a table within Access called tblData is now:
> owner.tblData
> or
> ownertblData
> If tables were created with the incorrect name you can change the table
> names pretty easily. Look up sp_rename within Books Online
> http://msdn2.microsoft.com/en-us/library/ms203721.aspx
> If the owner of the table is not dbo and is some database user that you have
> within SQL Server you should change the owner to dbo.
> Look up sp_changeobjectowner within Books Online.
> Once the owner is changed to dbo you should not have to specify the owner of
> the table when you retrieve data from the tables.
>
> Now that you are using SQL Server you might want to look into using stored
> procedures to perform all your data access (CRUD - create, read, update,
> delete)...but lets take things one step at a time.
>
> --
> Keith Kratochvil
>
> "FilMar" <FilMar@.gmail.com> wrote in message
> news:1159948972.268238.108090@.c28g2000cwb.googlegr oups.com...
|||I am glad that I could help!
Keith Kratochvil
"FilMar" <FilMar@.gmail.com> wrote in message
news:1160572041.620932.71790@.b28g2000cwb.googlegro ups.com...
> Thanks Keith, that does the trick.
> In Access they become owner_tblData but there you can rename them
> without problems. In ASP they need to be [owner].[tbl_Data] if there
> not owned by dbo but that is now solved with your solution.
> BTW: we did the migration with the upsize wizard of Access as it seems
> it does the job far better than the import/export of SQL-server.
> Many thanks,
> Filip
> Keith Kratochvil schreef:
>

DNS problems in ASP

I hope that I'm right here.
We have an intranet-site in ASP where, till now the data was in an
Access database and we connected to the data via DNS.
Now we would like to change the data to a SQL-server.
For me, it seems logic that logic that everything should work after
creating a new DNS to the new data.
It doesn't!
None of the tables where found, ... After some search I found that I
need to prefix the tables with the owner.
I suppose that I do something wrong here and that there is a solution
for this.
Also I keep a local copy of the site to develop new pages where I also
would like to keep the data in Access so that I don't work with the
real data.
Many thanks in advance,
FilipWhat tool did you use to migrate from Access to SQL Server?
You mention that you need to preface the tables with their owner.
Does that mean that a table within Access called tblData is now:
owner.tblData
or
ownertblData
If tables were created with the incorrect name you can change the table
names pretty easily. Look up sp_rename within Books Online
http://msdn2.microsoft.com/en-us/library/ms203721.aspx
If the owner of the table is not dbo and is some database user that you have
within SQL Server you should change the owner to dbo.
Look up sp_changeobjectowner within Books Online.
Once the owner is changed to dbo you should not have to specify the owner of
the table when you retrieve data from the tables.
Now that you are using SQL Server you might want to look into using stored
procedures to perform all your data access (CRUD - create, read, update,
delete)...but lets take things one step at a time.
Keith Kratochvil
"FilMar" <FilMar@.gmail.com> wrote in message
news:1159948972.268238.108090@.c28g2000cwb.googlegroups.com...
>I hope that I'm right here.
> We have an intranet-site in ASP where, till now the data was in an
> Access database and we connected to the data via DNS.
> Now we would like to change the data to a SQL-server.
> For me, it seems logic that logic that everything should work after
> creating a new DNS to the new data.
> It doesn't!
> None of the tables where found, ... After some search I found that I
> need to prefix the tables with the owner.
> I suppose that I do something wrong here and that there is a solution
> for this.
> Also I keep a local copy of the site to develop new pages where I also
> would like to keep the data in Access so that I don't work with the
> real data.
> Many thanks in advance,
> Filip
>|||Thanks Keith, that does the trick.
In Access they become owner_tblData but there you can rename them
without problems. In ASP they need to be [owner].[tbl_Data] if there
not owned by dbo but that is now solved with your solution.
BTW: we did the migration with the upsize wizard of Access as it seems
it does the job far better than the import/export of SQL-server.
Many thanks,
Filip
Keith Kratochvil schreef:
[vbcol=seagreen]
> What tool did you use to migrate from Access to SQL Server?
> You mention that you need to preface the tables with their owner.
> Does that mean that a table within Access called tblData is now:
> owner.tblData
> or
> ownertblData
> If tables were created with the incorrect name you can change the table
> names pretty easily. Look up sp_rename within Books Online
> http://msdn2.microsoft.com/en-us/library/ms203721.aspx
> If the owner of the table is not dbo and is some database user that you ha
ve
> within SQL Server you should change the owner to dbo.
> Look up sp_changeobjectowner within Books Online.
> Once the owner is changed to dbo you should not have to specify the owner
of
> the table when you retrieve data from the tables.
>
> Now that you are using SQL Server you might want to look into using stored
> procedures to perform all your data access (CRUD - create, read, update,
> delete)...but lets take things one step at a time.
>
> --
> Keith Kratochvil
>
> "FilMar" <FilMar@.gmail.com> wrote in message
> news:1159948972.268238.108090@.c28g2000cwb.googlegroups.com...|||I am glad that I could help!
Keith Kratochvil
"FilMar" <FilMar@.gmail.com> wrote in message
news:1160572041.620932.71790@.b28g2000cwb.googlegroups.com...
> Thanks Keith, that does the trick.
> In Access they become owner_tblData but there you can rename them
> without problems. In ASP they need to be [owner].[tbl_Data] if the
re
> not owned by dbo but that is now solved with your solution.
> BTW: we did the migration with the upsize wizard of Access as it seems
> it does the job far better than the import/export of SQL-server.
> Many thanks,
> Filip
> Keith Kratochvil schreef:
>
>

DMX query and ASP

I am trying to get along with SQL server 2005, just made the mining model and i use this DMX query to get the time series prediction. Now this is working and i get results in SQL server management studio.

SELECT PredictTimeSeries([Apot Sales],5)

FROM [Sales Bycom]

I tried running this as a query in ASP but obviously this can't be done

Here is my question. I have made an SQL server connection in ASP. But how can I get the prediction results displayed in to ASP?

Thanx

You will need to make an Analysis Server connection to execute the DMX query from ASP. Take a look at the code download at the bottom of this article for an example of how to use DMX queries from ASP: http://www.aspnetpro.com/newsletterarticle/2004/10/asp200410ri_l/asp200410ri_l.asp.|||Ok thanx I will check it out|||After spending some weeks of testing and reading I came up with this:

Its a part of the code of cross web application, I managed to make a model of timeseries in SQL server and I have hopefully (since I dont get any errors) run my dmx query through aspx. BUT I can get the result to display in aspx. My guess is because the original code was made for string variables and I am tring to get numeric variables in it. I suppose that all I need to do is to get the result from the DMX query in the array

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End If
End While

And here is my DMX query

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5)" + _
"FROM [Sales]"

********************************

I would appriciate any answers since this project is for my diploma and I really cant seem to get through

Thanks

|||What error(s) are you seeing? Keep in mind that PredictTimeSeries(<col>, N) returns a table with two columns - the first column is a time index ($TIME) and the second column contains the predicted values for the column you're predicting.|||Thanks Raman Iyer , The weird thing is that I dont get any errors neither any results

in the place where the results appear I get "Microsoft.AnalysisServices.AdomdClient.AdomdDataReader"

here is the whole code from predict.vb.asp

Imports System
Imports System.Collections
Imports System.ComponentModel
Imports System.Drawing
Imports System.Web
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports Microsoft.AnalysisServices.AdomdClient

Namespace MovieCrossSellApplication

Partial Public Class ShoppingBasket_Recommendations
Inherits System.Web.UI.Page

Protected Overrides Sub OnInit(ByVal e As EventArgs)
'
' CODEGEN: This call is required by the ASP.NET Web Form Designer.
'
InitializeComponent()
MyBase.OnInit(e)
End Sub 'OnInit

'/ <summary>
'/ Required method for Designer support - do not modify
'/ the contents of this method with the code editor.
'/ </summary>
Private Sub InitializeComponent()
End Sub 'InitializeComponent

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5) " + _
"FROM [Sales] "

' "SELECT FLATTENED TopCount(" + _
' "Predict([Customer Movies], INCLUDE_STATISTICS)," + _
' "$AdjustedProbability, 5) From [Movie Recommendations] " + _
' "NATURAL PREDICTION JOIN (SELECT ("
'Dim strDMX2 As String = ") AS [Customer Movies]) AS t"

'Iterate shopping basket and produce input case
'Dim cItems As Integer = vInputItems.Count
' Dim strDMX As String = ""
' Dim i As Integer
'For i = 0 To cItems - 1
' Dim item As String = vInputItems(i).ToString()
' item = item.Replace("’", "’’")
'strDMX += "SELECT " + "'" + item + "' AS " + "[Movie]"
' If i < cItems - 1 Then
' strDMX += " UNION "
'End If
'Next i

'Put together DMX prediction query to get 5 recommendations
'strDMX = strDMX1 + strDMX '+ strDMX2

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

'If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
'End If
End While

'Disconnect from Analysis Server
asSession.DisConnect()

End Sub 'GetRecommendations

Public Sub Button1_Click( _
ByVal sender As Object, _
ByVal e As System.EventArgs) 'Handles Me.Button1.Click

' Parse the input into an ArrayList of strings.
Dim alInputItems As New ArrayList()
Dim splitchar As Char() = {";"c}
Dim szInputItems As String() = Me.TextBox1.Text.Split(splitchar, 20)
Dim i As Integer
For i = 0 To szInputItems.Length - 1
alInputItems.Add(szInputItems(i).Trim())
Next i

' Add items to the shopping basket
dgShoppingBasket.DataSource = alInputItems
dgShoppingBasket.DataBind()

' Get top 5 recommendations
Dim alRecommendedItems As New ArrayList(5)
GetRecommendations(alInputItems, alRecommendedItems)

' Display recommendations
dgRecommendations.DataSource = alRecommendedItems
dgRecommendations.DataBind()
End Sub 'Button1_Click

End Class 'ShoppingBasket_Recommendations

'
' AnalysisServerSession manages
' - connecting to Analysis Server using ADOMD.NET,
' - executing commands and
' - fetching results
'
' Need to add reference to Microsoft.AnalysisServices.AdomdClient.dll
' (located under Program Files\Microsoft.NET\ADOMD.NET\90).
' You may also change this class to use ADO.NET (System.Data.Oledb)
' instead if neccessary, by replacing the AdomdConnection, AdomdCommand
' and AdomdDataReader with OledbConnection, OledbCommand and
' OledbDataReader. The rest of the code should stay the same.
'
Public Class AnalysisServerSession
Protected asCommand As Microsoft.AnalysisServices.AdomdClient.AdomdCommand
Protected asConnection As Microsoft.AnalysisServices.AdomdClient.AdomdConnection
Public asDataReader As Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
Public szServer As String = "localhost"
Public szCatalog As String = "myDSS"

Public Sub New()
asCommand = Nothing
asConnection = Nothing
asDataReader = Nothing
End Sub 'New

Public Function Connect() As Boolean
Dim asConnectionString As String = _
"Provider=MSOLAP.3;Data Source=" + _
szServer + ";Initial Catalog=" + szCatalog

asConnection = New AdomdConnection(asConnectionString)
asConnection.Open()

Return True
End Function 'Connect

Public Function ExecuteAndFetchResult(ByVal strCommand As string) As Boolean
If asConnection Is Nothing Then
Return False
End If
If asCommand Is Nothing Then
asCommand = New AdomdCommand()
End If
strCommand = strCommand.Replace("NaN", "null")
strCommand = strCommand.Replace("Infinity", "null")

Try
If Not (asDataReader Is Nothing) Then
If Not asDataReader.IsClosed Then
asDataReader.Close()
End If
End If
asCommand.Connection = asConnection
asCommand.CommandText = strCommand
asDataReader = asCommand.ExecuteReader()

Catch e As Exception

Log(e.Message)
Return False
End Try
Return True
End Function 'ExecuteAndFetchResult

Public Function DisConnect() As Boolean
Try
If Not (asConnection Is Nothing) Then
asConnection.Close()
End If
If Not (asCommand Is Nothing) Then
asCommand.Connection = Nothing
End If
If Not (asDataReader Is Nothing) Then
asDataReader.Close()
End If
Catch e As Exception
Console.WriteLine(e.Message)
End Try
Return True
End Function 'DisConnect

Private Sub Log(ByVal message As String)
'Log the message to some place
System.Diagnostics.Debug.Assert(False, message)
Return
End Sub 'Log

End Class 'AnalysisServerSession

End Namespace 'MovieCrossSellApplication

Any help appreciated
|||OK I think I am getting somewhere......

Raman Iyer you should be right I must have 2 collums in order to save the results in an array,
can't figure out how I can do that checked some tutorials but came up with nothing.....

If anyone could help or suggest a web site?

Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Dim vDSSItems(10) as string
While asSession.asDataReader.Read()

Dim val As string = asSession.asDataReader.GetString(0)
vDSSItems.add(val)
end while

DMX query and ASP

I am trying to get along with SQL server 2005, just made the mining model and i use this DMX query to get the time series prediction. Now this is working and i get results in SQL server management studio.

SELECT PredictTimeSeries([Apot Sales],5)

FROM [Sales Bycom]

I tried running this as a query in ASP but obviously this can't be done

Here is my question. I have made an SQL server connection in ASP. But how can I get the prediction results displayed in to ASP?

Thanx

You will need to make an Analysis Server connection to execute the DMX query from ASP. Take a look at the code download at the bottom of this article for an example of how to use DMX queries from ASP: http://www.aspnetpro.com/newsletterarticle/2004/10/asp200410ri_l/asp200410ri_l.asp.|||Ok thanx I will check it out|||After spending some weeks of testing and reading I came up with this:

Its a part of the code of cross web application, I managed to make a model of timeseries in SQL server and I have hopefully (since I dont get any errors) run my dmx query through aspx. BUT I can get the result to display in aspx. My guess is because the original code was made for string variables and I am tring to get numeric variables in it. I suppose that all I need to do is to get the result from the DMX query in the array

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End If
End While

And here is my DMX query

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5)" + _
"FROM [Sales]"

********************************

I would appriciate any answers since this project is for my diploma and I really cant seem to get through

Thanks|||What error(s) are you seeing? Keep in mind that PredictTimeSeries(<col>, N) returns a table with two columns - the first column is a time index ($TIME) and the second column contains the predicted values for the column you're predicting.|||Thanks Raman Iyer , The weird thing is that I dont get any errors neither any results

in the place where the results appear I get "Microsoft.AnalysisServices.AdomdClient.AdomdDataReader"

here is the whole code from predict.vb.asp

Imports System
Imports System.Collections
Imports System.ComponentModel
Imports System.Drawing
Imports System.Web
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports Microsoft.AnalysisServices.AdomdClient

Namespace MovieCrossSellApplication

Partial Public Class ShoppingBasket_Recommendations
Inherits System.Web.UI.Page

Protected Overrides Sub OnInit(ByVal e As EventArgs)
'
' CODEGEN: This call is required by the ASP.NET Web Form Designer.
'
InitializeComponent()
MyBase.OnInit(e)
End Sub 'OnInit

'/ <summary>
'/ Required method for Designer support - do not modify
'/ the contents of this method with the code editor.
'/ </summary>
Private Sub InitializeComponent()
End Sub 'InitializeComponent

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5) " + _
"FROM [Sales] "

' "SELECT FLATTENED TopCount(" + _
' "Predict([Customer Movies], INCLUDE_STATISTICS)," + _
' "$AdjustedProbability, 5) From [Movie Recommendations] " + _
' "NATURAL PREDICTION JOIN (SELECT ("
'Dim strDMX2 As String = ") AS [Customer Movies]) AS t"

'Iterate shopping basket and produce input case
'Dim cItems As Integer = vInputItems.Count
' Dim strDMX As String = ""
' Dim i As Integer
'For i = 0 To cItems - 1
' Dim item As String = vInputItems(i).ToString()
' item = item.Replace("’", "’’")
'strDMX += "SELECT " + "'" + item + "' AS " + "[Movie]"
' If i < cItems - 1 Then
' strDMX += " UNION "
'End If
'Next i

'Put together DMX prediction query to get 5 recommendations
'strDMX = strDMX1 + strDMX '+ strDMX2

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

'If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
'End If
End While

'Disconnect from Analysis Server
asSession.DisConnect()

End Sub 'GetRecommendations

Public Sub Button1_Click( _
ByVal sender As Object, _
ByVal e As System.EventArgs) 'Handles Me.Button1.Click

' Parse the input into an ArrayList of strings.
Dim alInputItems As New ArrayList()
Dim splitchar As Char() = {";"c}
Dim szInputItems As String() = Me.TextBox1.Text.Split(splitchar, 20)
Dim i As Integer
For i = 0 To szInputItems.Length - 1
alInputItems.Add(szInputItems(i).Trim())
Next i

' Add items to the shopping basket
dgShoppingBasket.DataSource = alInputItems
dgShoppingBasket.DataBind()

' Get top 5 recommendations
Dim alRecommendedItems As New ArrayList(5)
GetRecommendations(alInputItems, alRecommendedItems)

' Display recommendations
dgRecommendations.DataSource = alRecommendedItems
dgRecommendations.DataBind()
End Sub 'Button1_Click

End Class 'ShoppingBasket_Recommendations

'
' AnalysisServerSession manages
' - connecting to Analysis Server using ADOMD.NET,
' - executing commands and
' - fetching results
'
' Need to add reference to Microsoft.AnalysisServices.AdomdClient.dll
' (located under Program Files\Microsoft.NET\ADOMD.NET\90).
' You may also change this class to use ADO.NET (System.Data.Oledb)
' instead if neccessary, by replacing the AdomdConnection, AdomdCommand
' and AdomdDataReader with OledbConnection, OledbCommand and
' OledbDataReader. The rest of the code should stay the same.
'
Public Class AnalysisServerSession
Protected asCommand As Microsoft.AnalysisServices.AdomdClient.AdomdCommand
Protected asConnection As Microsoft.AnalysisServices.AdomdClient.AdomdConnection
Public asDataReader As Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
Public szServer As String = "localhost"
Public szCatalog As String = "myDSS"

Public Sub New()
asCommand = Nothing
asConnection = Nothing
asDataReader = Nothing
End Sub 'New

Public Function Connect() As Boolean
Dim asConnectionString As String = _
"Provider=MSOLAP.3;Data Source=" + _
szServer + ";Initial Catalog=" + szCatalog

asConnection = New AdomdConnection(asConnectionString)
asConnection.Open()

Return True
End Function 'Connect

Public Function ExecuteAndFetchResult(ByVal strCommand As string) As Boolean
If asConnection Is Nothing Then
Return False
End If
If asCommand Is Nothing Then
asCommand = New AdomdCommand()
End If
strCommand = strCommand.Replace("NaN", "null")
strCommand = strCommand.Replace("Infinity", "null")

Try
If Not (asDataReader Is Nothing) Then
If Not asDataReader.IsClosed Then
asDataReader.Close()
End If
End If
asCommand.Connection = asConnection
asCommand.CommandText = strCommand
asDataReader = asCommand.ExecuteReader()

Catch e As Exception

Log(e.Message)
Return False
End Try
Return True
End Function 'ExecuteAndFetchResult

Public Function DisConnect() As Boolean
Try
If Not (asConnection Is Nothing) Then
asConnection.Close()
End If
If Not (asCommand Is Nothing) Then
asCommand.Connection = Nothing
End If
If Not (asDataReader Is Nothing) Then
asDataReader.Close()
End If
Catch e As Exception
Console.WriteLine(e.Message)
End Try
Return True
End Function 'DisConnect

Private Sub Log(ByVal message As String)
'Log the message to some place
System.Diagnostics.Debug.Assert(False, message)
Return
End Sub 'Log

End Class 'AnalysisServerSession

End Namespace 'MovieCrossSellApplication

Any help appreciated|||OK I think I am getting somewhere......

Raman Iyer you should be right I must have 2 collums in order to save the results in an array,
can't figure out how I can do that checked some tutorials but came up with nothing.....

If anyone could help or suggest a web site?

Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Dim vDSSItems(10) as string
While asSession.asDataReader.Read()

Dim val As string = asSession.asDataReader.GetString(0)
vDSSItems.add(val)
end while

DLL use In Crystal Reports

Hi K.Babu!

This is Ramesh and i have a qury for u,i have developed a DLL in VB and registered it for usein asp and vb and i aslo want to make use of this DLL in Crystal Reports without VB/ASp as Interface for Reports

Please tell me how to call the functions written in DLL in Crystal Reports directly

i have put my DLL in the directory where other Crystal Reports DLL are and registered but dont know how to call this dll in reports

Waiting for ur reply soonHi,
You need to have your DLL name start with CRUFLxxxx where xxxx is the rest of the DLL name i.e. CRUFLmyStuff.DLL, then copy into Crystal dir and then register from DOS prompt with: -
regsvr32 c:\winnt\crystal\CRUFLmyStuff.DLL)
after this restart CR and you should find your DLL function's from
formula workshop / functions / additional functions / Visual Basic UFL's and there should be your DLL functions.

If this don't work check out Crystals white paper SCR_User_Defined_Functions.pdf

good luck!|||Hello mramesh73,

Just curious to know whether phil's resolution helped. I have plans of creating dll for the same purpose down the road.

Thanks

Distributed transaction error, Help pls

I post this in ASP incase too.. thanks for any help pinning this one
anyone

My hosting recently upgraded their firewall , file server
and sql server and now my existing code fails whenever
I use
then you normal ASP webpage,
with odbc and sql calls , html and

and bottom of the page, the abort procedure to produce nice error to end user
if one occurs.

' The Transacted Script Abort Handler. This sub-routine
' will be called if the script transacted aborts

Sub OnTransactionAbort()
Dim objErrorInfo
Set objErrorInfo = Server.GetLastError

response.clear
Response.write "FULL ERROR DETAILS"

Response.Write("ASPCode = " & objErrorInfo.ASPCode&"")
Response.Write("ASPDescription = " & objErrorInfo.ASPDescription&"")
Response.Write("Category = " & objErrorInfo.Category&"")
Response.Write("Column = " & objErrorInfo.Column&"")
Response.Write("Description = " & objErrorInfo.Description&"")
Response.Write("File = " & objErrorInfo.File&"")
Response.Write("Line = " & objErrorInfo.Line&"")
Response.Write("Number = " & objErrorInfo.Number&"")
Response.Write("Source = " & objErrorInfo.Source&"")
Response.Write ""
then some ifs to redirect to alternative
page one specified errors.
Worked very well for 8months on their
old server using sql7 and IIS5
assume it was still Win2000 fileserver/webserver

I have reduce some of the code above.
Now they dont understand this piece of
code. But I got the code from a developer network who said was only
way to trap all errors including SQL,ODBC,JET,ASP calls.

Now my pages that use this on their new web server, firewall, SQL2000 setup

gives the following output instead of
processing and working as it used too,

FULL ERROR DETAILS

ASPCode =
ASPDescription =
Category = Microsoft OLE DB Provider for SQL Server
Column = -1
Description = Distributed transaction error
File = /anypagethatdoesa-sqlserveropen.asp
Line = 44
Number = -2147168246
Source =

They seem to think its my coding.. which it is not..

it seems to me a problem with the transactional service setup
and sql setup permissions or maybe firewall blocking..

have you any clues where i can direct them to look to
so we can fix this issue cheers.As far as I know MS DTC service will assign a port dynamically (different from 1433) so it won't pass through firewall...

Originally posted by pss2010
I post this in ASP incase too.. thanks for any help pinning this one
anyone

My hosting recently upgraded their firewall , file server
and sql server and now my existing code fails whenever
I use
then you normal ASP webpage,
with odbc and sql calls , html and

and bottom of the page, the abort procedure to produce nice error to end user
if one occurs.

' The Transacted Script Abort Handler. This sub-routine
' will be called if the script transacted aborts

Sub OnTransactionAbort()
Dim objErrorInfo
Set objErrorInfo = Server.GetLastError

response.clear
Response.write "FULL ERROR DETAILS"

Response.Write("ASPCode = " & objErrorInfo.ASPCode&"")
Response.Write("ASPDescription = " & objErrorInfo.ASPDescription&"")
Response.Write("Category = " & objErrorInfo.Category&"")
Response.Write("Column = " & objErrorInfo.Column&"")
Response.Write("Description = " & objErrorInfo.Description&"")
Response.Write("File = " & objErrorInfo.File&"")
Response.Write("Line = " & objErrorInfo.Line&"")
Response.Write("Number = " & objErrorInfo.Number&"")
Response.Write("Source = " & objErrorInfo.Source&"")
Response.Write ""
then some ifs to redirect to alternative
page one specified errors.
Worked very well for 8months on their
old server using sql7 and IIS5
assume it was still Win2000 fileserver/webserver

I have reduce some of the code above.
Now they dont understand this piece of
code. But I got the code from a developer network who said was only
way to trap all errors including SQL,ODBC,JET,ASP calls.

Now my pages that use this on their new web server, firewall, SQL2000 setup

gives the following output instead of
processing and working as it used too,

FULL ERROR DETAILS

ASPCode =
ASPDescription =
Category = Microsoft OLE DB Provider for SQL Server
Column = -1
Description = Distributed transaction error
File = /anypagethatdoesa-sqlserveropen.asp
Line = 44
Number = -2147168246
Source =

They seem to think its my coding.. which it is not..

it seems to me a problem with the transactional service setup
and sql setup permissions or maybe firewall blocking..

have you any clues where i can direct them to look to
so we can fix this issue cheers.