VS 2008 Getting Field Values From A Table Using A Foreign Key?
Jul 13, 2011
For simplicity, I have a database with two tables, as follows:
/---------------------------------------
| Table Name: GRADES |
|---------------+---------+-------------|
| Column | Type | Comments |
|---------------|---------|-------------|
[code]....
On my form, I'm trying to display information about a grade. I'm using a DataSet with BindingSources and TableAdapters. Using the tip from here, I'm populating the selected grade's ID, HazardType_ID and Code. Unfortunately, I want to display the the HazardType, and not the foreign key index. I've accomplished this with the following
' get the data
Dim data As ProjectDataSet.GradesRow = DirectCast(GradesBindingSource.Current, DataRowView).Row
' fill in form fields
[code]....
Note, I'm using VB Express 2008 (.NET 3.5) and connecting to a MS Access database.
View 6 Replies
ADVERTISEMENT
Feb 22, 2011
I have an Access database where I want to store the contacts in a company: In this database I have a table named "users" where I keep the users which will have access to the database. In the main table, named "contacts", two of my fields "insert_user" and "contact_user" refer to the user who entered the contact information and the user who actually made the contact. The way I am thinking it, I will have to set both of the fields as foreign keys to the same table/field, namely "users"/"username".
So:
I set two foreign key constrains in two different fields ("insert_user", "contact_user"), which refer to the same table/field ("users"/"username") in an
Access database, using VB.Net OleDB (See the SQL Statement below)
ALTER TABLE contacts ADD CONSTRAINT insert_user_foreignkey FOREIGN KEY (insert_user) REFERENCES users (username)
ALTER TABLE contacts ADD CONSTRAINT contact_user_foreignkey FOREIGN KEY (contact_user) REFERENCES users (username)
I open the database with MS-Access. I see that all the tables are fine (as they should be).
However when I click on relationships at Access I get all my tables plus a table named users_1 connected to "contacts"/"contact_user" (the table users is only connected to "contacts"/"insert_user")
It is very strange that in the Table View of Access I don't see this table, but only in the Relationships View.
Questions
Is my thinking right (to set 2 foreign keys, that refer to the same table/field)? What is this table "users_1"? Will my database work or am I doing something wrong?
View 1 Replies
Jul 8, 2009
I currently have this working already for one of my combo box's, but when i try to use the same code i get this error ''There is no row at position 6.'' . The are 2 fields in the table asset_type_id, asset_type_name', im trying to dispaly the values in a combo box for the field 'asset_type_name.
the code for ' Friend Sub RetrieveCustomerInformation2()' works but the other friend sub throws the error.
Imports System.Data.SqlClient
Imports System.Data
Imports System
Imports System.Data.OleDb
[Code]...
View 3 Replies
Aug 17, 2011
I want to insert data into my tables. Table 1 (Recipes1) has a primary key (Autonumber) Table 2 (Ingredients) has a Foreign key with the same name as the primary key in Table2 ("RecipeNumber) this is a normal number field. The problem is that when i update the tables, it does not even make it past table1 , which should be ok.
[Code]...
View 6 Replies
Oct 8, 2010
I have two tables:
*Transaction Record
*Account Details
Account details table has the field "Account Number" as its primary key.
Transaction record has the field "transaction number" as its primary key and "account number" as its foreign key. This is a table created in ms access. I am creating a program in vb.net to maintain this database.
When i try to find rows using an oledb_data_adapter, an error occurs stating that there are muliple values.
i think this is due to the fact that the records in the transaction details table are being selected by the account number. Many records in the transaction details table can have the same account number.
i need a coding to help me display the most recent 10 transaction records of one account number, one after the other.
P.S: transaction record table also contains "transaction date" field.
View 1 Replies
Dec 30, 2010
Is there a simple way that I can replace a foreign key in a datatable, with a corresponding value from the table that the key is referring to, without having to create a new table or use tableadapter? Ive got a table that has a foreign key to a child table, and I need to get a column from that table to fill a dataview?
View 4 Replies
Nov 12, 2009
I have an application where I need to be able to search for the workers related to a company from a contacts list. I am able to do this by entering the company number but I am unsure how I can set the query to allow to search for the information in the other table.
[Code]...
SELECT RecordNo, CompanyID, AccntRep, CustNumber, Person, SubjectID, StatusID FROM dbo.Contact
WHERE CompanyID = @companyNo I think that I need to create a join in the SQL statement to the company table but am unsure how to go about this and I would be grateful if someone would give me a point in the right direction so that i can search for the company name instead of the companyID.
View 2 Replies
May 17, 2012
I'm trying to use a DataTable to get the schema of a SQL Server DB.But, when try to detect the ForeignKeys, the constraints collection only brings the UNIQUE constraints.
Private Sub ShowConstraints(ByVal tableName As String)
Dim table As DataTable = New DataTable(tableName)
Using connection As SqlConnection = New SqlConnection(GetConnectionString)
[code]....
How can I get the ForeignKey Constraints?
View 2 Replies
Jun 29, 2010
I've been having a hard time trying to figure out why some values, which happen to be foreign keys, are not passing between my stored procedure and my code on a button click event. I keep receiving the error, "Procedure or Function 'sp_OrderTracking_Update_UpdateItem' expects parameter '@depotRefnbr', which was not supplied. This is happening on the line in red. As far as I can see, the @depotRefnbr is being given a value. It appears that other values, which are not foreign keys are passing fine. [Code]
View 2 Replies
Mar 2, 2011
I have 2 Tables , OrderDetails and Requests In my LINQ to SQL dbml file. OrderDetailsID is a foreign key in Requests Table.
I want to write the query to get the sum of UnitCost from OrderDetails based on OrderId. And If there is a row in Requests Table for each OrderDetailsID, and the Requests.Last.RequestType="Refund" I want to reduce the total refund amount from the main sum otherwise If there is no row based on OrderDetailsID, add to sum.
Here is the way I implement that. I am looking to prevent using "For each".
iRefund = (From od1 In dc.OrderDetails _
Where od1.OrderID =1 _
Select od1.UnitCost).Sum
[Code]....
View 1 Replies
Mar 4, 2009
I have 2 Tables , OrderDetails and Requests In my LINQ to SQL dbml file.OrderDetailsID is a foreign key in Requests Table.I want to write the query to get the sum of UnitCost from OrderDetails based on OrderId.And If there is a row in Requests Table for each rderDetailsID, and the Requests.Last.RequestType="Refund" I want to reduce the total refund amount from the main sum otherwise If there is no row based on OrderDetailsID, add to sum
View 3 Replies
Jun 14, 2012
I'm currently trying to create a function to parse error messages to make them more helpful for the end user. Currently I'm working with SQLServer and VB.NET.
Right now I'm raising error 547 which looks like this:
DELETE statement conflicted with COLUMN REFERENCE
constraint Constraint Name. The conflict occurred
in database 'Database Name', table 'Table Name',
[Code].....
I'm able to pull every piece of information I need from the error message except the name of the parentTable. I've already determined that the SqlException doesn't store the statement that caused the exception, and as far as I can tell it doesn't store information about the name of the parent table, only the table that is trying to reference it.
Is there an easy way to get the name of the parentTable?
View 1 Replies
Jul 20, 2009
I am using a number of tables on my application and two of which have a foreign key constraint I have set between them. When I change the account number on the main table via a combo box all of the relevant information for that table on the form is updated accordingly. However, the child table which has a foreign key reference to the Account Number of the main table is not updated when the acocunt number is changed. I have set the update to cascade in the references but still am having issues.
View 5 Replies
Apr 24, 2009
I would like to have a pop up messagebox that asks users for confirmation before deleting a record in datagridview and if the record is a foreign key to another table, another messagebox will appear and warns users saying" before you can delete this record, make sure you remove all the related records" I have tried but I don't know how to complete it.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.StaffTableAdapter.Fill(Me.StaffDBDataSet.Staff)
BindingNavigator1.DeleteItem = Nothing
End Sub
[code]....
View 6 Replies
Mar 8, 2010
Ok i have 3 tables in a dataset
Table1 contains items for sale (referenced)
Table2 contains Stores (Parent)
Table3 Contains items in a particular store (Child)
the problem is the data in this case. Table 2 contains a StoreId field as a primary key. Table1 Contains an ItemId field. My problem is in table 3 this contains 3 fields, StoreId, Name and Quantity.
Now my Table1 contains a field called Name and one called PluralName, I need to determine which field in table one relates to the Name field in table3.
The following 2 constraints exist currently in my app
Table2 - StoreId (one) to Table3 - StoreId (many)
Table1 - Name (one) to Table3 - Name (one)
can i add another constraint :- Table1 - PluralName (one) to Table3 - Name (one) ???
I am presuming if i do i will get a constraint error as the property does not relate on a one to one level if the store has more than one item in stock and vice versa when it has only one in stock.
View 30 Replies
Mar 25, 2011
How to add values from field ( Access 2007 ) to a combobox in visual basic 2008?
View 3 Replies
Feb 24, 2010
I am building a data based application using VB 2008 an SQL Express. I need to create textboxes on my form using code, (With & End With) method. I need a simple code string that will allow the app to check if the field to wich the textbox wil be databound is Nul, If so the textbox will not be created.
View 8 Replies
Jun 12, 2011
My client wants that my table be filtered based on the values from a field regardless of its order.
Example: I want to search for a student whose surname is 'Thomas' but the keyword is not the first in the field. The 'Surname' field, instead, contains the value "Ian Thomas".
If I am going to use this code;
bindingsource.filter = "surname Like '" & textbox1.text & "'"
It will base the searching on the first characters of the field only.
View 1 Replies
Jul 11, 2009
I'm trying to select a 'Username' value from a table called 'tblUsers' where the 'UserID' is 1 and assign it to the 'Text' property of a field. The SQL command is fine, but I don't know how to assign the value to the file. I've changed the SQL command to select the value I want. I just don't know how to assign it to a field:
'Use these import statements, so you don't have to fully qualify the Oledb Object names:
Imports System.Data.OleDb
Imports System.Data
Public Class Register
Function Register()
[Code] .....
View 8 Replies
Jun 26, 2009
I want to update a date field in my table when a record is updated to reflect when the last update was made. I have this for the parameter: objEXTCommand.Parameters.AddWithValue("@entryDate", Now.Date)
However, this inserts the Date and a time of "12:00:00". I want the date and current system time. I've gone about several ways, but none seem to work. I try this: [Code]
View 11 Replies
Jul 22, 2010
I am creating a database in VB.NET for a movie rental place. I currently have three forms;
Member Information
DVD Information
Borrow DVDs
What I would like to do is when I am viewing a member's details, if I click a button 'Borrow DVD for Member', the member's ID number transfers over to the Borrow DVDs table in the Member ID which also would hold some information from the DVD Information table, but I'm sure if i can figure out how to do this firstly, I will be able to apply the same rule and work it out myself.I have been trying to use a query statement like;
INSERT INTO [Borrow DVDs].[Member ID] [IN goodstuffvideos.mdb]
SELECT [Member Information].[Member ID]
FROM [Member Information]
but that has been coming up with error codes and completely not working.
Borrow DVDs table fields are: Borrow ID, Member ID, DVD ID Number, Hiring Fee, Borrowing Limit?
DVD Information table fields are: DVD ID Number, Title, Rating, Hiring Fee, Borrowing Limit Member?
Information table fields are: Member ID, Family Name, Given Name, Address, Town/Suburb, Postcode?
The error coming up is; Error in INSERT statement. Unable to parse query text.And under that it says The query cannot be represented graphically in the Diagram and Criteria Pane.
View 1 Replies
Feb 23, 2011
I have a query that I have to run that requires me to get rows from a table based on the value of a field in another table (which I have gotten earlier). I currently have it done like this (simplified).
[Code]...
View 1 Replies
May 7, 2009
I am using OLEDB and using datareader and with the read function i am reading table to the form. But now i want the textbox values to be written to the table? I tried with the fill, but not getting.
View 1 Replies
Dec 9, 2011
There were three similar questions in StackOverFlow but none gave an answer.. If have found why this error in occurring but don't know the fix. I am using Strongly Typed Dataset for my project which is created as a dll for DAL. I have added the Sql Server Table into this dataset using the designer and has created a DataAdapter
[Code]...
View 1 Replies
Jun 18, 2009
I have to compare an item value in two tables. these two tables are on different database(same server).
I have succesfully generated sql query to return both table values.
the query runs in sql server without any problem and gives the results.
The same query i used in vb.net doesnt gives any result ...
It is resulting with no rows.
Does the connection to multiple databases wont work from vb.net.
The following classes are used to get the result ...
SqlConnection
SqlCommand
SqlDataAdapter
SqlDataAdapter is showing with no records returned. The same query returns the results in sql server query window...
View 3 Replies
Feb 7, 2010
I have a checked listbox that I've populated with a table that has three columns, Name, Address, Phone - and have done so successfully, but when i display the items in the listbox, i would like to retrieve additional values of the checked items other than the primary displayitem.[code]'we now have an array full of the Name column only, from our checked items 'i'd like to grab the other two as well (not to display in the listbox, but to use elsewhere as variables) 'and keep the checked listbox as a control in the above code, the fill_array variable is only populated with the items displayed in the checkedlistbox - my question is, can i keep the display item the same and pull the other two columns from the datasource, but only for the ones that make the checkeditems list?
View 5 Replies
Dec 26, 2010
I have a login form that consists of 2 text boxes: txtUser, txtPass. Now these are not bound to any data table & I don't what them to be bound. When a new user enters his user name & password, I want to add these on a data table that has two columns: User, Pass. Data table information: Data Source = Login.accdb, Data table = LoginTable, Connection = LoginCn. I use visual basic 2010. So how do I do this?
View 1 Replies
Jan 20, 2012
i two tables named tblaccess(nID,nRoleID,nModuleID,cAccess) andtblmodule(nModuleID,cModuleName) and i have a datagrid which is bounded to tblaccess. the problem is, I want to display cModuleName from tblmodule instead of nModuleID. please tell me how.
View 1 Replies
Sep 23, 2009
I am using a string builder to set up an html table row by row so that it can be emailed. I currently gather my information from the database using a data reader and while dataReader.read, I use stringbuilder.Append to add each cell value to the table.The issue is that after about 200 rows, the html breaks and displays a cell value above the table in the email. I have tried changing the cell padding, but this was not successful.
View 2 Replies
Apr 5, 2010
I have a form with comboboxes, if the user doesnt select a value in the box I want a null to be sent to the database. The insert works fine, i look at the record in the db and see the null. The update doesnt work. gives me a n "Update statement conflicted with foreign key constaint....."
Public Sub Update()
TableAdapterCreate().Update(ID1, ID2, CType(cbox1.SelectedValue, Integer), CType(cbox2.SelectedValue, Integer), CType(cbox3.SelectedValue, Integer), CType(cbox4.SelectedValue, Integer),
[CODE]...
As I step through the selected value of the cbox 5 is shown as nothing (which it should be because the user didnt select a value for this combobox). I assume this needs to be a null instead of nothing to get written to the database. the combox boxes need to match a primary key in another table so sending a 0 would also result in the forieign key error since the related table does not have a 0 ID field.
View 4 Replies
