Problem with SQL Server, VB 5.0 using a Grid
'*******************************************

Software:
NT Server 4.0  (Service Pack 3)
SQL Server 6.5 (version 6.50.240, Service Pack 2)
VB 5.0         (service Pack 2)
Access 97      (Office 97 Service release 1)

Grid:
DBGrid (from VB 5.0)
Apex True DBGrid Pro 5.0 Evaluation
Sheridan Grid            Data Widgets 2.0 Evaluation

ODBC Driver:
SQL Server

Subject:
We want to update and add records in a SQL Server table through a Grid
control.

Needs:
We want to use Bound controls.

What have we done:
Create a project and a form
Added a Microsoft Remote Data Control to the form
Set up all the properties for the remote data control to permit editing
(see form1 code)
Added a grid control to the form.
Connected the grid control to the remote data control.
Set up all the properties for the grid control to permit Add, Edit and
Delete (see form1 code)

Problem:
New records are inserted into the database table BUT NOT IN THE RDC
resultset while edited records are updated both in the database and the
RDC.

Testing:
All the records can be edited.
The buffer line for new record is at the end of the grid and data in the
new record can be entered. When we are finished with the new record, the
BeforeUpdate Event for the grid and the validate event for the RDC (Remote
Data Control) are executed.
When using the Sheridan grid, the AfterUpdate event for the grid is then
executed (this is not happening with the Apex True DBGrid).
In both cases, the new record is appended to the SQL Server table (the
Update event of the RDC had an action = 12 - a new row was inserted into
the result set -)
When the user leaves the new line to an another line in the grid (a new
blank line or an existing record), the newly inserted record just disapear
from the grid (and from the RDC).
To see the new record, we have to run a Refresh for the RDC. This method
will show the new record, but after a refresh, the pointer is on the first
row of the grid (and no Find method is accessible neither from the RDC or
the RDC resultset) to point back to the newly inserted record.

NB: Bookmark cannot be used after a refresh, and a movelast is not a
solution (the new record can be in the middle of the grid after a refresh).

Notes:
1) For the RDC, almost all the combinations of  CursorDriver, EOFAction,
LockType and ResultSetType have been tried, and the problem is always the
same: New Records can only be seen in the grid after a refresh on the RDC.

2) The ODBC driver is not the problem, we can add new records with Access
97 using the same ODBC driver.

3) All the Grid controls we used had the same problem, so we expected the
problem to be related to the RDC (is this correct?).

