Cann't get my only record with OracleDatareader object

[cschang]

I a newbie to VB.net (only about 3 three weeks). I wrote a small module
to upload file to server and send out a e-mail with the upload files.
the codes basics as:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim num As Integer
cart_id = Request.QueryString("cart_id")
num = Request.QueryString("num")
..
End Sub
Sub SubmitButton_Click(ByVal Source As Object, ByVal e As EventArgs)
Dim filepath As String = "D:\Emallupload" ' "D:\Emallupload"
Dim uploadedFiles As HttpFileCollection = Request.Files
Dim strFilenamesCollection As String = ""
Dim i As Integer = 0
Dim arrPostedFile As New ArrayList
Dim fullFileName As String

Do Until i = uploadedFiles.Count
Dim userPostedFile As HttpPostedFile = uploadedFiles(i)
Try
If (userPostedFile.ContentLength > 0) Then
userPostedFile.SaveAs(filepath & "\" & cart_id &
"~" & _
Path.GetFileName(userPostedFile.FileName))

strFilenamesCollection = strFilenamesCollection +
cart_id + "~" + Path.GetFileName(userPostedFile.FileName) + ";"
fullFileName = cart_id & "~" &
Path.GetFileName(userPostedFile.FileName)
arrPostedFile.Add(fullFileName)
End If
Catch ex As Exception
Span1.InnerHtml += "Error:<br>" & ex.Message
End Try
i += 1
Loop
Span1.InnerHtml += strFilenamesCollection & " Has been saved.<p>"
StorePostedFile(cart_id, strFilenamesCollection, arrPostedFile)
End Sub

Private Sub StorePostedFile(ByRef cart_id As String, ByRef
strFilenamesCollection As String, ByRef af As ArrayList)
OracleConnection(ConfigurationSettings.AppSettings("strCon"))
Dim sTrans As OracleTransaction
oCon.Open()
sTrans = oCon.BeginTransaction()
Dim oCmd As New OracleCommand("set_order_attachments", oCon,
sTrans)
oCmd.CommandType = CommandType.StoredProcedure
oCmd.Parameters.Add("p_cart_id", OracleType.VarChar, 15).Value
= cart_id
oCmd.Parameters.Add("p_attachment", OracleType.VarChar,
255).Value = strFilenamesCollection
Try
oCmd.ExecuteNonQuery()
sTrans.Commit()
EmailPostedFile(cart_id, af, oCon)
Catch e As Exception
...
Finally
....
End Try

End Sub

Private Sub EmailPostedFile(ByRef cart_id As String, ByRef fn As
ArrayList, ByRef oCon As OracleConnection)
Dim ..
Dim sTrans As OracleTransaction
sTrans = oCon.BeginTransaction()
Dim eCmd As New OracleCommand("get_vendor_email_info", oCon,
sTrans)
eCmd.CommandType = CommandType.StoredProcedure
eCmd.Parameters.Add("p_cart_id", OracleType.VarChar, 15).Value
= cart_id
eCmd.Parameters.Add("o_FirstName", OracleType.VarChar,
40).Direction = ParameterDirection.Output
eCmd.Parameters.Add("o_LastName", OracleType.VarChar,
40).Direction = ParameterDirection.Output

Try
eCmd.ExecuteReader()
Dim myReader As OracleDataReader = eCmd.ExecuteReader()

If myReader.Read Then
FirstName = myReader.GetString(0)
LastName = myReader.GetString(1)
Else
...
End If
Catch e As Exception
....
Finally
..
End Try
If noEmail Then
.. rest of email codes
End If
End Sub

Although the first Sub work (an Insert), I had dificult to run the
second Sub, which selected one row back. When I used the
OracleDataReader object, the myReader.read was always false even I have
tested outside that there was definetely had a record return. Since the
myreader.read is a standard way of syntax. I could not figure out what
went wrong or missing. Can anyone help me out ? Thanks.

C Chang

 

[David Browne]

I a newbie to VB.net (only about 3 three weeks). I wrote a small module to
upload file to server and send out a e-mail with the upload files. the
codes basics as:

Private Sub EmailPostedFile(ByRef cart_id As String, ByRef fn As
ArrayList, ByRef oCon As OracleConnection)
Dim ..
Dim sTrans As OracleTransaction
sTrans = oCon.BeginTransaction()
Dim eCmd As New OracleCommand("get_vendor_email_info", oCon,
sTrans)
eCmd.CommandType = CommandType.StoredProcedure
eCmd.Parameters.Add("p_cart_id", OracleType.VarChar, 15).Value =
cart_id
eCmd.Parameters.Add("o_FirstName", OracleType.VarChar,
40).Direction = ParameterDirection.Output
eCmd.Parameters.Add("o_LastName", OracleType.VarChar,
40).Direction = ParameterDirection.Output

Try
eCmd.ExecuteReader()
Dim myReader As OracleDataReader = eCmd.ExecuteReader()

ExecuteReader is for queries, not stored procedures. Just run
eCmd.ExecuteNonQuery and examine your output parameters.

David

 

[Morgan Vermef]

yes u can use ExecuteReader with stored procedures in Oracle you will need
to use a RefCursor

TYPE gCursor IS REF CURSOR; -- creating a package this can be global in
scope for all datareader procedures.

CREATE PROCEDURE GetFoo
AS
begin
open gCursor for
select * from foo;
close gCursor
end;
end GetFoo;

Only draw back is that you will have to include an output parameter for each
procedure defined this way in this manner

eCmd.Parameters.Add(new OracleParameter("retCursor",
OracleType.RefCursor).Direction = ParameterDirection.Output

this is kinda of deceiving since your declaring a parameter in the Procedure
yourself...

Morgan

 

[Morgan Vermef]

adendum to previous post... change noted inline..


TYPE gCursor IS REF CURSOR; -- creating a package this can be global in
scope for all datareader procedures.

CREATE PROCEDURE GetFoo
AS
begin
open gCursor for
select * from foo;
close gCursor; <<< this may cause compiler error in oracle for .NET
end;
end GetFoo;


Morgan

 

[Morgan Vermef]

I am goofing this all up

CREATE PROCEDURE GetFoo(cursor OUT gCursor)

would be the procedure definition..