stored procedure

lokey · March 3rd, 2005, 08:55 AM

Hi,
My problem is how to insert multiple rows of records using stored procedure. i am using msflexigrid in vb to insert the records in detail table.
Pls help me in this regard
thanx
lokey

Raghavendra_Mudugal · March 22nd, 2005, 05:54 AM

Hello Lokey

As I know, you can insert only one record at a time. So you have to loop from
first to last rows of the msflexgrid and pass the values to the procedure.
If you are using ADO then, use command object with respect to stored procedure,
loop here-> pass new values from the grid to command params and execute it.
I have check it with my code sometime before, i used have more than 600 of rows
in mshflexgrid ans used this logic and it completed in 15 seconds.

With Regards,
Raghavendra Mudugal

BrianWren · March 22nd, 2005, 12:09 PM

You can put all of the values into an array, and pass that array to the stored procedure (SP).
You would then write the SP to extract the values from the array, inserting records in a loop within the SP.

It is probably easier to call the SP repeatedly from a loop than to try to bundle the values and have the SP do the looping.

JelfMaria · April 25th, 2005, 06:34 AM

:)How to call an Oracle procedure using vb6?

BrianWren · April 25th, 2005, 10:28 AM

Create and open a connection object to the DB
Create and instantiate a Command object.
Within its properties, set the active connection to the connection object
Set its command type to stored procedure (such as adCmdStoredProcedure)
Set the command text to the name of the stored procedure.
Add parrameters as necessary, and set their values
run it with the .Execute method.

JelfMaria · April 26th, 2005, 12:52 AM

Can u able to send me a sample for it

BrianWren · April 26th, 2005, 11:44 AM

I have a function for opening a connection to Oracle, called OpenConnection. It fills in the username and password, and so forth. ISessionObject is an ASP Session object, which operates exactly like a collection, with name/value pairs. IRequest is exactly the same, and holds data from an internet request. (This code is used in conjunction with the internet.)

Code:

Public Sub RevDwg(Sn As ISessionObject, Rq As IRequest)

    On Error GoTo Er

    Dim c   As ADODB.Connection:    Set c = OpenConnection(Sn)
    Dim cmd As ADODB.Command:       Set cmd = New ADODB.Command
    Dim prm As ADODB.Parameter:         cmd.CommandType = adCmdStoredProc
    Dim r   As ADODB.Recordset:         cmd.CommandText = "COPY_FS.Create_Rev"  ' Schema and procedure name

    cmd.ActiveConnection = c

    Set prm = New ADODB.Parameter:  prm.Type = adInteger
                                    prm.Direction = adParamInput
                                    prm.Value = Sn("DWG_ID")
                                    cmd.Parameters.Append prm

    ' re-use the object prm.  The 1st one has been added, so 
    ' a reference to it is being held by the command object.
    Set prm = New ADODB.Parameter:  prm.Name = "DST_DWG_REV":  prm.Size = 2
                                    prm.Type = adVarChar
                                    prm.Direction = adParamInput
                                    prm.Value = Sn("Rev")
                                    cmd.Parameters.Append prm
    cmd.Execute

    Sn("A2DwgNum Number") = Rq.Form("SrcFS")
    Exit Sub

Er: SetCustomMessages Sn, , "Revise Error", "An error was encountered when attempting to create the revision."
    Err.Clear
    Exit Sub

End Sub

Retreiveing values through parameters:

Code:

Public Sub GetRevisionStatus(DwgNum As String, Sn As ISessionObject)

    On Error GoTo Er

    Dim cmd As ADODB.Command:               Set cmd = New ADODB.Command
    Dim c   As ADODB.Connection:            Set c = OpenConnection(Sn)
    Dim prm As ADODB.Parameter:             cmd.ActiveConnection = c
                                            cmd.CommandText = "GET_REV_STATUS"
                                            cmd.CommandType = adCmdStoredProc '________________________________

    Set prm = New ADODB.Parameter: prm.Type = adNumeric
                                   prm.Name = "FS_NUM_IN"
                                   prm.Direction = adParamInput
                                   prm.Value = DwgNum
                                   cmd.Parameters.Append prm '________________________________________
    Set prm = New ADODB.Parameter: prm.Type = adVarChar
                                   prm.Name = "FS_REV_OUT"
                                   prm.Direction = adParamOutput
                                   prm.Size = 2
                                   cmd.Parameters.Append prm '________________________________________
    Set prm = New ADODB.Parameter: prm.Type = adNumeric
                                   prm.Name = "FS_NOFORN_OUT"
                                   prm.Direction = adParamOutput
                                   cmd.Parameters.Append prm '________________________________________
    Set prm = New ADODB.Parameter: prm.Type = adVarChar
                                   prm.Name = "FS_BR_ID_OUT"
                                   prm.Direction = adParamOutput
                                   prm.Size = 5
                                   cmd.Parameters.Append prm:    Set prm = Nothing
    Set prm = New ADODB.Parameter: prm.Type = adNumeric
                                   prm.Name = "ISSUE_STAT_OUT"
                                   prm.Direction = adParamOutput
                                   cmd.Parameters.Append prm '________________________________________


    ' Because I named the parameters, I can read them by using their name, rather than just their index #.
    cmd.Execute
    Sn("HighestRev") = Nz(cmd("FS_REV_OUT"), "")
    Sn("Dwg_NOFORN") = Val(Nz(cmd("FS_NOFORN_OUT")))
    Sn("Dwg_BR_ID") = Nz(cmd("FS_BR_ID_OUT"), "")

    Sn("Issued") = cmd("ISSUE_STAT_OUT")            ' This value can be Null
                                                    '            Â¯Â¯Â¯
Rs: Exit Sub

Er: SetCustomMessages Sn, , , "There was an error looking up the drawing.  Please try again."

    Resume Rs

Resume
End Sub

Using a loop to change parameters repeatedly, re-running the SP with the new values:

Code:

Public Function SaveStdRefs(Sn As ISessionObject, Rq As IRequest) As Boolean

    Dim i   As Integer
    Dim c   As ADODB.Connection:    Set c = OpenConnection(Sn)
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter
    Dim intLin As Integer

    Set cmd = New ADODB.Command:    cmd.CommandText = "import_SRL"
                                    cmd.CommandType = adCmdStoredProc
                                    cmd.ActiveConnection = c

    Set prm = New ADODB.Parameter:  prm.Name = "intStdRefId"
                                    prm.Type = adInteger
                                    cmd.Parameters.Append prm

    Set prm = New ADODB.Parameter:  prm.Name = "iLin"
                                    prm.Type = adInteger
                                    cmd.Parameters.Append prm

    Set prm = New ADODB.Parameter:  prm.Name = "iFsId"
                                    prm.Type = adInteger
                                    cmd.Parameters.Append prm

    Set prm = New ADODB.Parameter:  prm.Name = "iFsNf"
                                    prm.Type = adInteger
                                    prm.Value = 0
                                    cmd.Parameters.Append prm
    Set prm = Nothing

    intLin = Val(Rq.Form("MaxLIN")) + 1

    ' Get max LIN value, and add these as max+1, max+2, max+n, ...
    For i = 1 To Val(Rq.Form("MaxItems"))
        If Rq.Form("AddItem" & i) = "on" Then
            cmd.Parameters("intStdRefId").Value = Val(Rq.Form("StdIndex" & i))
            cmd.Parameters("iLin").Value = intLin: intLin = intLin + 1
            cmd.Parameters("iFsId").Value = Val(Sn("Fs_ID"))
            cmd.Parameters("iFsNf").Value = 0

            cmd.Execute
        End If
    Next i

    Set cmd = Nothing

End Function

Raghavendra_Mudugal · June 30th, 2005, 12:37 AM

Hello Brian

could you please explain how to pass the array of data from
the vb6 to stored procedure. and how to extract the values
of the array in the sp and insert into the table.

With Regards,
Raghavendra Mudugal