by Gregory Mazarakis (4 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Fri 13th June 2003
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
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
VBcoders
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
No comments have been posted about Syntax for Running SQL Stored Procedures, with Parameters, from Visual Basic. This code uses Northw. Why not be the first to post a comment about Syntax for Running SQL Stored Procedures, with Parameters, from Visual Basic. This code uses Northw.
