Datagrid question

[WiseGuy]

yeah it's me again, if I drive you guys nut, just tell me try to keep in mind that I'm an absolute newbe!

I'm trying to fill my datagrid with a table from an oracle DB but it doesn't do a thing, I don't get any error's so I gues everythings ok, but I propably forgot somthing.
Also I don't understand half of what's in the code so I'll just put the complete form here:

Option Explicit

Private cn As ADODB.Connection 'this is the connection
Private rs As ADODB.Recordset 'this is the recordset

' Available command buttons
Private Enum CommandButtons
  BUTTON_TOEVOEGEN
  BUTTON_INZIEN
  BUTTON_WIJZIGEN
  BUTTON_VERWIJDEREN
  BUTTON_ZOEKEN
  BUTTON_AFDRUKKEN
  BUTTON_SLUITEN
End Enum

' Save pointer of form to return to
Dim fMyReturnForm As Form
Dim fThisForm As Form

Private Sub cmdCommand_Click(Index As Integer)

  Dim strSQL As String
  Dim strValue As String
  
  StoreReturnForm Me  ' Set pointer to form to return to
  Select Case Index
  Case BUTTON_TOEVOEGEN
    gsFormParm = CMD_ADDRCD
    frmArtikelen.Show
    Me.Enabled = False
  Case BUTTON_INZIEN
    'gsFormParm = CMD_VIEW & "|" & datPrimaryRS.Recordset![ArtikelId]
    frmArtikelen.Show
    Me.Enabled = False
  Case BUTTON_WIJZIGEN
    'gsFormParm = CMD_EDITRCD & "|" & datPrimaryRS.Recordset![ArtikelId]
    frmArtikelen.Show
    Me.Enabled = False
  Case BUTTON_VERWIJDEREN
    'gsFormParm = CMD_DELRCD & "|" & datPrimaryRS.Recordset![ArtikelId]
    frmArtikelen.Show
    Me.Enabled = False
  Case BUTTON_ZOEKEN
    If GeneralDialog(DIATYPE_INVOER, Me.Caption, _
                 "Artikelnummer?", strValue) = DIABUTTON_OK Then
      If (Not IsNull(strValue)) _
         And (Len(strValue) <> 0) _
         And (IsNumeric(strValue)) Then
        strValue = "ArtikelId>=" & strValue
        rs.Find strValue
        If Not rs.EOF Then ' Gevonden!
          grdDataGrid.Refresh
        Else
          GeneralDialog DIATYPE_MELDING, Me.Caption, "Artikelnummer niet gevonden!", ""
        End If
      Else
        GeneralDialog DIATYPE_MELDING, Me.Caption, "Artikelnummer niet correct!", ""
      End If
    End If
  Case BUTTON_AFDRUKKEN
    PrintTableReport frmHoofdMenu.rptReport, RPT_ARTIKELEN
  Case BUTTON_SLUITEN
    fMyReturnForm.Enabled = True ' Re-enable the return form
    fMyReturnForm.Show     ' MLS Toegevoegd voor display van bovenliggend menu 19-11-98
    Screen.MousePointer = vbDefault
    Unload Me
  End Select
  
End Sub

Private Sub Form_Activate()

  Dim FindCrit As String
  Dim FindParm As String
  
  FindParm = GetParmInString(gsFormParm, 2)
  If Len(FindParm) <> 0 Then
    Select Case GetParmInString(gsFormParm)
    Case CMD_ADDRCD, CMD_EDITRCD, CMD_DELRCD
      FindCrit = "ArtikelID>=" & GetParmInString(gsFormParm, 2)
      rs.Find (FindCrit)
      'If (rs.RecordCount <> 0) And rs.NoMatch Then
      If (rs.RecordCount <> 0) And rs.EOF Then
        rs.MoveFirst
      End If
    End Select
  End If
  
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

  CheckFunctionKey KeyCode, Shift
  
End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)

  Select Case KeyAscii
  Case Asc("T"), Asc("t")
    cmdCommand_Click (BUTTON_TOEVOEGEN)
  Case Asc("I"), Asc("i")
    cmdCommand_Click (BUTTON_INZIEN)
  Case Asc("W"), Asc("w")
    cmdCommand_Click (BUTTON_WIJZIGEN)
  Case Asc("V"), Asc("v")
    cmdCommand_Click (BUTTON_VERWIJDEREN)
  Case Asc("Z"), Asc("z")
    cmdCommand_Click (BUTTON_ZOEKEN)
  Case Asc("A"), Asc("a")
    cmdCommand_Click (BUTTON_AFDRUKKEN)
  Case Asc("S"), Asc("s")
    cmdCommand_Click (BUTTON_SLUITEN)
  Case 27  ' ESC
    cmdCommand_Click (BUTTON_SLUITEN)
  End Select
    
End Sub

