I have a 4 table database and need to query at least 2 tables (the single table query works fine). The output goes in textboxes / labels on an existing form.
I'm trying something like this:
Private
Sub AddCustomer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
[Code].....
Code:
Tables:
- Company
- Department
- Employee
- CertificationA
- CertificationB
I am trying to return a recordset that gives some summary information about each company. The fields in my returned recordset should look like the following.
Company.Name, NumberOfDepartments, NumberOfEmployees, NumberOfCertificationA, NumberOfCertificationACompleted, NumberOfCertificationB, NumberOfCertificationBCompleted
There are actually about 10 different Certification tables but, with Certification A and B you should get the hang of it. When an employee starts a certifaction, a record is added to the that certification table.Below is a query I wrote that works (gets me the data I need), but I am unsure if it is the most efficient way. There will be quite a lot of data so performance is an issue.
Code:
Dim sqlNumberOfDepartments As String
sqlNumberOfDepartments = _
"(SELECT COUNT(*) " & _
"FROM DEPARTMENT " & _
[code].....
How to Query multiple tables in a dataset to form single consolidated table without using any external database. I know this can be done using 'JOIN' in SQl.
Now my condition is I have multiple tables filled with data in a dataset and each table is interrelated with a common 'column'. For example ,
Table 1
ID
Name
1
[Code].....
Here I mentioned only two tables but in my case there are many tables. I need a generic answer for this dynamic query .
I have a VB .Net linq query that I want to return values from two tables. I currently have this:
Dim query = (From c In db.Client _
Join u In db.Users _
On u.UserID Equals c.UserID _
Where (c.ClientID = 1)
Select c, u).ToList
query (System.Collections.Generic.List(Of )) returns several rows as expected each item containing a c and a u.
How do I iterate through the queries collection?
i am trying to use the sleep command in vb 2008 multiple times to constantly change back color but am told, Unable to load DLL 'Kernel 32': The specified module could not be found. (Exception from HRESULT: 0x8007007E). i tried it with another program and one sleep(1000) and it worked so is there a way to use this multiple times or any other command.ian hendry
View 7 RepliesI want to select to multiple sql tables using select query in vb.net..I have 2columns in sql table..I have tried this query.. But i'm getting error.
TextBox11.Text = Val(27)
cmd = New SqlCommand("SELECT outside,power FROM vijay21 INNER JOIN vijay22 ON vijay21.outside=vijay22.outside WHERE outside BETWEEN " & Val(TextBox11.Text) & " AND " &
[code].....
have a datagridview containing 2 tables left joined, so that:table1 LEFT JOIN table 2 ON table1.id=table2.idI get an error whenever I try to edit my datagridview."invalidOperationException was unhandled by the user codeDynamic SQL generation is not supported against multiple base tables."The error points to this line:
da.FillSchema(dt, SchemaType.Mapped)
da.Update(dt) << This line
'da = dataadapter
[code].....
I programmed in VB6 but with VB2008 I have trouble with this:I have a table called Clientes (Clients). One of the fields is called Provincia (State), but this is a numeric field wich references to a Provincias table.So, when I show the Clientes from, the Provincias field must use a combobox with the province name instead of a textbox with a number.
View 9 Repliesi 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 RepliesI want a to create a combo box that gets the selectable items from a table and when I save the form I want the selected item saved to a different table. I'm sure this is simple I'm not a database wiz and still very much a newbie.
View 4 RepliesI have a vb.net web application and when a particular function runs , i get data timeouts in the rest of the application..(ie..row not found errors or column does not belong to table but it does) The function is adding multiple rows in multiple tables in the database and is running in a for loop. It seems to be all SQL related but I am not seeing anything in the error logs in SQL or in the application Right now I am assuming it is memory related where to start note..the for loop will be replaced with a bulk insert but right now I jest need to resolve the issue of the timeouts
View 1 RepliesI have written a number of programs that query the Event viewer of a windows server box, but have never been able to figure out how to do it from the top of the list. The default appears to be from the bottom up. Normally that would be fine, but I am going to soon be writing an application that runs as a service and polls the Event Log for updates constantly. Where the problem is sitting at right now is out log files are huge and necessarily so. What I want to do is query the logs from the top down and break out once I get to the log time that my previous polling had reached. Is there a way to read from the logs from the top down?I had recently kicked around the idea about passing it into a Collection and scanning it from there, but haven't researched that process too much yet. Not sure if you can control how you scan a collection.
View 2 RepliesI have a small project i am doing for work to compare our website catalogue to our actual inventory in our financial software. To do this, i have exported the online catalogue as a delimited text file using "^" as the delimiter. I then want to go into our Accpac data a find out if all the items are still active, the current price and the stock on hand.
[Code]...
I have an SQL table(for example, it have 1 column "kod" with 3 rows:1,2,3 ). To operate it, I used class LINQ to SQL and I create tmp dataset with one table, with one column "col" with values - 2,3,4. To operate with this tmp table i use IEnumerable(T).I want to find only new values in tmp table and write its in SQL table. To find this values, i want to use LINQ query. Queris to each table are worked well. But query for 2 tables don't work.
Imports System.Linq
Imports System.Data.Linq
Public Class Form1
[code]....
Whilst writing a routine in Access I founf it rather fiddly for user...was thinking it would be much better for them to click a button in VB.net and that would do all the hard work.
So is it possible to do these task against a Acecss 2007 database
-Delete table
-Compact database (to clear out table)
-then run query to remake table
is this doable?
I am having difficulty with a SQL query in my project.I have 3 tables.
T1:
- Date
- ID
- Location_ID
[code]....
From this I need the T1.Date and T3.Result based on Location in T2.So my I need a query to compare a known location, get the Location_ID. Then Use that Location_ID to get the Date and ID. Use the ID to get the Result from T3. (Value is a variable with the known Location)
This is what I have tried:
"SELECT T1.Date,T3.Result _
FROM T1,T2,T3 _
WHERE T1.Location_ID = (SELECT T2.Location_ID FROM T2 WHERE T2.Location = '" & Value & "') AND T3.ID = (SELECT T1.ID FROM T1 WHERE
T1.Location_ID = (SELECT T2.Location_ID FROM T2 WHERE T2.Location = '" & Value & "'))"
I get the error 'Sub query returns more than 1 value...'
I'm using VB.Net express with an Access file, and I have the following tables:
table Formula
id | name
-------------------
1 | formula 1[code]....
So each formula have one or more components.Which query will I use if I want all the formulas with for example Component A AND Component D (Result: formula 1, formula 2)? I try something with intersect, but it seems it doesn't work in VB...
I COMPLETELY understand how redundant this is but I NEED it.Upon users creating a User account which only contains the ID, Username, Password and CustomerID I must also have a record in Customers create itself but null everything and the User.User_Customer_ID must update to the newly created Customer.Customer_ID
I have everything working but this pesky update query. The ONLY thing it needs to do is change User.User_Customer_ID to Customer.Customer_ID I managed to story the User.User_ID in an integer called UserID and Customer.Customer_ID in an integer called CustomerID I have tried a million different ways with no success
[Code]...
I have a simple "two-tables" database (SQL EXpress) and a very simple application (VB 2008 Express). The two tables are customer and order and their structures are:
customer(IDcustomer, LastName, FirstName) IDcustomer=primary key orders(IDord, Number, Description, IDcust)
IDord=primary key the tables are related (IDcustomer-->ID cust)
In VB2008 environment I create a dataset, by means of Data Source Configuration Wizard, which encompass both customer and order tables (xsd file). By opening xsd file I set the relationship in the following manner:
"Both relation and foreign key constraint"
Update Rule = Cascade
Delete Rule = Cascade
In my form I show customer table in detail-mode and orders table in datagridview-mode. Two Fill method are created in the Load event and they work correctly.In "CustomerTableAdapter" I create, with QueryBuilder, a search query (say LastName query) and I write the following code:
Private Sub frmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.CustomerTableAdapter.FillByLastName(Me.CustomerOrdersDataSet.custom er, "D") 'All Lastnames with first letter = D
[Code]...
there are three tables in dbf , table1,2,3 respectively all are contains similar structure but different data Structure : S.no Quiz Question i want the query which select the Quiz Question/records through user given Range from Textbox1.text to Textbox2.text
View 3 RepliesI want to update two tables by single query
View 2 RepliesI have 4 panels and I want them to be shown in PrintPreviewDialog and also to be able to print but suddenly,Theres a error [code]
View 2 RepliesIm trying to create a form in VB that allows the user to perform queries on a number of tables which are in access.
View 3 RepliesHow can i execute my query to insert records in tables?[code]...
View 7 RepliesI 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 3 tables t1,t2,t3..if i update t1,t2 and t3 must also be updated.i have just been tried
UPDATE t1,t2,t3 SET t1.f1='sample',t2.f1='sample',t3.f1='sample' WHERE t1.f1='test' and t2.f1='test' and t1.f1='test'
using this,update fails if t2 and t3 could not find 'test' in their fields,if i use OR all records will be..i have also tried JOINS
Update tbl_maincat AS m LEFT JOIN tbl_maincat AS m2 ON m.cTitle = m2.cTitle,
tbl_main AS c LEFT JOIN tbl_main AS c2 ON c.MainCat = c2.Maincat,
tbl_subcat AS s LEFT JOIN tbl_subcat AS s2 ON s.csCat = s2.csCat
[code]....
I have 2 tables. I'm getting info from table1 which is loaded in an rtb.
vb.net
]Dim cmd_info As New MySqlCommand("SELECT kan_info FROM kan_info WHERE kan_id= '" & kt_id & "'", conn)
reader = cmd_info.ExecuteReader
While reader.Read
beslag_kanton_info.Text = _
"RB_REF: " & vbNewLine & _
"KT_REF: " & vbNewLine & vbNewLine & _
reader.Item("kan_info").ToString
End While
This works great, but I need to add extra info after "RB_REF:" and "KT_REF:" (e.g: RB_REF: 12345 / KT_REF:6789) Those values are located in another table. How do I change the code/query so it also get the necessarily records from that table? Obviously there is a column "kan_id" with same values. I can't merge the tables btw.
I have used VB (versions 2 - 6) through many years; however, I am crash-course training myself into the VB2008 / VB2010 world kicking a screaming. I would like suggestions as to what SQL database access method should I focus more on in my learning process without making me feel that I'm a million years behind. I know I have WinForms where I can create a datasourse and drag / drop fields or tables on a form to create a grid (not really what I'm looking for).
My trouble isn't so much designing the form but in how I access the database. Theres XML, LINQ to SQL, ADO.NET, and many other methods. Not only do I need to grasp these methods quickly but I also need to know what type of projects I should create. What I mean is...I was thinking that I would design a WinForm app; however, I see that there are WPF apps and others to chose from. This is getting deep. I know it depends on the project that I'm working on. My plans are to write an app based off of either an SQL Express 2008 or SQL Server 2008 database. This first app will be standalone for now but may later become multi-user. I know I'm far behind on my learning curve coming from VB6. I have read a bit on VB2005 / 2008 / 2010. I own
both VB2008 and VB2010. I use VB2008 at work. I know ADO.NET is still alive but by what I read online, it's a dying method and is only kept for backward compatibility. XML and LINQ to SQL and other methods are all pretty new to me.
how to input data in mysql, different tables with one query?is it possible to do in gridview in vb.net 2008?
View 4 RepliesHow 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