Use LINQ Query To Convert DBNull To String?

Oct 12, 2009

I've created a small WPF app in VB9 to catalog our sizable DVD collection. This exercise has been more about learn programming than the need to have a movie database app, but I digress.The SQL database I created has a field for "Genre" that does not have an entry for all the movies in our collection. As such, when I run the method that filters the movie listing by "Genre" the compiler throws the "StrongTypingException was unhandled" error stating "The value for column 'Genre' in table 'Movies' is DBNull.".

I now understand where the DBNull problem comes from and have been searching for several hours to find a VB solution that converts the DBNull fields to strings on the fly during the LINQ query. I've found many solutions people have tried but, since I'm quite new to all this, none of them have worked for me. Pasted below is the method that throws the exception.

[Code]...

View 2 Replies


ADVERTISEMENT

Convert This String-based Sql Query To Use Linq?

Feb 3, 2011

I have the following routine (that works) but which is messy to update owing to the hand-typed strings it uses:

Private Sub ListDefaults()
Dim conn As New SqlConnection( _
"server=bas047AUTODESKVAULT;Database=DWGDetails;Integrated Security=SSPI")
'Dim conn As New SqlConnection( _

[code]....

View 1 Replies

VS 2008 Convert A String Into A Linq Query?

May 20, 2012

Is there a way to convert a string into a linq query? Searching for those words brings up tons of stuff unrelated to what I'm trying to do..For instance, this works, of course...

Dim lstMyQuery = (From item in htMyTable.values where item(0) = "Apple" Select item)

But somehow I want to do this:

Dim strQuery = "From item in htMyTable.values where item(0) = " & CHR(34) & "Apples" & CHR(34) & " Select item"
Dim lstMyQuery = (Somehow run/convert strQuery as a query)

The reason is strQuery will be different each time depending on what the user has selected earlier in the program.R if lstMyQuery is already a collection, is there a way to add to it?

Dim lstMyQuery = (From item in htMyTable.values where item(0) = "Apples" Select item)

(this obviously doesn't work, but it's basically what I want to do...)

lstMyQuery.Add(From item in htMyTable.values where item(0) = "Oranges" Select item)

View 3 Replies

.net - LINQ Replace DBNull With Blank String?

Mar 10, 2010

In this example, an error is raised if either row.FirstName or row.LastName are NULL.

How do I rewrite the Select clause, to convert a DBNull value to a blank string ""?

Dim query = From row As myDataSet.myDataRow in myDataSet.Tables("MyData") _
Select row.FirstName, row.LastName

NOTE: Since the DataSet is strongly-typed. I can use row.isFirstNameNull(), but IIF(row.isFirstNameNull(), "", row.FirstName) will not work since all parameters are referenced.

View 3 Replies

Convert DBNull To Blank String In Access DB?

May 15, 2010

I'm a new member to this site, and I have been searching for a solution to this problem.. Although I have found variations in the problem, nothing has seemed to work for me. Just started programming last year.

I am trying to populate text boxes in a form based on information from an Access Database. Everything is working, except when there are empty field values; the program then crashes because a DBNull value is returned. I set up an if statement to check the index as the rows are read for DBNull values, the problem is How do I actually change the value in the field from DBNull to a blank string ("") ?? Everything I try gives me an error, most often saying something about setting up an object. The problem happens within my if statement of the do loop...I'm thinking maybe I need to set up parameters ? Or an actual edit session within a datarow variable?[code]...

View 4 Replies

VS 2008 CellValidating - Cannot Convert A DBNULL Value To STRING

Nov 2, 2009

I am having a problem with the DIM statement. It is saying that it can not convert a DBNULL value to STRING even though there is something in the cell.

[Code]...

View 14 Replies

How To Convert SQL Query Into LINQ

Dec 8, 2011

