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 Accessing SQL tables with NO Stored Procedures, directly from VB. This code uses the Northwind Database as an example.
API Declarations
'Make 2 listboxes on your form just to display the data...
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 = "SELECT ProductName, TotalPurchase = Round(Sum(CONVERT(decimal(14, 2), OD.Quantity * (1 - OD.Discount) * OD.UnitPrice)), 0) " _
& " FROM [Order Details] OD, Orders O, Products P, Categories C " _
& " Where OD.OrderID = O.OrderID " _
& " AND OD.ProductID = P.ProductID " _
& " AND P.CategoryID = C.CategoryID " _
& " AND C.CategoryName = '" & "Beverages" & "'" _
& " AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = 1998 " _
& " GROUP BY ProductName " _
& " ORDER BY ProductName"
objCommand.CommandTimeout = 20
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
No comments have been posted about Syntax for Accessing SQL tables with NO Stored Procedures, directly from VB. This code uses the Nor. Why not be the first to post a comment about Syntax for Accessing SQL tables with NO Stored Procedures, directly from VB. This code uses the Nor.
