Load data from SQL server to unbound DBGrid

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
yyjulieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

clifABBCommented:
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
yyjulieAuthor Commented:
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
clifABBCommented:
Try setting iRow to 1 instead of 0 (Row 0 is the heading row)
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

zsiCommented:
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
clifABBCommented:
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
zsiCommented:
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
clifABBCommented:
That's ok, I carry my own personal soapbox where ever I go.  :)
0
yyjulieAuthor Commented:
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
zsiCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yyjulieAuthor Commented:
zsi's and clifABB,

Thank you for your helping hands.

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

0
clifABBCommented:
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
zsiCommented:
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
clifABBCommented:
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
zsiCommented:
...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
yyjulieAuthor Commented:
I know that MSFlexGrid is suitable for displaying data but it does not allow to make changes in data.  Am I right?

0
clifABBCommented:
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
zsiCommented:
*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
clifABBCommented:
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
zsiCommented:
See Above.

:)
0
yyjulieAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.