DataGrid update DB not working as it should

[Españolita]

I have a DataGrid which is bound to an ADODC and it allows updates. The database consists of different tables and the SQL for this ADODC is "SELECT stID, stName, grID, grName FROM tblStudent, tblGroup WHERE tblGroup.grID=tblStudent.stGroup ORDER BY grName, stName". The student's group is referenced to the Group table Key field.
I want to be able to easily assign a student to a group by changing the Group column in the DataGrid. However, it changes the name of the group in tblGroup instead of the Group ID in tblStudent.
I can overcome this by showing stGroup in the datagrid and the user enters the Group code (key) instead of the group name but that is VERY messy!
In an ideal world I would have a combobox in the Group column and the user simply selects the correct group for that student.
Please can anyone help me with this, how to fix it, or suggest a different approach - preferably not msFlexGrid because that looks very complicated since it does not allow updates.
My code is below:

Private Sub FillGrid()
Dim sSQL As String
   sSQL = "SELECT stID, stName, grID, grName FROM tblStudent, tblGroup WHERE tblGroup.grID=tblStudent.stGroup"
   sSQL = sSQL & " ORDER BY grName, stName"
   ADOAllocate.RecordSource = sSQL
   ADOAllocate.CommandType = adCmdText
   ADOAllocate.Refresh
   dgAllocate.AllowRowSizing = False
   dgAllocate.Columns(0).Width = 0            'hide Key row
   dgAllocate.Columns(1).Width = 2500
   dgAllocate.Columns(1).Locked = True        'can't change student name
   dgAllocate.Columns(2).Width = 0            'hide group code
   dgAllocate.Columns(3).Width = 900          'Group
   dgAllocate.ScrollBars = dbgHorizontal
End Sub

[Españolita]

anybody? please?

[The Welshman]

How I would approach your problem:
As you only want to edit the group ID for the student (which you can't see), don't make the grid edittable. Instead, when the user clicks on the group, show a listbox/combobox/menu with the available groups. When the user makes a selection from there, run an SQL Update to set the groupID for that student and refresh the grid.

Probably not the slickest solution, but it should work.

[SamOscarBrown]

Buenos Dias!
May I recommend you move away from the ADODC control, and start using other controls to hold/display your database information (MSFlexgrids, listboxes, textboxes, etc) which you should get using ADO (and definitely not the old DAO) structured queries. You will find a LOT more immediate help with your projects. I'd recommend you start with THIS project, and rewrite it. It will save you lots of headaches down the road.

[gibra]

I have a DataGrid which is bound to an ADODC and it allows updates.
---/---
I can overcome this by showing stGroup in the datagrid and the user enters the Group code (key) instead of the group name but that is VERY messy!
---/---
In an ideal world I would have a combobox in the Group column and the user simply selects the correct group for that student.

1) In you SQL statement you must add ALL columns you need to update, eventually hide columns that user doesn't to access it.
2) you must use appropriate DataGrid events named After* and Before* to manage data, so you can accept or reject values entered by user.

3) Doesn't exists a 'bound' ComboBox to DataGrid (as in ComponentOne TrueDBGrid control), so if you want to use a ComboBox then you have to manage it by code using a hidden control, see:
How to Place a Combo in DataGrid in VB6
http://www.ehow.com/how_10031164_pla...agrid-vb6.html

4) I strongly recommend you to leave the ADODC control and replace it with the ADODB object (code only), much more efficient and bug-free.

[Españolita]

First, thank you for your replies.
OK, ADODC has been ditched. My new code now also includes the reference to the group code in the Student table:

Dim sSQL As String
   sSQL = "SELECT stID, stName, stGroup, grID, grName FROM tblStudent, tblGroup WHERE tblGroup.grID=tblStudent.stGroup"
   sSQL = sSQL & " ORDER BY grName, stName"
   Set CnAllocate = New ADODB.Connection
   CnAllocate.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=" & App.Path & "\Student.mdb;Persist Security Info=False"
   CnAllocate.Open
   Set RsAllocate = New ADODB.Recordset
   RsAllocate.CursorLocation = adUseClient
   RsAllocate.Open sSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
   Set dgAllocate.DataSource = RsAllocate
   dgAllocate.Refresh
   dgAllocate.AllowRowSizing = False
   dgAllocate.Columns(0).Width = 0            'hide Key row
   dgAllocate.Columns(1).Width = 2500
   dgAllocate.Columns(1).Locked = True        'can't change student name
   dgAllocate.Columns(2).Width = 0            'hide group code
   dgAllocate.Columns(3).Width = 0            'hide group code in Student table
   dgAllocate.Columns(4).Width = 900          'Group
   dgAllocate.ScrollBars = dbgHorizontal

Gibra. I had already found that link you gave me. The problem is that step 3 states:

Get the top position, row height and column width of the cell on "DataGrid1"

It doesn't say how to do that and searching has now given me anything. I tried to get the co-ordinates of the clicked cell as a starting point with this code:

Private Sub dgAllocate_Click()
Dim Rw As Integer
Dim Cl As Integer
Rw = dgAllocate.Row
Cl = dgAllocate.Col
MsgBox "Row:" & Rw & "  Col:" & Cl
End Sub

The problem with this is that is gives the row and column number of the PREVIOUS cell clicked on.
Can anyone give me guidance on how to follow Step 3 as mentioned above?

[gibra]

You don't use the Click event, which is fired for DataGrid control only, not for records collection.
As already sayed, use After* and Before* events, their allows you to stop/cancel/modify values entered by user Before and/or After the entered values.
Eventually, use the RowColChange event, also, but maybe secondary.

[SamOscarBrown]

