Populating DataGridView With Data From Two Tables?
Oct 11, 2007
I am sorry for asking such a broad question, but I have been working on this and from what I can gather it can be done. My problem is that much of it has gone right over my head and I am getting more confused the more I read... I'm really, really confused...
Basically, I have a dataGridView that is populated with a number of fields from Table1 (ID, NameID, Status, Phone, Notes). This works fine, BUT I would like to access Table2 and have, where ID in Table2 = NameID in Table1, it load the First Name & Last Name into the dataGridView. I am able to load the information from Table 2 like so: SELECT NameFirst + ' ' + NameLast from Table2", but I can't get both Tables to work correctly.
I would like the dataGridView to be layed out like this:
ID NameID Name (NameFirst + NameLast) Status Phone Notes
I can't for the life of me understand or get this to work (Or for that matter even understand what I am trying to do...
Also, I am using Access 2007.
I would greatly appreciate some help, and possibly some explanation in laymans terms so that I might be able to understand this. I have read a lot about this, but for whatever reason it is just soooooo over my head that I can't follow it whatsoever.
Here is the code as it stands now:
//Populate the DataGridView
string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + @"DB.accdb;Jet OLEDBatabase Password=MyPassword;";
// create and open the connection
OleDbConnection conn = new OleDbConnection(conString);
OleDbCommand command = new OleDbCommand();
command = conn.CreateCommand();
// create the DataSet
DataSet ds = new DataSet();
// run the query
command.CommandText = "SELECT ID AS [#], NameID AS [Name], Status AS [Status], Phone AS [Phone], Notes AS [Notes] FROM Table1 WHERE ID = " + textBox13.Text + ";";
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter = new OleDbDataAdapter(command);
adapter.Fill(ds);
// close the connection
conn.Close();
bindingSource1.DataSource = ds.Tables[0];
dataGridView1.DataSource = bindingSource1;
// set the size of the dataGridView Columns
this.dataGridView1.Columns[0].Width = 10;
this.dataGridView1.Columns[1].Width = 100;
this.dataGridView1.Columns[2].Width = 100;
this.dataGridView1.Columns[3].Width = 100;
this.dataGridView1.Columns[4].Width = 176;
Any help and information is greatly appreciated.
Thanks Again,
View 5 Replies
ADVERTISEMENT
Jan 11, 2007
Hi guys,
Im having some issues understanding how to manipulate the data that is in my DataGridView
The View is created by calling a stored procedure from VB code and works on two tables:
people
pswds
The columns required from Table people are (personid, userid and sort).
The columns required from Table pswds are (comment and activeuser).
Table people is parent table while pswds is the child table.
The Relationship works between both tables using primary keys from both tables: which is called personid in both.
The link below shows the Relationship Diagram for the Two tables:
http://cale.csd.anglia.ac.uk/~csd03ib/images/RelationTables.bmp
The code for the Stored procedure can be found at the following Link:
http://cale.csd.anglia.ac.uk/~csd03ib/images/StoredProcedure.bmp
Now the DataGridView returned by the Stored procedure produces the following output found at this link:
http://cale.csd.anglia.ac.uk/~csd03ib/images/DataGridView.bmp
Of course there are many more results returned for this DataGridView but i have only shown a few of them. What i really need to know is:
Can this view be made to be editable?
I need to be able to update the both the Active user Check box and the Comment Columns from this view
I hope someone can help
Thx Flakky
View 8 Replies
View Related
Aug 24, 2006
In 2000, BCP seemed the way to go. DTS packages would also work. My question is, in 2005, what is the best choice? I seem to remember that BCP ignored all referential integrity constraints, and applying them afterwords was a royal pain. I'm not a BCP expert by any means. Running this at the command line means using the DOS prompt correct?
What is 2005's answer to this?
View 4 Replies
View Related
Sep 11, 2015
How do I correctly populate a fact table with the surrogate key from the dimension table?
View 4 Replies
View Related
Feb 27, 2008
hi, could anyone tell me how to display data retrieved using sql in DataGridView?
View 6 Replies
View Related
Apr 27, 2005
Hi
I am quite new to the complexities of MS SQL and have a problem, I would like to resolve. I have 2 tables with a unique identifier in both and want to populate a new table with information from both, but the second table I would like to populate just some fields that have a DOB eg
Table 1:
uniqueId
Name
Address
Table2:
uniqueId
Type
Setting
example of content for Table 2:
uniqueId Type Setting
123 DOB 03/04/74
234 TFN 12345678
567 POA Mr Smith
So the new table needs to be populated with a ll of info in table 1 and has a new field called DOB so only the clients with a DOB should populate this field, if the client in Table 1 has a TFN reference, this record should be added to the new table but no value needs to be entered eg
123 Chris Smith 1 high street 03/04/74
234 Jon brown 2 high terrace <Null>
Cheers
pommoz
View 1 Replies
View Related
Nov 28, 2007
Hi. I have a report which has several datasources which require a table to be populated before they read from it.
i.e. The first thing that needs to happen whenever the report is run, is a call to a stored procedure which populates the table the report datasources are based off of. The SP takes several minutes to complete and MUST complete before any of the datasources fetch their data.
How can this be achieved?
I can not find anything in the Visual Studio Report Designer which allows to me to instruct Datasource B to not execute before Datasource A has completed (or any other way to call a data population SP, before the data reader SP's execute).
Thanks.
View 2 Replies
View Related
Oct 19, 2007
Hi,
I have a table in Sql 2005 called
Customers
CustomerId
CustomerName
CustomerAge
CustomerRank
CustomerStCode
I have to transfer the records into 2 tables
CustomerMaster
CustomerId
CustomerStCode
CustomerDetails
CustomerId
CustomerName
CustomerAge
CustomerRank
I have to pick up a row from Customers and transfer it to CustomerMaster and CustomerDetails. CustomerId of CustomerMaster will be the CustomerId of CustomerDetails while transfer. Similarly for all other rows in Customers.
How to do this?
thanks
View 5 Replies
View Related
Apr 21, 2000
Hi,
I am new to SQL Server7. I need to populate some tables from an SQL Server7
database at the end of the day. How can I automate this process?
I also need to export these populated tables to a text file on daily basis.
I know I can use "DTS" to do this. But is there any way to make these
automated also? Or is there any third party tool to do all these?
Thanks in advance.
Mkhan
View 1 Replies
View Related
Dec 12, 2005
Hi!
I'm trying to setup a DTS that reads a flat file uses a Data Driven Query task and then selects ONLY records that does not exist in the database and then INSERT them to DB1.
This works fine but I need to add another functionality.
I need to create a record on another table(DB2) based on the freshly inserted records in DB1 using only some of the fields. How do I do it?
Is setting up a trigger possible so that everytime a record is inerted in DB1 it will automatically a populate DB2?
Flat file:
ID
Name
Phone
DB1
ID
Name
Phone
DB2
ID
PHone
Event (from 00 to 10)
NumActions (initialized to 0)
Please help.
Thanks.
$3.99/yr .COM!
http://www.greatdomains4less.com
View 2 Replies
View Related
Aug 12, 2005
I need to populate tables in my MS SQL 2000 DB with content from an excel file. I am not sure how this is done or how to format the excel file. If someone could help me with this it would be much appreciated!Thanks!
View 3 Replies
View Related
Oct 16, 2007
Hey guys, I'm an old DevShed member, but my old account isn't working for some reason, so I had to recreate..
I've recently decided to learn MS SQL, and having some trouble with creating and populating tables. Using MS SQL Express 2005.
Heres the code, I keep reading through my notes on how to do it, but I cant see what I'm doing wrong. This is my first attempt at it, so there may be more wrong that I think.
Code:
drop table Property_rental;
drop table Property_type;
drop table Property_owner;
drop table Staff;
drop table Tenant;
drop table Tenant_category;
create table Tenant_category
(TCATID SMALLINT PRIMARY KEY NOT NULL,
TTYPE NVARCHAR(15))
;
create table Property_type
(PTYPEID SMALLINT PRIMARY KEY NOT NULL,
PTYPE NVARCHAR(20) NULL)
;
create table Property_owner
(POWNERID SMALLINT PRIMARY KEY NOT NULL,
FNAME NVARCHAR(20) NULL,
SNAME NVARCHAR(20) NULL,
CONTACT NVARCHAR(15) NULL,
ADDR NVARCHAR(50) NULL)
;
create table Staff
(STAFFID SMALLINT PRIMARY KEY NOT NULL,
FNAME NVARCHAR(20),
SNAME NVARCHAR(20),
CONTACT NVARCHAR(20))
;
create table Property_rental
(ID SMALLINT PRIMARY KEY NOT NULL,
PTYPEID SMALLINT NOT NULL,
STAFFID SMALLINT NOT NULL,
POWNERID SMALLINT NOT NULL,
CONSTRAINT Prop_Type_fk FOREIGN KEY(PTYPEID) REFERENCES Property_type(PTYPEID),
CONSTRAINT Prop_Staff_fk FOREIGN KEY(STAFFID) REFERENCES Staff(STAFFID),
CONSTRAINT Prop_Owner_fk FOREIGN KEY(POWNERID) REFERENCES Property_owner(POWNERID))
;
create table Tenant
(TENANTID SMALLINT NOT NULL,
TCATID SMALLINT NOT NULL,
ID SMALLINT NOT NULL,
FNAME NVARCHAR(20),
SNAME NVARCHAR(20),
CONTACT NVARCHAR(20),
COMMENTS NVARCHAR(20),
CONSTRAINT Ten_Cat_fk FOREIGN KEY(TCATID) REFERENCES Tenant_category(TCATID),
CONSTRAINT Ten_Prop_fk FOREIGN KEY(ID) REFERENCES Property_rental(ID),
CONSTRAINT Ten_pk PRIMARY KEY (TENANTID, TCATID, ID))
;
Error messages I'm getting;
Code:
Msg 547, Level 16, State 0, Line 55
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 56
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 57
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 58
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 59
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 60
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 61
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 82
The INSERT statement conflicted with the FOREIGN KEY constraint "Prop_Staff_fk". The conflict occurred in database "master", table "dbo.Staff", column 'STAFFID'.
The statement has been terminated.
View 6 Replies
View Related
May 18, 2015
I have 14 Windows folders containing a mix of Word and PDF documents. Each folder contains up to 500,000 files and these documents are the source for a document management system.
I need to create an audit table which can take the file names and date modified for every document in each folder but I want to avoid having to do a DOS command like dir *.* > filenames.txt then importing as a text file 14 times. Is there a way of automating this in T-SQL?
Each Windows folder is named by year e.g. 2002Docs, 2003Docs, 2004Docs etc.
Documents within the folders are named like this - 20020401_doc1.doc, 20020401_doc2, 20020401_doc678.pdf etc.
View 9 Replies
View Related
Jun 10, 2014
I created a Fact Table with 3 Keys from dimension tables, like Customer Key, property key and territory key. Since I can ONLY have one Identity key on a table, what do I need to do to avoid populating NULLs on these columns..
View 3 Replies
View Related
Oct 3, 2007
Hi All,
I have a startdate (01/11/2007) and a enddate (01/11/2008). I need to add dates into a table for everyday between these dates. Can anyone help?
View 1 Replies
View Related
Jan 18, 2005
Hi
Need to populate data from SQL Server in a pdf file which is basically a government form.
Data should be fetched from the SQL server database and needs to be displayed in a pdf file.
Advice me on how to implement this.
Suggest me if there is any other idea for implementing the same.
Thanks in advance
View 1 Replies
View Related
Jul 13, 2007
I have a project that entails the following:
There are two separate SQL Server databases involved that reside on two different servers. One of the depts within our building wants to have building permit data imported from Permit Database on Server "A" to their own database on Server "B".
I dont think this will be an overly complicated process. There are only a few fields they want populated (5 or 6 tops). This will have to be ran every
weeknight via some sort of scheduled task in Windows or SQL.
I was just interested in seeing if anyone has had prior experience working on data transfer like this. I would like to know what would be the best and most efficient way to approach this.
Thanks in advance.
View 9 Replies
View Related
Oct 31, 2007
HI,
I populed my data base. But before I populate I created some reports beased on this DB. Now after I populating I can execute query and see results in Management studio or query designer. But When I change it the report to preview mode I can not see it.
Does anyone know why this is?
Thanks
View 2 Replies
View Related
Nov 16, 2004
Hi all,
I have a table where I want to populate it with data (certain fields) from another table. But I only want to populate the table if there is data in a certain field in the table I wish to get the data from.
How exactly would I do this test? Would an I have to perform an IF statement to test if the field contains a value first?
Thanks
Tryst
View 2 Replies
View Related
May 21, 2015
I have a date table(A) for every day of the year from 1990-2016 including the week number.There is another table(B) that has a number, date and week number.
I would like to join these 2 tables together, in order to populate the number from table B on every day of the week (of the week number in table B).
What makes it a little more complex is that the dates in table B not always correspond with the week number in the same row. Adding to the problem are some conditions:
- Every day of the week should have a number, and it should never be 0.
- If the 1st date of all rows in table B with the corresponding week number is not the monday of the week (but for example wednesday) it should start with this number on monday.
- Thousands of rows are in table B and for some of them the date corresponds with the week and for some of them they don't
- The rows can be grouped together using the week number.
Take the following example of table B:
B:
ID Date Number WeekNo
1 21-5-2015 25 21
2 23-5-2015 30 21
In this example the dates correspond with the weeknumber, because the 21st and the 23rd of may are week 21. By joining this with the date table (A), by using for example cross apply, I would hope to get the following result;
Date Number WeekNo
18-5 25 21
19-5 25 21
20-5 25 21
21-5 25 21
22-5 25 21
23-5 30 21
24-5 30 21
The same should work if the same example had the week number 22 in every row.
Take the following example of table B:
B:
ID Date Number WeekNo
1 21-5-2015 25 22
2 23-5-2015 30 22
In this example the dates do not correspond with the weeknumber, because the 21st and the 23rd of may are week 21 and not 22. By joining this with the date table (A), I would hope to get the following result;
Date Number WeekNo
25-5 30 22
26-5 30 22
27-5 30 22
28-5 30 22
29-5 30 22
30-5 30 22
31-5 30 22
Because the last result (ordered by date) of the week number 22 is 30, the whole week shows 30 on every day.
View 2 Replies
View Related
Nov 3, 1999
I have just started to look at SQL and have a theory question that I could apply to a test I want to run. I have some legacy data from a previous project and the database was not designed properly (in my opinion). They have ONE field to capture City and State information. All the data is formatted City, State .
Does SQL have commands that can look at data in a field, strip out info before and info after a comma and then write that to other fields?
So, I would like to normalize this to take the data in a field called CityState and parse it, trim it and then populate two new fields 1) City and 2) State.
Thanks for your help!
Scott
View 1 Replies
View Related
Mar 6, 2015
Table Name: EmployeeDetails
Columns: EMpID - Date - WorkedHours
For each day I get details of number of hours worked by each employee in this table.
Now my HR wants a report with such columns
empid - Week - Month - Qtr
So, week will have Sum of hours worked by employee in that week
Month will have Sum of hours worked by employee in that Month
Qtr will have Sum of hours worked by employee in that Qtr
View 6 Replies
View Related
Jul 19, 2007
Hi,
I have tables like the one below for my Stage and dimension tables:
Stage Table
accountid
name
address
Dimension Table
accountkey ---- surrogate key (DW key)
accountid ---- business key (transaction's primary key)
name
address
I used slowly changing dimension to detect the changes for the records inside my Dimension table. But I had a problem when a new record exists in the stage table. The accountkey is set as the primary key and it gets its value from a different table which stores the last account key that was created. I cannot load all the changes unless i have a business key. Is there a way that i can get the "last key" from a different table in the data flow area and then supply it together with the other fields in the new output branch of the slowly changing dimension?
cherriesh
thanks!
View 7 Replies
View Related
Feb 18, 2008
Hi.
i created a small database with a table to store two numbers and the sum. num1,num2 and sum column. In sql i used the computed columns specification to assign sum as num1+num2.
running the database, everything works fine. i input the numbers and the sum is auto calculated.
i try to do same thing by bring the table in a vb 2008 form using a datagrid view. when i run the application i can just enter the two values(num1 and num2) in the datagrid but the sum is not calculated despite pressing enter or tab and also the data entered is not saved.
any help
View 8 Replies
View Related
Jul 29, 2015
I have multiple web databases for storefront orders as linked servers on SQL Server 2008 R2. I need to organize the data for these orders into a structure that can be imported into my ERP application db frequently either on demand or periodically during the day. We are essentially trying to make the manual order entry process automated. My thought was to get the data into views that resemble the schema of the order table in my application db and then schedule a stored procedure with sql jobs or a load routine from within the ERP application that would insert data from the view into the order table.
View 2 Replies
View Related
Jul 20, 2005
I have a combo box where users select the customer name and can eithergo to the customer's info or open a list of the customer's orders.The RowSource for the combo box was a simple pass-through query:SELECT DISTINCT [Customer ID], [Company Name], [contact name],City,Region FROM Customers ORDER BY Customers.[Company Name];This was working fine until a couple of weeks ago. Now wheneversomeone has the form open, this statement locks the entire Customerstable.I thought a pass-through query was read-only, so how does this do atable lock?I changed the code to an unbound rowsource that asks for input of thefirst few characters first, then uses this SQL statement as therowsource:SELECT [Customer ID], [Company Name], [contact name],City, Region Fromdbo_Customers WHERE [Company Name] like '" & txtInput & "*' ORDER BY[Company Name];This helps, but if someone types only one letter, it could still bepulling a few thousand records and cause a table lock.What is the best way to populate a large combo box? I have too muchdata for the ADODB recordset to use the .AddItem methodI was trying to figure out how to use an ADODB connection, so that Ican make it read-only to eliminate the locking, but I'm striking outon my own.Any ideas would be appreciated.Roy(Using Access 2003 MDB with SQL Server 2000 back end)
View 2 Replies
View Related
May 20, 2008
Hello, I have a datagridview setup right now to display data from one table in my database. However, I am going to need it to pull certain columns from certain tables and put them all into one datagrid. The three tables are demographics, financial, and procedures. Can someone give me an example on how I'd go about doing this? I'm using vb.net.
The procedures table is a list of options that will be displayed in a combo box, depending on the option selected, other cells in the datagrid will be filled with certain data, this data is in the procedures table as well. All of this data will be alongside a customer id located in the demographics table, and will all be recorded into the financial table.
View 1 Replies
View Related
Mar 19, 2007
Hi all... can someone tell me why this does not work and how to make it work????
SELECT SQLkey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM,
PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id,
create_date
FROM ProposalDetail
INSERT INTO temp
(
SQLKey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList,
PropDCost, PropDGMDollar, PropDGM, PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus,
create_timestamp, update_timestamp,update_originator_id, create_date
)
VALUES
(
'1', '3/20/2007 8:13:10 AM', 'SomeUser', 'b1', '4', '0761046', 'APP', 'Miscellaneous Options',
'54.000', '27.00', '27.00', '50.00', '54.00', '54.00', '54.00', '1', 'Active', '3/20/2007 8:13:10 AM', '3/20/2007 8:13:10 AM', '0',
'3/20/2007 8:13:10 AM'
)
WHERE (PropDNumb = 'b1') AND (PropDRevNumb = '1')
Thanks a ton!
View 1 Replies
View Related
Feb 29, 2008
Hi,
Can anybody help me fetch "True" & "False" value from a CheckBox inside a DataGridView row.
I tried the following code:-
But the cell.Selected doesnot take the correct value.
objGrid = (System.Windows.Forms.DataGridView)controlObj[0];
foreach (DataGridViewRow row in objGrid.Rows)
{
DataGridViewCheckBoxCell cell = row.Cells[0] as DataGridViewCheckBoxCell;
if (cell.Value != cell.FalseValue)
{
if (cell.Selected == true)
{
///Some Code;
}
}
}
Kindly Help ASAP
Thanks
View 4 Replies
View Related
Oct 19, 2015
I have made a couple of queries I want to use into a Visual studio project, Now is my problem:
All my queries have different statements. I believe the best is to show the results in a Datagridview
How to view them there? I know how to do it... but I have got about 30 queries
Here is my code so far:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ComboBox1.DataSource = MyDB_DevDataSet.DataSetName.
Dim connectionString As String = "Data Source=myDBmySRV;Initial Catalog=Teknotrans_dev;Integrated Security=True"
Dim sql As String = "SELECT * FROM CompanyMain"
[Code] ....
View 2 Replies
View Related
Dec 7, 2006
Hi,
I am currently using VC++/Cli 2005. In a project, I'm using a DataGridView control to show records from a SQL Express 2005 table. Instead of updating a specific item directly within DataGridView control, I would like to open a new form and allow user to update selected record/item within that form. The reason to update this way is conditionned by the fact that I have 3 levels of detail as following:
Level 0 Level 1 Level 2
1:N 1:N
Furniture --> Component --> Component
You all understand that update form of Level1 will/must include, as Level0 do, another DataGridView control to display/detail all related items issued from next level. This explains why I can't allow user to update Level 1 directly from DataGridView control.
I've searched in MSDN and even bought a few books on subject but unfortunately I found nothing on how to do it this way. All articles on DataGridView control were only showing how to update record directly from control.
My approach, I think, would be to transmit to my level1 updating form, as a single parameter, the selected DataRow object (or a brand new one if currently adding) issued from DataGridView and let user update it's content. When User finally leaves level0 update form, then I presume that DataGridView corresponding table would be automatically updated according to DataGridView's content.
What would be the proper way to do it? I would certainly appreciate to hear you view on this.
Also, what can I do if I want to refresh DataGridView's content when coming back from update form. Is it done automatically? I would certainly be sure that it reflects the reality, not only when I update it myself but also especially when other users could concurrently update same records?
Thanks in advance,
Stéphane
View 7 Replies
View Related
Aug 14, 2015
I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
View 3 Replies
View Related
Jul 13, 2007
Please give me advise ครับ
View 1 Replies
View Related