Displaying last record in SQL database table

Got a question here and as I am no expert programmer, this should be easy for you gurus. I have this fairly generic code I've created where I return data from an SQL table in a DataList control. I want to take it to the next level and return only the last record in the table, but I am unsure of how to do that. Perhaps I shouldn't even be using a DataList control, I'm not sure.

Basically, I have a form I developed in Visual Studio using ASP.NET VB. I submit the form and now I want to recall the last entry into the database I would have just made and display it on the following page (thank you page).
Here is the code I have:

PublicClass WebForm1

Inherits System.Web.UI.Page

ProtectedWithEvents SqlSelectCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlInsertCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlUpdateCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlDeleteCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlConnection1As System.Data.SqlClient.SqlConnection

ProtectedWithEvents SqlDataAdapter1As System.Data.SqlClient.SqlDataAdapter

ProtectedWithEvents DataSet1As System.Data.DataSet

ProtectedWithEvents DataList1As System.Web.UI.WebControls.DataList

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.

<System.Diagnostics.DebuggerStepThrough()>PrivateSub InitializeComponent()

Me.SqlSelectCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlInsertCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlUpdateCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlDeleteCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlConnection1 =New System.Data.SqlClient.SqlConnection()

Me.SqlDataAdapter1 =New System.Data.SqlClient.SqlDataAdapter()

Me.DataSet1 =New System.Data.DataSet()

CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).BeginInit()

'

'SqlSelectCommand1

'

Me.SqlSelectCommand1.CommandText = "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract FROM" & _

" authors"

Me.SqlSelectCommand1.Connection =Me.SqlConnection1

'

'SqlInsertCommand1

'

Me.SqlInsertCommand1.CommandText = "INSERT INTO authors(au_id, au_lname, au_fname, phone, address, city, state, zip, " & _

"contract) VALUES (@.au_id, @.au_lname, @.au_fname, @.phone, @.address, @.city, @.state," & _

" @.zip, @.contract); SELECT au_id, au_lname, au_fname, phone, address, city, state" & _

", zip, contract FROM authors WHERE (au_id = @.au_id)"

Me.SqlInsertCommand1.Connection =Me.SqlConnection1

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_id", System.Data.SqlDbType.VarChar, 11, "au_id"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.phone", System.Data.SqlDbType.VarChar, 12, "phone"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.address", System.Data.SqlDbType.VarChar, 40, "address"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.city", System.Data.SqlDbType.VarChar, 20, "city"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.state", System.Data.SqlDbType.VarChar, 2, "state"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.zip", System.Data.SqlDbType.VarChar, 5, "zip"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.contract", System.Data.SqlDbType.Bit, 1, "contract"))

'

'SqlUpdateCommand1

'

Me.SqlUpdateCommand1.CommandText = "UPDATE authors SET au_id = @.au_id, au_lname = @.au_lname, au_fname = @.au_fname, ph" & _

"one = @.phone, address = @.address, city = @.city, state = @.state, zip = @.zip, cont" & _

"ract = @.contract WHERE (au_id = @.Original_au_id) AND (address = @.Original_addres" & _

"s OR @.Original_address IS NULL AND address IS NULL) AND (au_fname = @.Original_au" & _

"_fname) AND (au_lname = @.Original_au_lname) AND (city = @.Original_city OR @.Origi" & _

"nal_city IS NULL AND city IS NULL) AND (contract = @.Original_contract) AND (phon" & _

"e = @.Original_phone) AND (state = @.Original_state OR @.Original_state IS NULL AND" & _

" state IS NULL) AND (zip = @.Original_zip OR @.Original_zip IS NULL AND zip IS NUL" & _

"L); SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract" & _

" FROM authors WHERE (au_id = @.au_id)"

Me.SqlUpdateCommand1.Connection =Me.SqlConnection1

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_id", System.Data.SqlDbType.VarChar, 11, "au_id"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.phone", System.Data.SqlDbType.VarChar, 12, "phone"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.address", System.Data.SqlDbType.VarChar, 40, "address"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.city", System.Data.SqlDbType.VarChar, 20, "city"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.state", System.Data.SqlDbType.VarChar, 2, "state"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.zip", System.Data.SqlDbType.VarChar, 5, "zip"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.contract", System.Data.SqlDbType.Bit, 1, "contract"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_id", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_address", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "address", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_fname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_lname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_city", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "city", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_contract", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "contract", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_phone", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "phone", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_state", System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "state", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_zip", System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "zip", System.Data.DataRowVersion.Original,Nothing))

'

'SqlDeleteCommand1

'

Me.SqlDeleteCommand1.CommandText = "DELETE FROM authors WHERE (au_id = @.Original_au_id) AND (address = @.Original_addr" & _

