Hi all,
I have three tables; in fact they are two that I will present in a later step. What I want is to use the table adapter but I am getting a problem.
Tables:
1) Movie
MovieID,MovieTitle,MovieGenre,MovieTrailer,MoviePoster,...
2) Cast
CastID,CastName,CastBIO,CastDOB,CastPicture,...
3) CastMovie
MovieID,CastID
The problem is that I want to use a formview by using the table adapter; this table adapter should retrieve the movie information from the movie table and retrieve the cast name from the cast table, so that we can have the movie with the entire cast name within it.
How to do that with the available tables, by the way I am using the objectDataSource?
if anyone have another tools that we can use to insert, delete and edit other than the formview just provide me with it
RegardsYou could use SQL JOINs, probably CROSS JOIN (http://www.java2s.com/Code/SQL/Join/CROSSJOINinMySQL.htm).
thanx for your fast reply, but i already made a cross table by joining the cast and movie; the table which is called castmovie. however this table will not work.
All I can think of, in that case you would need a column common to both Tables, and related to each other. But wait, somebody could prove me wrong!
SELECT M.MovieID, M.MovieTitle, M.MovieGenre, M.MovieTrailer, M.MoviePoster, C.CastID, C.CastName, C.CastBio, C.CastDOB, C.CastPicture FROM Movie M LEFT JOIN CastMovie CM ON CM.MovieID = M.MovieID LEFT JOIN Cast C ON C.CastID = CM.CastID
Or something similar. It will get you multiple rows per movie though.
thank you alot
Wouldn't that require you to have CastMovie table in your database?? you said that you will remove it later. :confused:
i want to be able to enter more than one cast per movie as well as i want to be able to delete the movie info without the cast info, thats why i think its required to have a third table (many to many relatiobship).
regards,
Showing posts with label tableadapter. Show all posts
Showing posts with label tableadapter. Show all posts
Saturday, March 31, 2012
TableAdapter .NET 2.0 Limitations/Gotcha's ?
Do the TableAdapters have any limitations, gotcha's, or anything that
may bite ya in the rear down the road?
I'm looking for lessons learned and/or past experience. I'm starting
a new application and learning towards using the .NET 2.0 Strongly
Typed Dataset, TableAdapter, & partial classes for my DAL.
(For example, I was told performance was horrible, and from past
experience a company totally abandoned strongly typed datasets.
However, on further research it appears this was an issue with .NET
1.x but has been fixed in 2.0.)
-YinThe TableAdapters will cement you into a .NET 2.0 way of looking at things.
That is the major gotcha I can think of. You can avoid some of this by
abstracting the data access a bit. This will allow you to move to LINQ (or
something else) later without a complete rewrite.
Short answer: Don't embed everythign in your ASP.NET application and you
should be fine here.
STDs are a great way of looking at data, especially if you are going to head
to a more SOA type implementation. As they are already XML, they are easily
serialized. They are not the best for single entities, but you can translate
to business entities rather easily and even use the DataRows as a pseudo
business entity by wrapping the row.
STDs are also great for lazy loading and the TableAdapters can help
tremendously here.
Examine the tutorials on ASP.NET 2.0 on the MSDN site. There are some good
offerings on tiered development using .NET 2.0 features. Research a bit and
you can answer your own question, based on your applications.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)
****************************************
********
Think outside the box!
****************************************
********
"Yin99" <ws@.ziowave.com> wrote in message
news:1182980286.876587.230920@.k29g2000hsd.googlegroups.com...
> Do the TableAdapters have any limitations, gotcha's, or anything that
> may bite ya in the rear down the road?
> I'm looking for lessons learned and/or past experience. I'm starting
> a new application and learning towards using the .NET 2.0 Strongly
> Typed Dataset, TableAdapter, & partial classes for my DAL.
> (For example, I was told performance was horrible, and from past
> experience a company totally abandoned strongly typed datasets.
> However, on further research it appears this was an issue with .NET
> 1.x but has been fixed in 2.0.)
> -Yin
>
may bite ya in the rear down the road?
I'm looking for lessons learned and/or past experience. I'm starting
a new application and learning towards using the .NET 2.0 Strongly
Typed Dataset, TableAdapter, & partial classes for my DAL.
(For example, I was told performance was horrible, and from past
experience a company totally abandoned strongly typed datasets.
However, on further research it appears this was an issue with .NET
1.x but has been fixed in 2.0.)
-YinThe TableAdapters will cement you into a .NET 2.0 way of looking at things.
That is the major gotcha I can think of. You can avoid some of this by
abstracting the data access a bit. This will allow you to move to LINQ (or
something else) later without a complete rewrite.
Short answer: Don't embed everythign in your ASP.NET application and you
should be fine here.
STDs are a great way of looking at data, especially if you are going to head
to a more SOA type implementation. As they are already XML, they are easily
serialized. They are not the best for single entities, but you can translate
to business entities rather easily and even use the DataRows as a pseudo
business entity by wrapping the row.
STDs are also great for lazy loading and the TableAdapters can help
tremendously here.
Examine the tutorials on ASP.NET 2.0 on the MSDN site. There are some good
offerings on tiered development using .NET 2.0 features. Research a bit and
you can answer your own question, based on your applications.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)
****************************************
********
Think outside the box!
****************************************
********
"Yin99" <ws@.ziowave.com> wrote in message
news:1182980286.876587.230920@.k29g2000hsd.googlegroups.com...
> Do the TableAdapters have any limitations, gotcha's, or anything that
> may bite ya in the rear down the road?
> I'm looking for lessons learned and/or past experience. I'm starting
> a new application and learning towards using the .NET 2.0 Strongly
> Typed Dataset, TableAdapter, & partial classes for my DAL.
> (For example, I was told performance was horrible, and from past
> experience a company totally abandoned strongly typed datasets.
> However, on further research it appears this was an issue with .NET
> 1.x but has been fixed in 2.0.)
> -Yin
>
TableAdapter approach
I'm starting an application using a TableAdapter instead of direct sql
calls.
In my table adapter there's already an stored procedure that returns
columns a, b and c.
Now, I added another query, but now I need it to return columns b, c,
d, and f.
As you can see, columns does not match.
calls.
In my table adapter there's already an stored procedure that returns
columns a, b and c.
Now, I added another query, but now I need it to return columns b, c,
d, and f.
As you can see, columns does not match.
When I close the table adapter wizard for adding the query, it warns
me, or well, it informs me about this missmatch and tells me to check
it if that's the correct desire.
I thought this would throw an error, but it doesn't. I have a method
that return a different set of columns from those listed in the
TableAdapter.
Is this approach "correct" ?
If no, what's the proper way to do this ?
Thanks in advance,Any idea ?
TableAdapter Configuration Wizard
Hey
I am trying to get some practice on VS 2005 TableAdapter Configuration
Wizard. I wrote a Database project and include a couple of stored procedures
one of which was an insert procedure. The insert procedure obviously accepts
a few parameters (the values to insert).
I have a problem creating the connection in the TableAdapter Configuration
Wizard for the insert procedure. Basically I cannot map the parameters to the
database table columns where the value was to be inserted.
Can someone please point in the direction of a good tutorial that
demonstreates the creation and use of the TableAdapter Configuration Wizard.
I read something in 15seconds.com: http://www.15seconds.com/issue/050728.htm.
So if you have something other than this, It'd be deeply appreciated.
Thanks
HectorHi Hector:
Here are a couple to look at:
http://weblogs.asp.net/scottgu/arch.../15/435498.aspx
http://msdn.microsoft.com/vbasic/de...bleadapters.asp
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Sat, 4 Feb 2006 18:34:27 -0800, "hecsan07" <hecsan07@.hotmail.com>
wrote:
>Hey
>I am trying to get some practice on VS 2005 TableAdapter Configuration
>Wizard. I wrote a Database project and include a couple of stored procedures
>one of which was an insert procedure. The insert procedure obviously accepts
>a few parameters (the values to insert).
>I have a problem creating the connection in the TableAdapter Configuration
>Wizard for the insert procedure. Basically I cannot map the parameters to the
>database table columns where the value was to be inserted.
>Can someone please point in the direction of a good tutorial that
>demonstreates the creation and use of the TableAdapter Configuration Wizard.
>I read something in 15seconds.com: http://www.15seconds.com/issue/050728.htm.
>So if you have something other than this, It'd be deeply appreciated.
>Thanks
>Hector
TableAdapter Configuration Wizard
Hey
I am trying to get some practice on VS 2005 TableAdapter Configuration
Wizard. I wrote a Database project and include a couple of stored procedures
one of which was an insert procedure. The insert procedure obviously accepts
a few parameters (the values to insert).
I have a problem creating the connection in the TableAdapter Configuration
Wizard for the insert procedure. Basically I cannot map the parameters to th
e
database table columns where the value was to be inserted.
Can someone please point in the direction of a good tutorial that
demonstreates the creation and use of the TableAdapter Configuration Wizard.
I read something in 15seconds.com: [url]http://www.15seconds.com/issue/050728.htm.[/url
]
So if you have something other than this, It'd be deeply appreciated.
Thanks
HectorHi Hector:
Here are a couple to look at:
http://weblogs.asp.net/scottgu/arch.../15/435498.aspx
http://msdn.microsoft.com/vbasic/de...
eadapters.asp
Scott
http://www.OdeToCode.com/blogs/scott/
On Sat, 4 Feb 2006 18:34:27 -0800, "hecsan07" <hecsan07@.hotmail.com>
wrote:
>Hey
>I am trying to get some practice on VS 2005 TableAdapter Configuration
>Wizard. I wrote a Database project and include a couple of stored procedure
s
>one of which was an insert procedure. The insert procedure obviously accept
s
>a few parameters (the values to insert).
>I have a problem creating the connection in the TableAdapter Configuration
>Wizard for the insert procedure. Basically I cannot map the parameters to t
he
>database table columns where the value was to be inserted.
>Can someone please point in the direction of a good tutorial that
>demonstreates the creation and use of the TableAdapter Configuration Wizard
.
>I read something in 15seconds.com: [url]http://www.15seconds.com/issue/050728.htm.[/ur
l]
>So if you have something other than this, It'd be deeply appreciated.
>Thanks
>Hector
I am trying to get some practice on VS 2005 TableAdapter Configuration
Wizard. I wrote a Database project and include a couple of stored procedures
one of which was an insert procedure. The insert procedure obviously accepts
a few parameters (the values to insert).
I have a problem creating the connection in the TableAdapter Configuration
Wizard for the insert procedure. Basically I cannot map the parameters to th
e
database table columns where the value was to be inserted.
Can someone please point in the direction of a good tutorial that
demonstreates the creation and use of the TableAdapter Configuration Wizard.
I read something in 15seconds.com: [url]http://www.15seconds.com/issue/050728.htm.[/url
]
So if you have something other than this, It'd be deeply appreciated.
Thanks
HectorHi Hector:
Here are a couple to look at:
http://weblogs.asp.net/scottgu/arch.../15/435498.aspx
http://msdn.microsoft.com/vbasic/de...
eadapters.asp
Scott
http://www.OdeToCode.com/blogs/scott/
On Sat, 4 Feb 2006 18:34:27 -0800, "hecsan07" <hecsan07@.hotmail.com>
wrote:
>Hey
>I am trying to get some practice on VS 2005 TableAdapter Configuration
>Wizard. I wrote a Database project and include a couple of stored procedure
s
>one of which was an insert procedure. The insert procedure obviously accept
s
>a few parameters (the values to insert).
>I have a problem creating the connection in the TableAdapter Configuration
>Wizard for the insert procedure. Basically I cannot map the parameters to t
he
>database table columns where the value was to be inserted.
>Can someone please point in the direction of a good tutorial that
>demonstreates the creation and use of the TableAdapter Configuration Wizard
.
>I read something in 15seconds.com: [url]http://www.15seconds.com/issue/050728.htm.[/ur
l]
>So if you have something other than this, It'd be deeply appreciated.
>Thanks
>Hector
TableAdapter Insert (Primary Key)
Hi
I have just started to learn ASP.Net. However I have some experience with ASP.
Anyway I have created a asp-form which should create a new entry in the database after you have clicked the submit button.
I created a tableadapter and following simple code:tbUsersTableAdapter topAdapter=new tbUsersTableAdapter();
topAdapter.InsertUser(DateTime.Now, DateTime.Now, DateTime.Now,true,false, txtNickname.Text, 2, txtEMail.Text, txtPassword.Text,"");
Here is the SQl Command without the primary key:INSERTINTO[tbUsers] ([CDate], [MDate], [ADate], [Active], [Activated],[Nickname], [Role],
[IP])VALUES (@dotnet.itags.org.CDate, @dotnet.itags.org.MDate, @dotnet.itags.org.ADate, @dotnet.itags.org.Active, @dotnet.itags.org.Activated, @dotnet.itags.org.Nickname, @dotnet.itags.org.Role, @dotnet.itags.org.eMail, @dotnet.itags.org.Password, @dotnet.itags.org.IP)
This works fine as long as I put the ID (Primary Key) into the Insert command. But I don't know how I can find out the next avaiable primary key. I have thought it would be automatically incremented. Anyway I went to the properties of the tableadapter and there was a property which I switched on for my primary key field "AutoIncrement". However it still doesn't work.
Before I used to work with MS Access and OleDB Connection. There I just skipped the Primary Key Value and it has been created automatically. Here I get an error message that the primary key can't be null.
I hope somebody could help me with that or has an idea what I'm doing wrong.
thx
Hi
I searched and tried out a lot of things. Finally I found the problem today. I had to open the table with the Server Explorer. Actually I set my ID field to Primary Key but I forgot to set the Identity Column. After I set the Identity Column to my ID field it worked... great;-)))) Now I can just add a new record with skipping the Primary Key and it will be generated automatically.
thx all
TableAdapter Problem
I'm comparing use of DataView -ObjectDataSource -TableAdapter to
DataView -SqlDataSource using the Customers table on the NorthWind
database.
DataView -SqlDataSource using the Customers table on the NorthWind
database.
The SQL given to both wizards is:
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address,
City, Region, PostalCode, Country, Phone, Fax
FROM Customers
But the two wizards generate different UPDATE statements (Pasted below) and
I see the TableAdapter is trying to set the CustomerID Key which looks
wrong. The problem here is that the TableAdapter does not work saying that
it can't fine a generic Update statement with the correct parameters.
Why is the TableAdapter wizard generating bad Update SQL ??
ObjectDataSource:
UPDATE Customers SET CustomerID = @dotnet.itags.org.CustomerID, CompanyName =
@dotnet.itags.org.CompanyName, ContactName = @dotnet.itags.org.ContactName, ContactTitle = @dotnet.itags.org.ContactTitle,
Address = @dotnet.itags.org.Address, City = @dotnet.itags.org.City, Region = @dotnet.itags.org.Region, PostalCode =
@dotnet.itags.org.PostalCode, Country = @dotnet.itags.org.Country, Phone = @dotnet.itags.org.Phone, Fax = @dotnet.itags.org.Fax
WHERE (CustomerID = @dotnet.itags.org.Original_CustomerID)
SqlDataSource:
UPDATE [Customers] SET [CompanyName] = @dotnet.itags.org.CompanyName, [ContactName] =
@dotnet.itags.org.ContactName, [ContactTitle] = @dotnet.itags.org.ContactTitle,
[Address] = @dotnet.itags.org.Address, [City] = @dotnet.itags.org.City, [Region] = @dotnet.itags.org.Region, [PostalCode] =
@dotnet.itags.org.PostalCode, [Country] = @dotnet.itags.org.Country, [Phone] = @dotnet.itags.org.Phone, [Fax] = @dotnet.itags.org.Fax
WHERE [CustomerID] = @dotnet.itags.org.CustomerID">
--
Regards,
Gary BlakelyHello Gary,
Thanks for your posting.
Regarding on this issue, I've noticed that you've posted another duplicated
thread
"TableAdapter generates bad SQL" in the following newsgroup:
microsoft.public.dotnet.framework.adonet
Our member Kevin has replied you in that thread and will continue to work
with you there. I'd appreciate if you have a look there, and if you have
any further questions, please feel free to post there.
Thanks for your understanding.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
thanks. Working that over there
--
Regards,
Gary Blakely
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.comwrote in message
news:XYIwk0IsGHA.2004@.TK2MSFTNGXA01.phx.gbl...
Quote:
Originally Posted by
Hello Gary,
>
Thanks for your posting.
>
Regarding on this issue, I've noticed that you've posted another
duplicated
thread
>
"TableAdapter generates bad SQL" in the following newsgroup:
>
microsoft.public.dotnet.framework.adonet
>
Our member Kevin has replied you in that thread and will continue to work
with you there. I'd appreciate if you have a look there, and if you have
any further questions, please feel free to post there.
>
Thanks for your understanding.
>
Sincerely,
>
Steven Cheng
>
Microsoft MSDN Online Support Lead
>
>
This posting is provided "AS IS" with no warranties, and confers no
rights.
>
tableadapter question
What is the usage of tableadapter?Hello ken,
TableAdapters are used to create queries / command associated with a specifi
c
table. For example you could connect a TableAdapter to a customer table and
give the adapter functions like "GetCusomter(int)" "ListCustomers" "UpdateCu
stomer(string,
string, string)".
Here some info on MSDN: [url]http://www.lowendahl.net" target="_blank">http://msdn2.microsoft.com/en-us/li...w.lowendahl.net
http://www.cornerstone.se
> What is the usage of tableadapter?
>
TableAdapters are used to create queries / command associated with a specifi
c
table. For example you could connect a TableAdapter to a customer table and
give the adapter functions like "GetCusomter(int)" "ListCustomers" "UpdateCu
stomer(string,
string, string)".
Here some info on MSDN: [url]http://www.lowendahl.net" target="_blank">http://msdn2.microsoft.com/en-us/li...w.lowendahl.net
http://www.cornerstone.se
> What is the usage of tableadapter?
>
tableadapter question
What is the usage of tableadapter?Hello ken,
TableAdapters are used to create queries / command associated with a specific
table. For example you could connect a TableAdapter to a customer table and
give the adapter functions like "GetCusomter(int)" "ListCustomers" "UpdateCustomer(string,
string, string)".
Here some info on MSDN: http://msdn2.microsoft.com/en-us/library/bz9tthwx.aspx
--
Patrik Lwendahl [C# MVP]
http://www.lowendahl.net
http://www.cornerstone.se
> What is the usage of tableadapter?
TableAdapter: How do I create one which inserts values from a form into two tables?
I am creating a simple booking system. The system requires the users
to provide information regarding themselves and also the booking
detail. I want to send the data into two tables. One, the personalinfo
table and another bookinginfo. The bookinginfo, also has a column for
personalID which links it to the personalInfo table. How can I do this
with eiter an ASP button control use a table adapter to do it.
Sorry if this sounds trivial but I am really new to ASP and SQL.
RegardsYou'd need to simply write the data out to the table. Have a look at the
connection objects in .NET there is actually a lot of code that already does
what you want posted all over the web.
Regards,
Alvin Bruney
---
Shameless author plug
Excel Services for .NET is coming...
OWC Black book on Amazon and
www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley
"Fendi Baba" <effendi@.epitome.com.sg> wrote in message
news:1171175411.943113.134530@.h3g2000cwc.googlegroups.com...
>I am creating a simple booking system. The system requires the users
> to provide information regarding themselves and also the booking
> detail. I want to send the data into two tables. One, the personalinfo
> table and another bookinginfo. The bookinginfo, also has a column for
> personalID which links it to the personalInfo table. How can I do this
> with eiter an ASP button control use a table adapter to do it.
> Sorry if this sounds trivial but I am really new to ASP and SQL.
> Regards
>
to provide information regarding themselves and also the booking
detail. I want to send the data into two tables. One, the personalinfo
table and another bookinginfo. The bookinginfo, also has a column for
personalID which links it to the personalInfo table. How can I do this
with eiter an ASP button control use a table adapter to do it.
Sorry if this sounds trivial but I am really new to ASP and SQL.
RegardsYou'd need to simply write the data out to the table. Have a look at the
connection objects in .NET there is actually a lot of code that already does
what you want posted all over the web.
Regards,
Alvin Bruney
---
Shameless author plug
Excel Services for .NET is coming...
OWC Black book on Amazon and
www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley
"Fendi Baba" <effendi@.epitome.com.sg> wrote in message
news:1171175411.943113.134530@.h3g2000cwc.googlegroups.com...
>I am creating a simple booking system. The system requires the users
> to provide information regarding themselves and also the booking
> detail. I want to send the data into two tables. One, the personalinfo
> table and another bookinginfo. The bookinginfo, also has a column for
> personalID which links it to the personalInfo table. How can I do this
> with eiter an ASP button control use a table adapter to do it.
> Sorry if this sounds trivial but I am really new to ASP and SQL.
> Regards
>
TableAdapter: How do I create one which inserts values from a form into two tables?
I am creating a simple booking system. The system requires the users
to provide information regarding themselves and also the booking
detail. I want to send the data into two tables. One, the personalinfo
table and another bookinginfo. The bookinginfo, also has a column for
personalID which links it to the personalInfo table. How can I do this
with eiter an ASP button control use a table adapter to do it.
to provide information regarding themselves and also the booking
detail. I want to send the data into two tables. One, the personalinfo
table and another bookinginfo. The bookinginfo, also has a column for
personalID which links it to the personalInfo table. How can I do this
with eiter an ASP button control use a table adapter to do it.
Sorry if this sounds trivial but I am really new to ASP and SQL.
RegardsYou'd need to simply write the data out to the table. Have a look at the
connection objects in .NET there is actually a lot of code that already does
what you want posted all over the web.
--
Regards,
Alvin Bruney
----------------
Shameless author plug
Excel Services for .NET is coming...
OWC Black book on Amazon and
www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley
"Fendi Baba" <effendi@.epitome.com.sgwrote in message
news:1171175411.943113.134530@.h3g2000cwc.googlegro ups.com...
Quote:
Originally Posted by
>I am creating a simple booking system. The system requires the users
to provide information regarding themselves and also the booking
detail. I want to send the data into two tables. One, the personalinfo
table and another bookinginfo. The bookinginfo, also has a column for
personalID which links it to the personalInfo table. How can I do this
with eiter an ASP button control use a table adapter to do it.
>
Sorry if this sounds trivial but I am really new to ASP and SQL.
>
Regards
>
Subscribe to:
Posts (Atom)