Using variables instead of objects

Question

I used this line

.RecordSource = "select * from tblpersonal where empid like '" & Me.lblIDNumber.Caption & "*'"

...my question is, what if I use a variable (varIDNumber) instead of object (lblIDNumber), what would be the syntax? I am using VB6.0

I am confused about lblIDNumber vs txtIDNumber. Are you using a label or a text box? I assume that when you say lblIDNumber.Caption you mean txtIDNumber.Text. — mgiuca
– mgiuca, Commented Apr 9, 2011 at 7:20
Then I'm a little confused -- if you're getting it out of a label then it isn't something the user can modify. Is it just supposed to be a constant? — mgiuca
– mgiuca, Commented Apr 9, 2011 at 7:28
Because I answered the question assuming this was a text field and that you wanted the variable to update whenever the user changes the text. Labels are only used for displaying text to the user, not getting input, so what is the purpose of reading the caption out of a label? — mgiuca
– mgiuca, Commented Apr 9, 2011 at 7:30

mgiuca · Accepted Answer · 2011-04-09 07:17:04Z

1

You didn't mention txtIDNumber in the code -- you mentioned lblIDNumber. I assume you mean for those two to be the same. In other words, the code you have at present should be something like this:

.RecordSource = "select * from tblpersonal where empid like '" & Me.txtIDNumber.Text & "*'"

So you are using the value of a text box in a form to populate the SQL query. Am I right so far?

And you are asking, what if I store the ID number in a variable rather than a text field? I agree, this is probably a step in the right direction.

So you might create a variable in the "General Declarations" section of the form using:

Dim idNumber As Integer

With the idea being to update the value of that variable each time the text field changes. Note: I am assuming that the "ID number" is an integer -- if not, you should use a String instead.

Now you need to update that variable when the text field changes. In the txtIDNumber_Change event, you will want to add code to convert the string txtIDNumber.Text into an Integer, and store it in idNumber. I forget the exact syntax, but I am guessing something like:

idNumber = Int(txtIDNumber.Text)

Finally, you can now use the idNumber variable in the SQL query rather than the text box:

.RecordSource = "select * from tblpersonal where empid like '" & idNumber & "*'"

answered Apr 9, 2011 at 7:17

mgiuca

21.4k7 gold badges56 silver badges71 bronze badges

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

3 Comments

mgiuca Over a year ago

A note about SQL injection vulnerabilities: Your original code has a security problem. The user could type into the text field 4'; drop table tblpersonal; select * from tblpersonal where empid = ' -- this would actually execute their malicious table-deleting code because you have not sanitised the string. Converting it to an integer as I suggested would solve that problem for this particular case, but if you want to allow full strings anywhere it will be much harder to correct the problem.

Aaron Over a year ago

@mgiuca... so what is the best syntax for this? I am using this code for the Search Program.

mgiuca Over a year ago

If the purpose of the code is to be able to search for an employee by ID that the user types into a text field, then you should have a text field, not a label... as I said in response to the question I'm confused about that. If you do have a text field, then my answer gives all the syntax you need ... define the variable, convert the string into an integer and store it in a variable, and then use the variable in the query.

Mr. Zen · Accepted Answer · 2011-04-09 07:14:07Z

0

Replace Me.lblIDNumber.Caption with varIDNumber

answered Apr 9, 2011 at 7:14

Mr. Zen

7123 gold badges7 silver badges17 bronze badges

2 Comments

Aaron Over a year ago

I am asking for the syntax, .RecordSource = "select * from tblpersonal where empid like '" varIDNumber "*'" Is this correct?

mgiuca Over a year ago

Well, you still need the ampersands. So ... where empid like '" & varIDNumber & "*'". But you still need some code to populate the variable.

mgiuca · Accepted Answer · 2011-04-09 07:41:06Z

0

If you have a constant in the label and would prefer for the constant to be stored in a variable instead, create a Const in the form's code. Let's say the label has the text "43" in it.

In the general declarations section of the form, add the code:

Const idNumber As Integer = 43

Then, when constructing the query:

.RecordSource = "select * from tblpersonal where empid like '" & idNumber & "*'"

This will construct the query using the constant 43. I don't really see the point of this -- if you want the employee number to be something the user can type in, see my other answer.

answered Apr 9, 2011 at 7:41

mgiuca

21.4k7 gold badges56 silver badges71 bronze badges

2 Comments

Aaron Over a year ago

...would you mind asking you? what is the purpose of the asterisk? '" & idNumber & "*'" what if I replace it with percent sign? What would be the outcome?

mgiuca Over a year ago

I'm not sure what the * is for. I think the % is what you'd normally use, as a wildcard, in SQL. That means, search for any employee with an idNumber that starts with those digits.

Collectives™ on Stack Overflow

Using variables instead of objects

3 Answers 3

3 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related