Solved

MySQL Data Grid with Inner Join

Posted on 2004-09-28
38
1,055 Views
Last Modified: 2013-12-25
Hello,

I'm converting my database from access to Mysql. However , I'm exprience a problem implementing the datagrid in mysql.
in access it work fine.
in mySql I can read perfectly , however when I make changes in the datagrid I get an error:
"Row cannot be located for updating. Some values may have been changed since it was last read.".
i'm using visual basic 6.
I did some experiments, I do a "normal" query (like select * from table) it work fine.
this problem is driving me crazy.
please help, thanks in advance.

here is my code:
Private Sub ShowPrevResults()
Dim myDbase1 As ADODB.Connection
Dim myRst1 As ADODB.Recordset
Dim myRst2 As ADODB.Recordset
Dim mySQL1 As String
Dim mySql2 As String
Dim i As Integer
Dim myPath As String
Dim PatientNumber
'-----------------------------------------------------------------------------
  DBGrid1.Move 10, 80, 4400, 500
   DBGrid2.Move 10, 580, 4400, 300
DBGrid1.ZOrder 0
DBGrid2.ZOrder 0
'-----------------------------------------------------------------------------
DBGrid1.Visible = True

myPath = App.Path
PatientNumber = (StatusBar.Panels("SerialNumber").Text)
If Not IsNumeric(PatientNumber) Then
    MsgBox "No Patient select", , "NICOM System"
    Exit Sub
End If
If Val(PatientNumber) = 0 Then Exit Sub

Screen.MousePointer = vbHourglass


mySQL1 = "SELECT CONCAT(LastName, ' ' ,FirstName)  AS Name, tblNico.Date, CONCAT(tblNico.Time) As E_Time, " _
        & "tblNico.IntegralDzDt, tblNico.dZdt, tblNico.VET, tblNico.SV, tblNico.CardOut, tblNico.COC, " _
        & " tblNico.CI, tblNico.Z0, tblNico.L, CONCAT(tblNico.BeatPerMinute) AS BPM, tblNico.StudyNumber, " _
        & " tblNico.VascResist, tblNico.Note1 FROM tblPatient INNER JOIN tblNico ON " _
        & " tblPatient.SerialNumber = tblNico.SerialNumber Where (((tblNico.SerialNumber) =" & PatientNumber & ")) ORDER " _
        & " BY tblNico.StudyNumber DESC, tblNico.Date DESC, tblNico.Time DESC"
Set myDbase1 = New ADODB.Connection
Dim sConnection As String
    sConnection = _
    "DRIVER={MySql ODBC 3.51 Driver};server=localhost;uid=root;pwd=;database=nicodb"
myDbase1.Open sConnection
Set myRst1 = New ADODB.Recordset
myRst1.CursorLocation = adUseClient
myRst1.Open mySQL1, myDbase1, adOpenStatic, adLockOptimistic

Set DBGrid1.DataSource = myRst1
''DBGrid1.AllowUpdate = True

For i = 0 To 14
    DBGrid1.Columns(i).Alignment = dbgCenter
Next i

DBGrid1.Columns(1).NumberFormat = "dd/mm/yy"

DBGrid1.Refresh


DBGrid1.Visible = True
DBGrid1.ZOrder 0
Screen.MousePointer = vbNormal

----------

Thanks,
Eyal

0
Comment
Question by:eylkrn
  • 20
  • 14
  • 4
38 Comments
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
In ADO if your SQL query is based on JOINed tables then you can not use the Update method of a recordset.  This is what is happening then you change the value in your grid.

What you need to do is to run an UPDATE SQL statement to change the values instead and then reload the grid.

Leon
0
 

Author Comment

by:eylkrn
Comment Utility
Hi Leon,

If in ADO I cant based on JOINEd table so how does the datagrid work with the access database?

What I want is that the users will be able the update the database via the datagird (like they used to do with the access based program). it is very important. I even though on coding this datagrid in ado.net (vb.net).

Please tell me if I can do this with ADO and if not then how can I do it with ADO.net?

Eyal

0
 
LVL 29

Assisted Solution

by:leonstryker
leonstryker earned 100 total points
Comment Utility
>>If in ADO I cant based on JOINEd table so how does the datagrid work with the access database?

Were you coding in Access or VB?

>>What I want is that the users will be able the update the database via the datagird

