question

technette avatar image
technette asked

SQL DataReader Entity Framework

I spent a week trying to figure this one out and have been unsuccessful. I found that getting data through web services is much cleaner than using domain services. So I'm now rewriting queries using web services. I have successfully returned data through the web services for one module. For another query, however, get the following error when I review the inner exception and no data is returning from the database. It looks like the data reader is not even reading... **In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.** In my stored procedure and in the entity, there is an Alias (Job As JobNumber). Will the data reader have trouble evaluating this? Public Function SPEmpJOBS(ByVal Contract As String) As List(Of EmpJobs_vw) Dim list As List(Of EmpJobs_vw) = New List(Of EmpJobs_vw)() Using conn As SqlConnection = New SqlConnection("MyConnection") conn.Open() Using dcmd As SqlCommand = New SqlCommand("MyStoredProcedure", conn) Dim prms As New SqlParameter("@Contract", SqlDbType.VarChar, 11) prms.Value = Contract & "%" dcmd.Parameters.Add(prms) dcmd.CommandType = CommandType.StoredProcedure Using reader As SqlDataReader = dcmd.ExecuteReader While reader.Read() Dim d As DateTime If reader("TermDate") IsNot DBNull.Value Then d = DateTime.MinValue Else d = Convert.ToDateTime(reader("TermDate")) End If list.Add(New EmpJobs_vw() With { _ .Contract = reader("Contract").ToString(), _ .ContractDesc = reader("ContractDesc").ToString(), _ .Emp = reader("Emp").ToString(), _ .EmpName = reader("EmpName").ToString(), _ .EntityName = reader("EntityName").ToString(), _ .JobNumber = reader("JobNumber").ToString(), _ .JobTitleDesc = reader("JobTitleDesc").ToString(), _ .YtdHours = reader("YtdHours").ToString(), _ .YearIncur = reader("YearIncur").ToString(), _ .TermDate = d _ }) End While conn.Close() End Using Return list End Using End Using End Function StoredProcedure: ALTER PROCEDURE [dbo].[EmpJOBS] @Contract varchar (11) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT dbo.Employee.Emp, dbo.Employee.EmpName, dbo.JobTitles.JobTitleDesc, dbo.Entity.EntityName, dbo.JobKeys.Job, dbo.EmployeeJobs.YtdHours, dbo.EmployeeJobs.YearIncur, dbo.Employee.TermDate, dbo.JobKeys.[Contract], dbo.[Contract].ContractDesc FROM dbo.EmployeeJobs INNER JOIN dbo.Employee ON dbo.EmployeeJobs.Emp = dbo.Employee.Emp LEFT JOIN dbo.JobKeys ON dbo.EmployeeJobs.Job = dbo.JobKeys.Job LEFT JOIN dbo.JobTitles ON dbo.Employee.JobTitleCode = dbo.JobTitles.JobTitleCode LEFT JOIN dbo.Entity ON dbo.Employee.Entity = dbo.Entity.Entity LEFT JOIN dbo.[Contract] ON dbo.JobKeys.[Contract] = dbo.[Contract].[Contract] WHERE (dbo.JobKeys.[Contract] = '@Contract') END
sqldatareaderentityframework
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
I see one problem which prevents your proc from returning any data: in you code you have **prms.Value = Contract & "%"**
yet in the proc you have **WHERE (dbo.JobKeys.[Contract] = '@Contract')** The where clause should read LIKE in place of the equal sign. Another problem I think is with this part: Dim d As DateTime If reader("TermDate") IsNot DBNull.Value Then d = DateTime.MinValue Else d = Convert.ToDateTime(reader("TermDate")) End If The logic appears to be reversed. Oleg
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Remove the single quotes around @Contract in the WHERE clause
3 Likes 3 ·
Oleg, thank you for responding. I made the changes, changing the equal sign to LIKE in the stored procedure and changing the logic in the reading of the datetime to: Dim d As DateTime If reader("TermDate") Is DBNull.Value Then d = DateTime.MinValue Else d = Convert.ToDateTime(reader("TermDate")) End If I still don't get data.
0 Likes 0 ·
Thank you very much Oleg and Scot! I really appreaciate your keen code eye. :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.