DataGridView And Two Tables - Best Approach?
Aug 26, 2009
I have a database with three tables:
stock
orders
order_details
Stock and Orders are self explanatory. Order_details is a lookup table, holding basically just the orderID, stockID and quantity for each item in the order. That's not very friendly for the user however, so what I want to show is a DataGridView with some elements from order_details, and some from stock, i.e.:
Part_Code, Item_Name, Description, Quantity, Price
I can create a database view for this easily enough, and bind it to a datagridview. It shows existing items in the order fine, but the problem is the view doesn't update when I add new elements to order_details in the dataset. It appears that it's bound to the main database, and so won't update until I save the record. That also has the problem that it's now much harder to update the database. A simple datagridview bound to order_details would make it easy for the user to enter quantities and for me to save the record, this one now needs a custom update statement.
In summary:
- I want to show data from two tables
- I want the datagridview editable (well, the quantity column anyway)
- I want the datagridview to update when I add entries in the dataset
What is the best/easiest way to do this? Is my current approach of a view in the database and custom update statements sensible? How can I get the datagridview to refresh whenever there's a change to the local dataset?
View 8 Replies
ADVERTISEMENT
Dec 7, 2010
I started out with just one table called products but I had problems with doing an update; it said sql to complex which was because the product table had nearly 100 columns in. The products table consists of header information like product quality, colour etc. Then there are 3 blocks of columns which mainly consist of a size as being the column name and a quantity as being the value. I have put these in three seperate tables to get around the SQL update problem. My problem is what is the best way to code this. The tables link to the header table using an autonumber primary key.
[Code]...
View 2 Replies
Jul 20, 2011
I want to see if anyone has used one table that has a primary key (not displayed) in the DataGridView. Then use a text box on the same form to call and group what if viewed in the DataGridView table. I want to group the displayed data of the DataGridView by the primary key of both tables.
View 1 Replies
Jun 6, 2011
SQL statements:
SQL = "SHOW TABLES;"
Try
conn.Open()
The remarked area works fine when I'm accessing a specific table. I've successfully inserted the products_model field into the combobox too. The datagridview does show the table names in the db using the SHOW TABLES sql statment, but I'm not understanding how to get this info into the combobox instead.
View 1 Replies
Mar 17, 2011
i have 2 sql database tables and one datagridview i want to fill those table in one datagridview like this
table 1
Column1 Column2 Column3 column4
1 INV total Balance
2 INV total Balance
[code]....
first table first Row then 2nd table first row ? its possible to fill table like this in datagridview?
View 10 Replies
Mar 8, 2010
I have a datagridview who's datasource is a BindingSource. The problem that I need to resolve is that the DGV has 6 columns, 5 of which come from the same database table, while the other column comes from another database table. Is there anyway of configuring the BindingSource that will make it contain all 6 columns?
The same grid in Part 1 works in the way that when a user selects a row, textboxes and comboboxes are populated with the DGV Row of data in order for the data to be modified. When the user makes any changes, this modified data needs to update the DGV row or BindingSource. How do I associate the changed values back to the BindingSource or the DGV?
View 4 Replies
Jul 22, 2009
I need to upon edit of datagridview save to sql table by button click or by cell value changed doesnt matter to me.
View 1 Replies
Mar 17, 2012
so ai have created this new TableADAPTER in my dataset, and it pulls data from two different tables namely card and transaction. I have created a datagridview to see the result, however when I try to filter the result, nothing is returned to fatagridview.
[Code]...
View 2 Replies
Jun 10, 2009
I have 3 related tables in a DataSet - Customers, Products and Orders.I am trying to create an "editable" DataGridView which, ideally, will contain columns from all the tables. I have tried creating the joins in the TableAdapter SQL, but this results in a read-only data grid.
Customers and Orders are joined in a 1:M relationship by CUSTID.
Products and Orders are joined in a 1:M relationship by PRDID.
When the user adds a new Order record they will key the ORDERID, CUSTID, PRDID and QTY.I would like the DataGridView to display the related CUSNAM and PRDDES as soon as the user has keyed the Order record.The data must be displayed on a single data grid row. I am not interested in drill down or multiple data grids.My data looks like this:
Orders: ORDERID, CUSTID, PRDID, QTY
Products: PRDID, PRDDES
Customers: CUSTID, CUSNAM
The data grid should look like this:
ORDERID, CUSTID, CUSDES, PRDID, PRDDES, QTY
My SQL would look like this:
SELECT Orders.ORDERID, Orders.CUSTID, Customers.CUSNAME, Orders.PRDID, Products.PRDDES, Orders.QTY
FROM (ORDERS
INNER JOIN CUSTOMERS ON Orders.CUSTID = Customers.CUSTID)
INNER JOIN PRODUCTS ON Orders.PRDID = Products.PRDID;
View 1 Replies
Jun 21, 2010
I am attempting to read an XML and display certain portions of the XML in a datagridview. I was able to get one table at a time, but I am having trouble figuring out how to get ALL the info I want from the XML and not just one table at a time.
I was speaking to someone and they told me that my issue is that the dataset contains datatables and it's not just one big piece of information that I can use. I looked into merge code, but I haven't managed to get anything working the way I would like.[code...]
View 2 Replies
Jan 1, 2011
I have two tables: One wich contains the columns "first_name", "last_name" and "status_id". The other table contains "status_id" and "description".
Now, I want to display "first_name", "last_name" and "description" in one DataGridView. What is the best way to do this?
What I'm doing now, is that I'm manually adding a datacolumn to the first table. Then I'm looping through each row and filling the new column manually. Now, that doesn't seem very smart and efficient, but I haven't found any better solution.
View 3 Replies
Oct 21, 2011
I have a Dataset with 2 different tables. What I have to do is to display those tables in a DataGridView.What I have done with a reference is given below.
[Code]...
View 2 Replies
Mar 3, 2010
this is for VS 2008 w/ an Access database.My database has 3 simple tables, Customer, Services and Workorder.
-Customer table = Customer ID (Customer Name, Address)
-Services table = Service ID(Service type, Cost,)
-Workorder table = Workorder ID(Service ID FK, Customer ID FK, Cost FK, Description, Date)
How I have my database set up is the Workorder table is like a linking table linking all three tables together. This is what I need out of my Windows Form.
-List view 1 contains customers names. Selecting it shows all of the customers previous workorders in the Datagrid view. (This works fine)
-List view 2 contains available services. Selecting it filters the datagrid view that was previously filtered by list view 1(customers). (This I cant figure out)
-In the end the Datagrid view would show all certain records for an individual. You when then be allowed to add the needed information and it would populate the database.All relationships were created to reflect the FKs in the workorder table.What function or coding should I be looking at to accomplish this task?
View 4 Replies
Jan 4, 2012
I have a DGV that is bound by an untyped Dataset. My select query consists of using 5 columns from 2 different tables. I fill a dataset with the results of the query. I assign the dataset to the DataSource of the DGV but don't assign the DataMember. The DGV doesn't populate, however, when I test the query it runs like it's supposed to
View 4 Replies
Dec 15, 2011
i created 20 tables in ms access.. and only one table is connected to vb 2008 using the DataGridView?.. how can put the remaining tables in ms access to the vb 2008?
View 1 Replies
Jun 23, 2010
Using Visual Basic 2005 So this is the problem I have run into. I need to populate a Datagrid with Data from 4 different tables of which I have gotten down to 3 DataTables in one DataSet.
The first one is called Letters and includes: LastPrintedOn & requiredpaper The second is called ImportParams and includes: ImportParamsID, BlockLetterGroup1, BlockLetterGroup2 and BlockLetterGroup3
The Last is called ClientInfo and includes: ClientGroupID, CapturedOn, ProcesingCommittedOn, ProcessingStartedOn, ProcessingCompletedOn, ImportParamsID
Now I can attack this from one of two ways (at least that is how I see it)
Select the ClientGroupID's I need (1,2,3,6) from that I will have the ImportParamsID which will then get me to the second table which has the blocked letters 1, 2 or 3 of them of which I can then check against the last table to get the date. There is one clients as of now that will end up with 3 thus being 3 seperate lines on the DGV or Table to show the different letter types.
Method 2:Bascially the reverse, look at the letter types and work backwards though I think the first method is easier. Where should I start? I'm not looking for code but the path I need to be on to get to the end.
View 5 Replies
Aug 26, 2010
I have a DGV that is bound by an untyped Dataset. My select query consists of using 5 columns from 2 different tables. I fill a dataset with the results of the query. I assign the dataset to the DataSource of the DGV but don't assign the DataMember. The DGV doesn't populate, however, when I test the query it runs like it's supposed to.
View 6 Replies
Mar 1, 2010
[code]I'm able to display Data from the tables to Datagridview but how do I update two tables from Datagridview?For some reason under Advanced Options Generate Insert, update and delete statements gets unchecked.
View 4 Replies
Nov 3, 2009
On a VB.NET 2008 form I have a DataGridView, BindingSource and TableAdapter.The BindingSource DataSource is a dataset.In the dataset I have a Fill command that joins three tables and this is displayed without a problem in the DataGridView.However, I am unable to Update the dataGridView because it has multiple tables from a single TableAdapter? Does anyone know a simple way I can update. The tables has over 200 columns and I only want to update the columns that are changed. If I use a single table I can edit data in theDataGridView and the database is updated ok.
View 2 Replies
Aug 6, 2010
How to bound this dataset to datagridview?
[Code]...
View 4 Replies
Jul 17, 2009
I've been trying to use lookup tables to display names instead of ID's in a DataGridView using a DataGridComboBoxColumn and have even copied the example shown in the Forms over Data videos produced by Microsoft but still get the following error message:System. =Argument Exception:DataGridView ComboBoxCell value is not valid..Everything was done in the designer and the problem only occurs with the DataGridView; lookups work fine using a combo box when displaying single records.Also the error message only pops up when I close the form. Prior to that all the names are displayed correctly for each record, the drop down list shows the names correctly and I can edit, add and delete rows and save all my edits with no problems. Its just when I close the form that the problem occurs.Each time I press OK in the message box the names in the combo boxes are removed, one by one.
View 5 Replies
Dec 21, 2010
I am trying to produce a form in VB2008 which shows data from two related database tables, and then allows the data in one of the tables to be updated. I would like to do this using a DataGridView, but I have no idea how I would go about this.
View 4 Replies
Jul 2, 2010
I'm going to describe this as simple as possible, and it a typical setup for a database...
I have 1 DataSet, 2 DataGridViews (DGV), and 2 BindingSources (BS).
The DataSet has 2 tables and 1 Relationship (FK).
Table 1 is a employee table
Table 2 is timeclock data.
Both tables contain a column: EmployeeId
FK is linked to EmployeeId of both tables.
DGV1 has it's datasource=BS1
BS1 has it's datasource=DataSet and it's DataMember=Table 1.
Thus DGV1 shows Table 1.
DGV2 has it's datasource=BS2
BS2 has it's datasource=BS1 and it's DataMember=FK
Thus DGV2 shows Table 2 filtered by what's selected in DGV1.
Now when I use the mouse or keyboard to select a cell in DGV1, the relationship causes DGV2 to update to only show data from Table 2 where the EmployeeId row equals that from the selection in Table 1. With me so far?
Well all that works great. But what if I need to programmatically select a row in Table 1 to cause Table 2 to update? I've tried everything I can think of, but Table 2 NEVER updates when I attempt to programmatically select a row or cell in Table 1.
View 3 Replies
Sep 12, 2010
Background: Not a developer by trade. I did however, endeavor to learn VB a few years ago and I got reasonably far but without proper motivation for the time, things were difficult to take further. Fast forward half a dozen years and while working with the sales team in my company, I noticed that they were struggling with cataloguing and tracking their calls to clients. They were all trying to use one excel spreadsheet to tabulate all the clients details and calls. Of course, this was a mess. So the idea is to build them a very simple database app that will allow multiple users to add entries to a db and we could pull reports from the data in the end to check performance and such.
Application: The application so far is a very simple access db with windows forms front end. Two tables, one holding �Client� data and the other �call� data. Idea being that we can now track multiple calls per client as opposed to just the next call as was the norm. Client and CallID are the primary keys of the table, with a clientID foreign key in tbCalls . There is one query which I tried to use to do this, which joins calls and clients so that I had everything in one table. Cool. There are four forms, basically frmCaptureClientInfo, frmSearchClients, frmScheduleCalls and frmMakeCalls. Most of these forms are working fine by just dragging the dataset fields onto the form and allowing the IDE to do the coding and then a little more code to validate that the inputs are correctothing fancy just add, delete, update is all this app does.
Issue: The last form is the killer form. Killer form, because it�s caused a huge rise in blood pressure. Anyway it sounds so simple. On the form is a datagrid that is populated with contents of above query (all client and call details), filtered by the parameter that checks if the call is complete or not. Sweet. Below I have dragged in fields from tblCalls. The �callers� need to edit three fields TimeCompleted, CallNotes and a call completed checkbox. What I need is the tblCallBindingNavigator to jump to the record that I have selected in the Datagrid view. Right now they are independent and I can�t join them. If this is at all possible?I have tried using just the query, which has the right behavior except the update command is not generated automatically. Spent Many hours researching updating queries but either was too complex for me to know it was the solution, or was for different products. If there is a way to do this then that would be awesome.I have also tried editing the CallBindingNavigator databindings settings but I am not sure what is going on there, it seems to block out the navigator controls. Here is some more info:
View 3 Replies
Jan 26, 2011
I need to get a table on my Form that has different columns from differen tables.As I call many tables by their Identity ID, I also need a way to show the information from that table.I created a query in my TableAdapter which selects information from 2 different tables. Tested it in the query builder and all was OK.I tried to put an empty DataGridView onto my Form and inserted the correct BindingSource for my Data Source.I then added a query and selected the query I created in my TableAdapter.
View 4 Replies
Jan 19, 2010
I'm looking for a simple example in VB2008 code.I have the Northwind DB installed on one of our SQL servers.
I've worked with the Datagridview on serveral occasions but never used its update,delete or add functions. I've Googled around the web, but could not find a simple example.
Whats the best way to do this and implement it using LINQ?
View 5 Replies
Jul 17, 2009
I've been trying to use lookup tables to display names instead of ID's in a DataGridView using a DataGridComboBoxColumn and have even copied the example shown in the Forms over Data videos produced by Microsoft but still get the following error message:
[Code]...
View 18 Replies
Feb 5, 2010
I have 3 tables. customer,item, pricing, each with keys customerid,itemid,priceid. pricing is related to customer and item tables by a 1 to many.pricing (priceid,customerid,itemid,price)
[Code]...
View 1 Replies
Dec 15, 2011
OK, I have this loop I want to perform which involves the serial port. Not to get too specific I'm actually sending data to an external device within a loop The loop involves a Start number, increments by a Step and ends when it gets to Stop This repeats until I click a STOP button.
View 15 Replies
Jul 25, 2012
I am using VS2008 and SQLite. I have created a database and several tables and am attempting to populate the tables with data from arrays.
The following code (example) works fine but stops after it inserts 30 to 50 records. I am running the VB code in debug mode and when it stops, I press pause to see where it is. It stops on "SQLcommand.ExecuteNonQuery()" and when I press continue (F5) it will insert another 30 to 50 records.
I do not get an error message. Is this a timeout issue? How do I keep the loop running to the end of the array?
Following is example code.
Dim
SQLconnect As New SQLite.SQLiteConnection()
Dim SQLcommand As SQLiteCommand
CODE:.....................
View 4 Replies
