Sqldatareader抛出providerbase.fieldnamelookup.getordinal（string fieldname）超出索引错误

本文介绍了Sqldatareader抛出providerbase.fieldnamelookup.getordinal（string fieldname）超出索引错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有一个基于表单的应用程序，在从下拉框中选择SKU之后，通过从SQL读取数据来填充表单上的许多不同文本框。在我的Sql datareader中，我有一个条件来读取不同的查询。条件是基于用户触发器通过表单切换1或2。默认情况下，如果用户只是从下拉框中选择SKU，则应用Dim ShiftSnapShot作为Integer = 1。但是，如果用户在从下拉框中选择SKU后单击表单上的按钮，则ShiftSnapShot = 2. 基于此，我有以下Datareader子例程 尝试 Dim strSQL 作为 字符串 Dim connstring As String connstring = 数据源= PLP01DB004R;初始目录= MMS;集成安全性=真 如果 ShiftSnapShot = 1 那么 strSQL = 从PostBlendGrading中选择前1个CountPerPound，其中CountPe通过entrydatetime描述rPound不是空订单 否则 strSQL = 选择avg（CountPerPound）作为来自PostBlendGrading的CPP，其中CountPerPound不为null，并且在DATEADD（Hour，-8，EntryDateTime）和EntryDateTime之间的entrydatetime 结束 如果 Dim sqlconn 作为 新 SqlConnection Dim sqlcmd 作为 新 SqlCommand Dim sqldr As SqlDataReader sqlconn.ConnectionString = connstring sqlconn.Open（） sqlcmd。 Connection = sqlconn sq lcmd.CommandText = strSQL sqldr = sqlcmd.ExecuteReader 返回 sqldr sqlconn.Close（） Catch ex As 例外 Dim msg 作为 字符串 = 从PostBlendGradingTbl检索数据时出错 MsgBox（msg& vbLf& 日志文件以& amp; vbLf& ex.Message& vbLf& ex.StackTrace） ErrorLog（msg，ex.StackTrace， PostBlendGradingTbl数据提取 - 错误） 结束 尝试 结束 功能 此数据阅读器用于在以下子程序的帮助下填充文本框 公共 Sub PopulateDataFromPostBlendGradingTbl（） 尝试 Dim PostBlendGradingTblDataReader As SqlDataReader Dim CurrentDate 作为 日期 =今天。日期 PostBlendGradingTblDataReader = GetDataFromPostBlendGradingTbl（） Dim PostBlendGradingTbl_GravityDataReader As SqlDataReader PostBlendGradingTbl_GravityDataReader = GetDataFromPostBlendGradingTbl_Gravity（） 如果 PostBlendGradingTblDataReader Nothing 然后 MsgBox（ PostBlendGradingTbl为空=& Str（CurrentDate.Month）& Year =& Str（CurrentDate.Year）） Else while PostBlendGradingTblDataReader.Read 如果 PostBlendGradingTblDataReader（ CountPerPound ）.ToString<> 和 ShiftSnapShot = 1 然后 我 .txtCPP.Text = CType （PostBlendGradingTblDataReader（ CountPerPound ）.ToString， Double ） ElseIf PostBlendGradingTblDataReader（ CPP）。ToString<> 和 ShiftSnapShot = 2 然后 我 .txtCPP.Text = CType （PostBlendGradingTblDataReader（ CPP ）.ToString， Double ） 否则 我 .txtCPP.Text = 0 。 0 结束 如果 结束 while 结束 如果 如果 PostBlendGradingTbl_GravityDataReader Nothing 然后 MsgBox（ 给定月份的PostBlendGradingTbl为空=& Str（CurrentDate.Month）& Year =& Str（CurrentDate.Year）） Else while PostBlendGradingTbl_GravityDataReader.Read 如果 PostBlendGradingTbl_GravityDataReader（ AverageSpecificGravity ）.ToString<> 然后 Me .txtSpGravity.Text = CType （PostBlendGradingTbl_GravityDataReader（ AverageSpecificGravity）。ToString， Double ） 否则 我 .txtSpGravity.Text = 0 。 0 结束 如果 结束 ' 调用UpdateForm1Object（） 结束 如果 Catch ex As 例外 Dim msg As String = 从PostBlendGradingTbl填充错误 MsgBox（msg& vbLf& 日志文件以& amp; vbLf& ex.Message& vbLf& ex.StackTrace） ErrorLog（msg，ex.StackTrace， PostBlendGradingTbl - 错误） 结束 尝试 结束 Sub 但是当我有ShiftSnapShot = 2时，PopulateDataFromPostBlendGradingTbl抛出providerbase.fieldnamelookup.getordinal（string fieldname）错误。这个问题与我在查询中使用avg（CountPerPound）的方式有关。如果我选​​择CountPerPound而不是select avg（CountPerPound）那么它工作正常并且不会给我一个错误。由于某种原因，datareader无法识别我的列CPP，这是一个基于avg（CountPerPound）作为CPP的已定义列。请帮助解决方案 它'' s因为您的第一个查询返回一个名为 CountPerPount 的字段，而您的第二个查询返回一个名为 CPP的字段 然后你有一段代码... 如果 PostBlendGradingTblDataReader（ CountPerPound）。ToString<> 和 ShiftSnapShot = 1 然后 我 .txtCPP.Text = CType （PostBlendGradingTblDataReader（ CountPerPound ）.ToString， Double ） ElseIf PostBlendGradingTblDataReader（ CPP）。ToString<> 和 ShiftSnapShot = 2 然后 我 .txtCPP.Text = CType （PostBlendGradingTblDataReader（ CPP ）.ToString， Double ） 但是如果ShiftSnapShot = 2则没有名为 CountPerPount 返回 - 代码将抛出错误。 我不确定VB.NET如何评估 if 语句 - 如果在第一个条件失败时失败并且没有尝试评估下一个条件，那么只需先检查ShiftSnapShot的值，然后再检查返回的列...例如 如果ShiftSnapShot = 1和PostBlendGradingTblDataReaderCountPerPound）。ToString<> 或者在你的sql中使用相同的列名，即 选择avg（CountPerPound）作为CountPerPound I have a form based application where after selecting a SKU from drop down box, many different text boxes on the forms get populated by reading data from SQL. In my Sql datareader I have a condition to read different queries. The condition is a switch of 1 or 2 based on user triggers through the form. By default if the user just selects SKU from drop down box then Dim ShiftSnapShot as Integer = 1 gets applied. But if the user clicks on a button on the form after selecting a SKU from drop down box then ShiftSnapShot = 2.Based on this I have the following Datareader subroutineTry Dim strSQL As String Dim connstring As String connstring = "Data Source=PLP01DB004R;Initial Catalog=MMS;Integrated Security=True" If ShiftSnapShot = 1 Then strSQL = "select top 1 CountPerPound from PostBlendGrading where CountPerPound is not null order by entrydatetime desc" Else strSQL = "select avg(CountPerPound) as CPP from PostBlendGrading where CountPerPound is not null and entrydatetime between DATEADD(Hour, -8, EntryDateTime) and EntryDateTime" End If Dim sqlconn As New SqlConnection Dim sqlcmd As New SqlCommand Dim sqldr As SqlDataReader sqlconn.ConnectionString = connstring sqlconn.Open() sqlcmd.Connection = sqlconn sqlcmd.CommandText = strSQL sqldr = sqlcmd.ExecuteReader Return sqldr sqlconn.Close() Catch ex As Exception Dim msg As String = "Error retrieving data from PostBlendGradingTbl" MsgBox(msg & vbLf & "The log file is written in " & vbLf & ex.Message & vbLf & ex.StackTrace) ErrorLog(msg, ex.StackTrace, "PostBlendGradingTbl Data Extraction - Error") End TryEnd FunctionThis data reader is used to populate textbox with the help of following subroutinePublic Sub PopulateDataFromPostBlendGradingTbl() Try Dim PostBlendGradingTblDataReader As SqlDataReader Dim CurrentDate As Date = Today.Date PostBlendGradingTblDataReader = GetDataFromPostBlendGradingTbl() Dim PostBlendGradingTbl_GravityDataReader As SqlDataReader PostBlendGradingTbl_GravityDataReader = GetDataFromPostBlendGradingTbl_Gravity() If PostBlendGradingTblDataReader Is Nothing Then MsgBox("PostBlendGradingTbl empty for the given Month = " & Str(CurrentDate.Month) & " Year = " & Str(CurrentDate.Year)) Else While PostBlendGradingTblDataReader.Read If PostBlendGradingTblDataReader("CountPerPound").ToString <> "" And ShiftSnapShot = 1 Then Me.txtCPP.Text = CType(PostBlendGradingTblDataReader("CountPerPound").ToString, Double) ElseIf PostBlendGradingTblDataReader("CPP").ToString <> "" And ShiftSnapShot = 2 Then Me.txtCPP.Text = CType(PostBlendGradingTblDataReader("CPP").ToString, Double) Else Me.txtCPP.Text = 0.0 End If End While End If If PostBlendGradingTbl_GravityDataReader Is Nothing Then MsgBox("PostBlendGradingTbl empty for the given Month = " & Str(CurrentDate.Month) & " Year = " & Str(CurrentDate.Year)) Else While PostBlendGradingTbl_GravityDataReader.Read If PostBlendGradingTbl_GravityDataReader("AverageSpecificGravity").ToString <> "" Then Me.txtSpGravity.Text = CType(PostBlendGradingTbl_GravityDataReader("AverageSpecificGravity").ToString, Double) Else Me.txtSpGravity.Text = 0.0 End If End While 'Call UpdateForm1Object() End If Catch ex As Exception Dim msg As String = "Error Populating from the PostBlendGradingTbl" MsgBox(msg & vbLf & "The log file is written in " & vbLf & ex.Message & vbLf & ex.StackTrace) ErrorLog(msg, ex.StackTrace, "PostBlendGradingTbl - Error") End TryEnd SubHowever when I have ShiftSnapShot = 2 the PopulateDataFromPostBlendGradingTbl throws providerbase.fieldnamelookup.getordinal(string fieldname) error. the problem is something related to the way I have been using avg(CountPerPound) in my query.. If I select CountPerPound instead of select avg(CountPerPound) then it works fine and does not give me an error. for some reason the datareader is not able to identofy my column CPP which is a defined column based on avg(CountPerPound) as CPP.. Please Help 解决方案 It''s because your 1st query is returning a field called CountPerPount and your 2nd query is returning a field called CPPYou then have a section of code ...If PostBlendGradingTblDataReader("CountPerPound").ToString <> "" And ShiftSnapShot = 1 Then Me.txtCPP.Text = CType(PostBlendGradingTblDataReader("CountPerPound").ToString, Double) ElseIf PostBlendGradingTblDataReader("CPP").ToString <> "" And ShiftSnapShot = 2 Then Me.txtCPP.Text = CType(PostBlendGradingTblDataReader("CPP").ToString, Double)But if ShiftSnapShot = 2 then there is no column called CountPerPount returned - and the code will throw an error.I''m not sure how VB.NET evaluates an if statement - if it fails when the first condition fails and does not attempt to evaluate the next condition, then simply check the value of ShiftSnapShot first and then the returned column ...e.g.If ShiftSnapShot = 1 And PostBlendGradingTblDataReader"CountPerPound").ToString <> ""Alternatively use the same column name in your sql i.e.select avg(CountPerPound) as CountPerPound 这篇关于Sqldatareader抛出providerbase.fieldnamelookup.getordinal（string fieldname）超出索引错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！