从sql-vb.net检索最大值 [英] Retrieving maximum value from sql-vb.net

问题描述

我正在尝试从sql中检索最大值-vb.net

I am trying to retrieve a maximum value from sql - vb.net

这是我的代码:我收到此错误:

This is my code: I get this error:

无法将类型为'System.DBNull'的对象强制转换为类型 'Oracle.DataAccess.Client.OracleDataReader'

Unable to cast object of type 'System.DBNull' to type 'Oracle.DataAccess.Client.OracleDataReader'

请帮助. :(

Dim cmd2 As New OracleCommand
cmd2.Connection = conn
'SELECT MAX(LASTNO) FROM d001005 where lbrcode = '104' and CODE1 = 'CASH' and lnodate = '14-NOV-08' 
Dim datepara As String = Date.Now.ToString("dd-MMM-yy")
'datepara = "14-NOV-08"
cmd2.CommandText = "SELECT MAX(LASTNO) FROM d001005 where lbrcode = '" + lbr + "' and CODE1 = 'CASH' and lnodate = '" + datepara + "'"
cmd2.CommandType = CommandType.Text
Dim dr2 As OracleDataReader = cmd2.ExecuteScalar
Dim result As Decimal = dr2.Item("LASTNO")

推荐答案

您正在使用集合函数查找名称为"LASTNO"的项目取最大值.此外， ExecuteScalar 返回第一行的第一值！另一点是，您应该使用 OracleParameter 用于任何类型的动态语句，防止SQL注入！

There is no item with the name "LASTNO" as you are using an aggregate function to find out the maximum value. Furthermore ExecuteScalar returns the first value of the first row! Another point is, that you should use OracleParameter for any kind of dynamic statement, to prevent SQL injection!

Dim cmd2 As New OracleCommand
cmd2.Connection = conn
Dim datepara As String = Date.Now.ToString("dd-MMM-yy") ' see hint below

cmd2.CommandText = "SELECT MAX(LASTNO) as maxLastNo FROM d001005 where lbrcode = :lbr and CODE1 = 'CASH' and lnodate = :lnodate"

' add parameter value directly
cmd2.Parameters.AddWithValue("lbr", lbr)   ' lbr seems to be your var which holds the according value

' or create parameter object and set type and add it afterwards
Dim param as new OracleParameter
param.ParameterName = "lnodate"
param.Value = datepara    ' or any other value (furthermore: no need to convert to string)
param.ParameterType = OracleType.DateTime ' or OracleType.Varchar which is set by default
cmd2.CommandType = CommandType.Text

Dim val = cmd2.ExecuteScalar()
if Not val is Nothing then
    Dim maxValue as Decimal = Convert.ToDecimal(val)
    Console.WriteLine(maxValue)
End if

这篇关于从sql-vb.net检索最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！