Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 3rd, 2005, 08:55 AM
Registered User
 
Join Date: Mar 2005
Location: chennai, tamilnadu, India.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default stored procedure

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
Reply With Quote
  #2 (permalink)  
Old March 22nd, 2005, 05:54 AM
Friend of Wrox
Points: 687, Level: 9
Points: 687, Level: 9 Points: 687, Level: 9 Points: 687, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: Chennai, Tamilnadu, India.
Posts: 221
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old March 22nd, 2005, 12:09 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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.
Reply With Quote
  #4 (permalink)  
Old April 25th, 2005, 06:34 AM
Authorized User
 
Join Date: Jan 2005
Location: Chennai, , India.
Posts: 66
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to JelfMaria
Default

:)How to call an Oracle procedure using vb6?


Reply With Quote
  #5 (permalink)  
Old April 25th, 2005, 10:28 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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.
Reply With Quote
  #6 (permalink)  
Old April 26th, 2005, 12:52 AM
Authorized User
 
Join Date: Jan 2005
Location: Chennai, , India.
Posts: 66
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to JelfMaria
Default

Can u able to send me a sample for it

Reply With Quote
  #7 (permalink)  
Old April 26th, 2005, 11:44 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old June 30th, 2005, 12:37 AM
Friend of Wrox
Points: 687, Level: 9
Points: 687, Level: 9 Points: 687, Level: 9 Points: 687, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: Chennai, Tamilnadu, India.
Posts: 221
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
stored procedure prashant_telkar SQL Server 2000 1 July 9th, 2007 07:57 AM
Stored Procedure jezywrap SQL Server ASP 1 January 3rd, 2007 12:29 AM
stored procedure kdm260 SQL Server 2000 2 June 19th, 2006 04:45 PM
Stored Procedure rajanikrishna SQL Server 2000 0 July 18th, 2005 05:01 AM
Help About Stored Procedure zhuge6 BOOK: ASP.NET Website Programming Problem-Design-Solution 3 May 20th, 2005 09:27 AM



All times are GMT -4. The time now is 07:16 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.