Data type mismatch in criteria expression.
-
i am trying to read a number from an access table check it and use it to control access to another page. this is the code snippet where i think the problem is and the feedback i'm getting is saying this "Data type mismatch in criteria expression" - referring to the highlighted line of code below. Dim PinNumber As String PinNumber = "SELECT DISTINCT Pin FROM Accounts WHERE Pin='" & Me.tbPin.Text & "'AND AccountID = '" & Me.tbID.Text & "'" comm.CommandText = PinNumber 'the variable is declared as a command dr = comm.ExecuteReader 'gives the variable a value If dr.Read() Then 'if the entry is correct (readable) Form2.Show() 'show the index varibale (form) Me.Close() 'close the current form Else 'if the entry is incorrect MessageBox.Show("Invalid Pin, please try again") 'displays error message tbPin.Clear() 'clears the managerid tbID.Clear() End If
-
i am trying to read a number from an access table check it and use it to control access to another page. this is the code snippet where i think the problem is and the feedback i'm getting is saying this "Data type mismatch in criteria expression" - referring to the highlighted line of code below. Dim PinNumber As String PinNumber = "SELECT DISTINCT Pin FROM Accounts WHERE Pin='" & Me.tbPin.Text & "'AND AccountID = '" & Me.tbID.Text & "'" comm.CommandText = PinNumber 'the variable is declared as a command dr = comm.ExecuteReader 'gives the variable a value If dr.Read() Then 'if the entry is correct (readable) Form2.Show() 'show the index varibale (form) Me.Close() 'close the current form Else 'if the entry is incorrect MessageBox.Show("Invalid Pin, please try again") 'displays error message tbPin.Clear() 'clears the managerid tbID.Clear() End If
You appear to be doing no validation before injecting the values from the user interface into the SQL. An invalid input could be causing problems. It could also be used by a malicious person in order to damage the database. See SQL Injection Attacks and Tips on How To Prevent Them[^]
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
i am trying to read a number from an access table check it and use it to control access to another page. this is the code snippet where i think the problem is and the feedback i'm getting is saying this "Data type mismatch in criteria expression" - referring to the highlighted line of code below. Dim PinNumber As String PinNumber = "SELECT DISTINCT Pin FROM Accounts WHERE Pin='" & Me.tbPin.Text & "'AND AccountID = '" & Me.tbID.Text & "'" comm.CommandText = PinNumber 'the variable is declared as a command dr = comm.ExecuteReader 'gives the variable a value If dr.Read() Then 'if the entry is correct (readable) Form2.Show() 'show the index varibale (form) Me.Close() 'close the current form Else 'if the entry is incorrect MessageBox.Show("Invalid Pin, please try again") 'displays error message tbPin.Clear() 'clears the managerid tbID.Clear() End If
peteyshrew wrote:
& Me.tbPin.Text & "'AND AccountID = '"
Don't you need a space before the AND. I.e. "' AND AccountID...
-
peteyshrew wrote:
& Me.tbPin.Text & "'AND AccountID = '"
Don't you need a space before the AND. I.e. "' AND AccountID...
What he needs is to parameterise the query.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
peteyshrew wrote:
& Me.tbPin.Text & "'AND AccountID = '"
Don't you need a space before the AND. I.e. "' AND AccountID...
RichardBerry wrote:
Don't you need a space before the AND. I.e. "' AND AccountID...
No... the ' (single quote) acts as a delimiter too, and this will work, provided that PIN is a text type field.
-
RichardBerry wrote:
Don't you need a space before the AND. I.e. "' AND AccountID...
No... the ' (single quote) acts as a delimiter too, and this will work, provided that PIN is a text type field.
ChandraRam wrote:
and this will work, provided that PIN is a text type field.
And so long as the user doesn't type anything dodgy in to the PIN TextBox because as soon as that happens all hell breaks loose and the company finds that their database is compromised.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website