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?
 
mastooConnect With a Mentor Commented:
Dim query As String = "SELECT * FROM tblUsers WHERE fldUserID = """ & txtUserName.Text & """ AND fldPassword = """ & txtPassword.Text & """"
0
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.