Like I said, you will have to use SQL INSERT statements to do this.

Leon
0
 

Author Comment

by:eylkrn
Comment Utility
>>Were you coding in Access or VB?
my program is in Vb. It is working with access mdb databases via ADO.  it works fine.
I want to change the database to mySQL however , I have this datagrid problem that not appear with the access.

>>Like I said, you will have to use SQL INSERT statements to do this.

can you please be more specific ? how can I tell the datagrid which statements it should use?

Eyal




0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
>>my program is in Vb. It is working with access mdb databases via ADO.  

I am suprised it worked, but Access does certain things differently, so it is possible.

>>how can I tell the datagrid which statements it should use?

You basically look at the values in the grid and test for the ones which the user changed.  Once you find one you update it.  You can set this up in the Change or some other even of the grid.
0
 

Author Comment

by:eylkrn
Comment Utility
Hi Leon,

I'm trying to implement what you suggested
What I though is to do a list which will save which cells were updated and then update them . I added this code for testing to the Change event of the datagrid1:
----
   MsgBox DBGrid1.col
    MsgBox DBGrid1.Row
    DBGrid1.col = 15
    DBGrid1.Row = 0
    MsgBox DBGrid1.Text
----
However , I have a question: How can I tell the datagrid not to update the cells but do allow editing?
if I do dbgrid1.allowupdate=false then the user cannot edit the cell at all and also I get a runtime error on these lines:
    DBGrid1.col = 15
    DBGrid1.Row = 0
    MsgBox DBGrid1.Text
. so my second question  is how can I read a cell content of a datagrid?

Eyal
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
>> How can I tell the datagrid not to update the cells but do allow editing?

Disconnect the recordset.  Set the recordset ActiveConnection property to Nothing.

>>how can I read a cell content of a datagrid?

You should be able to access the specific row and column of a grid.

Leon
0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
It probably worked differently in Access because you weren't breaking any rules. Some joins are updateable and some are not. Check your constraints on your SQL Table.

Also the variable you have for the recordset should be at form level, if you put them in a procedure they will run out of scope when the procedure finishes. Thats what it sounds like with the datagrid not being able to pick the recordset up. Your code as well has the recordset defined at the procedural level.
0
 

Author Comment

by:eylkrn
Comment Utility
Hi,

Arundel - I've checked my mysql db. It seem that the constraints are ok. can I send an empty access and mysql database that you'll look at it?

Leon - I've coded a procedure that will track the changes in the datagrid and update the mysql via update statement . however, there is some bug. I think its not in my code , but in the datagrid:
There is a column name "Note", If its empty everything work fine. but if not , I will describe the bug:
Lets add the value "TEST" in row1  .  now if I change some row for "TES" its work fine, however If I will change some row to "TEST1" (or another long string than the length of "TEST"(4 chars) I will get an error: "Multiple-step operation generated error Check each status value." . I should note that although the datagrid will ignore my change it is not a runtime error and my code that track the changes DO update the mysql (so If I will open the datagrid again I will see "TEST1" ).

here is my code :

---
update_grid=true
If change_grid = False Then
    myRst1.ActiveConnection = Nothing
End If
-------

Private Sub DBGrid1_Change()
    change_flag = True
End Sub

Private Sub DBGrid1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  Dim time_val As String
  Dim date_val As String
  Dim col_name As String
 If change_flag = True Then
   time_val = DBGrid1.Columns(2).Text
   date_val = DBGrid1.Columns(1).Text
   Select Case DBGrid1.col
        Case 13: col_name = "StudyNumber"
        Case 15: col_name = "Note1"
    End Select
   
   List3.AddItem ((StatusBar.Panels("SerialNumber").Text) & "," & date_val & "," & time_val & "," & col_name & "," & DBGrid1.Text)
   
   change_flag = False
End If
End Sub

Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
DBGrid1.Visible = False
DBGrid2.Visible = False

If update_grid = True Then
    If List3.ListCount <> 0 Then
        Dim i
    Dim sConnection As String
          sConnection = _
        "DRIVER={MySql ODBC 3.51 Driver};server=localhost;uid=root;pwd=;database=nicodb"
        myDbase.Open sConnection

        For i = 0 To (List3.ListCount - 1)
           '64,17/08/04,15:06:32,Note1,value (example of the format)
           Dim sn
           Dim date_val
           Dim time_val
           Dim col
           Dim val
           Dim sSpilt
           Dim mySql
           sSpilt = Split(List3.List(i), ",")
           sn = sSpilt(0)
           date_val = sSpilt(1)
           time_val = sSpilt(2)
           col = sSpilt(3)
           val = sSpilt(4)
           
          mySql = "UPDATE tblNico SET " & col & "='" & val _
                        & "' WHERE (SerialNumber=" & sn & ") AND" _
                        & " (Time=STR_TO_DATE('" & time_val & "','%H:%i:%s') ) AND" _
                        & " (DATE=STR_TO_DATE('" & date_val & "','%d/%m/%y'));"
        myDbase.Execute (mySql)
        Next i
        myDbase.Close
        List3.Clear
    End If
    update_grid = False
End If
End Sub


Thanks,
Eyal



0
 
LVL 3

Accepted Solution

by:
Arundel_Castle earned 400 total points
Comment Utility
You don't need to send it to me. Run your join in the SQL Enterpirse Mangaer as a view. If its updatable there, then the problem lies with your data grid. If its still not updatetable check that you don't disallow null on some of the tables or give them default values. The problem is that your probably not pulling all the fields from both tables and then when you adding new data its conflicting with your table definition.
0
 
LVL 3

Assisted Solution

by:Arundel_Castle
Arundel_Castle earned 400 total points
Comment Utility
Opps I'm thinking about SQL Server not mySQL. Your bound to be able to create views in MySQL but I don't know what the support software is like. If you create a view based on your join and issue and update statement against that will tell you if the view is updateable. If you can open views like in SQL Enterprise mangager you can check if there updatable by simply changing values.
0
 

Author Comment

by:eylkrn
Comment Utility
Hi Arundel,

for mySql i'm using navicat from premium soft (www.navicat.com), I've run my query and it is not updatable. wierd, does the odbc provider for access does magic? another Microsoft conspiracy??? :)