I am new to LINQ, how to convert the following SQL query to LINQ?
SELECT
Date,ShiftName,Max(Score) AS Score, 1 AS IsPreferred
FROM
Temp_Nurse_Fill_RequestNumbers
group by
ShiftName,date

View 4 Replies

How To Convert The SQL Query Into LINQ

Dec 8, 2011

I am new to LINQ.convert the following SQL query to LINQ?

SELECT
Date,ShiftName,Max(Score) AS Score, 1 AS IsPreferred
FROM

[code]....

View 4 Replies

Convert LINQ Query Expression From C# To VB?

Mar 10, 2009

I have the following C# LINQ query expression:

[code]...

how to convert this to VB.NET?

View 1 Replies

Convert SQL Query/subquery To LINQ (VB)?

Jan 16, 2012

Here is my SQL query..

SELECT EqID, MakeID, Model, Description,
(SELECT MAX(EvDateEnd) AS MaxOfDateEnd
FROM tblEvent WHERE (EqID = tblEquip.EqID) AND (Event = 'REG')) AS RegExpire,

[code]....

For example, I want to get the Max EvDateEnd from the Events table for each record in tblEquip, but not all records in tblEquip have the "REG" event, while others have multiple REG events.Some events (like EAE) I leave the tblEvent.EvDateEnd field blank to show the event is open and ongoing, such as issuing a vehicle to an employee(entity). when the vehicle is returned I enter the date in EvDateEnd essentially closing the event.

View 1 Replies

Convert A LINQ Query Resultset To A DataTable?

Jul 20, 2010

How can i write this query with LINQ to a FoxPro database?SELECT count(*) FROM Table group by item1I wrote it as below, but it doesn't work

Dim Query
Dim dt As New DataTable
Dim da = New Odbc.OdbcDataAdapter("SELECT * FROM table1",connection)

[code].....

View 3 Replies

Asp.net - Convert SQL (Pseudocode) To Dymanic LINQ Query From List<Custom> Parameters?

Apr 5, 2012

GOAL: Bind nested ListViews to LINQ generated iQueryable of anonymous type. I want to use LINQ because you can use GroupBy and bind the nested ListView to the 'it' keyword.

SETUP: I have groups of sets of conditions. Each set of conditions is stored in the BillingCodes table. Each group of BillingCodes is stored in the BillingGroups table.

I have a custom object that stores the ID, Name, and NumCodes for each BillingGroup that the user has chosen.I have a collection of these objects called GroupsList that has a list of the groups that the user has chosen.

Problem 1: I can iterate through GroupsList and grab all the IDs. How do I translate the SQL 'WHERE ID IN(a string of comma delineated IDs)' for LINQ to SQL? Is that the best way to do that?

Problem 2: Once I have the list of BillingGroups I need to iterate through each group. For each group, I need to iterate through the BillingCodes. For each BillingCode I need to generate a WHERE clause that has all of the conditions in the BillingCode. I propose something like so:

for each BillingGroup in BillingGroups
for each BillingCode in BillingGroup.BillingCodes
where1 = "..."
next
next

Problem 3: Here's the part where I don't have a clue. I need to dynamically create a query in LINQ to SQL. Keep in mind that I don't know how many groups there'll be or how many codes are in each group.

There are 2 tables:

**transactions**
transaction_id
patient_id

[code]....

View 1 Replies

Convert A Two-table Exists Query From SQL To Linq Using Dynamic Fields In The Subquery?

Jun 7, 2011

I'm trying to query old Access database tables and compare them with SQL Server tables.They often don't have primary keys, or they have extra fields that had some purpose in the nineties, etc., or the new tables have new fields, etc.I need to find records - based on a set of fields specified at runtime - that are in one table but not another.So, I do this kind of query all the time in SQL, when I'm comparing data in different tables:

dim fields_i_care_about as string = "field1, field2, field3"
'This kind of thing gets set by a caller, can be any number of fields, depends on the
'table
dim s as string= ""

[code]....

