November 21, 2017 at 12:59 pm
Greetings...
Hair ~ Gone
I cannot figure this out...I'm actually working in a VBE trying to write an UPDATE query loop based on selections made in a ListBox
[Code]
Private Sub Command0_Click()
Dim i As Integer
Dim MySQL As String
With Me.List1
For i = 0 To .ListCount - 1
If .Selected(i) Then
MySQL = "UPDATE [Tbl_Associates]"
Set [Tbl_Associates].[Approved] = "Yes"
WHERE [Tbl_Associates].[Agent Name] = Me!List1.Column(0) And [Tbl_Associates].[Updated] = Me!List1.Column(1);
End If
End If
Next i
'Exit For
End With
Exit Sub
[/Code]
I have configured the above UPDATE query at least a dozen different ways ~ nothing works
With the above iteration I am getting a Syntax Error on the entire bottom line
Thank You so much for any help
November 21, 2017 at 4:57 pm
A syntax error on what line? The last line I think should be End Sub if that's where you are getting the error.
I see where you declared a SQL string but I don't see anything that executes it. Or anything that connects to SQL Server. And it looks like you have one if and two end ifs.
Sue
November 30, 2017 at 8:28 am
Hi Sue ~
Sorry for the late reply and the cryptic explanation... The line causing the error was the 'WHERE' clause...I have since moved to another task and may address this when it comes back up...So please don't run away.
In the meantime I am trying to resolve another issue which I will need some guidance and will post that here in a moment...
Thanks Sue...
November 30, 2017 at 8:46 am
You haven't built your string properly, and like Sue said, you're not even executing it. I don't know much about VB, so this is almost pseudo-code, but try this:
MySQL = "UPDATE Tbl_Associates
SET Tbl_Associates.Approved = 'Yes'
WHERE Tbl_Associates.Agent Name = " & Me!List1.Column(0) & " ANDTbl_Associates.Updated = " & Me!List1.Column(1) & ";"
However, it's horribly inefficient to do your updates one row at a time. How do your populate your List array? If you could get that into a staging table in your database, you could join Tbl_Associates to that staging table and do the update in a single operation.
John
November 30, 2017 at 9:23 am
John Mitchell-245523 - Thursday, November 30, 2017 8:46 AMYou haven't built your string properly, and like Sue said, you're not even executing it. I don't know much about VB, so this is almost pseudo-code, but try this:
MySQL = "UPDATE Tbl_Associates
SET Tbl_Associates.Approved = 'Yes'
WHERE Tbl_Associates.Agent Name = " & Me!List1.Column(0) & " ANDTbl_Associates.Updated = " & Me!List1.Column(1) & ";"However, it's horribly inefficient to do your updates one row at a time. How do your populate your List array? If you could get that into a staging table in your database, you could join Tbl_Associates to that staging table and do the update in a single operation.
John
That's also open to SQL injection which is one of the greatest threats in security.
November 30, 2017 at 9:26 am
Luis Cazares - Thursday, November 30, 2017 9:23 AMThat's also open to SQL injection which is one of the greatest threats in security.
Only if there's user input in populating the array, which is one of the reasons why I asked how that is done.
John
November 30, 2017 at 9:43 am
John Mitchell-245523 - Thursday, November 30, 2017 9:26 AMLuis Cazares - Thursday, November 30, 2017 9:23 AMThat's also open to SQL injection which is one of the greatest threats in security.Only if there's user input in populating the array, which is one of the reasons why I asked how that is done.
John
There's always user input at some point in time. I'm sure you know how to be aware of little Bobby Tables, but I wanted the OP to learn about it before copying and pasting a solution.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply