[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Help with login script

I would like to increment a counter inside the Access DB when a user logs in.  Below is my code but it is not working.  The problem is in the select statement b/c for some reason I am not sending it valid data for the username.  Everything worked great until I added the code for the logincounter.  Please help!

Sub LoginUser(s As Object, e As EventArgs)
      Dim blnAuthenticate As Boolean = Authenticate(username.Text, password.Text)
      If blnAuthenticate Then
            FormsAuthentication.RedirectFromLoginPage(username.Text, False)
      
      Dim objConn As New OleDbConnection(ConfigurationSettings.AppSettings("DSN"))
      Dim objCmd As OleDbCommand
      Dim objDR As OleDbDataReader
      Dim lastlogintime As String = DateTime.Now.ToString()
      Dim logincounter As Integer
      Dim usernamelogin AS String = username.Text
      
      objCmd = New OleDbCommand("SELECT * FROM Members WHERE UserName= " & usernamelogin & "", objConn)
      objCmd.Parameters.Add("UserName", username.Text)
      objConn.Open()
      objDR = objCmd.ExecuteReader()
      If Not objDR("logincounter") Is DbNull.Value Then
      If objDR("logincounter")<>"" Then
      logincounter = objDR("logincounter")
      Else
      logincounter = 0
      End if
      End if
      objDR.Close()
      objConn.Close()
      
      Dim      incrementlogincounter As Integer = logincounter + 1
      
      objCmd = New OleDbCommand("Update Members SET lastlogin=@lastlogin, logincounter=@logincounter WHERE UserName=@UserName", objConn)
      objCmd.Parameters.Add("lastlogin", lastlogintime)
      objCmd.Parameters.Add("logincounter", incrementlogincounter)
      objCmd.Parameters.Add("UserName", username.Text)
      objConn.Open()
      objCmd.ExecuteNonQuery()
      objConn.Close()
      
      Else
            lblError.Text = "Your login was invalid. Please try again."
      End If
End Sub

Function Authenticate(strUsername As String, strPassword As String) As Boolean
      Dim objConn As New OleDbConnection(ConfigurationSettings.AppSettings("DSN"))
      Dim objCmd As OleDbCommand
      Dim objDR As OleDbDataReader
      Dim userFound As Boolean

      objCmd = New OleDbCommand("SELECT * FROM Members WHERE UserName='" & strUsername & "' AND Pwd='" & strPassword & "'", objConn)

      objConn.Open()
      objDR = objCmd.ExecuteReader()
      userFound = objDR.Read()
      objDR.Close()
      objConn.Close()
      
      Return userFound
End Function
0
azyet24
Asked:
azyet24
  • 12
  • 8
  • 3
  • +1
1 Solution
 
Sam_JayanderCommented:
Hi,

you have missed a else part. Try adding these lines.

If Not objDR("logincounter") Is DbNull.Value Then
  If objDR("logincounter")<>"" Then
    logincounter = objDR("logincounter")
  Else
    logincounter = 0
  End if
Else                                   ----------|
    logincounter = 0             ----------|--> Add these lines.
End if


Regards,
--Sam.
0
 
azyet24Author Commented:
This is the error message I get (even after adding the extra else statements)

No data exists for the row/column.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: No data exists for the row/column.

Source Error:


Line 30:       objConn.Open()
Line 31:       objDR = objCmd.ExecuteReader()
Line 32:       If Not objDR("logincounter") Is DbNull.Value Then
Line 33:       If objDR("logincounter")<>"" Then
Line 34:       logincounter = objDR("logincounter")
 


I'm thinking that it's not finding the record.  I have manually entered 0 for my test login and it still ways that there's no data...telling me that it's not looking at the right record.

azyet24
0
 
sguerraCommented:
Maybe your problem is here:

Dim usernamelogin AS String = username.Text  
objCmd = New OleDbCommand("SELECT * FROM Members WHERE UserName= " & usernamelogin & "", objConn)
Problem --> objCmd.Parameters.Add("UserName", username.Text)  <--

You are adding a parameter to a command that does not needs its since you are using " & usernamelogin & ".

Santiago G
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
azyet24Author Commented:
I commented out that line and this is what I got:

No value given for one or more required parameters.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

Source Error:


Line 29:       'objCmd.Parameters.Add("UserName", username.Text)
Line 30:       objConn.Open()
Line 31:       objDR = objCmd.ExecuteReader()
Line 32:       If Not objDR("logincounter") Is DbNull.Value Then
Line 33:       If objDR("logincounter")<>"" Then
 
0
 
YZlatCommented:
if logincounter is an integer field in the database, then it's default value is 0.

Try this:

Sub LoginUser(s As Object, e As EventArgs)
     Dim blnAuthenticate As Boolean = Authenticate(username.Text, password.Text)
     If blnAuthenticate Then
          FormsAuthentication.RedirectFromLoginPage(username.Text, False)
     
     Dim objConn As New OleDbConnection(ConfigurationSettings.AppSettings("DSN"))
     Dim objCmd As OleDbCommand
     Dim objDR As OleDbDataReader
     Dim lastlogintime As String = DateTime.Now.ToString()
     Dim logincounter As Integer
     Dim usernamelogin AS String = username.Text
 Try
     objCmd = New OleDbCommand("SELECT * FROM Members WHERE UserName= " & usernamelogin & "", objConn)
     objCmd.Parameters.Add("UserName", username.Text)
     objConn.Open()
     objDR = objCmd.ExecuteReader()
While objDR.Read
     If Not objDR("logincounter") Is DbNull.Value Then
     If objDR("logincounter")=> 0 Then
     logincounter = objDR("logincounter")
     Else
     logincounter = 0
     End if
     End if
End While
Catch ex as Exception
Console.write("ex.Message)
Finally
     objDR.Close()
     objConn.Close()
End Try
     
     Dim     incrementlogincounter As Integer = logincounter + 1
     
     objCmd = New OleDbCommand("Update Members SET lastlogin=@lastlogin, logincounter=@logincounter WHERE UserName=@UserName", objConn)
     objCmd.Parameters.Add("lastlogin", lastlogintime)
     objCmd.Parameters.Add("logincounter", incrementlogincounter)
     objCmd.Parameters.Add("UserName", username.Text)
try
     objConn.Open()
     objCmd.ExecuteNonQuery()
Catch ex as Exception
Console.write("ex.Message)
Finally
     objConn.Close()
end try
     
     Else
          lblError.Text = "Your login was invalid. Please try again."
     End If
End Sub


0
 
YZlatCommented:
also change

objCmd = New OleDbCommand("SELECT * FROM Members WHERE UserName= " & usernamelogin & "", objConn)


to

objCmd = New OleDbCommand("SELECT * FROM Members WHERE UserName= ' " & usernamelogin & " ' ", objConn)
0
 
sguerraCommented:
Well if you get that error try doing it like in the update statement


objCmd = New OleDbCommand("SELECT * FROM Members WHERE UserName= @UserName", objConn)
objCmd.Parameters.Add("UserName", username.Text)

Santiago G.
0
 
azyet24Author Commented:
YZlat,

I got this error:

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30201: Expression expected.

Source Error:

 

Line 32: While objDR.Read
Line 33:      If Not objDR("logincounter") Is DbNull.Value Then
Line 34:      If objDR("logincounter")=> 0 Then        <---- Error was here
Line 35:      logincounter = objDR("logincounter")
Line 36:      Else
 
0
 
YZlatCommented:
did you change

objCmd = New OleDbCommand("SELECT * FROM Members WHERE UserName= " & usernamelogin & "", objConn)


to

objCmd = New OleDbCommand("SELECT * FROM Members WHERE UserName= ' " & usernamelogin & " ' ", objConn)

0
 
YZlatCommented:
did If objDR("logincounter")<>"" Then
 work before?
0
 
azyet24Author Commented:
Yes.  Here's code:

Sub LoginUser(s As Object, e As EventArgs)
     Dim blnAuthenticate As Boolean = Authenticate(username.Text, password.Text)
     If blnAuthenticate Then
          FormsAuthentication.RedirectFromLoginPage(username.Text, False)
     
     Dim objConn As New OleDbConnection(ConfigurationSettings.AppSettings("DSN"))
     Dim objCmd As OleDbCommand
     Dim objDR As OleDbDataReader
     Dim lastlogintime As String = DateTime.Now.ToString()
     Dim logincounter As Integer
     Dim usernamelogin AS String = username.Text
 Try
     objCmd = New OleDbCommand("SELECT * FROM Members WHERE UserName= ' " & usernamelogin & " ' ", objConn)
     objCmd.Parameters.Add("UserName", username.Text)
     objConn.Open()
     objDR = objCmd.ExecuteReader()
While objDR.Read
     If Not objDR("logincounter") Is DbNull.Value Then
     If objDR("logincounter")=> 0 Then
     logincounter = objDR("logincounter")
     Else
     logincounter = 0
     End if
     End if
End While
Catch ex as Exception
Console.write("ex.Message)
Finally
     objDR.Close()
     objConn.Close()
End Try
     
     Dim     incrementlogincounter As Integer = logincounter + 1
     
     objCmd = New OleDbCommand("Update Members SET lastlogin=@lastlogin, logincounter=@logincounter WHERE UserName=@UserName", objConn)
     objCmd.Parameters.Add("lastlogin", lastlogintime)
     objCmd.Parameters.Add("logincounter", incrementlogincounter)
     objCmd.Parameters.Add("UserName", username.Text)
try
     objConn.Open()
     objCmd.ExecuteNonQuery()
Catch ex as Exception
Console.write("ex.Message)
Finally
     objConn.Close()
end try
     
     Else
          lblError.Text = "Your login was invalid. Please try again."
     End If
End Sub
0
 
azyet24Author Commented:
I changed it back to this: If objDR("logincounter")<>"" Then

and I no longer get an error, however.  when I logged in, it incremented from 0 to 1, but that's as far as it will go.  I log out and back in and it still says 1.  Any ideas?

Azyet24
0
 
azyet24Author Commented:
Wondering if since it is an integer and not a string, then when it looks to see if logincounter<>"" (which would be false), then it's assigning it 0 again.  Is this right?
0
 
azyet24Author Commented:
Guess not.  I commented out the inner If statement and just left this:

While objDR.Read
     If Not objDR("logincounter") Is DbNull.Value Then
   '  If objDR("logincounter")<>"" Then
     logincounter = objDR("logincounter")
     'Else
     'logincounter = 0
     End if
    ' End if
End While

Still stays at 1.
0
 
YZlatCommented:
try

While objDR.Read
    If Not objDR("logincounter") Is DbNull.Value Then
           If objDR("logincounter")<>"" Then
            If objDR("logincounter")<>"0" then
                       logincounter = objDR("logincounter")
            else
                  logincounter =0
            end if
           Else
                 logincounter = 0
           End if
    End if
End While

0
 
azyet24Author Commented:
I added your code above and it stays at 1.  
0
 
YZlatCommented:
print out the value of objDR("logincounter")

While objDR.Read
    If Not objDR("logincounter") Is DbNull.Value Then
Response.write( objDR("logincounter") )
          If objDR("logincounter")<>"" Then
          If objDR("logincounter")<>"0" then
                    logincounter = objDR("logincounter")
          else
               logincounter =0
          end if
          Else
               logincounter = 0
          End if
    End if
End While
0
 
azyet24Author Commented:
Once the authentication is made, the page is redirected so the print out is not working.
0
 
YZlatCommented:
While objDR.Read
    If Not objDR("logincounter") Is DbNull.Value Then
Response.write( "counter=" & objDR("logincounter") )
Response.end
          If objDR("logincounter")<>"" Then
          If objDR("logincounter")<>"0" then
                    logincounter = objDR("logincounter")
          else
               logincounter =0
          end if
          Else
               logincounter = 0
          End if
    End if
End While
0
 
azyet24Author Commented:
When it is redirected to default.aspx (after authentication is successful), nothing is printed out.  However, looking at the db I know that 1 is entered, but it doesn't go any further than that.  Does that make sense?
0
 
YZlatCommented:
Then do not redirect before you process everything:

Sub LoginUser(s As Object, e As EventArgs)
     Dim blnAuthenticate As Boolean = Authenticate(username.Text, password.Text)
     If blnAuthenticate Then
         
     
     Dim objConn As New OleDbConnection(ConfigurationSettings.AppSettings("DSN"))
     Dim objCmd As OleDbCommand
     Dim objDR As OleDbDataReader
     Dim lastlogintime As String = DateTime.Now.ToString()
     Dim logincounter As Integer
     Dim usernamelogin AS String = username.Text
 Try
     objCmd = New OleDbCommand("SELECT * FROM Members WHERE UserName= ' " & usernamelogin & " ' ", objConn)
     objCmd.Parameters.Add("UserName", username.Text)
     objConn.Open()
     objDR = objCmd.ExecuteReader()
While objDR.Read
    If Not objDR("logincounter") Is DbNull.Value Then
Response.write( objDR("logincounter") )
          If objDR("logincounter")<>"" Then
          If objDR("logincounter")<>"0" then
                    logincounter = objDR("logincounter")
          else
               logincounter =0
          end if
          Else
               logincounter = 0
          End if
    End if
End While
Catch ex as Exception
Console.write("ex.Message)
Finally
     objDR.Close()
     objConn.Close()
End Try
     
     Dim     incrementlogincounter As Integer = logincounter + 1
     
     objCmd = New OleDbCommand("Update Members SET lastlogin=@lastlogin, logincounter=@logincounter WHERE UserName=@UserName", objConn)
     objCmd.Parameters.Add("lastlogin", lastlogintime)
     objCmd.Parameters.Add("logincounter", incrementlogincounter)
     objCmd.Parameters.Add("UserName", username.Text)
try
     objConn.Open()
     objCmd.ExecuteNonQuery()
Catch ex as Exception
Console.write("ex.Message)
Finally
     objConn.Close()
end try
     ''redirect now
 FormsAuthentication.RedirectFromLoginPage(username.Text, False)
     Else
          lblError.Text = "Your login was invalid. Please try again."
     End If
End Sub
0
 
azyet24Author Commented:
Nothing is printed.
0
 
azyet24Author Commented:
Well, I took sguerra's advise and changed the select to the following and it now works:

objCmd = New OleDbCommand("SELECT * FROM Members WHERE UserName= @UserName", objConn)

Thanks for all the help!
0
 
sguerraCommented:
You are Welcome azyet24, thanks to you too!

Santiago G.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 12
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now