European Experts Exchange (EEE.org) :: Free Experts PHP MySql Oracle Microsoft Web SQL Delphi


Languages :: Visual Basic :: Dataset error
By: siefer1	Date: 10/01/2009 02:42:10	Points: 20	Status: Answered Quality : Excellent
Hello, I am stuck at a halt in my program. Here is my problem in detail. I am trying to update a table using the following code. Me.Validate() Me.Store_detailsBindingSource.EndEdit() Me.TableAdapterManager.UpdateAll(Me.ShopDataSet) When I compile and run my program I get this following error. TableAdapterManager contains no connection information. Set each TableAdapterManager TableAdapter property to a valid TableAdapter instance. How I get this error: When I add a data source to my project and add only 1 table this code will work perfect. It will update just fine. But When I add more then 2 tables say Shopdata_customers and Shopdata_Location to the data and use the WHERE clause to add the data together for 1 form I get the error. Yes I have been here: http://msdn.microsoft.com/en-us/library/bb384432.aspx Any help would be greatly appreciated. Thank you.
By: VGR	Date: 10/01/2009 08:38:03		Type : Comment
so, in fact, you have a dbGrid containing data from multiple tables when retrieved, and your problem is that the (probably modified) dataset should be written to the DB and you get an error ? If this is the case, as explained in the MS KB article, it's fairly normal. You usually can't update multiple tables in one go. Do you have foreign key constraints on your tables ? Or any constraints at all ? What is your DBMS ? Access/MS-SQL-Server? MySql ? ODBC? Give me some more details and I will probably be of some help, despite I don't use VB nor Access/MS-SQL-Server ;-)

By: siefer1	Date: 10/01/2009 09:32:51		Type : Comment
First of all thank you so much for such a fast reply. so, in fact, you have a dbGrid containing data from multiple tables when retrieved, and your problem is that the (probably modified) dataset should be written to the DB and you get an error ? Yes I am creating a form in Visual Studio 2008 that pulls data from different tables and when trying to update data on the tables I get that error. My first problem in the form process was trying to get everything to move at once. Ex: 2 Tables 1 contains a character name, character creation date and ID. The table name is Characters. Another table contains Character info such as Hit Points, Power, Character ID. The table name is Character_details. When I create the form and add text boxes, (character creation date, Hit Points and Power) a combo box (character name) with both table data, and I use the combo box to select a character it will only change the Character name and Character creation date not the Strength, Wisdom or Intelligence. But when I tried to use the below MySQL query to join the tables but it returned multiple records that were the same: SELECT `character_details`.`hp`, `character_details`.`power`, `characters`.`name` `characters`.`created_date` `character_details`.`char_id`, `characters`.`id` FROM `character_details` , `characters` When I did this code it joined the tables together with out duplicate info so this is the query I have been using in Visual Studio 2008: SELECT `character_details`.`hp`, `character_details`.`power`, `characters`.`name`, `characters`.`created_date`, `character_details`.`char_id`, `characters`.`id` FROM `character_details` , `characters` WHERE `character_details`.`char_id` = `characters`.`id` I just cant seem to get the 2 tables to update. What is your DBMS ? MySQL Do you have foreign key constraints on your tables As far as I know, not to my knowledge. I am still learning al the exciting facts of MySQL and I don't think I have reached this part. I hope this really helps you understand my problem thank you so much for taking the time to help me. I really appreciate it. I have been stuck on this for 3 months now.

By: VGR	Date: 10/01/2009 12:35:30		Type : Comment
ok, no problem then. You've a real RDBMS ;-)) (at least ;-) your problem lies in SQL and in design and has multiple solutions. first, it's normal that you get duplicates when trying the above SQL queries. A "normal" query like this would be a LEFT JOIN... ON() WHERE... (alternatively, play with GROUP BY) Also, do NOT backquote the column names if they don't contain invalid SQL characters (ie, spaces). They clutter your code. Now, the solutions : 1) the "write to db" code part should split the DB write in two, as you've two tables. Thus your code maintains the referential integrity itself (which I find good) 2) you ay CREATE OR REPLACE VIEW... and have what is known as an "updatable view". You'll be then able to UPDATE the view simply with the dataset's values. for all of this, go back to the mysql.com online manual HTH and RPG regards ;-) PS did I tell I am a former RPGA Regional Director ?

By: siefer1	Date: 10/01/2009 23:26:31		Type : Comment
the "write to db" code part should split the DB write in two, as you've two tables. Thus your code maintains the referential integrity itself (which I find good) Yes but I just cant figure the code out. That is where I have been stuck so long. I always get that error when I try and try. This is so tedious lol. PS did I tell I am a former RPGA Regional Director ? That's great man. For how long? I remember my first RPG was Final Fantasy 1 on NES. Long time ago :)

By: VGR	Date: 11/01/2009 10:45:25		Type : Comment
if you prefer : 1) either you set up the "write to db" part in successive pieces, each one writing to ONE table, according to the query which got the original data ; in your case, it means first writing the updated data to character_details, then a second part would write the updated data to characters. 2) or you set up in the DB an updatable view (it means the primary keys have to be part of the view definition) getting the data from the two tables, defined exactly as the "select" query you have. Then your "get original daa" part is a "select * from theview where..." and the "write to db" part is "update theview set... where..." I am in RPGs since 1981, starting with AD&D

