[dba-VB] Calling SQL Stored Procedure From Visual Basic 6

Here's a DAO procedure, but I think Lorraine's ADO method is better.

John


---------------

Function SqlServerSP_Test()

'This will essentially create a pass-thru query

Dim strODBCConnection  As String
'Store the ODBC connect string into a variable
strODBCConnection = "ODBC;DRIVER={SQL
Server};SERVER=DatabaseSERVERName;UID=SA;PWD=MyPassword;DATABASE=SQL_DATABAS
EName"

    'Create a query def object
    Dim MyQry As DAO.QueryDef
    Set MyQry = CurrentDb().CreateQueryDef("") 'no need to give it a name
since it's just a temporary object

    'If you do give it a name, it will be saved in your database window as a
pass-thru query.

    'Set the connection string
    MyQry.Connect = strODBCConnection

    'Timeout is typically 60 seconds. If you think it may take a while to
run,
    'increase it accordingly. Otherwise, an error may occur
    MyQry.ODBCTimeout = 120

    MyQry.ReturnsRecords = False 'If not returning records, set to false

    'Set the sql statement of the query def to be the stored procedure.
    'Include the EXECUTE command
    MyQry.SQL = "EXECUTE SP_MyStoredProcedure"

    'You can also pass an argument. Make sure these is a space after the SP
before adding the argument
    MyQry.SQL = "Execute SP_MyStoredProcedure" & " " & "45" 'Passing a
numeric value
    MyQry.SQL = "Execute SP_MyStoredProcedure" & " " & "'Hello'"   'Passing
a string

    'Now execute the query
    MyQry.Execute 'Use this when not returning records

    'Close your query def object
    MyQry.Close

End Function

----------------------

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com]On Behalf Of Christian,
Lorraine
Sent: Wednesday, December 31, 2003 1:16 PM
To: dba-vb at databaseadvisors.com
Subject: RE: [dba-VB] Calling SQL Stored Procedure From Visual Basic 6


Hi there!

Not be near my source code.  Would something like this help?

Public Sub AutoNumber()

    Dim cmd As New ADODB.Command

    cmd.ActiveConnection = myConn2
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "NBPHI.NEXT_INTVW_ID_NR"

    cmd.Parameters.Append cmd.CreateParameter(, adInteger, adParamOutput)
    cmd.Parameters.Append cmd.CreateParameter(, adInteger, adParamOutput)

    cmd.Execute
    iErrorResponse = cmd.Parameters(0).Value

    If cmd.Parameters(0).Value = 0 Then
        intKey = cmd.Parameters(1).Value
    Else
        MsgBox "An error occured creating this record.  Contact your
support", vbCritical
    End If


End Sub


HTHs

Lorraine

Happy New Year
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com]On Behalf Of Francisco H
Tapia
Sent: Wednesday, December 31, 2003 12:35 PM
To: paul.hartland at fsmail.net; dba-vb at databaseadvisors.com
Subject: Re: [dba-VB] Calling SQL Stored Procedure From Visual Basic 6


paul.hartland at fsmail.net wrote:

>To all,
>
>I am currently writing a visual basic application with which the back-end
will be on SQL Server 7.0.  I currently have a data environment with a
command object that links to a stored procedure on the SQL Server.  What I
want to be able to do is do away with the DataEnvironment and execute the
stored procedure from a module and pass the results (i.e. if I had a SELECT
SP into a recordset.
>
>Anyone any ideas how to do this.
>
>Thanks in advance.
>
>Paul Hartland
>
>
Use ADO.
for help on ADO check your online help, MSDN or
http://www.able-consulting.com/tech.htm

--
-Francisco


_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com



---------------------------------------------------------
This e-mail transmission may contain information that is proprietary,
privileged and/or confidential and is intended exclusively for the person(s)
to whom it is addressed. Any use, copying, retention or disclosure by any
person other than the intended recipient or the intended recipient's
designees is strictly prohibited. If you are not the intended recipient or
their designee, please notify the sender immediately by return e-mail and
delete all copies.

---------------------------------------------------------

_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com