It tells me it can't convert a Boolean - Is there any way to do this without Linq expressions? They seem far more complex than what I'm trying to do here, and they take a lot of code, and also I can't seem to find examples of Expressions where we're comparing two fields in a subquery.Is there a simpler way? I know I could do the usual EXISTS query using JOIN or IN - in this case I don't need the query to be super fast or anything. And I don't need to use a DataTable or DataSet - I can put the data in some other kind of object.

View 1 Replies

C# - Using LINQ To SQL To Query A Word Started With Something Within String?

Dec 7, 2010

I'm using LINQ to SQL to query some data.Here's my Employees table colummns:

-FirstName (or givename i.e. John)
-LastName (has middle name and family name seperated by space i.e. Mac Tenny

Some people have middle name and some don't. Here's some sample data.

-FirstName LastName
John Mac Tenny (with middle name "Mac")
Mike Mika (no middle name)

[code]....

I want to query for people who have their middle or last name started with search criteria. Here's how I search for employees with middle name started with
"joe" :

query = From emp In db.Employees _
Where emp.LastName.StartsWith("joe") _
Select emp

How do I query for employees with family name(last part of the LastName) started with "joe"?

View 2 Replies

Assign Result (from LINQ To XML Query) To List(Of String)?

Jul 6, 2011

Is it wrong? Please suggest me correct way.

Public Function ReadXML() As List(Of String)
Dim list As New List(Of String)
Dim xmlDoc As XDocument = XDocument.Load("C:\MappingFile.xml")

[code].....

View 1 Replies

Linq Query To Compare If Collection Contains List Of String?

Feb 3, 2012

I am using a path comparison to find any children tags which works well when there is only 1 tag selected. This is my code for when multiples tags are selected but it is not working. Can you point me in the right direction?

Single Tag Selection (working)
Dim tagpath = uxTags.SelectedItem.Text
lnqCases = From i In lnqCases Where i.HelpDeskTagItems.Any(Function(x)

[code]...

View 1 Replies

Using LINQ To Query XML And Return Empty String If No Data Is Present?

Mar 8, 2011

What I'm trying to do is to write a generic rss reader that I plug in any URL into without worry if the feed has all the common properties. For instance in my example below I'm looking for pubDate, however if no pubDate exists in the xml I'd like to return the current Date. I cannot seem to get the syntax right though.

Dim xmldoc As New XDocument
xmldoc = XDocument.Load(url)
Dim feeds = From feed In xmldoc.Descendants("item") Select New With { _

[code].....

View 2 Replies

Convert A Query Answer To A String To Display?

Mar 18, 2010

I have this query that returns the number of rows which is :

SELECT COUNT(*) AS MemberTotal
FROM Members

i'm trying to write a readonly property called count to return that value in string its suppose to display the result of my query in a tool strip status label, I have this in my code

Public ReadOnly Property count() As String
Get
Return memberAdapter.GetMemberCount()
End Get
End Property

but it gives me an error saying I can't convert dataset to string something like that and I don't have any idea on how to convert it I tried the Cstr but that didn't work?

View 2 Replies

.net - Filtering DBNull With LINQ?

Mar 8, 2010

Why does the following query raise the error below for a row with a NULL value for barrel when I explicitly filter out those rows in the Where clause?

Dim query = From row As dbDataSet.conformalRow In dbDataSet.Tables("conformal") _
Where Not IsDBNull(row.Cal) AndAlso tiCal_drop.Text = row.Cal _
AndAlso Not IsDBNull(row.Tran) AndAlso tiTrans_drop.Text = row.Tran _

[code]....

Run-time exception thrown : System.Data.StrongTypingException - The value for column 'barrel' in table 'conformal' is DBNull.

How should my query / condition be rewritten to work as I intended?

View 2 Replies

DBNull When Using LINQ To DataSet

May 20, 2010

I've got the following LINQ Statement:[code]invRecord.Pack_Num is a field of type Integer. This means that when I try to access it, if it is DBNull I get a StronglyTypedException. The above code throws this exception. If, however, I remove the "invRecord.Pack_Num = PSNum" and in its place put something like "True", the code works fine. So I guess my question is, why is that that invRecord.IsPack_NumNull() returns False when the value is in fact DBNull and what can I use as a conditional instead? I've been beating my head against the wall for a while now and I can't find a solution to this problem.

View 1 Replies

LINQ Query Is Enumerated - Turn On Some Kind Of Flag That Alerts Me Each Time A LINQ Query Is Enumerated?

Sep 22, 2009

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.

View 3 Replies

Getting Error With DBNull When Using LINQ To DataSet

Nov 12, 2011

I've got the following LINQ Statement:

[code]...


invRecord.Pack_Num is a field of type Integer. This means that when I try to access it, if it is DBNull I get a StronglyTypedException. The above code throws this exception. If, however, I remove the "invRecord.Pack_Num = PSNum" and in its place put something like "True", the code works fine. So I guess my question is, why is that that invRecord.IsPack_NumNull() returns False when the value is in fact DBNull and what can I use as a conditional instead? I've been beating my head against the wall for a while now and I can't find a solution to this problem.

View 7 Replies

LINQ To Dataset - How To Handle DBNull

Jan 29, 2008

This is what I'm doing to select and display 3 columns of my datatable in GridView:

Dim myCustomers = From Cust In myDataset.Customers _
Select CustomerId = Cust.CustomerId, _
CustomerName = Cust.CustomerName, _
ExpiryDate = Cust.ExpiryDate
DataGridView1.DataSource = myCustomers.ToList

The expiry date has null values in many records whose expiry date is not set.On executing the query I get runtime error:System.Data.StrongTypingException "The value for column 'ExpiryDate' in table 'Customers' is DBNull."

Obviously, my LINQ query is wrong or incomplete, but I am not able to figure the way to do it correctly.

View 13 Replies

.net - LINQ Error Type DBNull Column?

Apr 29, 2011

I am querying a datagridview and it works great unless one of the cells has nothing (dbnull). How to over come this?Exceptions: Operator '=' is not defined for type 'DBNull' and type 'DBNull'.

Dim query = From row As DataGridViewRow In DataGridView1.Rows _
Where row.Cells(SelectedColumnIndex).Value = filter _
And row.Visible = False _
Select row Distinct

View 1 Replies

Convert DBNull To Date

Jun 5, 2011

I wants to ask how to convert DBNull to Date type?

For Each row As DataRow In dt.Rows
i = 0
Dim array As Object = row.ItemArray

[Code]....

array(i + 10) is datetime type, in the program above, if the Datetime column contains items, it still can run. However, when it loops to a row where the Datetime column does not have value of datetime (or the column is null), then an error " Conversion from type 'DBNull' to type 'Date' is not valid" is occured. I tried to use the if statements as program above but it seems like cannot works.

View 9 Replies

MS-Access Query Does Not Correctly Interpret DBNull.Value Using OLEDB.NET

Oct 1, 2011

I am connecting to a MS-Access 2007 database using VB 2010 and OLEDB. Conducting the following test seems to suggest that MS-Access does not interpret DBNull.Value correctly when sent as a parameter from OLEDB:[code]

View 2 Replies

.net - ActiveRecord Linq/NHibernate Linq Not Building Query Completely?

Jul 14, 2011

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

View 1 Replies

Performance Gain In LINQ Query Vs LINQ Stored Procedure?

Aug 6, 2009

if there is that much of a performance gain in running a LINQ stored procedure versus a LINQ query?

View 1 Replies

Asp.net - Complex Linq Query - Add A .Where() Or .Any() Predicate To Reduce Query Complexity (in VB)?

Sep 14, 2010

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

[code]....

View 2 Replies

LINQ Query Using The Dynamic LINQ Library?

Mar 31, 2011

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."

View 1 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved