Table relationship[code]...
There are some TimeSheet that have no TimeRecord, which I need to select as well. How can I select all of them to make selection like above wanted result? I'm thinking of how I can include some condtion checking in the query to see if this TimeSheet has no TimeRecord then no need to Sum(tr.BonusHour) just assign TotalHour to zero.
In this query against a datatable i'm trying to do some conditional filtering.The check against Timeband(index N) should only be done when the Index exists. (the base code only had three item fields, i've converted them to a simple list)
Dim res As DataTable =
(
From dr As DataRow In dtTimedRow.AsEnumerable()
Select dr
[code]....
The above code triggers an Exception if the count = 1. It executes the code next to imeBands.Count > 1 which it should not. What would be the correct solution for this code.In the mean time i've added a simple filter function.
imgTitle.Image = My.Resources.title
If imgTitle.Image Is My.Resources.title Then
MsgBox("Success")
Else
[code]....
I have conditional code checking in a card game Im working on and it just feels wrong.I m looking for a better way.Right now, all my functions return a card. If I get a card Im usually done and can fall out of my nested structure.If I dont get a card I have to test a condition If card is nothing.And this condition gets repeated ad-nauseum.Here is a small sample of the code.
card = FindMaxTrump(playableCards)
If Not card Is Nothing Then
'If we found a trump at all[code]....
This might be a fine way to do it, but the constant check for a null card is what has me wondering if there is a better approach I'm missing.
So I'm writing the equality operator overload (Operator =())for a custom object, and the resulting mess of If conditionals is just an eyesore. But so far, it seems like the only sane way to check the values to as to match the specific behavior of this object.
The rules are:Num1 is required, period, and both the left and right operands and must be equal for True. Else, False.Num2 is optional, but if specified, must be present for both the left and right operands, and must be equal for True. Else, False.Num3 is optional, but can only be specified if Num2 is also present. Else, False.Num3, if specified, must be present for both the left and right operands, and must be equal for True. Else, False.
[Code]..
I also know that I could cache elements of the checks into booleans and then use those to reduce the amount of text. But that seems like a waste of cycles just for code readability. So I wanted to know if the SO community had better thoughts on reorganizing it, just in case I'm being too verbose with my checks.
I created a sql query to check if the mobile number is registered or not. If its registered, it will send the new pin code to the customer and if its not, the customer will get a message that "This number is not registered or you are not authorized to this"... something like that... Sending of new code is already working. I just dont know how to make it as conditional statement like PHP. After you query on PHP, you can just set a variable and make a conditional statement..Here is my script in vb.net:
Private Sub CheSMS()
Dim SQLServer As String = My.Settings.SQLServer
Dim conn As SqlConnection[code]......
I know that LINQ queries are deferred and only executed when the query is enumerated, but I'm having trouble figuring out exactly when that happens.Certainly in a For Each loop, the query would be enumerated.What's the rule of thumb to follow? I don't want to accidentally enumerate over my query twice if it's a huge result.
For example, does System.Linq.Enumerable.First enumerate over the whole query? I ask for performance reasons. I want to pass a LINQ result set to an ASP.NET MVC view, and I also want to pass the First element separately. Enumerating over the results twice would be painful.It would be great to turn on some kind of flag that alerts me each time a LINQ query is enumerated. That way I could catch scenarios when I accidentally enumerate twice.
I'm working on an access database and there is a problem I can solve in a circumvented way but I was wondering if it can be done by using a function in query.The case: I have a main data table and 2 other tables joined to it.
maintable
country | city...... | postalcode [code].....
From one of the tables I select the respective zone for the postal codes in the main datatable, ie. if the row's postal code column contains 1000 (just an exmple) then the postal code is searched in the "zone" table "from" & "to" columns using "between", and it results in for example "zone1" (zone column). This is a clear thing, it works okay.However I need to select the respective rate column (based on the rows' weight column) from the rates table selecting the appropriate zone.
I can solve this by a switch statment (select country, city.... switch( zone="1", [1], zone="2", [2] etc.) but it gets very tedious when for example I have a rate table with 15 columns, you can imagine that.My question is if I could create a custom function which returned the respective column based on the result of the zone column.is there some way to get the function's return value to be translated in the select statement? for example select country, city....getzone("rates",[zone]) .....
I know that the rates table do not follow the relational database's main principle and it should look like fromkg | tokg | zone | rate and it would work like a charm with "where"but these tables are changed regularly and I receive them in excel...but maybe transforming the tables each time by unions would easily solve my problem doesnt it?I'm still interested if VBA custom function could be used for such a purpose.
I'm trying to create a sql query to search within an Access table that is showing inside a datagrid.
The search criterias used in the "where" bit are retrieved from info entered by the user in 3 textboxes and 1 DateTimePicker control. The first 2 fields are String type, the 3rd field is a date type (entered by the user using the DateTimePicker) and the last field in the DB table is Integer type.[ode]...
I am currently working on downloading the data automatically on this page[url]...
I would like to somehow be able to control the URL so that, say, when I use the [URL]..
the option selected for Location parameter would be PJM and when I do[code]...
This LINQ to SQL query From g In Db.Context.Current.Groups Select g.GroupID
generates this SQL:
SELECT [t0].[GroupID] FROM [dbo].[Groups] AS [t0]But this query From g In Db.Context.Current.Groups
Select g.GroupID, g.MemberCount
[Code]...
I very much want to select only the fields I need. How can I persuade LINQ to SQL not to select all columns?
given this function:
Public Function Search(ByVal StartIndex As Integer, _
ByVal MaxResults As Integer, _
ByVal AccountNumber As String, _
ByVal LastName As String, _
[Code]....
instead of what I expected:
Select count(*) from remitline where lastname = "smith"
What am I doing wrong building up the where clause? I'm using Castle ActiveRecord 2.1
if there is that much of a performance gain in running a LINQ stored procedure versus a LINQ query?
View 1 RepliesI have to join two main tables, and I need to filter the results by elements in an ASP.NET web form. These filters are created on the fly so I have to use a lot of where extensions to filter the query. I want to execute the query with as optimized SQL as possible.
I am first doing a simple join between TW_Sites and TW_Investigators. Then there are two sub-tables that are involved. TW_InvestigatorToArea and TW_InvestigatorToDisease. While most of the where clauses are working fine, I have found a performance issue that won't be an issue right now, but will be an issue as the table gets bigger.
The arrays DiseaseCategories and DiseaseAreas would be the results of a CheckBoxList result.
Protected Sub LoadResults()
'Get Dictionary of Filters
Dim FilterDictionary As OrderedDictionary = Session.Item("InvestigatorFilterDictionary")
' Initialize LinqToSql
[code]....
Forgive my ignorance on this.I have this LINQ Query:Dim ngBikersDataContext As New CarBikeWalkDataContext
bikersList = (From c In ngBikersDataContext.Reg_Bikers _
Order By c.L_Name _
Select New Bikers() With { _
.BikerID = c.BikerID, _
.F_Name = c.F_Name, _
[Code]...
with the error "Overload resolution failed because no accesible 'Select' accepts this number of arguments."
Over the "NEW" I get an error " ')'expected."
The following piece of code executes a SELECT query and places the result into a textbox, but if the query returns nothing an error appears saying that there is no value and it won't continue the lines of code after it. (I modified code I found on a website after a Google search, I'm not sure if this is the best way to do it but it seems to do what I want.. so far)
[code]...
How would I change the code so that it checks the query to see if it returns a result? I'm still not too sure what the SqlDataReader can do fully, so I'm not sure if it can check for a result.
I'm querying a SQL DB and sometimes the result is Null, so I thought I would test this but my code results in an error "Data is Null. This method or property cannot be called on Null values."
Here is a snipet of my code:
For i = 5 To 6
cmd = New MySqlCommand(query2t & i, db)
rdr = cmd.ExecuteReader()
rdr.Read()
If Not IsDBNull(rdr.GetUInt32(0)) Then
[Code] ......
Code:
Dim MainForm = Me.Controls.OfType(Of CheckBox)()
If Not MainForm.All(Function(cb) cb.Checked) Then
[code].....
We are doing a query against an in-memory collection of LINQ data objects. The wrinkle is that we are ordering by a column in a related table whose records have not necessarily been loaded yet (deferred loading:)
Dim oPkgProducts = _
From b In oBillPkg.BillProducts _
Where b.Successful.GetValueOrDefault(Common.X_INDETERMINATE) = _
[code]....
How do I add ROW_NUMBER to a LINQ query or Entity? How can I convert this solution to VB.NET?
[Code]...
I'm having trouble porting that last line. I have been unable to locate a VB.NET example. I'm actually not looking for any paging functionality like the example provides, just good old-fashioned Row_Number(Order By X) row index.
I am working on an application in Visual Basic Express using an Access 2000 database.I am trying to get athe following SQL query to work but get an error in Access.
The query is:
SELECT [ID], [Title], [Author], [Series], [YearPublished], [ISBN], [CoverPrice], [Style], [Condition], [Signed], [Comments], Count([Title]) AS CountOfBooks, Sum([CoverPrice]) AS TotalCoverPrice
FROM tblBooks
WHERE ID=[@ID];
The error is:"You have tried to execute a query that does not include the spec ified expression 'ID' as a part of the aggragate function."
The query works fine without the 'Count' and 'CoverPrice' count and sum to the query.
I was wandering is it possible to use a query within another query below is the code I am trying to use.
Public Function GetInventByComp(ByVal CompID As String) Using DC As New DataClassDataContext
'need to get company id's based off names? thats bad should be name from Id need to rethink this
Dim invent = (From C In DC.Inventors_Companies _
Where C.CompID = CompID _
Select C.InventorID).ToString
[Code]...
I was trying to us multiple values in a string like "1, 3, 5" but I can't seem to get that working either so I am trying to use just a single value now. Can anyone help me? "Yes I am new to this"
I've been reading a fair bit about the performance of using LINQ rather than using a for each loop and from what I understand using a LINQ query would be a little bit slower but generally worth it for convenience and expressiveness. However I am a bit confused about how much slower it is if you were to use the results of the query in a for loop.
Let's say that I have a set called 'Locations' and a set of objects called 'Items'. Each 'item' can only belong to one 'location'. I want to link items that are under the same location to each other. If I were to do this using a normal 'For Each' loop it would be something like this:
For Each it as Item in Items
If it.Location.equals(Me.Location)
Me.LinkedItems.Add(it)
End If
Next
However if i was to use LINQ it would instead be this:
For Each it as Item in Items.Where(Function(i) i.Location.equals(Me.Location))
Me.LinkedItems.Add(it)
Next
is the second (LINQ) option going to loop once through the entire 'Items' set to complete the query, then loop through the results to add them to the list, resulting in essentially two loops, or will it do the one loop like the first (For Each) option?
Is it possible to do the following? [code] Basically I have one Load_Gridview function that is called on all postbacks, and rather than creating a bunch of different cases.I want the filters to stack.My actual code has more filters set up (4 or 5 of the).It all compiles ok but when I run and try to execute with active checked, or a department selected I get the following error. [code]
View 4 RepliesI have below XML file
<?
xml version="1.0" encoding="utf-8" ?><
PageFlow>
<
attributegroup name="Search"><
attributes>
[Code]...
I am looking to sum the "Status" field.
Dim _detailRecords As New DataTable
_detailRecords.Columns.Add("y")
_detailRecords.Columns.Add("z")
[Code]....
I'm also wanting to keep this as an IEnumerable of DataRow because I will be creating a datatable from it with
Dim yyyyy As DataTable = query8.CopyToDataTable
I am Fairly new to Linq and I am trying to write a simple query to return the error messages within my xml file.
<?xml version="1.0" encoding="utf-8"?>
<Error xmlns="urn:xxxxx">
The following errors were detected:
[code].....
if I run this under c#
from p in Addresses where p.Address2 == null select p.AddressID
it generate this query
SELECT [t0].[AddressID]
FROM [dbo].[Address] AS [t0]
WHERE [t0].[Address2] IS NULL
if I run this under vb.net
from p in Addresses where p.Address2 = nothing select p.AddressID
it generate this query
SELECT [t0].[AddressID]
FROM [dbo].[Address] AS [t0]
WHERE [t0].[Address2] = ''
p.Address2 is a varchar field that accept null value
why vb.net is "wrong" ?
I'm converting the Linq query below from C# to VB.Net. Can you spot my error? The query joins 3 XML datasets. C# - This one works great.
List<Course> courses =
(from course in CourseXML.Descendants(ns + "row")
join coursecategory in CourseCategoryXML.Descendants("Table") on (string)course.Attribute("code") equals (string)coursecategory.Element("DATA")
[code]....
I have been trying the following but it returns unexpected results:
Dim xd As XDocument = _
<?xml version="1.0" encoding="utf-8"?>
<root>
[code].....
The above result returns both 'element' nodes however it should only return the first where element/subelement@id=1/subsubelement@id=3 However if results.Ancestors. is used it returns the correct 'subelement' and if that line is not included it returns a single 'subsubelement' whih is also correct I don't understand why when mvoing to the 'element' it returns both (I realise both have a subelement with id=1 but I thought each further query would filter out the presvious results)?