I dont have a huge wealth of experince with SQL statements and im struggling;
[code]...
I would like to list all of the job titles(for which there could be multiples as different employees can have the same job title) in the employee table and find the maximum amount of hours for that job title as the hours could be different for different empoyees.
[code]...
I have a set of LINQ queries filtering original datatable (loaded from Excel file) based on user selections in Comboboxes. Then the result is joined with Arraylist to further filter the set. Arraylist is a simple list of strings.
Query:
If (Samples.Item(0) Is Nothing) = False And Samples.Item(0) <> "All samples" Then
queryResults = From records In queryResults Join samp In Samples _
On records("SAMPNUM") Equals samp _
[Code]....
I am using/creating a DataTable so the "enforceConstrains" property is not avilable for DataTable.Error message: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Windows Form Load Event:
Private Sub Expenses_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
expensesGV.DataSource = listExpenses()
expensesGV.Columns(0).Visible = False
End Sub
I have this query that I tried to join 2 tables together, one which holds the product name, and product numbers, and take a product number, and go to the other where to find a Art_no which is like the product number.[code]
View 2 RepliesI'm trying to use the Entity Framework to create a complex query and get stuck in the joins. After browsing some of the documentations, I cannot seem to find the correct solution, so maybe someone here can help.
I have a data structure where a family has several family members, each of which can have more than one entry in the income table (on different registration dates) and can have more than one entry in the healthconditions table.
[Code]...
I have a datagrid which is getting populated by two tables. I want to know how do i perform an update on these tables once the user tries to save any edited data. I am doing the following which i found in one of the articles online: [Code]
If objDataset.HasChanges()
Then Dim data_adapter As OleDbDataAdapter Dim command_builder As OleDbCommandBuilder fillSql = "Select [Price Info].[Product Quick Find ID] As
[Code].....
I have a set off LINQ queries filtering original datatable (loaded from Excel file) according to user selections in Comboboxes. Then the result is joined with Arraylist to further filter the set. Arraylist is a simple string list from 1 to 5. [Code]
View 3 RepliesI am using an Access database to store (filepaths to) pictures as well as comments people can leave on them. One picture can have multiple comments, so the tables look like this:
_____________ _____________
|Photos | |Comments |
|------------| |-------------|
|Id (PK) | |Id (PK) |
|PhotoPath | |PhotoId (FK) |
|Description | |Comment |
|____________| |_____________|
What I need to do now is retrieve a list of all Photos along with their Comments. So I have a class Photo with a collection of Comments:
vb.net
Public Class Photo
Public Property Id As Integer
Public Property PhotoPath As String
Public Property Description As String
Public Property Comments As List(Of Comment) End Class Public Class Comment
Public Property Id As Integer
Public Property PhotoId As Integer
Public Property Comment As String
End Class
I can retrieve the records using an INNER JOIN query (right?), as such;
SELECT Photos.*, Comments.*
FROM (Photos INNER JOIN Comments ON Photos.Id = Comments.PhotoId)
From the result of that, for each row in the result I can create a new Photo instance, set its properties and put it in a List(Of Photo) for further processing (displaying). However, how do I handle the Comments? With this query I would get a lot of duplicate Photos. I'd get a row for each comment, but most of these rows will represent the same photos... How do I handle this the best way? I have used inner join queries before, but only for a one-to-one relationship so I never had this problem before. Now when I finally need a one-to-many relationship I realize I've no idea how to handle this appropriately...
There must be some standard way of handling this? When I previously handled cases like this I didn't use an inner join, I just retrieved all Photos first, then iterate through them and run another query to retrieve the Comments that belong to that Photo (SELECT * FROM Comments WHERE PhotoId = ?). That works fine, but when the tables are getting larger it is getting noticeably slower. So I want to do it in a single query, I definitely want to avoid running hundreds of queries if I can.
I've looked all around and spent way to long trying to convert this SQL statement into a Linq statement in VB. I'm sure it would be a good example for others out there - the statement is trying to pull products that have a many-to-many relationship with product categories, and the categories have a hierarchy of parents/children.
Here is the query I am trying to convert:
SELECT P.ProductID, P.ProductName, P.ProductSlug, P.PartNumber
FROM Products AS P
INNER JOIN Products_Categories AS PC ON PC.ProductID = P.ProductID
[code]....
I can get up to the point where I am trying to say "WHERE ... (P_Cats.Parent = 9)" but can't figure that part out.
Given a table like
ID | Name | City
1 | X | Y
2 | Z | Y
[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 .
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'm working on an ASP.NET 4.0 site, which I inherited ownership of. It has a number of existing LINQ datasources pointing to individual tables. For example, to the Patient Encounter Summary table in SQL Server. The problem is in displaying the data there. It's a normalized database, so that table contains (for example) the provider ID, rather than the provider name.
It's simple enough to join the Patient Counter Summary to the Providers table (in SQL)... but how does one do that in ASP.NET? I'm not sure of the correct nomenclature, but the 'mid layer' is VB.
[Code]...
i have a dataset that filled by this query :
SELECT Invoices.ID, Invoices.invoicenu, Invoices.pdate, InvoiceList.icaption, Invoices.icaptioncode, Invoices.ccode, Invoices.postedbill, Invoices.billnumber, SUM(InvoicesDetaile.price)
AS Tprice
FROM InvoiceList INNER JOIN
[code]....
a datagridview is showing dataset data and user check checkedcolumn of datagridview (postedbill filed) and after user mark a few rows , i want update (invoice table) in database .
Using VB 2008 EE
Here is a string called "TextCollect"
CODE:
This line of code splits it into an array so each line is accessible individualy
Code:
This line is supposed to turn it back into a single string
Code:
However it gives the error:
Overload resolution failed because no accessible 'Join' can be called without a narrowing conversion
What to combine three tables with the left join sql. keep getting the syntax error in Join operation.
SELECT SubmittedTeam
FROM (NETT
LEFT JOIN NETT ON NETT.SubmittedTeam = Team.Team)
LEFT JOIN tempDate ON NETT.Date = tempDate.date
WHERE tempDate.date is NULL AND Team.Team is NULL;
When Creating a query using the sear Criteria Builder . I keep getting the error : "The schema returned by the new query differs from the base query" what does that mean and how do i avoid this problem in future
View 2 RepliesI have two queries, my first query is the sum of 'payments' group by 'staffer_no' and my second query is sum of 'salary_adjustment' also group by 'staffer_no'. Now I am trying to join the staffer_no from both queries so I can have a query with columns staffer_no, their total payment, their total salary adjusment but this error appeared.
View 3 RepliesIwant to join two tables,'Employee' and 'Dispatch'. Dispatch has column 'DispatcherID' and 'TechnicianID' which are both foregn keys to EmployeeID in Employee table.I want to join these two tables using EmployeeID so that i can obtain the matching name to each id.but it only works when i make a single join to either DispatcherID or TechnicianID and not for both.
[code]
select Employee.firstname+' '+Employee,secondname as Technician,Employee.firstname+' '+Employee.secondname as Dispatcher from Dispatch inner join Employee on Dispatch.TechnicianID=Employee.EmployeeID inner join Employee on
[CODE]...
The database:
"ID (Primary key)" | "Title"
0 | "title1"
[code].....
OK, before adding values to database, we should check if a row exists with this values :)TO do this, creating a Stored Procedure is a best way to deal with the database fastly.So... The problem now is, at the runtime, Miss OleDB throw this error:Microsoft Jet database engine cannot find the input table or query 'IF
i should say hi experts :D . Help me with this pretty code :)
The database:
"ID (Primary key)" | "Title"
0 | "title1"
[code].....
OK, before adding values to database, we should check if a row exists with this values :)TO do this, creating a Stored Procedure is a best way to deal with the database fastly.So... The problem now is, at the runtime, Miss OleDB throw this error:Microsoft Jet database engine cannot find the input table or query 'IF
I have 7 company Divisions that sit in an access database.I then use this list to populate two comboboxes in my windows form.However I get a syntax error in my SQL Query if the Division name is more than one word.For example in my access database I have two columns one is a status column and the other is a division column. If for example the Division is called 'Corporate' the status column is called 'Status Corporate' and the division column is called 'Corporate'.The SQL query works fine in this scenario.However if I have a Division that is more than one word, lets say 'Operations Division' I get an error in my SQL Query that says -
Quote: Syntax error (missing operator) in query expression 'Operations Division'.Here is my code
sqlNewTenderDetails = "SELECT " & Me.DivisionBox.SelectedValue & " From BidRefCodes Where " & "[Status " & Me.DivisionBox.SelectedValue & "='Active'"
daNewTenderDetails = New OleDb.OleDbDataAdapter(sqlNewTenderDetails, NewTenderDetailsTableCon)[code]....
I've recently been trying to figure out a way to get parameterized queries to work and i think i'm almost there but seem to be getting an error when executing my query
Here's the code
[code]...
I'm trying to update data to a Access data base using the following [code]...
View 3 RepliesThis is not a homework of mine and i had never tried ms access database with vb so i just gave it a shot. so this is my first time and i am just trying not asking anyone to complete my homework. I have been searching for this a lot on internet but couldn't find a solution to this so i decided to ask this question here.
i have this sql code. what i want to do is that create a basic login form where users puts it's username in the UsernameTextbox and Password in the PasswordTextbox. If the the information match then a message box appears saying that the user is valid and if the information does not exist, a mesage box appears saying the users is not valid. now everytime i execute(run) the code, i come up with this error: Syntax error (missing operator) in query expression ''UserName' = 'Admin' 'PassWord' = 'testing123''.
The field names(UserName, PassWord) are also correct they are same as in the database. the information put in the username and password textbox are correct and they match the information on the database. But i dont know why this error comes up.
My code module is the following:
Imports System.Data.OleDb
Public Class LoginForm1
' Cancel button
Private Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel.Click
Me.Close()
End Sub
[CODE]...
I am trying to write a LINQ query with a Join in it, using VB on .NET 4.0.
I get the red squiggle under my line of code and when I mouse over it, it shows:
"Cannot not transform LINQ"
Note the double-negative.
Grammatically it would mean there is NO error, but I don't know why there should even be an error message then. This "error" also kills auto-complete on the line in question, as if there actually IS an error.[code]...
i use a database in access and I use a query for adaptor and dataset.
The query is next:
Select * from table1, table2 where table1.idsomething=table2.id and table2.name= 'Name1' AND datebirth>=#07.25.1988# AND datebirth<=#07.31.1988# order by datebirth asc
The error is: Syntax error in date in query expression
In the database the format date is dd-mm-yy.Why I have this problem? What is wrong at the query?
I am using visual studio 2010 to create a form where you can edit,delete, or insert data from an accessdatabase. When I try to run the form in a web browser I get the error "Query input must contain at least one table or query." I believe that the problem lies with my code/syntax but I am unable to determine the cause of the problem.
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/database.accdb" SelectCommand="SELECT * FROM [database]"
DeleteCommand="DELETE FROM [database] WHERE ([EmployeeID] = @EmployeeID)"
[Code].....
I am having a problem with this code. I have used this code before on other apps and it works, but I don't have a clue as to what is going on here.
This is the whole event. I get a runtime error msg thats says "Syntax error(Missing Operator) in query expression"
I think this is Legacy SQL coding but I don't know how to make it better.
Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
Dim Question As Integer
[Code].....
[code]getting an error in sql query regarding its update statement and table contents are of carHeader: cartype varchar ,id int,imagefile varchar,capacity int
View 1 Replies