sql query statement in visual basic 2005

I am trying to look up the user name and password stored in access 2007 table via this command and I receive the following error:Syntax error in string in query expression 'fldUserID = 'test' AND fldPassword = 'test'.

THIS IS MY QUERY STATEMENT:
Dim query As String = "SELECT * FROM tblUsers WHERE fldUserID = '" & txtUserName.Text & "' AND fldPassword = '" & txtPassword.Text & ""

what I am missing in this query?
LVL 1
systems_axAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PaulHewsCommented:
Most likely, fldUserID is not a text field type.  Double check that, or that you didn't mean to use a different field, like fldUserName
0
systems_axAuthor Commented:
the field was set to Text, any other ideas.
thank you for replying.
0
mastooCommented:
I'm not an Access person but doesn't it want double quotes instead of the single quotes?

Dim query As String = "SELECT * FROM tblUsers WHERE fldUserID = "" & txtUserName.Text & "" AND fldPassword = "" & txtPassword.Text & ""

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Joel CoehoornDirector of Information TechnologyCommented:
One thing you're missing is injection protection.  What happens if someone enters this into txtUserName:

'; DELETE tblUsers;--

It's a disaster waiting to happen.  Any text with an apostrophe will cause problems for your code.  Either escape the apostrophe or use a parameterized query.

To debug your specific problem, try pasting your query directly into query analyzer and see what it returns.
0
Joel CoehoornDirector of Information TechnologyCommented:
Oh, yeah.  I meant an access Query window, not query analyzer.
0
SanclerCommented:
If this is a direct cut and paste from your code

Dim query As String = "SELECT * FROM tblUsers WHERE fldUserID = '" & txtUserName.Text & "' AND fldPassword = '" & txtPassword.Text & ""

it's missing the single quote between the last pair of double quotes.

Roger
0
systems_axAuthor Commented:
Sancler,
I did include it with no success.
does this look like correct a validating query.  All I am trying to do is after the user enters the username and password via visual basic 2005 interface to validate what was entered against the access database.
0
mastooCommented:
Dim query As String = "SELECT * FROM tblUsers WHERE fldUserID = """ & txtUserName.Text & """ AND fldPassword = """ & txtPassword.Text & """"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PaulHewsCommented:
I think Sancler has it.  The last pair of quotes is empty.  Should be:

Dim query As String = "SELECT * FROM tblUsers WHERE fldUserID = '" & txtUserName.Text & "' AND fldPassword = '" & txtPassword.Text & "'"

Credit to Sancler http:#a20877153
0
systems_axAuthor Commented:
that did not work, PaulHews, any other ideas?
0
SanclerCommented:
Is the error still "Syntax error in string in query expression ..."?

Roger
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.