• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Syntax for Accessing SQL tables with NO Stored Procedures, directly from VB. This code uses the Nor

Syntax for Accessing SQL tables with NO Stored Procedures, directly from VB. This code uses the Nor

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



Rate Syntax for Accessing SQL tables with NO Stored Procedures, directly from VB. This code uses the Nor

• 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 = "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