Syntax error (missing operator) in query expression on vb/access

Question

I've been looking for an answer but I can't understand them since I'm noob on visual basic (I haven't been taught how to use it in school, I just search on the net to learn).

Here is my problem: Syntax error (missing operator) in query expression 'pinN='.

Dim con As New OleDbConnection("PROVIDER = Microsoft.ACE.OLEDB.12.0;Data Source =   C:\Users\Billy Otsuka\Desktop\VB Fiiles\Prototype-Prototype\Sample.accdb")
Dim cmd As OleDb.OleDbCommand = New OleDbCommand("SELECT * FROM [Table3] WHERE pinN= " & TextBox1.Text & "", con)
con.Open()
Dim sdr As OleDbDataReader = cmd.ExecuteReader()
If (sdr.Read() = True) Then
    adminLogin.Show()
    Me.Hide()
Else
    MsgBox("Invalid pin")
End If

The highlighted word is sdr As OleDbDataReader = cmd.ExecuteReader()

In this code I want to enter a 4 number pin and if its correct I can go to the next form but if its wrong it will not. I'm using an access database.

I really appreciate if anyone can tell me what's wrong since i don't have any idea what the error is.

Carlos Landeras · Accepted Answer · 2012-12-03 11:14:15Z

1

Use this:

 Dim cmd As OleDb.OleDbCommand = New OleDbCommand("SELECT * FROM
 [Table3] WHERE pinN= '"  & TextBox1.Text & "'", con)

You don't have the singles quotes on your select

EDIT: Try this:

 New OleDbCommand("SELECT * FROM [Table3] WHERE pinN= " & TextBox1.Text, con)

edited Dec 3, 2012 at 11:14

answered Dec 3, 2012 at 10:37

Carlos Landeras

11.1k11 gold badges61 silver badges84 bronze badges

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

7 Comments

Billy Otsuka Over a year ago

after putting single quotes i got now the error Data type mismatch in criteria expression.

Carlos Landeras Over a year ago

what is the value in Textbo2.text is it a date or plain text?. What value is in it debugging?

Billy Otsuka Over a year ago

value i put is 1234 since its asking for a pin.

Carlos Landeras Over a year ago

Trry with this: New OleDbCommand("SELECT * FROM [Table3] WHERE pinN= " & TextBox1.Text, con)

Billy Otsuka Over a year ago

can i ask one more. It works if i put numbers but if i leave it blank it gives me the missing operator error. do you know how to fix it?

|

afuzzyllama · Accepted Answer · 2013-10-31 15:43:55Z

0

What is textbox1.text type?

If textbox1.text is **numeric** :
   New OleDbCommand("SELECT * FROM [Table3] WHERE pinN= " & TextBox1 & ""
else
   New OleDbCommand("SELECT * FROM [Table3] WHERE pinN ='" & TextBox1 & "'"

edited Oct 31, 2013 at 15:43

afuzzyllama

6,5485 gold badges49 silver badges64 bronze badges

answered Oct 31, 2013 at 15:23

Hasan

1

Comments

hatsrumandcode · Accepted Answer · 2014-02-26 13:53:51Z

0

I had issue doing the concatenation, I used a parameter to solve this issue.

Dim cmd As OleDb.OleDbCommand = New OleDbCommand("SELECT * FROM [Table3] WHERE pinN= @myPinN", con)
con.Open()  
cmd.Parameters.Add("@myPinN", OleDbType.VarChar, 100).Value = "myPinNValue"

answered Feb 26, 2014 at 13:53

hatsrumandcode

1,8712 gold badges19 silver badges22 bronze badges

Collectives™ on Stack Overflow

Syntax error (missing operator) in query expression on vb/access

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related