Best data access method for VB6

Posted on 2002-06-27
Medium Priority
Last Modified: 2013-11-25
It seems like I am going around in circles. I am try to find the best way for me to access data using ADO. Some people say to directly code connections and recordsets and skip the DataEnvironment and the ADO Data Control. Some say using the ADO Data Control is quick and easy, but I seem to run into walls trying to do something with it.

I would like to have the most data access flexabilty.

That said, the main problem that I am having is linking two tables together, moving around the main table while the child table moves along with it. I also want to be able to add data. My form consists of text boxes that show the main data and two datagrids which show two child data sets. For clarity purposes, the text boxes display broker information (BrokerID, Name, Address, etc.). The first Datagrid shows notes related to the Broker. The third table shows the phone numbers the broker uses. I did this so that when a broker calls up, my program uses the caller ID info from the phone company and searches for the broker information. I have addnew enabled on the datagrids, but I keep on strugling with various errors. I am using 3 ADO Data controls (1 for the text boxes, 1 for the first datagrid and 1 for the second datagrid). I am using SQL to link the datagrid ADO datacontrols to the BrokerID text box using a WHERE clause. I keep on thinking it might be best to use a SQL join in one control and have all text boxes and grids get their data from that one control. That is where my problem arises. Would it be better to go with a DataEnvironment or with The ADO DataControl or just to create a recordset in code and have the text fields and grids datasource connected to that recordset?

This is like a maze. If you go in one direction, you spend a lot of time finding out if it was the right direction only to find out that you reached a dead end and you have to back track and do more learning.

Thanks for your time and help. I am sure many of you are very busy and I am grateful for the time that you are taking to read this and to hopefully reply.
Question by:dijon316
  • 2
  • 2
  • 2
  • +2
LVL 43

Expert Comment

ID: 7113616
This is a personal view but one shared by many.

Use code, this offers the maximum flexibility and control, simpler debugging (really!) and lower overheads in terms of additional controls therefore reducing the complexity of installation routines and compatibility.

Steer clear of binding any data to any control, even grid controls if you can, it is usually much better to handle this seperately from the display of the data though it can be a little more complicated.

It is quite simple to manage unbound data using the .Execute method of the connection object to perform Insert/Update/Delete statements and to keep the displayed information in sync with that stored in the database.

As far as using a Join goes, it is probably not worth it, if you wanted to achieve this sort of effect using a single query you should investigate the Data Shaping provider for use with ADO however this opens an entirely new can of worms which you can probably do without at this time.

One of the biggest problems that people encounter with data bound controls, textboxes, grids or whatever is that their table does not have a primary key, this is pretty vital in many ways especially with grids where if you make a change you may get an error such as "insufficient base table information for updating" or "multiple step oledb operation generated errors" both of these can often be traced back to the fact that the implicit Update statement generated because of you changing data is not precise enough to update just the row that you are working on. Using properly constructed simple statements in the execute method of the connection object in conjunction with nicely structured tables can eliminate 99.9%+ of these problems.

Expert Comment

ID: 7113790
I agree.  I certainly wouldn't use the data enviroment for that sort of thing, and I don't care for using any databound contols for that sort of thing unless you're just displaying data and not doing anything with it.  I've tried a lot of different ways to do what you're trying to do and have found that using code is the best way to go about it.  You have a lot more control over your data and you can handle any errors in your code a lot easier too.  Not to restate your comment, of course, Tim.

Author Comment

ID: 7113835

Are you suggesting using a Datagrid in unbound mode. If so, that greatly increases coding by manually populating the grid.

I am still on the lower end of the learning curve so that sound challenging.

Do you recommend any books which may help in this case such as manually coding datagrids?
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 43

Expert Comment

ID: 7113871
If you don't feel up to using a grid in unbound mode then stay as you are, the datagrid is very good in some respects but I personally prefer the VSFlexgrid which has all of the flexibility of the MSFlexgrid but also all of the data binding features of the datagrid. However I still use the grids unbound most of the time as I use stored procedures to access my data and only call these as necessary to update the database.

The most important thing when doing anything of this sort is to ensure that your data structures which support the user interface are robust and properly related. Using primary keys and ideally foreign keys and so on to ensure that the information is properly linked will solve a number of the issues that you may be finding.

Author Comment

ID: 7114005
Currently all of my tables do have primary and foreign keys. In fact, I am in the process of moving the program from MS Access to VB.

Do you happen to know where I might be able to find a good example of filling the DataGrid by code using stored procedures instead of binding it?

Thanks in advance!
LVL 18

Accepted Solution

mdougan earned 400 total points
ID: 7114248
I'm with Tim.  I only use unbound controls and grids.  The code is only tough the first time you do it.  From then on, you can just copy and paste the routines.  Yes, there is a lot of code, but if you organize your code into self-contained routines, then it's really pretty simple.  I'd suggest replacing the datagrid with the MSFlexGrid.  I'm not sure that the datagrid will let you work in unbound mode.

I use the VSFlexGrid almost exclusively, but the MSFlexGrid can be used with almost the exact same code.  The biggest drawback to the MSFlexGrid is that it does not allow in-cell editing.  So, if you don't want to spend the money to buy the VSFlexGrid Pro from www.ComponentOne.com, then you have to go to extra lengths to position a textbox over a cell that you want to edit.  Here is a sample of filling a FlexGrid from the database.  It's also an example of calling a SQL Server stored proc from VB using ADODB command and parameter objects.  This routine, called GetProducts takes a long which is a key into the products table.  If this product grid is a "detail" to a master record, let's say a Customer.  Then, whenever you switch customers, you can call this function to refill the detail grid.  So, if you have a combo box of customer names, and you have put the customer_no in the ItemData property of the combo box.  Then, whenever the user clicks on the customer combo:

