• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Syntax for Running SQL Stored Procedures, with Parameters, from Visual Basic. This code uses Northw

Syntax for Running SQL Stored Procedures, with Parameters, from Visual Basic. This code uses Northw

Syntax for Running SQL Stored Procedures, with Parameters, from Visual Basic. This code uses Northwind Database as an example.

API Declarations


'Make 2 listboxes on your form just to display the data...
'With this code you will access the Northwind DataBase
'and run Stored Procedure "SalesByCategory".

Public objCommand As New ADODB.Command
Public objRS As New ADODB.Recordset



Rate Syntax for Running SQL Stored Procedures, with Parameters, from Visual Basic. This code uses Northw

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

    Dim strConnection, Password, User, InitialC, Source
    Dim Teller, ProductName(500), TotalPurchase(500)
    
    Source = "SERVER"           'Your server name
    User = "USER"               'The SQL user you want to use (probably 'sa')
    Password = "PWD"            'The password for the previously defined User
    InitialC = "Northwind"      'The database you wish to maintain
    Teller = 1
    
    strConnection = "Provider=SQLOLEDB;Persist Security Info=False;Password=" & Password & ";User ID=" & User & ";Initial Catalog=" & InitialC & ";Data Source=" & Source
    objCommand.ActiveConnection = strConnection
    objCommand.CommandText = "SalesByCategory"
    objCommand.CommandTimeout = 600
    objCommand.CommandType = adCmdStoredProc
    objCommand.Parameters.Append objCommand.CreateParameter("@CategoyrName", adVarChar, adParamInput, 30)
    objCommand.Parameters.Append objCommand.CreateParameter("@OrdYear", adVarChar, adParamInput, 30)
    objCommand.Parameters("@CategoyrName") = "Beverages"
    objCommand.Parameters("@OrdYear") = "1998"
    Set objRS = objCommand.Execute
    Set objCommand = Nothing
    Set ObjParam = Nothing
    While Not objRS.EOF
        ProductName(Teller) = objRS("ProductName")
        TotalPurchase(Teller) = objRS("TotalPurchase")
        Teller = Teller + 1
        objRS.MoveNext
    Wend
    objRS.Close
    Set objCommand = Nothing
    Set objRS = Nothing
    Teller = Teller - 1
    
    For Display = 1 To Teller
        List1.AddItem ProductName(Display)
        List2.AddItem TotalPurchase(Display)
    Next

End Sub