Solved

Load data from SQL server to unbound DBGrid

Posted on 1998-05-05
20
356 Views
Last Modified: 2011-09-20
I have a form for invoice change.  The user need to input an invoice no. first, after pressing [Enter] key, the details of the invoice would be shown on an unbound dbgrid. I found that after loading 1 row data, a run-time error "Invalid row number" occured.

The following is my codings.

iRow = 0
SelectLoanItem = "SELECT ItemNo,Description,Serial FROM vwLoanDetailsForIIP "
    SelectLoanItem = SelectLoanItem & "WHERE LoanNo = " & CInt(TxtLoanNo)
    Set Myrecordset = MyDatabase.OpenRecordset(SelectLoanItem)
    Do Until Myrecordset.EOF
        DBGridLoanItem.Row = iRow
        DBGridLoanItem.Columns(0) = Myrecordset!itemno
        DBGridLoanItem.Columns(1) = Myrecordset!Description
        DBGridLoanItem.Columns(2) = Myrecordset!Serial
        iRow = iRow + 1
        Myrecordset.MoveNext
    Loop
0
Comment
Question by:yyjulie
  • 8
  • 7
  • 5
20 Comments
 
LVL 6

Expert Comment

by:clifABB
ID: 1441506
Add this code:
    Do Until Myrecordset.EOF
        If iRow > DBGridLoanItem.Rows Then  'Add these three lines
            DBRridLoanItem.Rows = iRow
        End If
        DBGridLoanItem.Row = iRow
        .
        .
        .
0
 

Author Comment

by:yyjulie
ID: 1441507
I have tried your proposed answer, however, the effect is the same.  "Invalid row number" error still occurs.
I found that it's always successful when I load the first invoice.
The case is that, if I load second which contains more details-line than the first one, this error would occur.(But I have already refresh the grid and reset iRow to 0 before loading details to it)

0
 
LVL 6

Expert Comment

by:clifABB
ID: 1441508
Try setting iRow to 1 instead of 0 (Row 0 is the heading row)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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 4

Expert Comment

by:zsi
ID: 1441509
yyjulie,

Make sure that you make clifAbb earn those 5 points.  Don't let him get away with just giving you text answers.  Insist on lots of code.
0
 
LVL 6

Expert Comment

by:clifABB
ID: 1441510
zsi:
C'mon, it's not the points, it's the pleasure in helping.
(Of course, I'm already in the top 15 list.)
;)
0
 
LVL 4

Expert Comment

by:zsi
ID: 1441511
Yes, I know.  It's all about helping people. blah blah blah.

This is why I always see people fighting each other>  It's not over points.

It's their earnest to help people. :)

The soapbox is free now.  Next!


hehehe
0
 
LVL 6

Expert Comment

by:clifABB
ID: 1441512
That's ok, I carry my own personal soapbox where ever I go.  :)
0
 

Author Comment

by:yyjulie
ID: 1441513
Hello clifABB,

I have tried to set iRow to 1, but same result.
Moreover, I can't load the first invoice.

I got some comments about the DBGrid of VB5, especially for the unbound one, people say that it's not flexible, and now i am experiencing from it!  Really, it's quite diffiult.  It's not easy to develop as the table form in SQL Windows.  I think their concept is the same.

They suggest me to buy a third-party product such as Apex True Grid.  i'm worry about whether it is really easy to use and more powerful than the DBGrid of VB5.


0
 
LVL 4

Accepted Solution

by:
zsi earned 0 total points
ID: 1441514
yyjulie,

Actually, it is the same company.  Apex created DBGrid for Microsoft on a contract-for-hire basis.  That is, they wrote the control for inclusion in VB5 under the agreement that Microsoft retains full rights to that version.

Apex, as part of that agreement, has a much more powerful and robust version called TrueGrid.

If you are concerned about its performance, you can always download an evaluation copy at httop://www.apexsc.com.

Hope this helps!

zsi
0
 

Author Comment

by:yyjulie
ID: 1441515
zsi's and clifABB,

