create view crosstab per sql-syntax

[karin weixler]

Hello,

i would like to create a crosstab view within vb.net 2005 per sql-syntax.
Can someone help me with the right syntax?

here is how i tried it (didn't work):

Dim mySQL As String

mySQL = "CREATE VIEW a_w_tmp_ReportVertrag_Kreuz AS " & _

"TRANSFORM Sum(a_w_tmp_reportVertrag_Gruppiert.ZahlGesamt) AS [Summe von
ZahlGesamt] " & _

"Select Case a_w_tmp_reportVertrag_Gruppiert.Gruppe2 " & _

"FROM(a_w_tmp_reportVertrag_Gruppiert) " & _

"GROUP BY a_w_tmp_reportVertrag_Gruppiert.Gruppe2 " & _

"PIVOT a_w_tmp_reportVertrag_Gruppiert.Gruppe1"

Dim myCmd As OleDbCommand

myCmd = New OleDbCommand(mySQL, Me.Connection, myTransaction)

Try

myCmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message, MsgBoxStyle.Information, "Datenbank Update")

Return False

End Try

Thanks in advance

Karin

 

[Larry Linson]

If I understand your question, it relates to using Microsoft SQL Server as a
database from Visual Basic.NET 2005. This newsgroup is for discussion of,
and questions and answers about Microsoft Access database software, the
database component of Microsoft Office. There are Microsoft-sponsored
newsgroups on VB.NET and on Microsoft SQL Server, and those would be better
sources for the answers you need.

While Microsoft Access does use "Visual Basic for Applications" (VBA), it is
related to classic VB rather than VB.NET and the SQL "dialect" used for
Access' default Jet and ACE database engines does differ from Microsoft SQL
Server's "dialect" of SQL.

Larry Linson
Microsoft Office Access MVP

 

[karin weixler]

Hello Larry,

sorry for not being exact. I try to create a crosstab in MS Access Database
out of VB.net. I have an OleDb Connection to the access database and try to
create the crosstab view with an OleDbCommand. Unfortunately the Sql-Syntax
which is used in Access to do the crosstab view doesn't work to create in
out of VB.net.

Thanks for your time and help
Karin

If I understand your question, it relates to using Microsoft SQL Server as
a database from Visual Basic.NET 2005. This newsgroup is for discussion
of, and questions and answers about Microsoft Access database software,
the database component of Microsoft Office. There are Microsoft-sponsored
newsgroups on VB.NET and on Microsoft SQL Server, and those would be
better sources for the answers you need.

While Microsoft Access does use "Visual Basic for Applications" (VBA), it
is related to classic VB rather than VB.NET and the SQL "dialect" used for
Access' default Jet and ACE database engines does differ from Microsoft
SQL Server's "dialect" of SQL.

Larry Linson
Microsoft Office Access MVP

Hello,

i would like to create a crosstab view within vb.net 2005 per sql-syntax.
Can someone help me with the right syntax?

here is how i tried it (didn't work):

Dim mySQL As String

mySQL = "CREATE VIEW a_w_tmp_ReportVertrag_Kreuz AS " & _

"TRANSFORM Sum(a_w_tmp_reportVertrag_Gruppiert.ZahlGesamt) AS [Summe von
ZahlGesamt] " & _

"Select Case a_w_tmp_reportVertrag_Gruppiert.Gruppe2 " & _

"FROM(a_w_tmp_reportVertrag_Gruppiert) " & _

"GROUP BY a_w_tmp_reportVertrag_Gruppiert.Gruppe2 " & _

"PIVOT a_w_tmp_reportVertrag_Gruppiert.Gruppe1"

Dim myCmd As OleDbCommand

myCmd = New OleDbCommand(mySQL, Me.Connection, myTransaction)

Try

myCmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message, MsgBoxStyle.Information, "Datenbank Update")

Return False

End Try

Thanks in advance

Karin

 

[John Spencer]

You can't do it (as far as I know) from vb.net. It can be done in SQL
statement if you know the pivot columns to be returned. It is complex and
involves using multiple subqueries in the FROM clause of the query. Basically
one sub-query per pivot column returned.

I have done the equivalent in an MS SQL pass through query. The Access
crosstab was 900 characters in length - the equivalent SQL was 13,000
characters in length.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hello Larry,

