Trying to use VB to automate some queries. Running into what looks like a string issue

Question

I'm using MS Access 2003 and I'm trying to execute a few queries at once using VB. When I write out the query in SQL it works fine, but when I try to do it in VB it asks me to "Enter Parameter Value" for DEPA, then DND (which are the first few letters of a two strings I have). Here's the code:

Option Compare Database

Public Sub RemoveDupelicateDepartments()

Dim oldID As String
Dim newID As String
Dim sqlStatement As String


oldID = "DND-01"
newID = "DEPA-04"

sqlStatement = "UPDATE [Clean student table] SET [HomeDepartment]=" & newID & " WHERE [HomeDepartment]=" & oldID & ";"

DoCmd.RunSQL sqlStatement & ""

End Sub

It looks to me as though it's taking in the string up to the - then nothing else. I dunno, that's why I'm asking lol. What should my code look like?

Do ***NOT*** use string concatenation to build queries like that. — Joel Coehoorn, May 10 '10 at 16:13
No they're strings. The question is answered, I just am not registered yet to mark it such — Jeff, May 11 '10 at 11:39
It is quite difficult to use SQL injection with an Access application. — Fionnuala, May 12 '10 at 08:28
Difficult, but not impossible. The damage that can be done is usually in revealing data that should be hidden, as it's impossible to inject DML since Jet/ACE can't take multiple ;-delimited SQL commands of any kind. See http://stackoverflow.com/questions/512174/non-web-sql-injection for an in-depth discussion. — David-W-Fenton, May 12 '10 at 22:41

score 5 · Accepted Answer · answered May 10 '10 at 16:21

5

Use (') character to set start and end of value

sqlStatement = "UPDATE [Clean student
table] SET [HomeDepartment]='" & newID
& "' WHERE [HomeDepartment]='" & oldID
& "';"

answered May 10 '10 at 16:21

volody

6,946
3
42
54

Because the literal query is something like "...SET field = 'abc'..." instead of "...SET field = abc...". If you try to run the second (unquoted) query directly in Access, you will see the same error. – Carl Manaster May 10 '10 at 16:46
Ah, thank you very much! This answers all my questions (except the one Mr. Coehoorn has me wondering about now :P ) – Jeff May 10 '10 at 16:52
Joel's point is that you are open to executing any arbitrary data that the user supplies for your variable. He assumes a lot but he has a point. See http://stackoverflow.com/questions/512174/non-web-sql-injection for a lengthy discussion of the issue. – David-W-Fenton May 12 '10 at 22:47

score 3 · Answer 2 · answered May 10 '10 at 16:18

3

You probably want to insert quotes around the IDs.

answered May 10 '10 at 16:18

Carl Manaster

39,912
17
102
155

Trying to use VB to automate some queries. Running into what looks like a string issue

2 Answers2