Private Sub cboCustomer_Click()
' first go get your customer data and fill the master fields on the screen

' Now, get the products for this customer

End Sub

' I like to define my columns with constants.  In that way, if I need to insert a new column in the middle
' of the table, I don't have to search all through the code changing all references to column 6 to column 7 etc.
' plus, it's much more self-documenting
Const COUNT_COL = 2
Const WAC_COL = 3
Const CUSIP_COL = 4
Const PRICE_COL = 8
Const BATCH_NO_COL = 9
Const FLAG_COL = 10

Public Sub GetProducts(lCustNo As Long)
Dim RS As ADODB.Recordset
Dim Cmd As ADODB.Command
Dim PARM1 As ADODB.Parameter

    On Error GoTo ErrorRtn
' You might want to set the columns at design time.  You can also set the format string property to get column headers
    tblProduct.Cols = 11
    tblProduct.Rows = 1
' Create the new command object
    Set Cmd = New ADODB.Command
' Set the properties for the command object, assumes CN, an ADODB.Connection has already been opened
    With Cmd
        .ActiveConnection = CN
        .CommandType = adCmdStoredProc
        .CommandText = "sp_get_products"
    End With
' Create the new parm object
    Set PARM1 = New ADODB.Parameter
' set the parms properties
    With PARM1
        .Name = "@customer_no"
        .Direction = adParamInput
        .Type = adInteger
        .Value = lCustNo
    End With
' Parameters must be appended in the order they appear in the SQL Statement
    Cmd.Parameters.Append PARM1

' Now, declare the new recordset
    Set RS = New ADODB.Recordset
' Tell the recordset to get it's data from the command object
    Set RS.Source = Cmd
' This runs the query
' Always check to see if the recordset was successfully opened
    If RS.State = adStateOpen Then
' Always check to see if there was data retrieved
        If Not RS.EOF And Not RS.BOF Then
            While Not RS.EOF
' Increment the row count
                tblProduct.Rows = tblProduct.Rows + 1
' I always append an empty string at the end in case the database column contained a null value
                tblProduct.TextMatrix(tblProduct.Rows - 1, PRODUCT_COL) = RS("product").Value & ""
                tblProduct.TextMatrix(tblProduct.Rows - 1, BALANCE_COL) = RS("balance").Value & ""
                tblProduct.TextMatrix(tblProduct.Rows - 1, COUNT_COL) = RS("loan_count").Value & ""
                tblProduct.TextMatrix(tblProduct.Rows - 1, WAC_COL) = RS("WAC").Value & ""
                tblProduct.TextMatrix(tblProduct.Rows - 1, CUSIP_COL) = RS("cusip_from_REF").Value & ""
                tblProduct.TextMatrix(tblProduct.Rows - 1, TRADING_ACCT_COL) = RS("trading_acct").Value & ""
                tblProduct.TextMatrix(tblProduct.Rows - 1, SETTLE_LOC_COL) = RS("settle_location").Value & ""
                tblProduct.TextMatrix(tblProduct.Rows - 1, ASSIGNEE_COL) = RS("assignee_program").Value & ""
                tblProduct.TextMatrix(tblProduct.Rows - 1, PRICE_COL) = RS("price").Value & ""
                tblProduct.TextMatrix(tblProduct.Rows - 1, BATCH_NO_COL) = RS("batch_no").Value & ""
                tblProduct.TextMatrix(tblProduct.Rows - 1, FLAG_COL) = "O"
' Move to the next record
            MsgBox "No products were found in the database"
        End If
' Close it here, as you know that it was successfully opened
        MsgBox "There was an error retrieving the Products"
    End If
' You can autosize the grid if you want, but I like to pre-define how much space each column gets.
    tblProduct.ColWidth(PRODUCT_COL) = tblProduct.Width * 0.17
    tblProduct.ColWidth(BALANCE_COL) = tblProduct.Width * 0.17
    tblProduct.ColWidth(COUNT_COL) = tblProduct.Width * 0.06
    tblProduct.ColWidth(WAC_COL) = tblProduct.Width * 0.09
    tblProduct.ColWidth(CUSIP_COL) = tblProduct.Width * 0.09
    tblProduct.ColWidth(TRADING_ACCT_COL) = tblProduct.Width * 0.09
    tblProduct.ColWidth(SETTLE_LOC_COL) = tblProduct.Width * 0.08
    tblProduct.ColWidth(ASSIGNEE_COL) = tblProduct.Width * 0.11
    tblProduct.ColWidth(PRICE_COL) = tblProduct.Width * 0.1
' You can hide columns by setting the width to zero, if you want to use these columns to hold the keys for applying updates later
    tblProduct.ColWidth(BATCH_NO_COL) = 0
    tblProduct.ColWidth(FLAG_COL) = 0
' Always clean up on your way out
    Set PARM1 = Nothing
    Set RS = Nothing
    Set Cmd = Nothing

    Exit Sub
' your error handler here
    GoTo ExitRtn
End Sub

Expert Comment

ID: 7163205

I reduced the points to 100 and refunded the rest. Please accept one experts comment as an answer and post a 100p question for the other expert with a title like : "Points for xxx" and a body "For your help in <URL>."

** Mindphaser - Community Support Moderator **

Expert Comment

ID: 7201747
Force accepted (request 20316889)

** Mindphaser - Community Support Moderator **

TimCottee, there will be a separate question with points for your help.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question