Solved

Best data access method for VB6

Posted on 2002-06-27
8
288 Views
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.
0
Comment
Question by:dijon316
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
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.
0
 

Expert Comment

by:VBGuy91917
Comment Utility
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.
0
 

Author Comment

by:dijon316
Comment Utility
Tim,

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?
0
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
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.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:dijon316
Comment Utility
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!
0
 
LVL 18

Accepted Solution

by:
mdougan earned 100 total points
Comment Utility
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
  GetCustomer(cboCustomer.ItemData(cboCustomer.ListIndex))

' Now, get the products for this customer
  GetProducts(cboCustomer.ItemData(cboCustomer.ListIndex))


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 PRODUCT_COL = 0
Const BALANCE_COL = 1
Const COUNT_COL = 2
Const WAC_COL = 3
Const CUSIP_COL = 4
Const TRADING_ACCT_COL = 5
Const SETTLE_LOC_COL = 6
Const ASSIGNEE_COL = 7
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
    RS.Open
   
' 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
                RS.MoveNext
            Wend
        Else
            MsgBox "No products were found in the database"
        End If
' Close it here, as you know that it was successfully opened
        RS.Close
    Else
        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
           
ExitRtn:
' Always clean up on your way out
    Set PARM1 = Nothing
    Set RS = Nothing
    Set Cmd = Nothing

    Exit Sub
   
ErrorRtn:
' your error handler here
    GoTo ExitRtn
End Sub
0
 
LVL 6

Expert Comment

by:Mindphaser
Comment Utility
dijon316

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 **
0
 
LVL 6

Expert Comment

by:Mindphaser
Comment Utility
Force accepted (request 20316889)

** Mindphaser - Community Support Moderator **

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now