OracleDataReader Class HasRows Method

[johneydluca]

Hi,

I have a problem something which we all used to have in Classic ASP where Recordset used to return -1 as recordcount.

What i need is to Add 1 to max ID returned from the table to Insure unique value to be inserted to table. If there are no values then I want to add 1 as primary key otherwise existing Max_value + 1.

Here I am using OracleDataReader Classes method HasROws to find if there any rows are returned. But it is always showing True even when I know there no records in my table.
So obviously since HasRows was returning yes, my program was failing next at line objReader.GetInt32(0)


SO in order to find if there are any existing records I was using HasRows with Simple SQL. So is RecordsAffected returns -1 no matter there is data or not.

I tried using simple SQL query which returns Max value as per first line in following code, but since HasRows is alwaystrue I had to force SQL to return 0 as MSGCNT.

Is there any way I can use simple SQL instead of forcing to return 0?

following is my code


Dim myConnection1 As New OracleConnection(ConnectionString)

'Dim sqlCount = "select max(MSG_RULE_ID) AS MSGCNT from ekt_comm_stmt_msg_rule"

Dim sqlCount = "select decode(max(MSG_RULE_ID), 0, 0, null, 0, max(MSG_RULE_ID)) AS MSGCNT from message_rules"

Dim objOraCommand As New OracleCommand(sqlCount, myConnection1)
myConnection1.Open()
Dim objReader As OracleDataReader = objOraCommand.ExecuteReader(CommandBehavior.CloseConnection)

objReader.Read()
Response.Write( "Has rows " & objReader.HasRows & "<br>")

Response.Write( "objReader.RecordsAffected " & objReader.RecordsAffected & "<br>")

If objReader.HasRows Then
Session("NextID1") = objReader.GetInt32(0)
objReader.Close()
Else
Session("NextID1") = 0
End If




Thanks in advance

jd