Using variables in SQL Statment

Question

I'm using Microsoft Access 2016 forms with SQL Server 2018, and Microsoft SQL Server Management Studio 18.4

Already I have :

UPDATE Student SET NAME = 'PRATIK' WHERE Age = 20;

It is OK.

When I define AA & BB as below :

Dim AA as integer
Dim AA as string

AA = 20 
BB = 'PATRIK'

And run as:

UPDATE Student SET NAME = BB WHERE Age = AA;

Error message said:

Invalid column Name 'BB' and 'AA'

I dont need data entry directly work on server to write AA and BB value (20, PATRIK).

I tried define as @AA=20, @bb='PATRIK' but Access 2016 forms reject of using Prefix@.

The variables are defined in VBA code. The update statement is sql code. It is not the same. Do you want VBA code? — forpas
– forpas, Commented Dec 27, 2019 at 16:07

Gustav · Accepted Answer · 2019-12-27 16:09:37Z

2

You must concatenate the variables:

SQL = "UPDATE Student SET [NAME] = '" & BB & "' WHERE Age = " & AA & ";"
CurrentDb.Execute SQL

or create a query with parameters and set the parameter values.

answered Dec 27, 2019 at 16:09

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

n8. Over a year ago

Concatenating with concern for single quotes around strings is a significant barrier to entry for new users.

Lee Mac · Accepted Answer · 2019-12-27 21:45:55Z

0

I would suggest something along the lines of:

Dim AA As Integer
Dim BB As String

AA = 20
BB = "PATRIK"

With CurrentDb.CreateQueryDef("", "update Student t set t.name = @name where t.age = @age")
    .Parameters(0) = BB
    .Parameters(1) = AA
    .Execute
End With

Using parameters in this way avoids the need to worry about formatting data types within the SQL statement and also avoids any potential for SQL injection.

edited Dec 27, 2019 at 21:45

answered Dec 27, 2019 at 21:32

Lee Mac

16.1k6 gold badges38 silver badges83 bronze badges

Collectives™ on Stack Overflow

Using variables in SQL Statment

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related