VB + Sql server Stored procedures

[ramesh_tankala]

Hi...friends...
i am using Sql Server as backend ,how to call the Stored Procedure In parameters & Out Parameters using Vb :)

[sashi]

Hi Ramesh,

i have been using the below code for years and it works fine on both the MSSQL 7.0 and MSSQL 2K.. hope its useful to you and hope it helps you to get started.. good luck my fren.. :)

Dim sServer As String
Dim sUser As String
Dim sPWD As String
Dim sDatabase As String
 
Dim DBcon As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objparameter As New ADODB.Parameter
Dim objRs As New ADODB.Recordset
 
    sServer = "(local)"
    sDatabase = "mydb"
    sUser = "sa"
    sPWD = "password"
 
    DBcon.ConnectionString = "Provider=sqloledb;" & _
        "server=" & sServer & ";uid=" & sUser & ";pwd=" & sPWD & ";database=" & sDatabase
    DBcon.CursorLocation = adUseClient
    DBcon.Open
 
    objparameter.Direction = adParamInput
    objparameter.Type = adVarChar
    objparameter.Size = 5
    objparameter.Value = "hello"
    objCmd.Parameters.Append objparameter
 
    objCmd.ActiveConnection = DBcon
    objCmd.CommandType = adCmdStoredProc
    objCmd.CommandText = "s_mysp"
    Set objRs = objCmd.Execute                       ' objCmd.Execute for no resultset
 
    set objRs.ActiveConnection = Nothing
    Set objCmd = Nothing
    DBcon.Close