"ess OR @.Original_address IS NULL AND address IS NULL) AND (au_fname = @.Original_" & _

"au_fname) AND (au_lname = @.Original_au_lname) AND (city = @.Original_city OR @.Ori" & _

"ginal_city IS NULL AND city IS NULL) AND (contract = @.Original_contract) AND (ph" & _

"one = @.Original_phone) AND (state = @.Original_state OR @.Original_state IS NULL A" & _

"ND state IS NULL) AND (zip = @.Original_zip OR @.Original_zip IS NULL AND zip IS N" & _

"ULL)"

Me.SqlDeleteCommand1.Connection =Me.SqlConnection1

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_id", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_address", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "address", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_fname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_lname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_city", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "city", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_contract", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "contract", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_phone", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "phone", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_state", System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "state", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_zip", System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "zip", System.Data.DataRowVersion.Original,Nothing))

'

'SqlConnection1

'

Me.SqlConnection1.ConnectionString = "data source=NDAVENPORT2;initial catalog=pubs;persist security info=False;user id=" & _

"sa;workstation id=NDAVENPORT2;packet size=4096"

'

'SqlDataAdapter1

'

Me.SqlDataAdapter1.DeleteCommand =Me.SqlDeleteCommand1

Me.SqlDataAdapter1.InsertCommand =Me.SqlInsertCommand1

Me.SqlDataAdapter1.SelectCommand =Me.SqlSelectCommand1

Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "authors",New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("au_id", "au_id"),New System.Data.Common.DataColumnMapping("au_lname", "au_lname"),New System.Data.Common.DataColumnMapping("au_fname", "au_fname"),New System.Data.Common.DataColumnMapping("phone", "phone"),New System.Data.Common.DataColumnMapping("address", "address"),New System.Data.Common.DataColumnMapping("city", "city"),New System.Data.Common.DataColumnMapping("state", "state"),New System.Data.Common.DataColumnMapping("zip", "zip"),New System.Data.Common.DataColumnMapping("contract", "contract")})})

Me.SqlDataAdapter1.UpdateCommand =Me.SqlUpdateCommand1

'

'DataSet1

'

Me.DataSet1.DataSetName = "NewDataSet"

Me.DataSet1.Locale =New System.Globalization.CultureInfo("en-US")

CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).EndInit()

EndSub