'********************************************
Form1 Code and settings:
VERSION 5.00
Object = "{F6125AB1-8AB1-11CE-A77F-08002B2F4E98}#2.0#0"; "msrdc20.ocx"
Object = "{BC496AED-9B4E-11CE-A6D5-0000C0BE9395}#2.0#0"; "ssdatb32.ocx"
Object = "{D981334D-B212-11CE-AE8C-0000C0B99395}#2.0#0"; "ssdata32.ocx"
Begin VB.Form Form1
   Caption         =   "Form1"
   ClientHeight    =   8235
   ClientLeft      =   2280
   ClientTop       =   1395
   ClientWidth     =   6585
   LinkTopic       =   "Form1"
   ScaleHeight     =   8235
   ScaleWidth      =   6585
   Begin VB.CommandButton Command2
      Caption         =   "Antall poster"
      Height          =   375
      Left            =   1560
      TabIndex        =   7
      Top             =   6480
      Width           =   1815
   End
   Begin VB.CommandButton Command1
      Caption         =   "Size LevNr"
      Height          =   375
      Left            =   120
      TabIndex        =   6
      Top             =   6480
      Width           =   1335
   End
   Begin VB.CommandButton cmdUpdateGrid
      Caption         =   "cmdUpdateGrid"
      Height          =   495
      Left            =   4920
      TabIndex        =   5
      Top             =   5880
      Width           =   1335
   End
   Begin VB.CommandButton cmdRebinfGrid
      Caption         =   "cmdRebinfGrid"
      Height          =   495
      Left            =   3480
      TabIndex        =   4
      Top             =   5880
      Width           =   1335
   End
   Begin VB.CommandButton cmdRefreshMSRDC1
      Caption         =   "cmdRefreshMSRDC1"
      Height          =   495
      Left            =   1560
      TabIndex        =   3
      Top             =   5880
      Width           =   1815
   End
   Begin VB.CommandButton cmdRefreshGrid
      Caption         =   "cmdRefreshGrid"
      Height          =   495
      Left            =   120
      TabIndex        =   2
      Top             =   5880
      Width           =   1335
   End
   Begin MSRDC.MSRDC MSRDC1
      Height          =   495
      Left            =   240
      Top             =   6960
      Width           =   6135
      _ExtentX        =   10821
      _ExtentY        =   873
      _Version        =   327681
      Options         =   0
      CursorDriver    =   1
      BOFAction       =   0
      EOFAction       =   2
      RecordsetType   =   3
      LockType        =   4
      QueryType       =   0
      Prompt          =   3
      Appearance      =   1
      QueryTimeout    =   30
      RowsetSize      =   100
      LoginTimeout    =   15
      KeysetSize      =   0
      MaxRows         =   0
      ErrorThreshold  =   -1
      BatchSize       =   15
      BackColor       =   -2147483643
      ForeColor       =   -2147483640
      Enabled         =   -1  'True
      ReadOnly        =   0   'False
      Appearance      =   -1  'True
      DataSourceName  =   "NBF_Test"
      RecordSource    =   "Select * From Leverandor Order By LevNr"
      UserName        =   "sa"
      Password        =   ""
      Connect         =   ""
      LogMessages     =   ""
      Caption         =   "MSRDC1"
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851}
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
   End
   Begin SSDataWidgets_B.SSDBGrid SSDBGrid1
      Bindings        =   "frmSSGrid.frx":0000
      Height          =   5535
      Left            =   240
      TabIndex        =   1
      Top             =   120
      Width           =   6135
      _Version        =   131078
      BeginProperty HeadFont {0BE35203-8F91-11CE-9DE3-00AA004BB851}
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      AllowAddNew     =   -1  'True
      AllowDelete     =   -1  'True
      RowHeight       =   423
      Columns.Count   =   7
      Columns(0).Width=   1535
      Columns(0).Caption=   "LEVNR"
      Columns(0).Name =   "LEVNR"
      Columns(0).CaptionAlignment=   0
      Columns(0).DataField=   "LEVNR"
      Columns(0).DataType=   8
      Columns(0).FieldLen=   256
      Columns(1).Width=   4128
      Columns(1).Caption=   "Navn"
      Columns(1).Name =   "Navn"
      Columns(1).CaptionAlignment=   0
      Columns(1).DataField=   "Navn"
      Columns(1).DataType=   8
      Columns(1).FieldLen=   256
      Columns(2).Width=   2963
      Columns(2).Caption=   "Kontakt"
      Columns(2).Name =   "Kontakt"
      Columns(2).CaptionAlignment=   0
      Columns(2).DataField=   "Kontakt"
      Columns(2).DataType=   8
      Columns(2).FieldLen=   256
      Columns(3).Width=   3200
      Columns(3).Caption=   "Adr1"
      Columns(3).Name =   "Adr1"
      Columns(3).CaptionAlignment=   0
      Columns(3).DataField=   "Adr1"
      Columns(3).DataType=   8
      Columns(3).FieldLen=   256
      Columns(4).Width=   3200
      Columns(4).Caption=   "Adr2"
      Columns(4).Name =   "Adr2"
      Columns(4).CaptionAlignment=   0
      Columns(4).DataField=   "Adr2"
      Columns(4).DataType=   8
      Columns(4).FieldLen=   256
      Columns(5).Width=   3200
      Columns(5).Caption=   "LandKode"
      Columns(5).Name =   "LandKode"
      Columns(5).CaptionAlignment=   0
      Columns(5).DataField=   "LandKode"
      Columns(5).DataType=   8
      Columns(5).FieldLen=   256
      Columns(6).Width=   3200
      Columns(6).Caption=   "PostNr"
      Columns(6).Name =   "PostNr"
      Columns(6).CaptionAlignment=   0
      Columns(6).DataField=   "PostNr"
      Columns(6).DataType=   8
      Columns(6).FieldLen=   256
      _ExtentX        =   10821
      _ExtentY        =   9763
      _StockProps     =   79
      Caption         =   "SSDBGrid1"
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851}
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
   End
   Begin SSDataWidgets_A.SSDBData SSDBData1
      Bindings        =   "frmSSGrid.frx":0011
      Height          =   495
      Left            =   240
      TabIndex        =   0
      Top             =   7560
      Width           =   6135
      _Version        =   131075
      _ExtentX        =   10821
      _ExtentY        =   873
      _StockProps     =   79
      Caption         =   "SSDBData1"
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851}
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Sub cmdRebinfGrid_Click()
   SSDBGrid1.ReBind