Eyal
0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
It is strange that your query is updateable in Access but not in mySQL. Its definately something to do with how you have created the tables in mySQL. Most of the time there should be no problems updating joins.
0
 

Author Comment

by:eylkrn
Comment Utility
I've check my tables again. I didnt found anything.
anyway,I did some testing,  I've added to the query the primary keys columns of the both tables and now I can update them via the datagird.

However , I still have this annoying bug that I describe before:
I'm adding the value "TEST" to the Note col.  .  now if I will add the value "TES" its work fine, however If I will change the value to "TEST1" (or another long string than the length of "TEST"(4 chars) I will get an error: "Multiple-step operation generated error Check each status value." .

0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
I think your getting a multiple step error because your trying to update from the grid and the update statement. When the grid is bound it should update the join without any need for an update statement. If you are going to use the update statement make sure your sending ALL the fields, if you don't you'll only get a couple updated. Its the same for Add New statment. All the fields must have a value that require a value. Again there should be no problem doing either but only use one method. Check your properties for the datagrid the "AllowUpdates" field showed be set to false if your going to use the update statement.
0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
Get the imediate window to print out your Update SQL statement and send me some code so I can have a look through along with SQL statement.
0
 

Author Comment

by:eylkrn
Comment Utility
As I said before :
>>I've added to the query the primary keys columns of the both tables and now I can update them via the datagird.

I dont use any update statement now.
The activeconnection is still myRst.
this bug also happen if the activeconnection is set to nothing.

Eyal

0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
Just as a note as well. Maybe this is off track but i'll tell you anyway. If don't have to use in line SQL statements. In fact I never do even when I'm working with access or SQL. I usually use Queries in Access or Views or Stored Procedures in SQL. If MySQL supports stored procedures you can place your join statment in one and specify a couple of parameters. You then can call up the recordset by supplying the parameters, plus stored procedures in SQL server are faster. Stored Procedures are not updateable though so you would have you use the update statement. Best way to do this is through a command object. Again you can either supply in line SQL or use another parameter stored procedure that hold your update statement and a couple of paremeters. This leaves you with much cleaner code (keeps SQL out of your program which is easier and clearer to debug) and as I said stored procedures are faster.

