I am having 3 tables in my DB Challans, Challan_Details & Customers. They are as follows
Table Challans:
Customer_ID (Related with Customer_ID in Customers Table) Challan_ID (Key) Challan_VchNo (Voucher No.) Challan_Date Invoice_ID
Table Challan_Details
[Code]....
I have two tables Employees and CafeLogs. Some employees can be cashiers and also customers at the Cafe shop.
Table structures:
Employees: EmployeeId(PK) , FirstName, LastName
CafeLogs: LogId (PK), CashierId, EmployeeId, Value, => CashierId and EmployeeId are the data from column EmployeeId of Empoyee table
Table relationship:
Employees 1:N CafeLogs (CashierId (FK))
[code]....
Right now I know how to select only LogId, Employee's name, and , Value, not with Cashier name yet.
Dim query = From log In db.CafeLogs _
Join emp In db.Employees On emp.EmployeeId Equals log.EmployeeId _
Select log.LogId, emp.FirsName, emp.LastName, log.Value
How to JOIN tables on nullable columns? I have following LINQ-query, RMA.fiCharge can be NULL:
Dim query = From charge In Services.dsERP.ERP_Charge _
Join rma In Services.dsRMA.RMA _
On charge.idCharge Equals rma.fiCharge _
Where rma.IMEI = imei
Select charge.idCharge
I get a "Conversion from type 'DBNull' to type 'Integer' is not valid" in query.ToArray():
Dim filter = _
String.Format(Services.dsERP.ERP_Charge.idChargeColumn.ColumnName & " IN({0})", String.Join(",", query.ToArray))
So I could append a WHERE RMA.fiCharge IS NOT NULL in the query. But how to do that in LINQ or is there another option?
The problem was that the DataSet does not support Nullable-Types but generates an InvalidCastException if you query any NULL-Values on an integer-column. The modified LINQ-query from dahlbyk works with little modification. The DataSet generates a boolean-property for every column with AllowDbNull=True, in this case IsfiChargeNull.
Dim query = From charge In Services.dsERP.ERP_Charge _
Join rma In (From rma In Services.dsRMA.RMA _
Where Not rma.IsfiChargeNull
Select rma)
On charge.idCharge Equals rma.fiCharge _
Where rma.IMEI = imei
Select charge.idCharge
I am working in vb express 2008.I want to combine two tables to show all the columns of two tables in one table. I tried following query:
"Select de.Description, SUM(de.Quantity) AS Quantity, SUM(de.Amount) AS Amount " & _
"FROM DailyExpenses de " & _
"WHERE de.EDate Between @START and @END " & _
[code]....
And i want to combine two tables to display the following result into a Listview through a SQL query:
Description Quantity AmountDescription Quantity Amount
Mineral Water 12 26 Coca Cola 2 25
Rice 3 215 Sprite 3 15
Beaf 4 150 Beaf 4 50
My problem is with the tickets table, I have listed the user that created the ticket, the tech who will solve the ticket and a user that over sees the ticket. All three users reference the users table. So how do I can I query the tickets table and get all three users that reference the same table storing the users?
Table1: Tickets
1) Ticketnumber
2) EnteredBy User 100
3) Issue
[code]....
What I can do now is something like this:
Select Ticketnumber, EnteredBy, Issue, UserName FROM Tickets INNER JOIN Users
ON Tickets.EnteredBy = Users.UserID
I'm getting this error 'column' argument cannot be null. Parameter name: column at the bold character instruction. Also I would like to know how to relate two tables with two key columns in each table
Dim connection As New OleDb.OleDbConnection(strTextoConn)
Dim EnPartesdataSet As DataSet = New DataSet("Enpartes")
Dim strsql As String
[Code].....
[Code]....
It is returning all the columns by id. I need only the primary key. When I add the following line it is empty: WHERE SIK.indid = 1 Why is that? Is there other ways to do the same thing and working? Since I am in the rush
I need to get the product of the 2 columns in the same row but my problem is the values of my datagridview come from database and the number for rows is not fixed.
View 2 RepliesI have three tables (Supplier, Product, PurchaseOrder) within a MS Access database. I am trying to display Product table in DataGrid1, Supplier in DataGrid2, PurchaseOrder in DataGrid3. What I am trying to do is to choose a product, the total list of suppliers for that product appears in DataGrid2. Up to here works fine. Now I want to add the third datagrid so that for product #1, supplier #1, there are four purchase orders listed - default. Then if I choose product #1, supplier #3, there are two purchase orders listed, and so on. I can not find out how to work with the DataAdapters and DataSet Relations for three joined tables.
View 5 RepliesI have three tables,
Products(ProductID, ProductName)
Orders(OrderID, OrderDate)
OrderDetails(ProductID, OrderID) Junction table between Products and Orders.
For example, there are Three ProductID in Products table A01, A02, A03
Four OrderID in Orders table
1. 01/01/2009, 2. 03/01/2009, 3. 05/01/2009, 4. 07/01/2009.
OrderDetails data as below:
A01 -- 01/01/2009
A02 -- 01/01/2009
A01 -- 03/01/2009
[Code]....
then I want to display the date that A03 is not ordered from range 02/01/2009 till 08/01/2009 which could have the result like this.
DateMissingOrder for Product A03 in range of date 02/01/2009 to 08/01/2009:
03/01/2009
07/01/2009
How can I do this in LINQ to SQL Visual Basic?
I have Dataset ds filled up with values Until now I was displaying values in GridView. Now I want that all the rows should be columns and columns should be rows.I have 2 options: Either 1 I can directly convert grid to columns and display it, or 2 I can convert the GridView to html and then write loops to convert. I was trying the 2nd option but I cant figure out how I should do that.[code]With this code I am still getting same as GridView. Please help me for converting rows to columns and vice versa.
View 1 RepliesI'm using VB.net 2005. I have working programs that I populate DataGridViews with something like the following:
[Code]...
Imagine the following scene: I have one table which have tree columns (ID, Number, Name).
A Select query result on this:
code:
Now, the user deletes the Number 3 and 4. So, now the Select query is going to be:
code:
And I want to have:
code:
How can I organize the column?
Anyway, i have a list of products in a product table. Primary key 'id', and 'Price' is a float(all variables defined at the top - cut main code out)
objConnect = New OleDbConnection
objConnect.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=|DataDirectory|Ecommerce.accdb;"
[code].....
I've the following code which successfully makes an average for all the columns from a table. What I need to do though is ignore certain columns in this equation.
Dim totalNumber as Double = 0
Dim count as Integer = 0
For x = 0 To xyz123.Tables(0).Columns.Count - 1
[Code]....
Averaging columns in a table - ignoring certain columns
View 2 RepliesI'm developing a policy administration system and I need to generate a unique policy in this formart
RP000000/HH
Where R is the Region i select from a Combobox, P is the product the client chooses and the auto generated six digit number and HH is the hour when the record was entered in the system. I want the auto generated number to be the ID in my ClientDetails table in my DB.Here is my code of what I have done so far.They want to be able to tell the region and the product by just looking at the policy number.
Private Function PolicyNumber(ByVal intRegion As Int16, ByVal intProductCover As Int16) As String
PolicyNumber = intRegion.ToString & intProductCover.ToString & "000000" & "/" & Hour(Now).ToString
End Function
[code]....
Right now i have a form in the VB where there are 2 combobox in it. user can choose from a range of products that comes together with some description and these are to be stored in a database table.. when i retrieve these data from another form in VB, i'm only able to retrieve the first product and it repeat e same product in my second textbox.
View 6 RepliesI have 2 datagrids. One to show Product information and another for Product purchase details entry. Both share the same dataset. The dataset (ds) is filled up with columns from 2 table
ProductMstrTbl : ProductID, ProductName..
ProductDetailsTbl: ProductID, PurchaseDate, Amt, BalanceQty..
DataSet(ds) : ProductID, ProductName, PurchaseDate, Amt, BalanceQty.
The problem is that: Showing from dataset or entering new records into the dataset is no problem, but am not able to save the modified dataset into DataBase. SqlCommandBuilder failed to generate the corresponding INSERT, UPDATE command as the dataset has multiple base tables.
good afternoon people, i got a problem here on my program, i need to query from a different tables in database but it gives me error, here is my code
[Code]...
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;
<vb.net 2008>
Table : MST_COMPANY / EMP_MASTER
Column : COMPANY_ID / LOCATION_ID
How do I add relations for two colums between two tables? I tried below way but it does not work.
Is there any other way?
Dim relCompanyEmp1 As DataRelation = ds.Relations.Add("relSectionEmp", ds.Tables("MST_COMPANY").Columns("COMPANY_ID"), ds.Tables("EMP_MASTER").Columns("COMPANY_ID"))
Dim relCompanyEmp2 As DataRelation = ds.Relations.Add("relSectionEmp", ds.Tables("MST_COMPANY").Columns("LOCATION_ID"), ds.Tables("EMP_MASTER").Columns("LOCATION_ID"))
[Code]...
Im making a search function for an application and they need to be able to search by any value in the DB in any of the three tables. Im wondering the best way to go about this. 2 of the 3 tables have the same columns while the last one is totally different. Below is what I kinda "thought" itd look like but no.
"SELECT * FROM cases, opencases, staff WHERE Crime ='" & txtsearch.Text.ToString & "'"
I need to query the results the user can search for anyterm and if it matches something in the columns it return it to the user. Im wondering if the best way to go about this is to maybe query all the tables and then put all the matching records in a seperate table and the dgv look at that table or how to exactly go about it.
I have two databases. One is an SQL database and the other is an Access database.
In the Access database, I have a table with three columns EMPID, FIRSTNAME and LASTNAME.
I load this in my VB program to a table called "EMPID_Table"
In the SQL database, there is a table that has a lot of data that I need to retrieve based on the Employee#, which is the same as the EMPID from the Access database. In my VB program this is loaded into a table called "WagesTable"
How can I merge these tables, or add the columns FIRSTNAME and LASTNAME to the WagesTable
to the correct Employee# in my VB program? I cannot change the SQL database at all, I can only read from it.
Access Query:
Dim EMPID_Query As String = _
"SELECT *" & "FROM [EMPID_Table]"
'This returns EMPID, FIRSTNAME and LASTNAME
'I fill this to a table called "EMPID_Table"
I am looking for a free utility or way to protect my software by requiring a Product ID (Which will be created when a customer requests a paid license) to use the application after a demo version. Like most shareware apps.
View 2 RepliesI'm trying to verify if data exists in two different tables in a single transaction. The reason for the single transaction is the database gets hit about 1-3 million times a day so adding anymore than 1 extra transaction would increase that number up to 9 million, and my poor little server needs a break :)
So I need to check if an ID exists in table X and table Y and return the results to my VB.net script so I can handle the outcome Ideally something like this would work
if exists (select id from X where id = @id)
print 'True,' else print 'False,'
if exists (select id from Y where id = @id)
print 'True' else print 'False'
Which gives me "True, True" if exists in both or "True, False" etc etc... But that only displays in SQL print and not actually returning it as an object/string or array values that I can use.
I'm open to any sort of solution of this nature that can give me two results from a single transaction and how to handle that response in vb.
I have three tables Student, TimeSheet and TimeRecord.
Talbe columns:
Student : StudentId, FirstName,
LastName
TimeSheet: TimeSheetId,StudentId, IsActive
[Code].....
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 RepliesI have multiple tables that I need to pull and display data from. I have created a new query in the TableAdapter of one table to pull data from related tables based on a parameter. In the Query Designer I can run my query and see all of the columns requested in my SELECT statement, however when I bind this table to a DataGridView and call the new query, it only returns the original columns from Table1.How do I see all of the columns I selected in my query?
View 3 RepliesRather than running numerous queries in Access I am trying to write a front end for a DB2 database. The Access database is getting to a size that it is causing Access 2007 to have reliability issues and frequent compacting is required. I have created the Dataset, the Table Adaptors, and the Connection String already, but when I put in a bit of code in to fill the Table Adaptors the remote connection I have to work is going crazy (chewed up about 180MB before I realised it was pulling data from work).
Before I started the project I copied the .accdb to my local PC HDD and I pointed the connection string to it, but it still looks like it is trying to pull data from the work network. If it was a small database I wouldnt be concerned but it is pulling major data every time I run it. I have only 2 out of the 4 Table Adaptors running and it didnt fill them even after 10 minutes.
Here is the code I was trying to run... Server name has been substituted for Server for obvious work reasons.
Because the tables are so large I was looking for a way to pull only the columns I need to from the tables, I tried adding .SOFT_ID to the first line of code but the TA spat the dummy message below...
Value of type 'System.Data.DataColumn' cannot be converted to Application_Name.DS_DB2Database.Server_SOFTDataTable'.
Code:
Me.TA_ServerSOFT.Fill(Me.DS_DB2Database.ServerSOFT.SOFT_ID)
Me.TA_ServerSOFT_FOUND.Fill(Me.DS_DB2Database.ServerSOFT_FOUND)
Me.TA_ServerHARD.Fill(Me.DS_DB2Database.ServerHARD)
Me.TA_ServerREF_SUP.Fill(Me.DS_DB2Database.ServerREF_SUP)
I would also like to pull just the data columns I need to speed up the query.
Note: In case youre wondering why I am not connecting directly to the DB2 database. I currently dont know where it is stored and while leveraging Access I am trying to put a level of separation between the app and the db for the time being, later I will try connect directly.