-
Feb 11th, 2014, 05:42 AM
#1
Thread Starter
New Member
"syntax error in where clause" - please help
Hi, please help me on the below code, i am new at VB.
Code:
Private Sub cmdOK_Click()
Dim sqlPassword As String
Dim rstPassword As ADODB.Recordset
Dim intDaysLeft As Integer
Dim dateHolder As Date
Dim strChngPassAnsw As String
Dim varBK As Variant
Set cnn = New ADODB.Connection
Set rstPassword = New ADODB.Recordset
cnnOpen
'Open up the database and search for username and password
sqlPassword = "Select * from tblEmployee where "
rstPassword.Open sqlPassword, cnn, adOpenKeyset, adLockPessimistic
With rstPassword
varBK = rstPassword.Bookmark
.Find "empusername = '" & txtUserName.Text & "'", , adSearchForward, 1
If Not .EOF Then
If .Fields("EmpPassword") = txtPassword.Text Then
If .Fields("EmpPassword") = "123456" Then 'If the default pswd then force them to change it
changepass = True
End If
dateHolder = FormatDateTime(.Fields("pswddate"), vbShortDate)
Me.Hide
Else
'Wrong Password
MsgBox "Invalid Password, try again!", , "Login"
txtPassword.Text = ""
txtPassword.SetFocus
Exit Sub
End If
Else
'Wrong User Name
MsgBox "Invalid User Name, try again!", , "Login"
txtPassword.Text = ""
txtUserName = ""
txtUserName.SetFocus
Exit Sub
End If
' Global variables to hold User's data for permissions
EmpCurrent.emUserName = .Fields("EmpUserName")
EmpCurrent.emDept = .Fields("EmpDeptID")
EmpCurrent.emFirstName = .Fields("EmpFirst")
EmpCurrent.emLastName = .Fields("EmpLast")
EmpCurrent.emGrade = .Fields("EmpGradeID")
EmpCurrent.emID = .Fields("EmployeeID")
EmpCurrent.empswd = .Fields("EmpPassword")
' Check the date and force a change every 90 days
intDaysLeft = Date - dateHolder
If intDaysLeft >= 85 Then
intDaysLeft = 90 - intDaysLeft
If intDaysLeft <= 0 Then
MsgBox "Your Password is expired, please change it now.", , "Password Expired"
changepass = True
Else
strChngPassAnsw = MsgBox("Your password expires in " & intDaysLeft & "days." & vbNewLine & _
"Would you like to change it now?", vbYesNo, "Password Expiration")
If strChngPassAnsw = vbYes Then
changepass = True
End If
End If
End If
End With
rstPassword.Close
Set rstPassword = Nothing
cnnClose
End Sub
-
Feb 11th, 2014, 05:49 AM
#2
Re: "syntax error in where clause" - please help
The problem is that you have no WHERE clause. You have the WHERE keyword but no condition following it. What condition do you want the records to match to be included in the result set? If you want every record then get rid of the WHERE keyword altogether.
-
Feb 11th, 2014, 05:58 AM
#3
Thread Starter
New Member
Re: "syntax error in where clause" - please help
Thanks, but i didn't understand (my ignorance). Would you please help me to rewrite it?
i am having error in
Code:
rstPassword.Open sqlPassword, cnn, adOpenKeyset, adLockPessimistic
-
Feb 11th, 2014, 05:59 AM
#4
Re: "syntax error in where clause" - please help
Originally Posted by jmcilhinney
If you want every record then get rid of the WHERE keyword altogether.
Correct, since 5 lines later he uses the Find-Method of the Recordset.
But for what he wants to do, a proper SELECT with a correct WHERE-Clause would be better
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Feb 11th, 2014, 06:13 AM
#5
Re: "syntax error in where clause" - please help
Originally Posted by Outstrip
Thanks, but i didn't understand (my ignorance). Would you please help me to rewrite it?
i am having error in
Code:
rstPassword.Open sqlPassword, cnn, adOpenKeyset, adLockPessimistic
No, you don't have an error there. That's where it shows up but there's no issue with that code. The issue is here:
Code:
sqlPassword = "Select * from tblEmployee where "
This is your SQL code:
Code:
SELECT *
FROM tblEmployee
WHERE
Where what? If you want a record where the user name and password are specific values then say that:
Code:
SELECT *
FROM tblEmployee
WHERE EmpUserName = @EmpUserName
AND EmpPassword = @EmpPassword
You should also follow the Database FAQ link in my signature and check out the link on how to use parameters in VB6.
-
Feb 11th, 2014, 06:18 AM
#6
Thread Starter
New Member
Re: "syntax error in where clause" - please help
Solved
Code:
sqlPassword = "SELECT EmployeeID, EmpGradeID, EmpLast, EmpFirst, EmpDeptID, pswddate, empusername, EmpPassword FROM tblEmployee"
Thanks very much to "jmcilhinney" & "Zvoni"
-
Feb 11th, 2014, 06:24 AM
#7
Re: "syntax error in where clause" - please help
Originally Posted by Outstrip
Solved
Code:
sqlPassword = "SELECT EmployeeID, EmpGradeID, EmpLast, EmpFirst, EmpDeptID, pswddate, empusername, EmpPassword FROM tblEmployee"
Thanks very much to "jmcilhinney" & "Zvoni"
I'm not sure that that is the best solution. As I said, if you want all the data then get rid of the WHERE altogether, which is what you've done. Do you really want all the data though? If you're going to retrieve all the data and then look through it in VB code for one record you should be looking for and retrieving just that one record in the first place.
-
Feb 11th, 2014, 06:25 AM
#8
Re: "syntax error in where clause" - please help
Originally Posted by Outstrip
Solved
Code:
sqlPassword = "SELECT EmployeeID, EmpGradeID, EmpLast, EmpFirst, EmpDeptID, pswddate, empusername, EmpPassword FROM tblEmployee"
Thanks very much to "jmcilhinney" & "Zvoni"
You do realize you are going to get all the IDs in that table right? That's great for a report on people's passwords but shouldn't be necessary just to log someone on.
Please remember next time...elections matter!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|