Solved

Load data from SQL server to unbound DBGrid

Posted on 1998-05-05
20
351 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
Comment Utility
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
Comment Utility
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
Comment Utility
Try setting iRow to 1 instead of 0 (Row 0 is the heading row)
0
 
LVL 4

Expert Comment

by:zsi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
That's ok, I carry my own personal soapbox where ever I go.  :)
0
 

Author Comment

by:yyjulie
Comment Utility
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
Comment Utility
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
Comment Utility
zsi's and clifABB,

Thank you for your helping hands.

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

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 6

Expert Comment

by:clifABB
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
...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
Comment Utility
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
Comment Utility
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
Comment Utility
*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
Comment Utility
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
Comment Utility
See Above.

:)
0
 

Author Comment

by:yyjulie
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

771 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

12 Experts available now in Live!

Get 1:1 Help Now