By: VGR	Date: 12/01/2009 23:26:36		Type : Comment
the 5.0 manual states that : With regard to view updatability, the overall goal for views is that if any view is theoretically updatable, it should be updatable in practice. This includes views that have UNION in their definition. Currently, not all views that are theoretically updatable can be updated. The initial view implementation was deliberately written this way to get usable, updatable views into MySQL as quickly as possible. Many theoretically updatable views can be updated now, but limitations still exist: * Updatable views with subqueries anywhere other than in the WHERE clause. Some views that have subqueries in the SELECT list may be updatable. * You cannot use UPDATE to update more than one underlying table of a view that is defined as a join. * You cannot use DELETE to update a view that is defined as a join.

By: siefer1	Date: 13/01/2009 02:41:05		Type : Comment
So any table that I have joined in my program cannot be updated. If that is the case then I just need to figure out the code on button push that will update both tables at the same time.

By: VGR	Date: 13/01/2009 07:53:57		Type : Comment
I don't know which IDE you use, but using Delphi, for instance, when you "button push" you can see a skeleton of code filling out the source code ; then you can enrich it with the program's logic. In your case, it means taking each updated row, and split the updated columns in groups, each group becoming a separate UPDATE SQL statement. It's the normal way of doing things like this. Having an updatable view is a bonus. In your case, I think the problem comes from a layout defect : the character_details are attributes of the character and should reside in the same table. Don't you have a (1,1) relation between the two tables ? ;-) If you had only one table, you could update it with no problems... An updatable view can update only one JOINed table because of this : when correctly designed, a DB layout has the (1,1) attributes in the correct table and thus the JOINed tables are usually only references (like long textual descriptions ) for keys (codes) in the main table. Am I understandable ?

By: siefer1	Date: 13/01/2009 08:22:30		Type : Comment
Yes I am creating a form in Visual Studio 2008 that pulls data from different tables and when trying to update data on the tables I get that error. using Delphi, for instance, when you "button push" you can see a skeleton of code filling out the source code ; then you can enrich it with the program's logic This is the same in visual basic. When I edit the button code I add: Me.Validate() Me.Store_detailsBindingSource.EndEdit() Me.TableAdapterManager.UpdateAll(Me.ShopDataSet) the character_details are attributes of the character and should reside in the same table. Sadly I am not the database creator so the person that did this for whatever reasons decided to split the character table in 2: Characters and Character_deails. There are a lot more tables such as zones, quests, npc's etc I will have to work with them later and I don't think it will be fun. lol. Don't you have a (1,1) relation between the two tables ? ;-) 1,1 relation I am not sure what that means but I think you are referring to this: In character_details there is a character_id and in characters there is an id. The character_details table character_id refers to the characters table id so that it puts the info together on the rpg server. I hope this is what you meant then yes. If you had only one table, you could update it with no problems Yes if I really wish I could only use 1 table because I can update 1 table just fine without any issues. But many people besides me use this database for pulling info etc, so I cant change the tables. An updatable view can update only one Joined table because of this : when correctly designed, a DB layout has the (1,1) attributes in the correct table and thus the Joined tables are usually only references (like long textual descriptions ) for keys (codes) in the main table. This part gets a little confusing for me. What you are saying is if the table is 1,1 I can update the 2 tables then but not 3? Again thank you so much for helping me out and having patience, I really appreciate it. If you would like to take a look at my database I can send you a username and password so if you need you can get a better reference. I am in RPGs since 1981, starting with AD&D I was born in 84 so you are way ahead of me lol, but that is awesome.

By: VGR	Date: 13/01/2009 11:47:28		Type : Answer
well, in fact it's one more benefit of using VIEWs. Let's image you had used a view in the first place for all users ("the people that use those tables"). Then you could change the underlying tables (re-arrange data) withtout users noticing, as long as you rebuild the VIEW afterwards... (or if it is dynamic, and updatable ;-) I see three solutions : - if feasible, redesign the DB layout and provide VIEWs bearing the old tables' names, so that other applications may still work ;-) while YOU would have a nice unique characters table. This is possible if there is the (1,1) relation. This means that there is one, and only one, id in characters for each id_character in character_details. (it means also the two tables should have been one ; there is absolutely NO benefit in having two tables ; it's a design error, simply as that) - if you can't redesign the DB layout, then design your own table, put in place a synchrinozation mechanism between the two (a trigger ON INSERT, ON DELETE, ON UPDATE etc on the old tables). This has the advantage of enabling a progressive migration of the "other people that..." (ie, a VIEW on the new unique table replaces a formerly separated old table, like character_details) - if really you can't do the above, then you've to fix your "DB writing" code. You can't do only those three lines. You've to separate the columns pertaining to the different tables and update each table separately. check your IDE manual. Besides "UpdateAll", for instance, I'm pretty sure there should be a "UpdateTbale" or "UpdateOne" method/function/procedure ;-))

By: siefer1	Date: 13/01/2009 12:07:30		Type : Comment
Ok now it is starting to come together. I will try and test all 3 and let you know what happens.

By: OpConsole	Date: 03/04/2011 19:04:13		Type : Comment
force close

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

Contact

page generated in 324.435000 milliseconds

Why Google AdSense ads ?

New Improved Search!