Private Sub Form_Load()
Dim SQL As String
'Me.MousePointer = 11 'this makes the mouse pointer the hourglass
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Data Source=SpijsDB;User ID=spijs;Password=spijs;"
    cn.Open
    Set rs = New ADODB.Recordset
    rs.Open "Artikelen", cn, adOpenKeyset, adLockPessimistic, adCmdTable
  PositionForm Me, FORMTYPE_BROWSER   ' Put form in the right place
  rs.Close
  SQL = "select * from artikelen"
  rs.Open SQL, cn, adLockOptimistic

  Set fMyReturnForm = GetReturnForm()
  'datPrimaryRS.DatabaseName = spijsDB
  
  If GetSecurityLevel < 2 Then
    cmdCommand(BUTTON_TOEVOEGEN).Enabled = False
    cmdCommand(BUTTON_WIJZIGEN).Enabled = False
    cmdCommand(BUTTON_VERWIJDEREN).Enabled = False
    cmdCommand(BUTTON_AFDRUKKEN).Enabled = False
  End If
  
End Sub

Private Sub Form_Unload(Cancel As Integer)
  
  Screen.MousePointer = vbDefault

End Sub

If someone can help me out again I would appreciate it a thousand times!

WiseGuy

[PilgrimPete]

OK. A few questions, a few answers, a few points...


These two chunks of code do pretty much the same thing. Don't know why you need both...

rs.Open "Artikelen", cn, adOpenKeyset, adLockPessimistic, adCmdTable
 
  SQL = "select * from artikelen"
  rs.Open SQL, cn, adLockOptimistic

Next, you don't seem to be telling your grid what to display. I'd expect something like this:

datPrimary.RecordSource = "..." [color=green]'something based on the record you've just found[/color]
grdDataGrid.DataSource = datPrimaryRS
grdDataGrid.Refresh

It's not really clear what goes in the grid, so I hope I've guessed right!

[WiseGuy]

So I need a datacontroller? becouse I thought it would possible to do everything in code? What I would like to see is that I can fill the grid with an sql statement, is that possible or do I have to use a datacontroller?

WiseGuy

[SineX]

With an ADO recordset called RS and an ADO connection called CNT.

RS.Open sSQL, CNT, adOpenDynamic, adLockOptimistic
    Set DataGrid1.DataSource = RS

just change sSQL to whatever query you want
Select * from table1
Select * from table2
select fields ect.....

[PilgrimPete]

No, you don't need to use them. Sorry, I saw your references to them in the code and assumed (never, never assume anything!).

Something like this then:

set grdDataGrid.DataSource = rs
grdDataGrid.Refresh

But now I don't know what you're trying to achieve with the Find...

[PilgrimPete]

I'm posting slow today. I must get a new ribbon for my typewriter...

[WiseGuy]

When I use the code of pelgrimpete I get runtime error 430 'Class does not support expected interface'

and exactly the same error with the other code from SineX

WiseGuy

[PilgrimPete]

I think you're still using a DBGrid (DAO control). Add a DataGrid to your project toolbox and use that instead...

[WiseGuy]

I'm getting a different error now, I've replaced the old grid for the new one as you told me, and I'm using the following code now:

  Dim sSQL As String
    
  sSQL = "select a.artikelID, a.productcode, a.artikelnaam, b.typecode, a.tolerantiepluskg, c.celnaam, a.natdroogverh from artikelen a, artikeltype b, cellen c"
  rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic
  Set grdDataGrid.DataSource = rs

I know that the SQL statement correct is, so that's not the problem but I keep getting the following error:

'Runtime error 7004, The rowset is not bookmarkable.'

WiseGuy

[PilgrimPete]

Is artikelID defined as a primary key on the table?

I found this: http://dbforums.com/archive/130/2001/11/3/209459 which might help...

[PilgrimPete]

The other possibility I turned up is to do with cursor location. Set it to client side:

cn.CursorLocation = adUseClient

[WiseGuy]

I'm going crazy right now!
ArtikelID was indeed a Primary key, so that wasn't the problem then I tried the code you gave me (don't exactly know what it is but that's ok) that got rid of the error.
But I still have no data in my grid and I don't get any error's either!

I'm lost.......


WiseGuy

[PilgrimPete]

Do you refresh the grid after setting the recordset?

Do you know that the recordset has records in it?

[Cursor location just sets where the recordset is 'held' adUseClient stores it in your local machine's memory; adUseServer stores it in the server's memory. I don't fully understand all the differences, I just know that there are some and they expose different methods. I've seen a few threads on this site that will explain much better if you're interested...]

[WiseGuy]

Yes I do know that there are records in the table ( if that is what you mean)

This is the code I'm using right now:

  cn.CursorLocation = adUseClient
  sSQL = "select a.artikelID, a.productcode, a.artikelnaam, b.typecode, a.tolerantiepluskg, c.celnaam, a.natdroogverh from artikelen a, artikeltype b, cellen c"
  rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic
  Set grdDataGrid.DataSource = rs
  grdDataGrid.Refresh

WiseGuy

[WiseGuy]

I've found out that some tables do work, and some don't! And I have no idea why that is!
This is the code that works: (I got rid of all the bull****)

Option Explicit

Private cn As ADODB.Connection 'this is the connection
Private rs As ADODB.Recordset 'this is the recordset

Private Sub Form_Activate()
  Dim sSQL As String
  Set cn = New ADODB.Connection
  cn.ConnectionString = "Data Source=spijsDB;User ID=spijs;Password=spijs;"
  cn.Open
  Set rs = New ADODB.Recordset
  grdDataGrid.Refresh
  cn.CursorLocation = adUseClient
  sSQL = "select * from test"
  rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic
  Set grdDataGrid.DataSource = rs
  grdDataGrid.Refresh

End Sub

any suggestions??

WiseGuy