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
mess)
ADO is better (performance wise) than DAO
The following objects will be needed
ADODB.Connection
ADODB.Recordset
ADODB.Command (This one will take care of the stored procs)
ADODB.Parameter
'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")
myRst.MoveNext
wend
myRst.Close
'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")
myCmd.close
myrst.close
conn.close
set param1 = nothing
set conn = nothing
set mycmd = nothing
set myRST = nothing
Good luck,
Rob
Quote:
> 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
Beautiful
