Using Stored Procedures 
Author Message
 Using Stored Procedures

VB6, MSDE, Access 2000

I have created a stored procedure in my MS Access .adp called
"AddShipMethod" as follows:

Alter Procedure AddShipMethod
    (






    )
As
INSERT ShipMethod (ListID, TimeCreated, TimeModified, EditSequence, strName,
IsActive)

How do I go about using this procedure in my VB6 application?  What I have
so far....

Dim cnn As New ADODB.Connection
cnn.Open "My DSN"

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandText = "AddShipMethod"
cmd.CommandType = adCmdStoredProc
...
How do I get my parameters into the command object?

Scott



Sun, 22 May 2005 00:58:42 GMT  
 Using Stored Procedures
Hi Scott,

Now, you would need to add parameters to Parameters collection of ADO
Command object

cmd.CommandText = "AddShipMethod"
cmd.CommandType = adCmdStoredProc


loParameter.Value=MyValue
loCommand.Parameters.Append loParameter

loParameter.Value=MyDateValue
cmd.Parameters.Append loParameter
......
cmd.Execute

--
Val Mazur
Microsoft MVP


Quote:
> VB6, MSDE, Access 2000

> I have created a stored procedure in my MS Access .adp called
> "AddShipMethod" as follows:

> Alter Procedure AddShipMethod
>     (






>     )
> As
> INSERT ShipMethod (ListID, TimeCreated, TimeModified, EditSequence,
strName,
> IsActive)


> How do I go about using this procedure in my VB6 application?  What I have
> so far....

> Dim cnn As New ADODB.Connection
> cnn.Open "My DSN"

> Dim cmd As New ADODB.Command
> Set cmd.ActiveConnection = cnn
> cmd.CommandText = "AddShipMethod"
> cmd.CommandType = adCmdStoredProc
> ...
> How do I get my parameters into the command object?

> Scott



Sun, 22 May 2005 02:01:47 GMT  
 Using Stored Procedures
Thanks again Val,

It looks like its going to work but how do I specify in my Stored Procedure
whether a parameter is optional?  For example, TimeCreated and TimeModified
may not be required to add the record...

Scott


Quote:
> Hi Scott,

> Now, you would need to add parameters to Parameters collection of ADO
> Command object

> cmd.CommandText = "AddShipMethod"
> cmd.CommandType = adCmdStoredProc


> loParameter.Value=MyValue
> loCommand.Parameters.Append loParameter

adParamInput)
> loParameter.Value=MyDateValue
> cmd.Parameters.Append loParameter
> ......
> cmd.Execute

> --
> Val Mazur
> Microsoft MVP



> > VB6, MSDE, Access 2000

> > I have created a stored procedure in my MS Access .adp called
> > "AddShipMethod" as follows:

> > Alter Procedure AddShipMethod
> >     (






> >     )
> > As
> > INSERT ShipMethod (ListID, TimeCreated, TimeModified, EditSequence,
> strName,
> > IsActive)


> > How do I go about using this procedure in my VB6 application?  What I
have
> > so far....

> > Dim cnn As New ADODB.Connection
> > cnn.Open "My DSN"

> > Dim cmd As New ADODB.Command
> > Set cmd.ActiveConnection = cnn
> > cmd.CommandText = "AddShipMethod"
> > cmd.CommandType = adCmdStoredProc
> > ...
> > How do I get my parameters into the command object?

> > Scott



Sun, 22 May 2005 02:48:42 GMT  
 Using Stored Procedures
Scott,

There is no exactly optional parameters in stored procedures, but instead
you need to specify default value in case if parameter was not passed

 Alter Procedure AddShipMethod
     (






But not sure if OLEDB provider will allow to skip parameter in case if you
are calling SP from your code. From Query Analyzer it works for sure

--
Val Mazur
Microsoft MVP


Quote:
> Thanks again Val,

> It looks like its going to work but how do I specify in my Stored
Procedure
> whether a parameter is optional?  For example, TimeCreated and
TimeModified
> may not be required to add the record...

> Scott



> > Hi Scott,

> > Now, you would need to add parameters to Parameters collection of ADO
> > Command object

> > cmd.CommandText = "AddShipMethod"
> > cmd.CommandType = adCmdStoredProc


40)
> > loParameter.Value=MyValue
> > loCommand.Parameters.Append loParameter

> adParamInput)
> > loParameter.Value=MyDateValue
> > cmd.Parameters.Append loParameter
> > ......
> > cmd.Execute

> > --
> > Val Mazur
> > Microsoft MVP



> > > VB6, MSDE, Access 2000

> > > I have created a stored procedure in my MS Access .adp called
> > > "AddShipMethod" as follows:

> > > Alter Procedure AddShipMethod
> > >     (






> > >     )
> > > As
> > > INSERT ShipMethod (ListID, TimeCreated, TimeModified, EditSequence,
> > strName,
> > > IsActive)


> > > How do I go about using this procedure in my VB6 application?  What I
> have
> > > so far....

> > > Dim cnn As New ADODB.Connection
> > > cnn.Open "My DSN"

> > > Dim cmd As New ADODB.Command
> > > Set cmd.ActiveConnection = cnn
> > > cmd.CommandText = "AddShipMethod"
> > > cmd.CommandType = adCmdStoredProc
> > > ...
> > > How do I get my parameters into the command object?

> > > Scott



Sun, 22 May 2005 03:14:10 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. More examples of using stored procedures / stored packages

2. Using Stored Procedures for a Report's Recordsource

3. Using Stored procedures or not?

4. Using Stored Procedures to populate Datasets.

5. Using Stored Procedures with unknown amount of parameters

6. Using Stored Procedures As Command Objects

7. The best way to organize VB6 projects using stored procedures

8. Crystal Reports using stored procedures

9. Using stored procedures with ADO programming

10. Using Stored procedures in VB 4.0

11. Adding NULL to database using Stored Procedure

12. Using Stored Procedure in Crystal

 

 
Powered by phpBB® Forum Software