I say all this because like myself you are moving from Access to SQL. Every uses stored procedures in SQL and its not such a big leap to learn them. If someone is twisting your head about development time then maybe leave it for another project but there worth investing some time in. I'll be righting a few demo programs later on in VB6 using stored procedures. Using the demo databases supplied. If you like I will send them to you.
0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
I read also about how you wanted to do this in ADO.NET. Well its different becasue there is a different object model. It might be called ADO in .Net but it sure doesn't code like it. Thats a new ball game that you will have to learn.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
Have you specified the columns in the grid or are you leting the binding do it.
0
 

Author Comment

by:eylkrn
Comment Utility
what do you mean by "Have you specified the columns in the grid " ?
0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
What I mean is in the properties for the data grid you can specify the columns and the fields that they will bind to. What version of VB are you running and which service pack is installed. If you think about this and that it ran fine with accesss it should run fine with MySQL. Check if you have made any alterations. In fact create a new form and datagrid and place your only the code to open the recordset and bind it to the grid. Create a new grid as well. It won't take you long. Hopefully everthing will run correct first time and you won't have problems.
0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
Its a quick way to track down if there is a bug in the grid.
0
 

Author Comment

by:eylkrn
Comment Utility
I will check it . by the way , its very intersting what you said about stored procedures , i've checked and mysql does have stored procedure. I would like to have more information , like how you get the store procedure result into the recordset and some examples how to implement this. I know this is off-topic so if you can send me some related stuff in the email I would be grateful.
0
 

Author Comment

by:eylkrn
Comment Utility
also,
about my ver of VB:
Version 8176.
VBA: Retail 6.0.8169
Forms3: 11.0.5601
0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
This is all I use to connect to a data base in code. Notice that I use the Me.DataGrid1.ReBind to force the datagrid again to pick up the columns. This code lets me edit a data grid no problem.

conString = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=LearningDB;Data Source=JAMESDESKTOPPC"
MainCon.ConnectionString = conString
MainCon.Open

'MyRec.Open "authors", MainCon, adOpenForwardOnly, adLockReadOnly, adCmdTable

Set RecEmployees.ActiveConnection = MainCon
RecEmployees.CursorLocation = adUseServer
RecEmployees.CursorType = adOpenStatic
RecEmployees.LockType = adLockOptimistic
RecEmployees.Open "Employee", , , , adCmdTable

Set Me.DataGrid1.DataSource = RecEmployees
Me.DataGrid1.ReBind
0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
It should say which service pack in on if you have one just above where you picked up those numbers.
0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
Mine says Visual Basic(6) SP5. Theres actually a service pack 6 out now. I mention it becasue it was years ago that there was a problem with the datagrid. I think it was actually to do with setting the datasource that didn't cause a binding of the coulmns properly and you had to do a rebind after it. Don't quote me on this. You should run with the latest service packs that way you can almost be sure there is no underlying bugs around. I will definately send you some info on using stored procedures either today or tommorow cause I have to rework some things myself.
But in the mean time look it up on MSDN.
0
 

Author Comment

by:eylkrn
Comment Utility
I've installed SP6, it didn't help.
I've also tried your code, it doest work with adUseServer. I get an error -  "The Rowset is not bookmarkable".
and with adUseClient I still have this annoying bug.
0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
I'm just checking, have you done the new form thing like I asked and use the rebind method.
0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
What datatype is Note1 in the database.
0
 

Author Comment

by:eylkrn
Comment Utility
>>I'm just checking, have you done the new form thing like I asked and use the rebind method

yes. and as I said , it didnt work.

>>What datatype is Note1 in the database.
longtext, I've also tried text,mediumtext,varchar.




0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
Hmmm, this is strange. Everthing should work. Ntext is updateable in the view insn't it?
0
 

Author Comment

by:eylkrn
Comment Utility

>> Ntext is updateable in the view insn't it?
yes. I dont know why it doesnt work.
But I think ii'll open a new question regarding this topic.

0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
I don't understand it myself, i'm stuck in that I can't see the code or the database. Plus I don't have MySQL installed on my machine. You could script the database and send it to me and I could try running it under SQL server.
0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
Thanks for the points, though I haven't fully answered your question.
0
 

Author Comment

by:eylkrn
Comment Utility
Hi Arundel,

What do you mean by script the database?

0
 
LVL 3

Expert Comment

by:Arundel_Castle
Comment Utility
In SQL server you can create a script that will rebuild the database on another machine. Just a question, have you tried leaving out nText in your view and try and update the smaller view in the grid.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

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…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

744 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

13 Experts available now in Live!

Get 1:1 Help Now