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

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

David

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
Show quoteHide quote

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


Show quoteHide quote

I am goofing this all up

CREATE PROCEDURE GetFoo(cursor OUT gCursor)

would be the procedure definition..

Show quoteHide quote

I want help with the jet 4.0 provider..
DataAdapter call ExecuteReader instead of ExecuteNonQuery
Add Connection to ADO Database
Different results with the same query?!?
CommandBuilder InsertCommand Update
ASP.NET vs VB.NET
Returning error from stored proc
ADO and ADO.net in the same transaction
How to INSERT pure <NULL> Into SmallDataTime Field (MS SQL Server)
ADO unable to get values