-
June 7th, 2012, 10:51 AM
#1
IF Statement
Hi,
I have the following function, which when exceuted doesn't do the following -
a) Update if the checkbox on the datagrid is changed (Cell(3)).
b) Insert data when the prac_no doesn't exist in the database..
Any help will be highly apprecaited
Code:
Private Sub UpdatePraclist()
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 strFlag As String = "Insert"
For irow = 0 To DgvPracExcl.Rows.Count - 1
'Read the practice number and status from TblPracExclude
Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude"), 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_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
strFlag = "Ignore"
Exit For
Else
MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
strFlag = "Update"
Exit For
End If
End If
Next irow
If strFlag = "Insert" Then
Call PracticeInsert()
ElseIf strFlag = "Update" Then
Call UpdatePracEnabled()
End If
End Sub
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
Code:
Private Sub PracticeInsert()
Dim conn As SqlConnection = GetDbConnection()
Dim query As String
Dim cmd As New SqlCommand
Dim irow As Integer
Try
query = "INSERT INTO dbo.TblPracExclude (prac_no, prac_name, prac_status, prac_enabled) VALUES (" & _
DgvPracExcl.Rows(irow).Cells(0).Value & ", '" & _
Replace(DgvPracExcl.Rows(irow).Cells(1).Value, "'", "''") & "', '" & _
DgvPracExcl.Rows(irow).Cells(2).Value & "', '" & _
DgvPracExcl.Rows(irow).Cells(3).Value & "')"
cmd = New SqlCommand(query, conn)
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
End Sub
Instead - I keep receiving the message box;
No Practice need Update
Please could you help me out..
-
June 7th, 2012, 03:18 PM
#2
Re: IF Statement
Code:
While Autoreader.Read()
prac_no = Autoreader.GetValue(0)
prac_enabled = Autoreader.GetValue(1)
End While
Note that even though you are going through all the records in the table you are not doing anything with them, Only the last one will have an impact. Since your grid was most likely loaded from the same table then the last item in the table which is what you will end up with here is always going to match an item in the datagrid because it was placed there from the database orginally.
I think you need to step back and think about what you are tying to accomplish here and modify the design.
Always use [code][/code] tags when posting code.
-
June 8th, 2012, 04:32 AM
#3
-
June 8th, 2012, 07:09 AM
#4
Re: IF Statement
Quick shot here...
See what happens when you rearrange the code like this
Code:
'Read the practice number and status from TblPracExclude
Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude"), 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)
For irow = 0 To DgvPracExcl.Rows.Count - 1
If prac_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
strFlag = "Ignore"
Exit For
Else
MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
strFlag = "Update"
Exit For
End If
End If
Next irow
End While
End Using
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
and before anyone shouts... Yes the code is not 100% fixed, Just showing how Nested loops are used for this sorta problem...
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 8th, 2012, 08:08 AM
#5
Re: IF Statement
Ok - I have tried this....
Code:
Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude"), 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)
For irow = 0 To DgvPracExcl.Rows.Count - 1
If prac_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
strFlag = "Ignore"
Exit For
Else
MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
strFlag = "Update"
Exit For
End If
End If
Next irow
End While
End Using
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
I receive the Message:
No Practice need Update.
When I click OK, I do receive another similar message and so on.
When I changed - DgvPracExcl.Rows(irow).Cells(3).Value from True to False.
I still received the same message - No Practice need Update..
My expectation is to show message - Practice need Update and call the UpdatePracEnabled() function..
Please help me to fix this..
Many thanks
-
June 8th, 2012, 08:41 AM
#6
Re: IF Statement
Originally Posted by dr223
I receive the Message:
No Practice need Update.
When I click OK, I do receive another similar message and so on.
So now you should be reciving one for Each item in the list... and one of them will return what your looking for (you might have missed it)..
So now lets change a few more lines...
Code:
strFlag = "Ignore"
While Autoreader.Read()
prac_no = Autoreader.GetValue(0)
prac_enabled = Autoreader.GetValue(1)
For irow = 0 To DgvPracExcl.Rows.Count - 1
If prac_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
' MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
Exit For
Else
MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
strFlag = "Update"
Exit For
End If
End If
Next irow
End While
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 8th, 2012, 08:45 AM
#7
Re: IF Statement
When I initially suggested that you structure your loop that way I had assumed that prac_no and Prac_Enadbled actually held the values of the new data that was being saved. It wasn't until afterwards that you showed the code where those variable were being assigned from the DB which changes the approach needed.
In keeping with the code from your last post you would need to do something like
Code:
Try
Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
Dim RecordsAffected as Integer=0
While Autoreader.Read()
prac_no = Autoreader.GetValue(0)
prac_enabled = Autoreader.GetValue(1)
Dim FlagVar as String="Insert"
For irow = 0 To DgvPracExcl.Rows.Count - 1
If prac_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
strFlag = "Ignore"
Exit For
Else
'MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
strFlag = "Update"
Exit For
End If
End If
Next irow
If FlagVar="Insert" then
'Call Insert Rountine
RecordsAffected=RecordsAffected+1
ElseIf FlagVar="Update"
'Call Update Routine
RecordsAffected=RecordsAffected+1
End If
End While
If RecordsAffected>0 then
Msgbox RecordsAffected & " Records inserted or updated"
Else
Msgbox "No Records need updating"
End If
End Using
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
Be aware that this should work but looping through all the data in the database is not the way to go. If the database has a lot of records it would be very slow and would be slower with more records to the point of being unacceptable.
Last edited by DataMiser; June 8th, 2012 at 08:48 AM.
Always use [code][/code] tags when posting code.
-
June 8th, 2012, 09:21 AM
#8
Re: IF Statement
Ok - Tried DataMiser Suggestion and had a code as;
Code:
Try
Using Autoreader As System.Data.SqlClient.SqlDataReader = Prac_Req.ExecuteReader()
Dim RecordsAffected As Integer = 0
While Autoreader.Read()
prac_no = Autoreader.GetValue(0)
prac_enabled = Autoreader.GetValue(1)
Dim FlagVar As String = "Insert"
For irow = 0 To DgvPracExcl.Rows.Count - 1
If prac_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
FlagVar = "Ignore"
Exit For
Else
'MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
FlagVar = "Update"
Exit For
End If
End If
Next irow
If FlagVar = "Insert" Then
'Call PracticeInsert()
RecordsAffected = RecordsAffected + 1
ElseIf FlagVar = "Update" Then
Call UpdatePracEnabled()
RecordsAffected = RecordsAffected + 1
End If
If RecordsAffected > 0 Then
MsgBox(RecordsAffected & " Records inserted or updated")
Else
MsgBox("No Records need updating")
End If
End While
End Using
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "VeriSIS")
End Try
N/B:
1) The highlighted code was inserted before
End While
End Using
Otherwise I receive the error message:
Name 'RecordsAffected' is not declared.
2)
Code:
If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
FlagVar = "Ignore"
Exit For
Else
'MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
FlagVar = "Update"
Exit For
Changed StrVar = FlagVar therefore
instead of StrVar = "ignore" changed it to FlagVar = "ignore"
3) I just want to get the Update function to work properly then I will look into the INSERT
Result at run time:
When I changed record 1 and clicked SAVE;
Message
1 Records inserted or updated Clicked OK Message still remains - clicked Ok and mesaage remains and so on. No effect to the table and prac_enabled was not changed from True to False as expected.
Ok - Unchecked other 3 records and clicked saved at run time..
Messages:
1 records inserted or updated
2 Records inserted or updated
3 Records inserted or updated - and remains at this message until I terminate the application.
Checked the database - Record 1 was changed from True to False as expected..
What am doing wrong?
Thank you
Last edited by dr223; June 8th, 2012 at 09:24 AM.
-
June 8th, 2012, 09:43 AM
#9
Re: IF Statement
The message is popping up because you moved it to the wrong place. It needs to be outside the loop.
I don't know what you tried when I first posted there was an error in the code which I went back and edited to correct.
First draft the block for testing RecordsAffected was outside the End Using when it should have been between the End While and End Using as it is now in the edited version of my post.
Sorry about the FlagVar strFlag I did not notice that I had used strFlag before. The intention was for them to be the same but I was in a hurry.
Always use [code][/code] tags when posting code.
-
June 8th, 2012, 10:05 AM
#10
Re: IF Statement
Ok Thank you very much DataMiser - nearly there..
Test 1: When I don't perform any Change and click Ok
Message: No records need Updating. (CORRECT)!!
Test 2: Changed the prac_enabled Record 1 from True to False
Click Save - Message: No records need Updating (INCORRECT!!)
Test 3: Changed 3 records state from True to False
Click Save - Message: 2 Records inserted or updated.
Checked Database: Record 1 state changed True to False the other 2 records remain as TRUE. (INCORRECT)
Test 4: Opened the form and clicked saved -
Message: 1 Records inserted or updated.
Checked Database: Record 1 state was changed from False to True (CORRECT)
Also I think my update query is not right?
Code:
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()
Last edited by dr223; June 8th, 2012 at 10:41 AM.
-
June 8th, 2012, 10:42 AM
#11
Re: IF Statement
As I have mentioned before this is not the way you should be going about this. There are a few different ways you could do it, unfortunately I am not that up on ADO.Net as of yet as I am still doing most of my DB work in ADO and VB6.
Still you could be able to bind your grid and use an update method to update the data.
You could have the data stored in a datatable and loop through that rather than reading the whole table again.
You could do your updates 1 at a time as changes are made, rather than looping through the table you could use a query to see if the record is there and if then either update or insert
You could use the batch update method then create the update or insert as records are added/changed then use the updatebatch method when the save button is clicked.
Bottom line is that there are a bunch of ways to do what you are trying to do but looping through all the records and comparing them to all the records in the table is not the way you should choose as that is the slowest method available.
Always use [code][/code] tags when posting code.
-
June 11th, 2012, 02:41 AM
#12
Re: IF Statement
@Dr223 ..
One quick question .. Do you have a Unique ID column in the table? If so what is it's name??
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 11th, 2012, 08:58 AM
#13
Re: IF Statement
Prac_no - is a unique ID (PK)
-
June 11th, 2012, 09:13 AM
#14
Re: IF Statement
Right then .. Going back to code on OP..
Code:
strFlag = "Ignore"
For irow = 0 To DgvPracExcl.Rows.Count - 1
'Read the practice number and status from TblPracExclude
Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude where Prac_no = '" & _
DgvPracExcl.Rows(irow).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_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
'Exit For
Else
MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
strFlag = "Update"
Call UpdatePracEnabled()
'Exit For
End If
' End If
Next irow
If strFlag = "Insert" Then
Call PracticeInsert()
ElseIf strFlag = "Update" Then
End If
End Sub
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 11th, 2012, 10:06 AM
#15
Re: IF Statement
Tried amending the code as ;
Code:
strFlag = "Ignore"
For irow = 0 To DgvPracExcl.Rows.Count - 1
'Read the practice number and status from TblPracExclude
Dim Prac_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, prac_enabled From dbo.TblPracExclude where Prac_no = " & _
DgvPracExcl.Rows(irow).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_no = DgvPracExcl.Rows(irow).Cells(0).Value Then
If prac_enabled = DgvPracExcl.Rows(irow).Cells(3).Value Then
'MsgBox("No Practice need Update", MsgBoxStyle.Information, "VeriSIS")
'Exit For
Else
MsgBox("Practice need Update", MsgBoxStyle.Information, "VeriSIS")
strFlag = "Update"
Call UpdatePracEnabled()
'Exit For
End If
' End If
Next irow
If strFlag = "Insert" Then
Call PracticeInsert()
ElseIf strFlag = "Update" Then
End If
End Sub
TESt
-For the first 3 records I changed the prac_enabled flag from True to False and click Save
Result:
Message - Practice need Update
In the database ONLY the first record was changed from True to False the other two remained at TRUE. Why?
Many thanks
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
|