Home » Infrastructure » Windows » Calling Stored Procedures from VB is giving error. Urgent help !!!!!
|
|
|
|
|
|
| Calling Stored Procedures from VB6.0 PLease Help!!! [message #365742 is a reply to message #365700] |
Thu, 16 December 1999 13:43   |
Balaji K
Messages: 1
Registered: December 1999
|
Junior Member |
|
|
Hi,
Iam trying to execute stored procedures returning
recorsets from VB.
I have used the sample code given by Microsoft Knowledge Base at
http://support.microsoft.com/support/kb/articles/q176/0/86.asp
It gives a runtime error at the line
rs.Open
Its says OracleODBC Oracle Driver Syntax Error
I created the following table on oracle.
CREATE TABLE person(ssn NUMBER(9) PRIMARY KEY, fname VARCHAR2(15),
lname VARCHAR2(20));
INSERT INTO person VALUES(555662222,'Sam','Goodwin');
INSERT INTO person VALUES(555882222,'Kent','Clark');
INSERT INTO person VALUES(666223333,'Jane','Doe');
I have the package spec as ,
CREATE OR REPLACE PACKAGE packperson
AS
TYPE tssn is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER;
TYPE tfname is TABLE of VARCHAR2(15) INDEX BY BINARY_INTEGER;
TYPE tlname is TABLE of VARCHAR2(20) INDEX BY BINARY_INTEGER;
PROCEDURE allperson(ssn OUT tssn, fname OUT tfname, lname OUT tlname);
PROCEDURE oneperson(onessn IN NUMBER, ssn OUT tssn, fname OUT tfname, lname OUT
tlname);
END packperson;
The package body is,
CREATE OR REPLACE PACKAGE BODY packperson AS
PROCEDURE allperson(ssn OUT tssn, fname OUT tfname, lname OUT tlname) IS
CURSOR person_cur IS SELECT ssn,fname,lname FROM person;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
PROCEDURE oneperson(onessn IN NUMBER, ssn OUT tssn, fname OUT tfname, lname
OUT tlname) IS
CURSOR person_cur IS SELECT ssn,fname,lname FROM person WHERE ssn = onessn;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
END;
Iam using VB6.0 to call the procedure,
I have 2 buttons on a form
cmdGetEveryone
cmdGetOne
The Visual basic code is as follows,
Option Explicit
Dim Cn As ADODB.Connection
Dim CPw1 As ADODB.Command
Dim CPw2 As ADODB.Command
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim Conn As String
Dim QSQL As String
Dim inputssn As Long
Dim DSN
Dim Username
Dim Password
Private Sub cmdGetEveryone_Click()
Set rs1.Source = CPw1
Debug.Print rs1.Source
rs1.Open CPw1
While Not rs1.EOF
MsgBox "Person data: " & rs2(0) & ", " & rs2(1) & ", " & rs2(2)
rs2.MoveNext
Wend
rs1.Close
End Sub
Private Sub cmdGetOne_Click()
'Set rs2.Source = CPw2
inputssn = InputBox("Enter the SSN you wish to retrieve:")
CPw2(0) = inputssn
Set rs2 = CPw2.Execute
MsgBox "Person data: " & rs2(0) & ", " & rs2(1) & "," & rs2(2)
rs2.Close
End Sub
Private Sub Form_Load()
'Replace User ID, Password;, and Server with the
'appropriate parameters.
DSN = "xxxxx"
Username = "xxxxx"
Password = "xxxx"
Set Cn = New ADODB.Connection
With Cn
'.ConnectionString = Conn
.CursorLocation = adUseClient
.Open "DSN=" & DSN & ";UID=" & Username & ";PWD=" & Password & ";database=gtdt"
End With
QSQL = "{call packperson.allperson({resultset 9, ssn, fname, lname})}"
Set CPw1 = New ADODB.Command
With CPw1
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
End With
Set rs1 = New ADODB.Recordset
With rs1
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
QSQL = "{call schema.packperson.oneperson(?,{resultset 2, ssn, fname, lname})}"
Set CPw2 = New ADODB.Command
With CPw2
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
.Parameters.Append .CreateParameter(, adInteger, adParamInput)
End With
Set rs2 = New ADODB.Recordset
With rs2
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
End Sub
I get the error when I click on either of the buttons.
The error happens at the line,
rs1.Open
I think its a wrong syntax Iam using to open the
recordset.
Any help will be greatly appreciated.
Thanks very much
Bala
|
|
|
|
| msaccess2000 and vb6.0 [message #366051 is a reply to message #365566] |
Thu, 19 October 2000 03:29 |
Johnny
Messages: 15
Registered: October 2000
|
Junior Member |
|
|
dear sir
i am having a problem regarding the compatibilty of msaccess 2000 and vb6.0.i possible can i get the reason and solution for that
thank you
johnny
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 12:06:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
