DOWNLOAD THE CODE:
Download the Code 5114.zip

Now that you've completed the interface design, save the application.

Building the Database Code
Now you can add the code for database access routines. First, use the Project/Add Module command to create a new code module. Rename this module Customer.bas. This module contains the LoadCustomer function, which retrieves all the customers from the database and returns them to the calling code. This function uses the cmdCustomer data command to retrieve the customers. Add the function declaration. As the first lines of the function, add these variable definitions:

Function LoadCustomer() As 
   Variant
Dim sCustomerList() As String
Dim i As Integer, j As Integer

The sCustomerList variable is an undimensioned array that will contain the records retrieved from the database. The first step in programming using data commands is to open or execute the recordset. The Data Environment automatically creates a recordset object for you. The object will be named rs plus the name of your data command. In this example, the data command is rscmdCustomer. In this routine, enter the following line to open the recordset:

envNorthwind.rscmdCustomer.Open

Next, you need to know whether the recordset returned any records. You could use the EOF property to loop through the recordset, but if you do, you also need to redimension the sCustomerList array to the exact number of records in the recordset. SQL Server will return this array from the function to the calling code. To retrieve the record count, you can use the RecordCount method. Now, enter this line to retrieve the number of records in the recordset:

   i = envNorthwind.rscmdCustomer.RecordCount

You can use the number of records returned to test for a valid recordset and to redimension the sCustomerList array:

   If i > 0 Then
      ReDim sCustomerList(i - 1)

Now, you can start processing the records in the recordset. The following code uses a Do While statement to loop through the records until an EOF condition occurs. SQL Server processes each record by taking the CompanyName and CustomerID fields from the recordsets Fields collection and placing them in the sCustomerList array:

i = 0
Do While Not envNorthwind.rscmdCustomer.EOF
sCustomerList(i) = envNorthwind
         .rscmdCustomer _.Fields

         ("CompanyName") & " (" & _
         envNorthwind.rscmdCustomer

         .Fields("CustomerID") & ")"
      envNorthwind.rscmdCustomer.MoveNext
i = i + 1
   Loop
Prev. page     1 [2] 3     next page



You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

Works a treat - the problem I had was that every time I edited a query and saved it the parameter definitions reset themselves - you have to edit the data types for hte parameters after every save of the query...

Adrian Blakey- March 25, 2004

 
 

ADS BY GOOGLE