VB.NET - Linq To SQL - Invalid Column

I had an existing application using Linq To SQL linked to a LocalDB mdf.

The MDF is located in the main project folder.

I also copied the MDF to the bin folder, which is what the app uses when debugging from VS.

I've confirmed in SQL Server Management Studio that the field exists in both MDFs.

I used a DBML/EDMX tool to update my Linq Diagram to reflect the added column.

I also confirmed the field is properly shown in the database.designer.vb class.

But, when I run the app from VS and run a LINQ query on the entire table

Dim ticketsForBatch = From ticket In BatchTickets.MemberPanTickets _
						Order By ticket.BatchID
						Where ticket.BatchID = _currentBatchId

Open in new window


I get this error:  Invalid column name 'BillTo'.

BillTo is the column I added. That linq query worked fine before adding the new column.

I'm stumped. The field seems to exist in the underlying database, the LINQ Diagram, and the LINQ class.

What could be causing this?

Drilling further, I expanded the MemberPanTicket variable and got this as the SQL the LINQ creates:

SELECT [t0].[TicketID], [t0].[TicketNbr], [t0].[BatchID], [t0].[LocationID], [t0].[TicketDt], [t0].[Bottle], [t0].[EnteredDt], [t0].[ExcelDt], [t0].[SSMA_TimeStamp], [t0].[CommodityID], [t0].[BillTo], [t0].[Other], [t0].[WC], [t0].[BL], [t0].[RailCar], [t0].[Lot], [t0].[General], [t0].[Sta], [t0].[DoGrade], [t0].[DoProtein], [t0].[DoFalling], [t0].[DoMalt], [t0].[DoOther]
FROM [dbo].[MemberPanTickets] AS [t0]
WHERE [t0].[BatchID] = 27
ORDER BY [t0].[BatchID]

Open in new window


I copied that sql to Management Studio and ran it against the MDF the app is using.

No error.

I also confirmed the app is pointing to the right mdf by renaming the mdf and running the app. It gave a compile error for not finding the mdf. So, it's definitely pointing to the right database.

So, there must be something wrong with the Linq code that ties the Linq class to the actual table.

How do I track down that code?

EDIT: More info...

Here's the mapping code in PanTickets.designer.vb:

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_BillTo", DbType:="NVarChar(5)", UpdateCheck:=UpdateCheck.Never)> _
	Public Property BillTo() As String
		Get
			Return Me._BillTo
		End Get
		Set(value As String)
			If (String.Equals(Me._BillTo, Value) = False) Then
				Me.OnBillToChanging(Value)
				Me.SendPropertyChanging()
				Me._BillTo = Value
				Me.SendPropertyChanged("BillTo")
				Me.OnBillToChanged()
			End If
		End Set
	End Property

Open in new window


It looks the same, structurally, as the other fields in designer.

Does any of that help?
KapriceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

teebonProduct ManagerCommented:
Hi Kaprice,

Have you tried tracing the query with SQL profiler to check the actual query at the point of error?

Tracing with SQL Profiler
0
KapriceAuthor Commented:
When I tried it in the past, it wouldn't let me because it's a LocalDB mdf, and thus only allows a single connection. The profiler takes up a second connection.

But, as I posted, above, I used the expanders in debug mode to get to the actual sql. I copied it. Closed the app. And, ran the sql in Mgt Studio.

No error.
0
KapriceAuthor Commented:
More info...

I added this code just above the code that gives me the error:

	
		Dim BT = From t In BatchTickets.MemberPanTickets _
		Select t.BillTo

		If BT.Count > 0 Then
			MsgBox("OK")
		Else
			MsgBox("None")
		End If

Open in new window



When I run this, I get the Msgbox: "OK"

But, when I hit the OK button, it continues with the error on the next block.

Why would the first block work and the next give an error?

Maybe I'll try adding a select to the Linq query, though that's never been needed, before.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

KapriceAuthor Commented:
More Info...

In this code block:

		Dim ticketsForBatch = From t In BatchTickets.MemberPanTickets _
							Order By t.BatchID
							Where t.BatchID = _currentBatchId
							Select t

		MsgBox(ticketsForBatch.Count)
		Me.MemberPanTicketBindingSource.DataSource = ticketsForBatch

Open in new window


The Msgbox shows the count.

The error actually hits on the following line (setting the DataSource)

Does that help?
0
teebonProduct ManagerCommented:
Can you check the definition of MemberPanTicketBindingSource? You may want to share the definition code if require further investigation.
0
KapriceAuthor Commented:
Where would I find the definition? There are quite a few mentions of it in the formname.designer.

Should I copy the entire designer for the form? Or, can you direct me to a specific section you need?
0
teebonProduct ManagerCommented:
In your designer, click on MemberPanTicketBindingSource, check its current Type in properties window, make sure it's pointing to the correct class.
0
KapriceAuthor Commented:
I don't see a Type in properties. These are the properties:

Name
AllowNew
DataMember
DataSource
Filter
GenerateMember
Modifiers
Sort


BTW, the DataSource is set to: MemberTicketEntry.MemberPanTicket

Does any of that help?
What am I missing?
0
teebonProduct ManagerCommented:
Yes, I meant datasource. Can you see whether checkMemberTicketEntry.MemberPanTicket is the latest class with your new field?
0
KapriceAuthor Commented:
Sorry, teebon, for my ignorance. I'm fairly new to VS. I'm a long time VBA developer making the transition.

