Create data.xml on the fly

Jun 27, 2008 at 8:35 PM
Edited Jun 27, 2008 at 8:42 PM
Hello, I need some help with the following attempt to create the data.xml on the fly by pulling the data for the albums, images, and thumbs from a sql db using two stored procedures. This has worked for me before, but this time I need to have two seperate slideshows running from the same db for two different sites. So in order to do this I precede each stored procedure, table and reference to the table and such. with a qualifying value. As a mentioned the code works perfectly if every thing is album and slide, but not if I use LandT_album and LandT_slide.
Why is this:

Here is the code that I'm attempting to write:

Code behind which gets the data and creates the data.xml:

 Dim objDS As New DataSet
                objDS.DataSetName = "data"

                Dim objaCmd As SqlCommand = New SqlCommand("LandT_Get_Albums", objConn)
                objaCmd.CommandType = CommandType.StoredProcedure
                Dim AlbumTypeA As SqlParameter = objaCmd.Parameters.Add("@AlbumType", SqlDbType.VarChar, 50)
                AlbumTypeA.Value = "Public"
                Dim objaDA As New SqlDataAdapter(objaCmd)

                objConn.Open()

                objaDA.Fill(objDS, "LandT_Album")

                Dim objsCmd As SqlCommand = New SqlCommand("LandT_Get_Slides", objConn)
                objsCmd.CommandType = CommandType.StoredProcedure
                Dim AlbumTypeB As SqlParameter = objsCmd.Parameters.Add("@AlbumType", SqlDbType.VarChar, 50)
                AlbumTypeB.Value = "Public"
                Dim objsDA As New SqlDataAdapter(objsCmd)

                objsDA.Fill(objDS, "LandT_Slide")

                objConn.Close()

                Dim primarykey As DataColumn = objDS.Tables("LandT_Album").Columns("albumiD")
                Dim foreignkey As DataColumn = objDS.Tables("LandT_Slide").Columns("albumiD")

                Dim relation As DataRelation = objDS.Relations.Add(primarykey, foreignkey)
                relation.Nested = True

                Dim LandT_Album As DataColumn
                For Each LandT_Album In objDS.Tables("LandT_Album").Columns
                    LandT_Album.ColumnMapping = MappingType.Attribute
                Next

                Dim LandT_Slide As DataColumn
                For Each LandT_Slide In objDS.Tables("LandT_Slide").Columns
                    LandT_Slide.ColumnMapping = MappingType.Attribute
                Next

                objDS.WriteXml(Server.MapPath("data.xml"), XmlWriteMode.IgnoreSchema)


The stored procedures:

For the LandT_Album dataset:

 

ALTER

 

PROCEDURE [dbo].[LandT_Get_Albums]

 

@albumtype

 

varchar (50)

 

 

 

 

AS

SELECT

 

 

 

 

DISTINCT [albumid],

 

[title]

 

,

 

[description]

 

,

 

[image]

 

 

 

FROM

 

LandT_Album

 


For the LandT_Slide dataset:

 

 

ALTER

PROCEDURE [dbo].[LandT_Get_Slides]

 

@albumtype

 

varchar (50)

 

 

 

 

AS

SELECT

 

 

 

 

DISTINCT a.[slideid],

 

 

 

 

a

 

.[albumid],

 

a

 

.[title],

 

a

 

.[description],

 

a

 

.[image],

 

 

 

 

a

 

.[thumbnail]

 

 

 

FROM LandT_Slide a, LandT_Album b

WHERE

 

 

 

a.albumid = b.albumid

 

 

 

Tables:

 

CREATE

 

TABLE [dbo].[LandT_Album](

 

 

 

 

 

[albumid] [int]

 

IDENTITY(1,1) NOT NULL,

 

 

 

 

 

[title] [varchar]

 

(150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

 

 

 

 

 

[description] [varchar]

 

(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

 

 

 

 

 

[image] [varchar]

 

(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

 

 

 

 

 

[albumtype] [varchar]

 

(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

 

 

 

 

 

 

 

CONSTRAINT [PK_album] PRIMARY KEY CLUSTERED

 

 

 

 

 

CREATE

 

TABLE [dbo].[LandT_Slide](

 

 

 

 

 

[slideid] [int]

 

IDENTITY(1,1) NOT NULL,

 

 

 

 

 

[albumid] [int]

 

NULL,

 

 

 

 

 

[title] [varchar]

 

(150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

 

 

 

 

 

[description] [varchar]

 

(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

 

 

 

 

 

[image] [varchar]

 

(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

 

 

 

 

 

[thumbnail] [varchar]

 

(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

 

 

 

 

 

 

 

CONSTRAINT [PK_slide] PRIMARY KEY CLUSTERED

 

 

 

 

 

Jun 30, 2008 at 3:57 PM
Got it.. it was a typo in my xml declares.