PrivateSub Page_Init(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Init

'CODEGEN: This method call is required by the Web Form Designer

'Do not modify it using the code editor.

InitializeComponent()

EndSub

#EndRegion

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

SqlDataAdapter1.Fill(DataSet1)

DataList1.DataSource = DataSet1

DataList1.DataBind()

EndSub

EndClass

Any corrections, thoughts, comments, ideas, or criticism is welcome. Thanks.

Try sorting it usingdesc andtop 1 inselect statement.|||

Simple...great! Thanks for redirection.

Found that I could not order descending o_id field in my query. The syntax was fine, but it would not return the rows in descending order. I simply changed it wihin SQL Server itself (design) and returned top 1 and it's very nice now. Thanks!

Displaying Data in Datagrid from a Normalized Set of Tables

Ok, I'm fairly new to .NET and even newer to the whole database concept. But, don't run away yet, I'm no idiot and I shouldn't have too hard of a time understanding your responses if you're kind of enough to give them. That being said, here's my dilemma:

I'm trying to make a database of all the movies I own, the actors in them and the genre (s) they belong to. I have a set of tables that are in the 2NF (I think). I have a movies table, an actors table, a genres table, and two tables called movies_actors and movies_genres with primary-foreign key relationships to pull it all together (e.g. movie_id 1 has two entries in movies_genres, one for Action and one for Drama).

My problem arises that when execute my monster query to pull ALL the data on one movie, I get a row returned for every combination of Genres and Actors in a movie. Example:

movie_id movie_title comments actor_first actor_last genre_name

1 Casino blah blah Robert DeNiro Action

1 Casino blah blah Robert DeNiro Drama

1 Casino blah blah Joe Pesci Action

1 Casino blah blah Joe Pesci Drama

And here's the query that produced that:

1SELECT movies.movie_title, movies.comments, actors.actor_first,2actors.actor_last, genres.genre_name3FROM moviesINNERJOIN movies_actorsON movies.movie_id = movies_actors.movie_id4INNERJOIN actorsON movies_actors.actor_id = actors.actor_id5INNERJOIN movies_genresON movies_genres.movie_id = movies.movie_id6INNERJOIN genresON movies_genres.genre_id = genres.genre_id

So, I want to put all the actors for one movie into the same cell in the datagrid (same with the genres) and still keep it sortable by actor or genre. Is this possible with the .NET 2.0 datagrid? Do I have some fundamental misunderstanding of how my tables should be structured? Am I just really far off and acting like a n00b?

Can you provide some sample data from each of the tables (preferably with INSERT scripts) and expected output so it makes it easier for us to understand what you want and what you are doing to get what you want.

|||

Hi,

Your design and query looks ok, and query works as it supposed to, but I think your goal is a little bit different from what you are getting form your query.

To make it work as you want you will need to query only movie table first retrieving titles, than your data grid should have two template fields for actors and for genres which could contain a nested databound controls inside like a repeater or dataview, and those controls should have their own datasources that would rely on separate queries. For example:

first (master) query would look like this: select movie_id, movie_title, comments from movies.

the query for list of actors: select actor_first, actor_last from actors join movies_actors on movies_actors.actor_id = actors.actor_id where movies_actors.movie_id = @.movieId (this is a parameter for binding your nested data control that I mentioned about before)

and you would have similar query for genres.

|||

Ok, here's what a few basic INSERT's would look like on each of the tables:

movies table:

INSERT INTO movies (movie_id, movie_title,year, comments)
VALUES (1,'Casino', 1995,'Pretty cool movie')

actors table:

INSERT INTO actors (actor_id, actor_first, actor_last)--I think I might actually combine actor_first and actor_last
VALUES (1,'Robert','DeNiro'), (2, 'Joe' , 'Pesci')

movies_actors

INSERT INTO movies_actors (movie_id, actor_id)
VALUES (1, 1), (1,2)
genres
INSERT INTO genres (genre_id, genre_name)
VALUES (1,'Action'), (2,'Drama')
movies_genres
INSERT INTO movies_genres (movie_id, genre_id)
VALUES (1, 1), (1,2)
 So, that's just a little, but hopefully that's enough that you see how the tables are structured? If not, let me know and I can certainly elaborate further.
I'd like the output of the table to look something like this:
movie_idmovie_titleyearcommentsactorsgenres
1 Casino1995Cool movieRobert DeNiro,Action, Drama
 Joe Pesci
And, preferably, I'd still like to be able to sort by genre or actor, so if you sorted by Action this movie would still come up (i.e. you wouldn't need to sort by movies that are action AND drama).I guess if I wasn't such a stubborn person I'd have just said to hell with the normalized tables and put this all in one table (or I would have just bought software that does the same thing).
But, I know my table structure is supposed to be the way to do it ... like if I have to update the spelling of an actor's name this will cause the least trouble. (edit: yuck. Nuked the styling in the last part here somehow. Sorry.)
 
 

|||

--Prepare the tables and insert sample dataCreate table movies (movie_idint, movie_titlevarchar(100), [year]int, commentsvarchar(100))INSERT INTO movies (movie_id, movie_title, [year], comments)VALUES (1,'Casino', 1995,'Pretty cool movie') goCreate table actors (actor_idint, actor_firstvarchar(100), actor_lastvarchar(100))INSERT INTO actors (actor_id, actor_first, actor_last)Select 1,'Robert','DeNiro'unionallselect 2,'Joe' ,'Pesci' goCreate table movies_actors (movie_idint, actor_idint)INSERT INTO movies_actors (movie_id, actor_id)Select 1, 1unionallselect 1,2 gocreate table movies_genres (movie_idint, genre_idint )INSERT INTO movies_genres (movie_id, genre_id)Select 1, 1unionallselect 1,2create table genres (genre_idint, genre_namevarchar(100))INSERT INTO genres (genre_id, genre_name)Select 1,'Action'unionallSelect 2,'Drama'gocreate table movies_genres (movie_idint, genre_idint )INSERT INTO movies_genres (movie_id, genre_id)Select 1, 1unionallselect 1,2create table genres (genre_idint, genre_namevarchar(100))INSERT INTO genres (genre_id, genre_name)Select 1,'Action'unionallSelect 2,'Drama'Go--Create the required functionsCreate function dbo.fnGetActors( @.Movieidint)ReturnsVarchar(500)AsBeginDeclare @.Actorsvarchar(500)Set @.Actors =''Select @.Actors = @.Actors +', ' + (A.actor_first +' ' + A.actor_last )From movies_actors MAJOIN Actors Aon MA.actor_id = A.actor_idWHERE MA.movie_id = @.MovieidReturnRIGHT(@.Actors,LEN(@.Actors) - 1 )EndGoCreate function dbo.fnGetGenres( @.Movieidint)ReturnsVarchar(500)AsBeginDeclare @.Genresvarchar(500)Set @.genres =''Select @.genres = @.genres +', ' + genre_nameFrom movies_genres MGJOIN genres GON MG.genre_id = G.genre_idWHERE MG.movie_id = @.MovieidReturnRIGHT(@.genres,LEN(@.genres) - 1 )End--Write the queryselect M.* , Actor = dbo.fnGetActors(M.movie_id ) , Genres = dbo.fnGetGenres(M.movie_id)from movies M