sorry for not being exact. I try to create a crosstab in MS Access Database
out of VB.net. I have an OleDb Connection to the access database and try to
create the crosstab view with an OleDbCommand. Unfortunately the Sql-Syntax
which is used in Access to do the crosstab view doesn't work to create in
out of VB.net.

Thanks for your time and help
Karin

If I understand your question, it relates to using Microsoft SQL Server as
a database from Visual Basic.NET 2005. This newsgroup is for discussion
of, and questions and answers about Microsoft Access database software,
the database component of Microsoft Office. There are Microsoft-sponsored
newsgroups on VB.NET and on Microsoft SQL Server, and those would be
better sources for the answers you need.

While Microsoft Access does use "Visual Basic for Applications" (VBA), it
is related to classic VB rather than VB.NET and the SQL "dialect" used for
Access' default Jet and ACE database engines does differ from Microsoft
SQL Server's "dialect" of SQL.

Larry Linson
Microsoft Office Access MVP

Hello,

i would like to create a crosstab view within vb.net 2005 per sql-syntax.
Can someone help me with the right syntax?

here is how i tried it (didn't work):

Dim mySQL As String

mySQL = "CREATE VIEW a_w_tmp_ReportVertrag_Kreuz AS " & _

"TRANSFORM Sum(a_w_tmp_reportVertrag_Gruppiert.ZahlGesamt) AS [Summe von
ZahlGesamt] " & _

"Select Case a_w_tmp_reportVertrag_Gruppiert.Gruppe2 " & _

"FROM(a_w_tmp_reportVertrag_Gruppiert) " & _

"GROUP BY a_w_tmp_reportVertrag_Gruppiert.Gruppe2 " & _

"PIVOT a_w_tmp_reportVertrag_Gruppiert.Gruppe1"

Dim myCmd As OleDbCommand

myCmd = New OleDbCommand(mySQL, Me.Connection, myTransaction)

Try

myCmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message, MsgBoxStyle.Information, "Datenbank Update")

Return False

End Try

Thanks in advance

Karin

 

[karin weixler]

Now it works when i use 'Create Procedure' instead of 'Create View'

Hello Larry,

sorry for not being exact. I try to create a crosstab in MS Access
Database out of VB.net. I have an OleDb Connection to the access database
and try to create the crosstab view with an OleDbCommand. Unfortunately
the Sql-Syntax which is used in Access to do the crosstab view doesn't
work to create in out of VB.net.

Thanks for your time and help
Karin

If I understand your question, it relates to using Microsoft SQL Server
as a database from Visual Basic.NET 2005. This newsgroup is for
discussion of, and questions and answers about Microsoft Access database
software, the database component of Microsoft Office. There are
Microsoft-sponsored newsgroups on VB.NET and on Microsoft SQL Server, and
those would be better sources for the answers you need.

While Microsoft Access does use "Visual Basic for Applications" (VBA), it
is related to classic VB rather than VB.NET and the SQL "dialect" used
for Access' default Jet and ACE database engines does differ from
Microsoft SQL Server's "dialect" of SQL.

Larry Linson
Microsoft Office Access MVP

Hello,

i would like to create a crosstab view within vb.net 2005 per
sql-syntax. Can someone help me with the right syntax?

here is how i tried it (didn't work):

Dim mySQL As String

mySQL = "CREATE VIEW a_w_tmp_ReportVertrag_Kreuz AS " & _

"TRANSFORM Sum(a_w_tmp_reportVertrag_Gruppiert.ZahlGesamt) AS [Summe von
ZahlGesamt] " & _

"Select Case a_w_tmp_reportVertrag_Gruppiert.Gruppe2 " & _

"FROM(a_w_tmp_reportVertrag_Gruppiert) " & _

"GROUP BY a_w_tmp_reportVertrag_Gruppiert.Gruppe2 " & _

"PIVOT a_w_tmp_reportVertrag_Gruppiert.Gruppe1"

Dim myCmd As OleDbCommand

myCmd = New OleDbCommand(mySQL, Me.Connection, myTransaction)

Try

myCmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message, MsgBoxStyle.Information, "Datenbank Update")

Return False

End Try

Thanks in advance

Karin