VB6 and stored procedures using SQL 6.0 
Author Message
 VB6 and stored procedures using SQL 6.0

Your needs would be best suited with an entire
ADO solution.  (This was written off the top of my head, please excuse the
ADO is better (performance wise) than DAO

The following objects will be needed
ADODB.Command  (This one will take care of the stored procs)

'Code start
dim myConn as object
dim myRst as object
dim myCmd as object
dim param1 as object

set myConn = CreateObject("ADODB.Connection")
myConn.Open "DSNName", "Userid", "Password"

set myRst = CreateObject("ADODB.Recordset")
set myCmd = CreateObject("ADODB.Command")
set param1 = CreateObject("ADODB.Parameter")

'Open a recordset using sql
set myRst = myConn.Execute("select * from widgets")
while not myRst.EOF
    msgbox myRst("Field1")
    msgbox myRst("Field2")

'Stored proc time
myCmd.activeconnection = myConn
myCmd.commandtext = "sp_exampleproc"
myCmd.commandtype = adCmdStoredProc
set param1 = myCmd.CreateParameter("paramname", adVarChar,adParamInput, 20)
myCmd.parameters.append param1
set param1 = myCmd.CreateParameter("outputparamname",
adInteger,adParamOutput, 1)
myCmd.parameters.append param1
myCmd("paramname") = "Some input value"
myCmd("outputparamname") = 0
set myRst = myCmd.execute
returnval = myCmd("outputparamname")
msgbox myRst("Fieldname")

set param1 = nothing

set conn = nothing
set mycmd = nothing
set myRST = nothing

Good luck,

> Hi,

> I'm very new to all this so please forgive ignorance..

> Currently I am using a ADODB.Recordset to run a stored
> procedure, and
> this works fine, However i noticed in a VB book that you
> can use a Query

> Def to run a Stored Procedure in SQL server 6.5.  However
> Every time I
> do this it tells me I can't:-
> The code roughly goes like this:- (sorry haven't got the
> full code with
> me, and I can't remember it all, I know I have to define
> the database
> then set up the connection)

> dim db as Database
> Dim rs as recordset, qd as querydef
> Dim cnn as connection    'can't remember how this bit works
> sorry..

> 'this lin of code always opens a Datasource FILE source
> message ? asking

> to select the data source file
> set db = OPenDatabase("
> ",False,False,"ODBC;uid=sa;pwd=;database=TSRV_LIVE;DSN=Tmkt3
> 2_LIVE;")
> 'falls over here

> set qd= db.createQueryDef(" ")

> qd.connect
> = "ODBC;uid=sa;pwd=;database=TSRV_LIVE;DSN=Tmkt32_LIVE;"
> qd.SQL = "Execute P_cwPrintAdd"     'stored procedure
> passes in other
> values

> set rs = qd.openrecordset(dbopensnapshot)    'falls over
> here

> I believe their is a way using DAo but I don't know and
> haven't found a
> way  of doing this

> The reason behind trying to use a query def is that we
> currently cannot
> use ADODB.recordsets in a compiled version it does not like
> it.

> Anybody got any ideas as to how to execute a stored
> procedure from vb,
> using either ODBC, or DAO ?

> I would be most grateful for any help..

> SP

> * Sent from AltaVista http://www.*-*-*.com/ Where you can also find

related Web Pages, Images, Audios, Videos, News, and Shopping.  Smart is

- Show quoted text -

Wed, 07 Aug 2002 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)

2. HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)

3. SQL Stored Procedures Using Parameters in CR8.5/VB6

4. HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)

5. VB4 RDO Won't detect an error in an SQL Server 6.0 stored procedure

6. executing stored procedures from VB3.0/SQL Server 6.0

7. MS SQL Server 6.0 stored procedures

8. MS Sql Server 6.0 Stored Procedures

9. HELP! CreatePreparedStatement and SQL Server 6.0 stored procedure

10. HELP! CreatePreparedStatement and SQL Server 6.0 stored procedure

11. Pass parameter to a MS SQL stored procedure from Crystal Report 6.0

12. CR 6.0: Using Multiple Stored Procedures


Powered by phpBB® Forum Software