|||

First off, thanks a ton for the responses guys!

Robert, I understand what you're saying. I guess I just didn't know I could make a nested control with its own datasource. Is it possible you have a link to an example of something like this? I've tried a couple searches but I think I have my terminology a little mixed up ...

NDinakar, IthinkI understand your code, but what would that output look like? Would it really put both matching actors within the same cell?

|||

Hi,

Dinakar's idea is to put everything together on the database layer and it would perform much better since you would get what you want with only one database connection.

Nesting data controls gives you more flexibility in terms of presentation and data manipulation on the web form (for example actor names could be presented as links that would point to a page showing their biography etc.), however since each control has its own datasource the select statements for actors and genres would be executed for each movie row in the gridview.

I do not have any example of nested controls, but if you send me an sql script for creating your tables I could produce a simple example - it's very easy.

|||

newmanium:

NDinakar, IthinkI understand your code, but what would that output look like? Would it really put both matching actors within the same cell?

Well, why dont you give it a try. Promise, the script will not kill your server. I spent 20 mins creating the scripts. It will take you a fraction of that to cut/paste the script and check it out.

|||

I actually did envision having more control over my presentation on the web form, so perhaps the nested control would work better for me. I was hoping to have a hyperlink on every actor's name so I could click on it and execute a new query for just the movies containing that actor.

But yeah, if you could give me a simple example that would tremendous. Here's what my tables look like:

CREATETABLE`actors`(

`actor_id`INT(5)NOTNULLAUTO_INCREMENTPRIMARYKEY,
`actor_name`VARCHAR(50)NOTNULL

)ENGINE=innodb;

CREATE TABLE ' movies' (
'movie_id' INT (5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
'movie_name' VARCHAR (50) NOT NULL,
'comments' TEXT NOT NULL,
'year' INT (4) NOT NULL
) ENGINE = innnodb;

CREATE TABLE 'movies_actors' (
'movie_id' INT (5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
'actor_id' INT (5) NOT NULL
) ENGINE = innodb;

CREATE TABLE 'genres' (
'genre_id' INT (5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
'genre_name' VARCHAR (50) NOT NULL
) ENGINE = innodb;

CREATE TABLE 'movies_genres' (
'genre_id' INT (5) NOT NULL,
'movie_id' INT (5) NOT NULL
) ENGINE = innodb;

P.S. I typed that by hand just now without a client, so I apologize if it has an error or two.

|||

And Dinakar, I will try out your script :) I'm at work right now so I can't try it out very well, but I very much appreciate your time.

Display data in Reports based on Active Directory Group Membership...

Hello,

I am fairly new to SQL 2005 and Reporting Services.

We are trying to create a report that will display sales data based on group membership from Active Directory.

For example, if USER1 logs in and looks at a Year to Date Sales report, it will only show data that pertains to his group. If USER2 logs in and accesses the same report, it will display different Year to Date information because he is in a different group.

Background Information: We are running SQL 2005 Enterprise Edition Service Pack 2 with Analysis and Reporting Services. We are delivering the reports through a Sharepoint site.

Please let me know if anyone has a good way to tackle this.

Thanks,

Justin

Well, I'm not sure that this is the answer that you want to hear, but I would say that you would need to incorporate a "group" attribute in your SQL tables. How you implement that would be up to you, but I will tell you that bad database design can be quite chaotic.

If you are asking whether there is a magical function in SQL that will know how to interpret Active Directory membership without any extra design or programming, I would say definately no.

|||

What is the source of the data you want to limit by user account? SQL or Analysis Services.

If it's Analysis Services, you should be able to sort out the limitations by using Cube Roles connected to AD user groups.

See this article in SS2005 Books Online http://msdn2.microsoft.com/en-us/library/ms175408.aspx Granting User Access.

If it's from SQL Server 2005, it's not that simple. You can query Active Directory from SQL Server, so you can use some account data. Unfortunately, it's a bit of a hassle to create the right LDAP query. And you need to add some kind of group info to the database in any case, as you need to create a table containing the what data which group has access to, like GregSQL writes.

This article is on how to connect to AD from SQL Server

http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx

Kaisa Lervik

|||

I am working on this project with Justin - rather than limit access by the name of the group, we would like to pass the group name to the Analysis Services query in SSRS to determine which data is delivered to the logged in user.

Thanks

Bob