-
June 11th, 2012, 02:23 PM
#16
Re: IF Statement
The logic of the code is still off an insert will not take place should it be a new record.
Personally I think I would set up some kind of list that would hold the row numbers that had been changed since the data was last saved then go through that list and update the records which were changed. It is kinda silly to loop through all the items in a grid and do a select for every row. What if the user changed only 1 record but it was the last row and there were 10,000 rows? You would be doing 9,999 selects that do not need to be done before you get to the right one. Where if you had just stored the row number when the user changed it you could do the same task with a single select statement and it would be much much faster.
Always use [code][/code] tags when posting code.
-
June 12th, 2012, 02:23 AM
#17
Re: IF Statement
Originally Posted by DataMiser
Personally I think I would set up some kind of list that would hold the row numbers that had been changed since the data was last saved then go through that list and update the records which were changed. It is kinda silly to loop through all the items in a grid and do a select for every row. What if the user changed only 1 record but it was the last row and there were 10,000 rows? You would be doing 9,999 selects that do not need to be done before you get to the right one. Where if you had just stored the row number when the user changed it you could do the same task with a single select statement and it would be much much faster.
I was getting there.. ..
what we going to look at now .. is the DGV's Row validating event....
Code:
Private Sub DgvPracExcl_RowValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DgvPracExcl.RowValidating
End Sub
Just note that this Event Fires the moment they move off the DGV cell that was changed..
Now we can tighten up the code to this..
Code:
Private Sub DgvPracExcl_RowValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DgvPracExcl.RowValidating
Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude where Prac_no = '" & _
DgvPracExcl.Rows(e.RowIndex).Cells(0).Value), conn)
Try
Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
While Autoreader.Read()
prac_no = Autoreader.GetValue(0)
prac_enabled = Autoreader.GetValue(1)
End While
End Using
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
If prac_enabled = DgvPracExcl.Rows(e.RowIndex).Cells(3).Value Then
'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
Else
MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
Call UpdatePracEnabled()
End If
End Sub
Then My next Question is what is in the Sub "UpdatePracEnabled"...
Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
WPF Articles : 3D Animation 1 , 2 , 3
Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.
-
June 12th, 2012, 06:13 AM
#18
Re: IF Statement
In the above code shouldnt it also match the prac_no = DgvPracExcl.Rows(irow).Cells(0).Value to make sure it is comparing the right prac_no and prac_enabled?
UpdatePracEnabled is the functon that performs the update as shown below;
Code:
Private Sub UpdatePracEnabled()
Dim conn As SqlConnection = GetDbConnection()
Dim query As String
Dim cmd As New SqlCommand
Dim irow As Integer
Try
query = "UPDATE dbo.TblPracExclude SET prac_enabled ='" & _
DgvPracExcl.Rows(irow).Cells(3).Value & "' where Prac_No='" & _
DgvPracExcl.Rows(irow).Cells(0).Value & "'"
cmd = New SqlCommand(query, conn)
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
End Sub
Many thanks
Last edited by dr223; June 12th, 2012 at 06:16 AM.
-
June 12th, 2012, 06:20 AM
#19
Re: IF Statement
Ahhh ... Now this explains it ALL ... Give the FULL picture and it becomes easier to fix...
this function needs to be called for EACH and EVERY Update, With the Correct parameter for each one too....
So modify the function as such..
Code:
Private Sub UpdatePracEnabled(Byval RowID as integer)
Dim conn As SqlConnection = GetDbConnection()
Dim query As String
Dim cmd As New SqlCommand
Dim irow As Integer
Try
query = "UPDATE dbo.TblPracExclude SET prac_enabled ='" & _
DgvPracExcl.Rows(RowID).Cells(3).Value & "' where Prac_No='" & _
DgvPracExcl.Rows(RowID).Cells(0).Value & "'"
cmd = New SqlCommand(query, conn)
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
End Sub
and the last code i gave you modify as such
Code:
Private Sub DgvPracExcl_RowValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DgvPracExcl.RowValidating
Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude where Prac_no = '" & _
DgvPracExcl.Rows(e.RowIndex).Cells(0).Value), conn)
Try
Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
While Autoreader.Read()
prac_no = Autoreader.GetValue(0)
prac_enabled = Autoreader.GetValue(1)
End While
End Using
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
If prac_enabled = DgvPracExcl.Rows(e.RowIndex).Cells(3).Value Then
'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
Else
MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
Call UpdatePracEnabled(prac_no)
End If
End Sub
And it all should now work...
Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
WPF Articles : 3D Animation 1 , 2 , 3
Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.
-
June 12th, 2012, 06:34 AM
#20
Re: IF Statement
Ok - how should I call the function - DgvPracExcl_RowValidating as
Call DgvPracExcl_RowValidating ()
Error: Argument not specified for parameter 'e' of private sub DgvPracExcl_RowValidating....
Thanks
Last edited by dr223; June 12th, 2012 at 06:56 AM.
-
June 12th, 2012, 07:37 AM
#21
Re: IF Statement
Originally Posted by dr223
Ok - how should I call the function - DgvPracExcl_RowValidating as
Call DgvPracExcl_RowValidating ()
Error: Argument not specified for parameter 'e' of private sub DgvPracExcl_RowValidating....
Thanks
You don't call it....
Originally Posted by GremlinSA
Just note that this Event Fires the moment they move off the DGV cell that was changed..
It's a DGV Event that is raised for a specified trigger...
Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
WPF Articles : 3D Animation 1 , 2 , 3
Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.
-
June 12th, 2012, 08:12 AM
#22
Re: IF Statement
Ok - this is what I have the functions
Code:
Private Sub DgvPracExcl_RowValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DgvPracExcl.RowValidating
Dim conn As SqlConnection = GetDbConnection()
Dim cmd As New SqlCommand
Dim prac_no As Integer
Dim prac_enabled As String = Nothing
'Dim irow As Integer
Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude where Prac_no = " & _
DgvPracExcl.Rows(e.RowIndex).Cells(0).Value), conn)
Try
Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
While Autoreader.Read()
prac_no = Autoreader.GetValue(0)
prac_enabled = Autoreader.GetValue(1)
End While
End Using
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
If prac_enabled = DgvPracExcl.Rows(e.RowIndex).Cells(3).Value Then
'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
Else
MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
Call UpdatePracEnabled(prac_no)
End If
End Sub
Code:
Private Sub UpdatePracEnabled(ByVal RowID As Integer)
Dim conn As SqlConnection = GetDbConnection()
Dim query As String
Dim cmd As New SqlCommand
'Dim irow As Integer
Try
query = "UPDATE dbo.TblPracExclude SET prac_enabled ='" & _
DgvPracExcl.Rows(RowID).Cells(3).Value & "' where Prac_No='" & _
DgvPracExcl.Rows(RowID).Cells(0).Value & "'"
cmd = New SqlCommand(query, conn)
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
End Sub
At runtime and when I change the value of prac_enabled from TRUE to FALSE and click another place I receive the message - Practice need update - I click OK ..
Tried it few times, and whenever I do the checkbox uncheck (i.e., change from TRUE to FALSE ) received the message.. and clicked OK..
When I check the database no chnage has been made.. Why?
Many thanks
-
June 12th, 2012, 09:34 AM
#23
Re: IF Statement
Use Debug (F9 & F8) to check the functions....
Are you sure that no changes are been made...
I'm on the road for the next hour and so..
Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
WPF Articles : 3D Animation 1 , 2 , 3
Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.
-
June 13th, 2012, 08:47 AM
#24
Re: IF Statement
Hi,
I am getting the update now I updated code to;
[code]
For RowID = 0 To DgvPracExcl.Rows.Count - 1
Try
query = "UPDATE dbo.TblPracExclude SET prac_enabled ='" & _
DgvPracExcl.Rows(RowID).Cells(3).Value & "' where Prac_No='" & _
DgvPracExcl.Rows(RowID).Cells(0).Value & "'"
cmd = New SqlCommand(query, conn)
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
Next
[code]
Two issues,
1) when I check/uncheck the checkbox its not INSTANT it takes few seconds to take effect on the datagridview - the first check is quick but the second onwards are very slow. IS to do with the code I inserted.
2) Secondly, I have added highlighted code;
Code:
Dim RecordsAffected As Integer = 0
Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude where Prac_no = " & _
DgvPracExcl.Rows(e.RowIndex).Cells(0).Value), conn)
Try
Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
While Autoreader.Read()
prac_no = Autoreader.GetValue(0)
prac_enabled = Autoreader.GetValue(1)
End While
End Using
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
If prac_enabled = DgvPracExcl.Rows(e.RowIndex).Cells(3).Value Then
'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
Else
'MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
Call UpdatePracEnabled(prac_no)
RecordsAffected = RecordsAffected + 1
If RecordsAffected > 0 Then
MsgBox(RecordsAffected & " Records inserted or updated")
Else
MsgBox("No Records need updating")
End If
End If
I receive after every update that 1 Records inserted or updated.
I want to do at the end of the update..
For example, I have done 3 changes - I receive the message at the end of all the update with 3 Records inserted or updated.
Thank you
-
June 13th, 2012, 09:23 AM
#25
Re: IF Statement
Originally Posted by dr223
Hi,
Two issues,
1) when I check/uncheck the checkbox its not INSTANT it takes few seconds to take effect on the datagridview - the first check is quick but the second onwards are very slow. IS to do with the code I inserted.
Code:
For RowID = 0 To DgvPracExcl.Rows.Count - 1
Your now Updating ALL THE RECORDS in the Database every time...
Originally Posted by dr223
2) Secondly, I have added highlighted code;
Code:
Dim RecordsAffected As Integer = 0
....
If prac_enabled = DgvPracExcl.Rows(e.RowIndex).Cells(3).Value Then
.....
Else
....
Call UpdatePracEnabled(prac_no)
RecordsAffected = RecordsAffected + 1
If RecordsAffected > 0 Then
MsgBox(RecordsAffected & " Records inserted or updated")
Else
MsgBox("No Records need updating")
End If
End If
I receive after every update that 1 Records inserted or updated.
Look how i highlighted it ....
You set it to 0 (zero) ..
You add one , AND Immediately ...
You check it
Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
WPF Articles : 3D Animation 1 , 2 , 3
Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.
-
June 13th, 2012, 10:15 AM
#26
Re: IF Statement
1)
Code:
Your now Updating ALL THE RECORDS in the Database every time...
How can I prevent that and ONLY update the record/records updated.
2) True, but this will trigger the message whenever a record is updated e.g., record 1 is changed it gives the message 1 Record inserted or updated.
I want to update all the records and have a final message saying like 10 records inserted or updated.. This kind of logic..
Many thanks
-
June 13th, 2012, 01:54 PM
#27
Re: IF Statement
Don't use EXIT FOR everywhere. It should only execute when you are done executing ALL records in the loop, or if you need to exit due to emergency. Not after the current one works! (Which is what you've described)
-
June 14th, 2012, 06:41 AM
#28
Re: IF Statement
Ok - I changed the Update code to
Code:
Dim conn As SqlConnection = GetDbConnection()
Dim query As String
Dim cmd As New SqlCommand
Try
query = "UPDATE dbo.TblPracExclude SET prac_enabled ='" & _
DgvPracExcl.Rows(RowID).Cells(3).Value & "' where Prac_No='" & _
DgvPracExcl.Rows(RowID).Cells(0).Value & "'"
cmd = New SqlCommand(query, conn)
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
With the above code, it doesn't do any updates at ALL.
Then when I change it to;
Code:
Dim conn As SqlConnection = GetDbConnection()
Dim query As String
Dim cmd As New SqlCommand
For RowID = 0 To DgvPracExcl.Rows.Count - 1
Try
query = "UPDATE dbo.TblPracExclude SET prac_enabled ='" & _
DgvPracExcl.Rows(RowID).Cells(3).Value & "' where Prac_No='" & _
DgvPracExcl.Rows(RowID).Cells(0).Value & "'"
cmd = New SqlCommand(query, conn)
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
Next
Updates each record even if no change has taken place and this is why as you mentioned it takes time before the second change becomes visible and takes effect.
So how can I update the code so that it updates each record rather than updating the whole table.
This should make it quicker to reflect the change.
Thanks
Thanks
Last edited by dr223; June 14th, 2012 at 06:45 AM.
-
June 15th, 2012, 08:05 AM
#29
-
June 15th, 2012, 08:14 AM
#30
Re: IF Statement
Originally Posted by GremlinSA
Use Debug (F9 & F8) to check the functions....
Have you checked the values with debug ?????
Have you checked that your passing the function the right RowID ????
With out your full project and Database we can't do the Debugging .. we can only offer sample codes and methods....
Get in and do some of the dirty work, it's the only way you will learn!!!!!
Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
WPF Articles : 3D Animation 1 , 2 , 3
Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|