From r In ReceiptLines
Where
r.RECEIPT.RECEIPTDATE >= _reportStartDate
And r.RECEIPT.RECEIPTDATE <= _reportEndDate
[Code].....
I am fetching all departments and their sales, average, count from the ReceiptLine, Receipt, ReceiptDiscount tables. The problem i am facing is, if i remove where discount > 0, I am getting null exception. But if I include that, then I only get sales that has discount. How would I write query that bring all sales less discount (if it has one).
I have a sql query which is returning null value below is the section the iID is null.Once debugger reaches this it goes to exception as its result is NULL. How to control debugger from going to the exception.
I have a bunch of tables who share the same kind of information, but each table has a different name for each. (There was no standardization on the naming scheme). Some tables have important data that others do not contain. Some tables that have shared important data, but they are null values. I'm writing a program to combine all the tables into one master table.
The problem is when I read in a field, the reader.getvalue(i) is null, and I cannot use that value to insert into my new master table, obviously. The null values are so random, and I cannot write IF THEN statements for every single field for every single table in the database. I'd have thousands of lines of code constantly checking for nulls, and then trying to figure out what to do with them. What can I do to convert these reader values from NULL into something VB/SQL can handle? ere is the code so you can get a picture of how I'm approaching this problem:
If I have a datatble column set as int16 but rows can contain null values for this column. How do i write the linq query. I keep getting an error null value exception. Dim query = From n In resultstable.AsEnumerable Where n.value is dbnull.value does n't work.
I'd like to take all suggestion either in C# or VB.NET.I have a DB diagram like the image below. I also include the database script here [URL]In the Students table, CountryId and RoomId column are allowed null. Because some records do not have info about room and country yet.Also, some students do not have essays.I'm doing a joint query with all tables. I want to select all students to project the result like this:Wanted query result.
Here's my current query that gives the result like the image below:
Dim db As New DBDataContext Dim query = From st In db.Students _ Join c In db.Countries On c.Id Equals st.Id _ Join r In db.Rooms On r.Id Equals st.RoomId _
[code].....
current query result I got only one result back because I have (2) William NoMan record in every table. But I don't get anything about others, like (3) Sync Master who has everything but RoomId.What do I need to modify the query above so it will give me all students like in the wanted query image above?
My issue is with getting the attribute values. I tried this: For Each xattr As XAttribute In attr cboBrandChoice.Items.Add(test.Value) Next
However, I keep getting the following error: {"Unable to cast object of type '<GetAttributes>d__8' to type 'System.Xml.Linq.XAttribute'."} I'm new to using Linq so this error isn't making a whole lot of sense to me.
I have 2 tables Table1 with columns [BId,Name,Amount] Table2 with columns [CId,BId, ExpenseType,Expense]. BId Is the foreign key in Table2. The Amount field in Table1 is always higher than Expense in Table2. I need to update the values of Amount (increase or decrease) based on value of Expense and I want to do it in single query in LINQ. Eg If the Expense has to be updated with 200, I would decrease(negate) Amount value with 200. If Expense is reduce to 100 then the Amount is increased by a value of 100.
've got a function that takes two parameters (a delegate and an integer) and then creates a LINQ MethodCallExpression, which it uses to gets the results:
Public Delegate Function CompareTwoIntegerFunction(ByVal i1 As Integer, ByVal i2 As Integer) As Boolean Public Function Test(ByVal pFunc As CompareTwoIntegerFunction, ByVal i1 As Integer, ByVal
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 am looping through the rows of a DataRowCollection and assigning the fields of each row to a variable. I always get "Input string was not in a correct format" no matter how I cast this. I just need gapCd to contain 0 if the field is null or the value otherwise. It seems the IsDbNull is not returning true properly. I've also tried DbNull.Value comparisons with no l
I 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
I am running into a problem where my CRUD operations on an entity sourced from an SQL View is not calling the generated methods for said operations.I press "Delete" in a ListView on an item, connected to a LinqDataSource. It throws an error saying that it cannot perform the operation because it affects multiple base tables. That's fine, I understand that. What I don't understand is why this code won't run on insert/delete:[code]In debug, it won't break on the method, so it's not being called. I even did a test where I deleted the entity by attaching/DeleteOnSubmit and still no-go. Is this a bug or am I not handling the right method? I can handle a data source's OnDeleting event, cancel, etc. (which is my temporary fix) but I'd really like to catch ALL delete operations in a central place no matter how I delete the entity.
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."
I 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 have the following query, I'd like to sum the NULL value also. Some TimeSheet don't records in TimeRecord and some tr.TimeIn and tr.TimeOut are NULL.The query select only TimeSheet that has reords in TimeRecord. How I can have it select everything, and sum up the NULL value as well. So, the SUM of NULL will be just zero.
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) = _
i am using the LookupEdit control from DevExpress fairly heavily in my projects. It is like a combobox, but it is bound to a datasource and can display multiple columns from the datasource. A single column is identified as the "value" column and a single column is identified as the "Display" column.
I use the LookupEdit control on my forms to give users a pick-list of appropriate values for a particulat database field. Each field has its own table in the database holding said values, commonly referred to as a lookup table, and each lookup table is used as the datasource for each paired LookupEdit control. Of course when a form is being filled-in by the user this works great. And it works good when the user opens a saved record into the form and I set the EditValue of the LookupEdit based on the value stored in the database. Pretty basic stuff right?
Now here is the problem... Let's say a lookup value is no longer an appropriate entry and so it is removed from the lookup table. Say I sell cars and one of the fields in the Cars table is Color. And I have a lookup table called CarColors and the selections are: Black, White, Red, and Silver. Now let's say we sold some Red cars in the past, but now we no longer carry Red as an option and we probably never will. So I remove "Red" from my lookup table, and now when I load an old Order into my form the LookupEdit for Car Color value now shows NULL. What is the best way to handle this situation? ProcessNewValue? Set NullText property? Use a bit field in my lookup table for IsActive, and set to FALSE for Red instead of deleting it?
Obviuosly I want to retain the historical values while maintaining a currently valid lookup list. And I know this question goes deeper as there are probably database design considerations like whether or not to put a constraint on the foreign key relationship between the lookup and child table, etc... I should also mentioin I am using LookupEdit control rather than a ComboBox becasue a lot of my lookups have other information like Description, Sort Order, or are coded and thus require a multiple column display.
Public Sub CreateXml() Dim db As New MDataContext Dim Customers = <gallery columns="3" rows="3">
[code]....
Could i mix local values with the ones returned from the LINQ query...Something like the following?
Public Sub CreateXml(ByVal **Col** As String, ByVal **Row** As String) Dim db As New MDataContext Dim Customers = <gallery columns="& **Col** &" rows="& **Row** &">
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 develop in VB and often I'm writing long TSQL statements in SSMS but getting them into VB is a bit of a pain. I like SSMS during development because I can change the query / structure and test it when there are problems. Some of them become stored proceduresand some of them I just keep in the code.
I like keeping them in the code because when I have a problem with something, I can GREP my projects directory and find the code that does something (that I've forgotten) or someone asks something about how something works, I can look it up. I've yet to find a GREP for stored procedures. Is there such a thing?I've read in a "best practices" white paper how we're not supposed to concatenate super long strings with & _
[code]...
C# handles these things much better than VB. I'm wondering how I can keep my code together during development and searchable later. I had my hopes up when VS 2010 offered implicit line continuation, that this would help my multi line SQL procedures.Again, I was mistaken. Perhaps I could write it all on one line. I'd be interested in knowing if there's a feature that I am not aware of. Well there are certainly many features which I'm not aware of, but I was referring to "long strings".
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"
This seems like a pedestrian problem, but I just can't seem to find a solution. My problem is that I can't seem to find a way to search my datasourcefor a null value. Here's a simplified version of the problem: suppose we have a form with 3 textboxes and we wish to find a record on our datasource. if fields 1 and 2 have a value and field 3 is null, then how can I make a query using .net that searches for the values of the first 2 fields and a null value for the 3rd field? For example, suppose field 1 has a value of "ABC" and field 2 has a value of "DEF" and field 3 has a null value Now, how can I construct a query that searches "ABC" for field 1, "DEF" for field 2, and null for field 3?I'm using sqlce and below are some examples of what I've tried so far:
Dim Fld1 As New SqlServerCe.SqlCeParameter, Fld2 As New SqlServerCe.SqlCeParameter, Fld3 As New SqlServerCe.SqlCeParameter Fld1.SqlDbType = SqlDbType.NVarChar:Fld2.SqlDbType = SqlDbType.NVarChar:Fld3.SqlDbType =
I've got a subquery that returns the most recent value from a child table. In some cases the subquery returns nothing. The query below fails at runtime because the inferred type of MemberPrice is decimal and is not nullable. [code]...