End Sub

Private Sub cmdRefreshGrid_Click()
   SSDBGrid1.Refresh
End Sub

Private Sub cmdRefreshMSRDC1_Click()
   MSRDC1.Refresh
End Sub

Private Sub cmdUpdateGrid_Click()
   SSDBGrid1.Update
End Sub

Private Sub Command1_Click()
   SSDBGrid1.Columns(0).Width = 900
End Sub

Private Sub Command2_Click()
   MsgBox "Antall poster: " & MSRDC1.Resultset.RowCount
End Sub

Private Sub MSRDC1_Validate(Action As Integer, Reserved As Integer)
   MsgBox "MSRDC1_Validate Action: " & Action
   MsgBox "Antall poster: " & MSRDC1.Resultset.RowCount
End Sub

Private Sub SSDBGrid1_AfterInsert(RtnDispErrMsg As Integer)
   MsgBox "SSDBGrid1_AfterInsert"
   MsgBox "Antall poster: " & MSRDC1.Resultset.RowCount
End Sub

Private Sub SSDBGrid1_AfterPosChanged(ByVal WhatChanged As Integer, ByVal
NewIndex As Integer)
   MsgBox "SSDBGrid1_AfterPosChanged WhatChanged: " & WhatChanged & "  
NewIndex: " & NewIndex
   MsgBox "Antall poster: " & MSRDC1.Resultset.RowCount
End Sub

Private Sub SSDBGrid1_AfterUpdate(RtnDispErrMsg As Integer)
   MsgBox "Rad: " & SSDBGrid1.IsAddRow
   MsgBox "SSDBGrid1_AfterUpdate"
   MsgBox "Antall poster: " & MSRDC1.Resultset.RowCount
End Sub

Private Sub SSDBGrid1_BeforeInsert(Cancel As Integer)
   MsgBox "SSDBGrid1_BeforeInsert"
   MsgBox "Antall poster: " & MSRDC1.Resultset.RowCount
End Sub

Private Sub SSDBGrid1_BeforeUpdate(Cancel As Integer)
   MsgBox "SSDBGrid1_BeforeUpdate"
End Sub

Private Sub SSDBGrid1_RowLoaded(ByVal Bookmark As Variant)
   ' MsgBox "SSDBGrid1_RowLoaded  BookMark: " & Bookmark
End Sub

Private Sub SSDBGrid1_UpdateError(ByVal ColIndex As Integer, Text As
String, ErrCode As Integer, ErrString As String, Cancel As Integer)
   MsgBox "SSDBGrid1_UpdateError ColIndex: " & ColIndex & "  Text: " & Text
& " ErrCode: " & ErrCode & "   ErrString: " & ErrString
End Sub

'********************************************

Question:
Do you have a solution to this problem ?...

Are Pettersen and Louis Vivaldi
Mandator AS
Oslo

Thank's