Thank you for your helping hands.

Anyway, can someone help me to solve the problem of unbound  DBGrid.

0
 
LVL 6

Expert Comment

by:clifABB
ID: 1441516
Actually, in my opinion, for unbound grid, I much prefer MSFlexGrid.  It's actually a product of Videosoft (like DBGrid is a product of Apex), and has a much better interface for working with unbound code.
0
 
LVL 4

Expert Comment

by:zsi
ID: 1441517
I've resorted to Farpoint's Spread myself.  I needed ComboBoxes as a cell datatype.  I coded the whole functionality over the standard VB grid (with textboxes, too), but didn't feel comfortable with the results.  


0
 
LVL 6

Expert Comment

by:clifABB
ID: 1441518
yyjulie:
Are you locked into using DBGrid?  If there's not *too* much code behind it, and you don't mind switching, you might consider using MSFlex which also comes with VB.
If you decide on this, let me know and I'll help you with the switch.
0
 
LVL 4

Expert Comment

by:zsi
ID: 1441519
...and if you *do* have a lot of code, Apex's TrueGrid is a 'drop-in' replacement for the dbGrid.  Meaining that you will not have to change any of your code.

Or, at least, that's the theory.
0
 

Author Comment

by:yyjulie
ID: 1441520
I know that MSFlexGrid is suitable for displaying data but it does not allow to make changes in data.  Am I right?

0
 
LVL 6

Expert Comment

by:clifABB
ID: 1441521
You can edit a cell's contents with MSFlexGrid, however it's not quite as straightforward as you might like.

Add A textbox to your form.
  Set it's visible property to false.
  Set it's BorderStyle to 0 - None

Add the following code to MSFlexGrid's EnterCell event:
Private Sub MSFlexGrid1_EnterCell()

  With MSFlexGrid1
    Text1.Move .Left + .CellLeft, .Top + .CellTop, .CellWidth, .CellHeight
    Text1.Text = .TextMatrix(.Row, .Col)
    Text1.Visible = True
    Text1.SetFocus
  End With
End Sub

Add the following code to MSFlexGrid's LeaveCell event:
Private Sub MSFlexGrid1_LeaveCell()

  With MSFlexGrid1
    .TextMatrix(.Row, .Col) = Text1.Text
  End With
End Sub


0
 
LVL 4

Expert Comment

by:zsi
ID: 1441522
*danger* Will Robinson *danger*

Don't do it!  It seems harmless at first.  Just overlay a textbox on the grid.  What harm could it do?  

Then your users ask for a combo box.

Then they want graphics.

Then they want option buttons and checkboxes and drop-down trees and...

They you are out of hair.

I started down this seemingly innocent path of trying to make the stock grid control do things it wasn't meant to do.  I wasted too much time of my life and have since resorted to an editable grid (Spread, warts and all).

Also, a big problem with this method is that you lose a lot of your keyboard navigation.  Syncronizing the controls is also a major headache (at least, if you do it right and try to make the whole gamut one big object).  The grid control does not always report the proper cell cordinates and on and on.

I am a big fan of minimizing the number of controls used in a project.  I prefer to do as much as possible through code.  I wholeheartedly support the Common Control Replacement Project (http://www.mvps.org/ccrp).  However, when it comes to this control, you will find yourself spending more time working on the control than the project itself.

I normally agree with my esteemed colleague (yeah, right :), but this time I have to take a suggest a different approach.

zsi
0
 
LVL 6

Expert Comment

by:clifABB
ID: 1441523
zsi:
You do have a point, I was there as well.  However, this is the code that is supplied by VideoSoft (the original developers of the Flex control) for editing their grid.
0
 
LVL 4

Expert Comment

by:zsi
ID: 1441524
See Above.

:)
0
 

Author Comment

by:yyjulie
ID: 1441525
For both of you, zsi's and clifABB, thank you very much.  I really learnt from you. :)

I have decided to use either Apex DBGrid Pro or Spread 2.5.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

829 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