code_junkie

How do you use ExecuteScalar to return a single value from an Oracle Database?

Nov 12th, 2011
93
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.31 KB | None | 0 0
  1. Dim oracleConnection As New OracleConnection
  2. oracleConnection.ConnectionString = LocalConnectionString()
  3.  
  4. Dim cmd As New OracleCommand()
  5. Dim o racleDataAdapter As New OracleClient.OracleDataAdapter
  6.  
  7. cmd.Connection = oracleConnection
  8. cmd.CommandText = "FALCON.CMS_DATA.GET_MAX_CMS_TH"
  9. cmd.CommandType = CommandType.StoredProcedure
  10.  
  11. cmd.Parameters.Add(New OracleParameter("i_FACID_C", OracleType.Char)).Value = facilityShortName
  12. cmd.Parameters.Add(New OracleParameter("RS_MAX", OracleType.Cursor)).Direction = ParameterDirection.Output
  13.  
  14. Try
  15. Using oracleConnection
  16. oracleConnection.Open()
  17. Using oracleDataAdapter
  18. oracleDataAdapter = New OracleClient.OracleDataAdapter(cmd)
  19.  
  20. Dim workingDataSet As DataSet
  21. oracleDataAdapter.TableMappings.Add("OutputSH", "RS_MAX")
  22. workingDataSet = New DataSet
  23. oracleDataAdapter.Fill(workingDataSet)
  24.  
  25.  
  26. For Each row As DataRow In workingDataSet.Tables(0).Rows
  27.  
  28. Return CDate(row("MAXDATE"))
  29.  
  30. Next
  31. End Using
  32. End Using
  33.  
  34. oracleConnection.Open
  35. Dim obj as object 'Object to hold our return value
  36. obj = cmd.ExecuteScalar()
  37. oracleConnection.Close
  38.  
  39. If obj IsNot Nothing then
  40. Return CDate(obj)
  41. end if
Add Comment
Please, Sign In to add comment