Sqldatareader抛出providerbase.fieldnamelookup.getordinal（string fieldname）超出索引错误 [英] Sqldatareader throwing providerbase.fieldnamelookup.getordinal(string fieldname) out of index error

问题描述

我有一个基于表单的应用程序，在从下拉框中选择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 = < span class =code-string> 从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<>   然后 
 < span class =code-keyword> Me  .txtSpGravity.Text =  CType （PostBlendGradingTbl_GravityDataReader（  AverageSpecificGravity）。ToString， Double ）
 否则 
 我 .txtSpGravity.Text =  0 。 0  
 结束 如果 
 结束   
 ' < span class =code-comment>调用UpdateForm1Object（） 
 
 结束 如果 
 
  Catch  ex  As 例外
  Dim  msg  As   String  = < span class =code-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则没有名为<$ c $的列c> 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 subroutine

Try

        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 Try

End Function

This data reader is used to populate textbox with the help of following subroutine

Public 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 Try
End Sub

However 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 CPP
You 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）超出索引错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！