VBA sql query problem

Question

Below query is resulting NO rows

lstResults.RowSource = "select EmpId from tblTesting where Empid ='" & Me.txtSearchEmpId.Value & "'"

Where below working fine :

lstResults.RowSource = "select * from tblTesting"

WHere is the fault here? I check the value of '" & Me.txtSearchEmpId.Value & "'" using break point its having the value of "123" (numerical)

My empid is numerical value

Please help

Please take some time to learn a bit about SQL. Does the table have row(s) for EmpID = 123? What data type is the EmpID field? If it is a text field, do a TRIM on txtSearchEmpID — shahkalpesh
– shahkalpesh, Commented Jul 14, 2009 at 6:47

mechanical_meat · Accepted Answer · 2009-07-14 06:46:52Z

3

If your EmpId is numerical, you probably want to remove the single-quotes:

lstResults.RowSource = "select EmpId from tblTesting where Empid = " & Me.txtSearchEmpId.Value

How does that work?

answered Jul 14, 2009 at 6:46

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

Comments

Community · Accepted Answer · 2017-02-08 14:07:40Z

1

First, remove the single quotes from around your value, if it really is a number.

Second, cleanse your input. What if someone types 123 or true into your input field? You've then let them select all inputs. You might want to convert the value to an integer and then back to a string to make sure it is pure.

See xkcd #327:

edited Feb 8, 2017 at 14:07

CommunityBot

11 silver badge

answered Jul 14, 2009 at 6:50

lavinio

24.4k5 gold badges57 silver badges72 bronze badges

2 Comments

David-W-Fenton Over a year ago

Exactly what could happen if they don't do that? Remember, the context is Access, and SQL injection risks are severely limited in Access as compared to other programming environments because of the fact that Access/Jet can't executed batched SQL statements.

onedaywhen Over a year ago

@David W. Fenton: In the txtSearchEmpId.Value you could type x' = 'x and your assumption that the resultset will contain 1 or zero rows is blow out of the water e.g. could reveal information you were trying to restrict. Hard to be specific for a SQL statement targeting a table named tblTesting ;)

Collectives™ on Stack Overflow

VBA sql query problem

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related