Using LINQ To SQL With Views Need To Cast / Convert To Table
Jun 25, 2012
My SQL Server database contains several views which are essentially filtered (WHERE) / ordered (ORDER BY) versions of some of the tables i.e. no joins.
An example would be my Downloads table, which has a view such as DownloadsOnlineOnly e.g.
SELECT * FROM Downloads WHERE Live = 1 ORDER BY FolderName ASC;
I can drag and drop these views onto my LINQ to SQL DataContext, and use LINQ to query these views and retrieve data fine...
But the foreign key relationships are not being picked up in my DataContext, so I've lost the ability to:
Dim dl as DownloadOnlineOnly = (From r in db.DownloadOnlineOnlies Select r).First
Dim fol as Folder = dl.Folder ' Syntax error as there is no association between
' DownloadOnlineOnly and Folder in my DataContext.
I understand why this is happening, but how can I get around this:
Without manually adding associations in my DataContext? Can I change the type of the Entity like changing the return type of an SP on the DataContext? Can I cast or convert between a DownloadOnlineOnly object and Download object? Or some other method?
View 1 Replies
ADVERTISEMENT
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
Sep 9, 2010
I am trying to Delete an entity but am having trouble with an InvalidCastException. The debugger breaks at the Next statement in the ForEach loop.My entity class is called Material.
Dim materialsTable As Table(Of Material) _
= (New DataContext("Server=.SQLEXPRESS; Database=Materials; Trusted_Connection=yes;") _
.GetTable(Of Material)())
[code]....
View 1 Replies
Sep 3, 2010
I'm getting an invalid cast exception but I'm not sure why, here is the list of my conversions.
SQL -> VB.NET
char(4) -> String
varchar(50) -> String
tinyint -> Integer
char(1) -> Char
bit -> boolean
[Code]...
View 2 Replies
Mar 10, 2011
I created a master record in the master table and then a record in the detail table. when creating the detail record i get a Specified cast is not valid error. Now another weird thing is that once I create the master record I can't query and verify it was created. But when i got to sql management studio I can see the master record. Below is my two procedures and the table definitions.
Implements System.ComponentModel.INotifyPropertyChanging, System.ComponentModel.INotifyPropertyChanged
Private Shared emptyChangingEventArgs As PropertyChangingEventArgs = New
[code]......
View 5 Replies
Nov 5, 2010
I'm getting this error
Unable to cast object of type 'System.Int32' to type 'System.String'.
when doing a routine LINQ to SQL query:
Return (
From n In DbContext.Newsletters
Where n.NewsletterID = NewsletterID
[Code]....
How is it possible that an Int32 can't be cast to String? How can I debug something this? Since it all happens deep in the bowels of LINQ, I can't even get any indication what field this is angry about.
View 4 Replies
Feb 22, 2012
Here's my [code...]
On this line "db.jobs2surveys.DeleteOnSubmit(deleteJob2Survey)" I'm getting the following error.
[code...]
What exactly does this error mean and how can I fix this? I can't find much info on Google.
View 1 Replies
Mar 10, 2011
ok I have a weird issue. I created a master record in the master table and then a record in the detail table. when creating the detail record i get a Specified cast is not valid error. Now another weird thing is that once I create the master record I can't query and verify it was created. But when i got to sql management studio I can see the master record. Below is my two procedures and the table definitions. Master table primary key is INVNUM child table primary key it INVLIN_ID and the reference key is INVNUM
View 13 Replies
Jun 29, 2010
Ok, just needing a 2nd set of eyes looking at this to make sure the error isn't something else other than my LINQ code here. Here's the function class itself:
Public Function GetJacketByPolicyID(ByVal jacketID As Int32) As tblPolicy
Dim db As New DEVDataContext()
Dim j As tblPolicy = db.tblPolicies.Single(Function(p) p.policyNumber = jacketID)
Return j
End Function
and here is the code which calls this class function in the web control form itself:
Dim p As tblPolicy
Dim j As New Jackets()
p = j.GetJacketByPolicyID(3000050)
For some reason it's flagging the 2nd line in the GetJacketByPolicyID function saying the specified cast is not valid. So I'm guessing it's something I'm doing wrong. I'm sure the tblPolicy/tblPolicies class works right since I can create a new instance of a tblPolicy and set a few variables by hand and return it, so that's not it. I've also checked the datarow I'm fetching and there's no null values in the record, so that shouldn't be it either.
View 1 Replies
Nov 2, 2009
I've been programming in vb.net a lot for the last 9 weeks and I have just come across a lot of double functionalities (hope I used the right word for that).For instance, you can use CInt, CType, Directcast or Convert to turn something into a integer. (Directcast doesn't really belong in that line but whatever).
With calling methods you can do DoWork() or call DoWork()With returning values in functions you can do DoWork() = 3 or return 3 Why is this? Is it just to make the transition frm vb6 to vb.net easier? Or about giving the programmar a lot of options. I must admit I don't really like to have that choice, I am always in doubt whether I am using the right method .
View 11 Replies
Jul 6, 2009
I have a DataGridView that is bound to a list of objects called "BaseChange". The BaseChange objects are made up of 4 properties...
ChangeType
ChangeStatus
ChangeDescription
LastChangeDate
The datagridview has columns for all 4 values as well as a 5th (a checkbox column called "colIsSelected"). There is no problem binding the list to the grid and displaying the items. The problem is that the query that gets the selected items in the grid is giving me an implicit cast error when option strict is enabled.
This is the query...
Dim _changes As List(Of BaseChange)
_changes = (From _row As DataGridViewRow In dgvChanges.Rows() _
Where Convert.ToBoolean(_row.Cells(NAME_COLUMN_IS_SELECTED).Value) = True _
Select DirectCast(_row.DataBoundItem, BaseChange)).ToList()
...and it produces the correct results with option strict off. The implicit cast squiggle is on the "row As DataGridViewRow" code, and the full message is "Implicit conversion from 'Object' to 'System.Windows.Forms.DataGridViewRow'*".
If I exclude the "As DataGridViewRow" from the query, I get a late binding error on the _row.Cells and _row.DataBoundItem and this also fails option strict.I need this to work with Option Strict enabled, and in VB.
View 1 Replies
Aug 23, 2010
I have two tables Employees and CafeLogs. Some employees can be cashiers and also customers at the Cafe shop.
Table structures:
Employees: EmployeeId(PK) , FirstName, LastName
CafeLogs: LogId (PK), CashierId, EmployeeId, Value, => CashierId and EmployeeId are the data from column EmployeeId of Empoyee table
Table relationship:
Employees 1:N CafeLogs (CashierId (FK))
[code]....
Right now I know how to select only LogId, Employee's name, and , Value, not with Cashier name yet.
Dim query = From log In db.CafeLogs _
Join emp In db.Employees On emp.EmployeeId Equals log.EmployeeId _
Select log.LogId, emp.FirsName, emp.LastName, log.Value
View 1 Replies
Jun 27, 2012
How can I convert this code LINQ to SQL in C # LINQ to SQL in Vb.net
[Code]...
View 2 Replies
Jul 12, 2011
I am calling a GetSerialNo function But it showing some error like:
Cannot implicitly convert type 'decimal' to 'int'. An explicit conversion exists (are you missing a cast?).
Here is the code:
CODE:
View 3 Replies
Jan 13, 2011
I am using VB.NET 2010 (Visual Basic 2010 Express) on a WPF-based project. I am also using the SQL Server Express built-in to Visual Basic 2010 express.I have just about finished refining my code for hooking up my wpf-based form to an existing SQL database (agentroster.sdf). I have a global data source (AGENT_ROSTER) connected to this database. Connections are confirmed to work properly.This is the first part of the code I'm using, irrelevant code omitted,
Dim table_adaptor As New AGENT_ROSTERTableAdaptors.AGENT_ROSTERTableAdaptor
Dim roster_table As New DataTable("roster_table")
Dim rowposition As Integer
[code]....
how do I fill roster_table with table_adaptor (or alternate method)?
View 1 Replies
Oct 21, 2009
How can I convert an object of type System.Data.Linq.DataQuery to System.Linq.IQueryable?I'm working with Visual Basic/Silverlight, and the source code of my query is as follows:
Public Function Get_Cli_Pag() As IQueryable(Of V_Cliente_Pagare)
Dim Qry = From P In Me.Context.Pagares Join C In Me.Context.Codigos On C.Codigo
[code]....
View 1 Replies
Apr 10, 2012
I have 2 Tables Table "A" and Table "B". Here is little details.
Table "A"
---------
ItemID (Primery Key)
ItemSKU
[Code]....
View 5 Replies
Jul 23, 2009
I've got a painfully simple table that is giving me a "Specified cast is not valid" error when I try to delete one or more rows. The table has two columns, an "id" as the primary key (INT), and a "name" (VARCHAR(20)), which maps to a String in the LINQ to SQL dbml file. Both of these statements produce the error:
[Code]...
View 3 Replies
Jul 9, 2010
I have a table (Projects) which is linked to projectVersions on projectID..projectVersions contains several columns on which I'd like to filter a returned project (and associated projectVersions) list. For example there is a "capacity" column and a "country" column. I am doing a filtered list of projects on one page and I'd like to include all projects where any one of the associated projectVersions has a capacity of 750ml and a country of "France" for example.It may be that a particular parameter is not set and so I pass a zero to indicate not to filter on that.I guess this needs some kind of subquery as when I try and do something like this: [code] it doesn't work as the "xxx" bit, being one-to-many, expects me to specify an item to get at the entities within and yet I want to query where ANY of the associated versions match one of the criteria.
View 1 Replies
Jun 3, 2009
I read somewhere that older VB .net (pre VB .NET 2005?) couldn't use overloaded operators in a C# class. Is it true for overloaded cast operators?Either way do you guys think it's a disadvantage for a C# class library not to include them and arithmetic methods as actual methods for other languages, like:
ToDouble
ToFloat
FromDouble
Add
[code]....
View 1 Replies
May 22, 2012
I'm trying to solve a problem regarding types of list. First of all I have a stored procedure in my DB which does a select of a single column and I try to proceed it in my app in VB. By making a method function I declared a DataTable that loads through the SqlCommand(with the CloseConnection behavior). After that I publicly declared a List(Of String) which needs to be populated with the rows/items from the stored procedure that is on the way. Below is my snippet of the code:
Dim dt As New DataTable()
Try
If conn.State = ConnectionState.Open Then
[Code]....
It's LPrefix = collection.Cast(Of String)() where I get an exception error telling me that I can't really convert it. The old fashion way is to iterate with for/for each loop but that's not what I want for best use of performance especially if the list will have thousands of rows from a single column. So basically, I want to insert those items from that DataTable to the List(Of String) without For/For Each loop.
Running on VisualStudio2010 Ultimate, .NET FrameWork 4.0.
View 2 Replies
Jul 26, 2011
How can I convert below code to vb.net.
[code]...
View 2 Replies
Oct 20, 2009
I'm having trouble converting a C# Linq statement to VB.NET. I need the following statement converted. I am getting hung up on the i.Sum(v=>v) part.
from p in Products
from u in Users
let i = (from op in OrderProducts
where op.Order.User == u && op.Product == p
[code]....
how to get that converted to VB.NET and working?
View 1 Replies
Feb 19, 2012
Columns 0 & 1 are filled from a DataAdapter from Access Database and three are added. I had this working, but I changed it to check for the column "Feed" so I could add several Locations. At first this worked,but I did someething in perfecting it and it now fails after filling the first row of the datagridview (dgvFdMed).I ran a check and the table LotsTab has 8 rows filled. Column 1 & 2 are strings and the database appears they are fine. The added columns have just been created with no value. My plan is to enter the values and then use the data from the grid.
Private Sub sfillGrid(ByVal Loc As String)
Dim i As Integer
Dim column As DataGridViewColumn
[code]....
View 12 Replies
Feb 10, 2010
I'm trying to convert DataTable to Linq using
DIm result = From r in dt.AsEnumerable()
Select new ( col1 = r.Field<integer>("id"), col2 = r.Field<string>("desc"))
But i get error near 'new (' saying type expected.
View 2 Replies
Sep 13, 2009
I have this book from sanders (Pro MVC 1) and the code below is what I'm trying to convert from C# to VB.
public void RemoveLine(Product product)
{
lines.RemoveAll(l => l.Product.ProductID == product.ProductID);
}
However, I don't have the intellisense for the RemoveAll method. [:(]
Public Sub RemoveLine(ByVal product As ProductModel)
Lines.RemoveAll(Function(c) c.Product.ProductID = product.ProductId)
End Sub
View 7 Replies
Mar 30, 2010
[QueryInterceptor("Somethings")]
public Expression<Func<Something, bool>> OnSomethings()
{
// Code here
}
I had a view guesses, looked on msdn, but there are no examples matching the way that that is used.
View 1 Replies
Feb 21, 2009
I'm trying to write this as LINQ, [code]I'm a LINQ beginner, so far I'm not quite sure where to use and where not to. If in this case LINQ will do more harm then help, feel free to flame me.You can assume that there is an overload for AddLink() which takes IEnumerable
View 2 Replies
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
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