Lo siento, Esp....
If I were using an MSFlexGrid instead of a bound datagrid, this would be easy. I would simply click on the field in the MSFlexGrid, noting it's row and column (textmatrix(rownum, colnum)), and record that value (like your grId/stgroup), and THEN, I'd write an update query replacing those values in both tables. I simply don't use datagrids (not because they are not functional, but my experience has been (for MANY years) with flexgrids). I wish I could advise on your datagrid--I DO recognize your issue, but can't offer any good advice (except maybe using an MSFlexgrid--~smile~).
I am sure someone will assist you soonest.

[Españolita]

gracias Sam,
I tried adding MSFlexGrid but I couldn't attach it to my ADODB.Some checking online showed I need an MSHFlexGrid for that, so I installed that. I can now click on a cell and get it to show a ComboBox over that cell. I need guidance now, with how to take the value selected from the ComboBox, update the DataBase and refresh the HFlexGrid (because it doesn't allow editing.
My relevant code is below.:

Private Sub FillGrid()
Dim sSQL As String
   sSQL = "SELECT stID, stName, stGroup, grID, grName FROM tblStudent, tblGroup WHERE tblGroup.grID=tblStudent.stGroup"
   sSQL = sSQL & " ORDER BY grName, stName"
   Set CnAllocate = New ADODB.Connection
   CnAllocate.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=" & App.Path & "\Student.mdb;Persist Security Info=False"
   CnAllocate.Open
   Set RsAllocate = New ADODB.Recordset
   RsAllocate.CursorLocation = adUseClient
   RsAllocate.Open sSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
   Set hfgAllocate.DataSource = RsAllocate
   hfgAllocate.Refresh
   hfgAllocate.ColWidth(0) = 0
   hfgAllocate.ColWidth(1) = 0               'hide key row
   hfgAllocate.ColWidth(2) = 3500            'show name
   hfgAllocate.ColWidth(3) = 0               'Hide group code
   hfgAllocate.ColWidth(4) = 0               'Hide group code in student table
   hfgAllocate.ColWidth(5) = 1200             'show Group name
   hfgAllocate.ScrollBars = flexScrollBarHorizontal
End Sub
 
Private Sub hfgAllocate_Click()
   If hfgAllocate.Col = 5 Then
      Combo1.Top = hfgAllocate.CellTop + hfgAllocate.Top - 30
      Combo1.Left = hfgAllocate.CellLeft + hfgAllocate.Left
      Combo1.Width = hfgAllocate.CellWidth - 15
      Combo1.Visible = True
   End If
End Sub

ps. Spanish is not necessary. I'm English, I just happen to live in Spain

[gibra]

Lo siento, Esp....
If I were using an MSFlexGrid instead of a bound datagrid, this would be easy. I would simply click on the field in the MSFlexGrid, noting it's row and column (textmatrix(rownum, colnum)), and record that value (like your grId/stgroup), and THEN, I'd write an update query replacing those values in both tables. I simply don't use datagrids (not because they are not functional, but my experience has been (for MANY years) with flexgrids). I wish I could advise on your datagrid--I DO recognize your issue, but can't offer any good advice (except maybe using an MSFlexgrid--~smile~).
I am sure someone will assist you soonest.

My experience is:

1 - I don't use any flex grid (MsFlexGrid, MsHFlexGrid, VSFlexGrid, ...). I use ComponentOne TrueDBGrid. If I had not the TrueDBGrid I would use the DataGrid.
I used the FlexGrid for my customer, but it was a suffering, accustomed to using the TrueDBGrid!
Because of the integration between ADODB.Recordset and DataGrid/TrueDBGridis excellent.
When I change a value in the source (ADODB.Recordset) by code, it is automatically reflected in the DataGrid.
FlexGrid dosn't have a prepared methods to refresh data, as Requery and Resync, not to mention the performance for Filter, Find that in the any flexgrid are completely absent, and finally the Sort of flexgrid is not even comparable to the Sort of DataGrid .
I understand that the FlexGrid can have merged cells, colored, etc.. but these do not justify the lack of advanced features which instead has the DataGrid.


2 - I don't allow row-edit, never.
User must double-click the row to open a Form for Add or Edit record.
The data entry is more easy, by user, because into a Form I can use any type of specialized control (for numbers, dates, combobox, ...)
I can verify/check the data much better.
After saved, any changes are reflected instantly into DataGrid, simply calling Requery method.

Forgive me, but there is no comparison when working with data (database).

[Españolita]

OK, I have now got to the stage where I get the StudentID from column 1 of the clicked row in my HFG and I get the GroupID from the ItemData in the Combobox. I update the cell in my HFG with combobox.Text but this obviously does not update the Access DB.

My question now is, how do I update the DB? Can I just use the StudentID, and update the single field (stGroup) or do I have to load ALL the fields in the Student Table, hide all the unnecessary 30 or so columns, then, when the user changes the group allocation, read back the info in all the columns for that row in the HFG and rewrite the entire record?

[SamOscarBrown]

Your first response to your question is correct. If you can get the value of the ONE StudentID you want to update, put the group id and name into variables and do a new update SQL
e.g., update tblStudent set grId = myIDVariable, grName = myNameVariable where stID = myIDVariable
do the same thing for the other table using appropriate variables

You can do this on the click_event of your Grid. Now, I'm not sure how this will work with a bound grid....like I said before, I always use flexgrids and populate them with sql queries by going thorugh my recordcollection. I am sure there are plenty of folks out there who can help you with bound grids....I just don't use them.

ANYONE? Doog, DMiser, Dilettante, MAx??????? (and I am missing a lot of the EXPERTS out there).

[Españolita]

Thank you Sam, I have it working now. I didn't know about SQL SET.
+ rep for you.

[SamOscarBrown]

Gracias Esp.....glad you got it working......have a good night.