Other than what I posted in my original question, I don't know where to look to confirm I have the latest class. I showed some of the designer code and where BillTo is mapping to the class.

Also, The DataSource is MemberTicketEntry.MemberPanTicket and when I open the DataSources explorer and view the fields in MemberPanTicket, BillTo is in the list.

Where else should I look?
0
teebonProduct ManagerCommented:
Kaprice,

Could you post the full detailed error message?
0
KapriceAuthor Commented:
System.Data.SqlClient.SqlException was unhandled
  Class=16
  ErrorCode=-2146232060
  HResult=-2146232060
  LineNumber=1
  Message=Invalid column name 'BillTo'.
  Number=207
  Procedure=""
  Server=(LocalDB)\v11.0
  Source=.Net SqlClient Data Provider
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.ExecuteReader()
       at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
       at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
       at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
       at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at System.Data.Linq.Provider.BindingList.Create[T](DataContext context, IEnumerable`1 sequence)
       at System.Data.Linq.DataQuery`1.GetNewBindingList()
       at System.Data.Linq.DataQuery`1.System.ComponentModel.IListSource.GetList()
       at System.Windows.Forms.ListBindingHelper.GetList(Object list)
       at System.Windows.Forms.ListBindingHelper.GetList(Object dataSource, String dataMember)
       at System.Windows.Forms.BindingSource.ResetList()
       at System.Windows.Forms.BindingSource.set_DataSource(Object value)
       at MemberTicketEntry.FrmTicketForm.LoadBatch() in F:\zUsers\Kap\Documents\Visual Studio 2013\Projects\LCT\MemberTicketEntry\MemberTicketEntry\frmTicketForm.vb:line 853
       at MemberTicketEntry.FrmTicketForm.set_BatchId(Int32 value) in F:\zUsers\Kap\Documents\Visual Studio 2013\Projects\LCT\MemberTicketEntry\MemberTicketEntry\frmTicketForm.vb:line 296
       at MemberTicketEntry.frmMain.ShowNewForm(Object sender, EventArgs e) in F:\zUsers\Kap\Documents\Visual Studio 2013\Projects\LCT\MemberTicketEntry\MemberTicketEntry\frmMain.vb:line 34
       at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
       at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStripMenuItem.ProcessCmdKey(Message& m, Keys keyData)
       at System.Windows.Forms.ToolStripManager.ProcessShortcut(Message& m, Keys shortcut)
       at System.Windows.Forms.ToolStripManager.ProcessCmdKey(Message& m, Keys keyData)
       at System.Windows.Forms.ContainerControl.ProcessCmdKey(Message& msg, Keys keyData)
       at System.Windows.Forms.Form.ProcessCmdKey(Message& msg, Keys keyData)
       at System.Windows.Forms.Control.ProcessCmdKey(Message& msg, Keys keyData)
       at System.Windows.Forms.Control.ProcessCmdKey(Message& msg, Keys keyData)
       at System.Windows.Forms.ContainerControl.ProcessCmdKey(Message& msg, Keys keyData)
       at System.Windows.Forms.Form.ProcessCmdKey(Message& msg, Keys keyData)
       at System.Windows.Forms.Control.PreProcessMessage(Message& msg)
       at System.Windows.Forms.Control.PreProcessControlMessageInternal(Control target, Message& msg)
       at System.Windows.Forms.Application.ThreadContext.PreTranslateMessage(MSG& msg)
       at System.Windows.Forms.Application.ThreadContext.System.Windows.Forms.UnsafeNativeMethods.IMsoComponent.FPreTranslateMessage(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at MemberTicketEntry.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
       at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
       at System.Activator.CreateInstance(ActivationContext activationContext)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:
0
teebonProduct ManagerCommented:
Hi Kaprice,

Try deleting the edmx file and regenerate it again. Sometimes manually editing in edmx won't work.
0
KapriceAuthor Commented:
This is Linq to SQL, not Entity Framework. Does Linq to SQL generate an EDMX file? If so, I can't find it. Where would I look?

Or, did you mean DMBL for Linq to SQL? Are those equivalents?
0
KapriceAuthor Commented:
More info...

I changed the code block to:

	Dim ticketsForBatch = From t In BatchTickets.MemberPanTickets _
		Order By t.BatchID
		Where t.BatchID = _currentBatchId

		For Each t In ticketsForBatch *** Error Happens Here
			Debug.Print(t.TicketID, t.TicketNbr)
		Next

Open in new window


And, the error happens when the For loop tries to access the query results.

So, I think we can rule out any problems with the form's datasource because the error occurs even when the ds is not referenced.

Also, I deleted the DMBL file and recreated it. Same problem.


So, I added a select statement once without the offending column (BillTo) and once with:

Dim ticketsForBatch = From t In BatchTickets.MemberPanTickets _
		Order By t.BatchID
		Where t.BatchID = _currentBatchId
		Select t.TicketID, t.TicketNbr, t.BillTo

		For Each t In ticketsForBatch
			Debug.Print(t.TicketID, t.TicketNbr)
		Next

Open in new window


When the Select clause does not include BillTo, there's no error. When it does, I get the error.

What's REALLY odd is that the LINQ Intellisense gave me BillTo as an option when typing "t."

How can Intellisense find it if it's invalid? Baffled!

What's next?
0
KapriceAuthor Commented:
The problem turned out to be the Connection String. I had linked one MDF in my project but my connection string was pointing to another.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KapriceAuthor Commented:
No working solution was given, so I hired another programmer to help me track down the problem. This is what he discovered.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.