none
Help with a search function RRS feed

  • Question

  • I have been trying for a while to solve this problem. I am currently trying to write some code, which will search my database for an answer, it will check the answer and will return a Boolean output (True or False). I have got this far, but when using the code I was given it says 'System.Data.OleDb.OleDbException: 'No value given for one or more required parameters.' This shows me that the code for the search is not working, if anyone could help me it would be much appreciated.

    Private Sub Login_button_click(sender As Object, e As EventArgs) Handles Login_Button.Click
    
    If Not String.IsNullOrEmpty(Password_Box.Text) Then
                Dim dt As New DataTable
                Dim commandtext As String = "select answer from infotmation where username=@username and password=@password"
                Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\bobby\Documents\DOFE App\Starting_App Access Database\Database.accdb;")
                    Using cmd As New OleDbCommand(commandtext, cn)
                        cmd.Parameters.AddWithValue("@username", Username_Box.Text)
                        cmd.Parameters.AddWithValue("@password", Password_Box.Text)
                        cn.Open()
                        dt.Load(cmd.ExecuteReader())
                    End Using
                End Using
            Else
                MessageBox.Show("Please supply some text for the search")
            End If
    
    End Sub


    Tuesday, May 22, 2018 8:16 PM

All replies

  • Try the next command:

    Dim commandtext As String = "select answer from infotmation where username=? and password=?"
    

    Tuesday, May 22, 2018 8:28 PM
  • Not tested and don't forget your previous question to change in discussion otherwise somebody will merge it with this one. 

            If Not String.IsNullOrEmpty(Password_Box.Text) Then
                Dim dt As New DataTable
                Dim commandtext As String = "select answer from infotmation where [username]=? and [password]=?"
                Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\bobby\Documents\DOFE App\Starting_App Access Database\Database.accdb;")
                    Using cmd As New OleDbCommand(commandtext, cn)
                        cmd.Parameters.AddWithValue("?", Username_Box.Text)
                        cmd.Parameters.AddWithValue("?", Password_Box.Text)
                        cn.Open()
                        Dim answer = cmd.ExecuteScalar
                        If Not abswer Is Nothing AndAlso Not answer Is DBNull.Value Then
                            TextBox1.Text = CStr(answer)
                        End If
                    End Using
    
                End Using
    
            Else
                MessageBox.Show("Please supply some text for the search")
            End If


    Success
    Cor

    Tuesday, May 22, 2018 8:37 PM
  • Yeah, those are SQL parameters used with an Access database... that isn't going to work.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Tuesday, May 22, 2018 8:43 PM
    Moderator
  • Yeah, those are SQL parameters used with an Access database... that isn't going to work.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Yes it works, but I avoid it in my samples because persons think that it is needed. It does not matter what you use, you even can use the fieldnames like "password" and "username". 

    However, why you first propose my reply as answer and then unpropose it. There is nothing wrong in the code (or I should have made a typo).

    Now it makes my code suspicious. 


    Success
    Cor

    Tuesday, May 22, 2018 9:01 PM
  • I'm sorry, my mistake.  I wasn't sure it was safe to use the same parameter name twice in AddWithValue, but I see now that it is ok.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Tuesday, May 22, 2018 9:17 PM
    Moderator
  • Hello,

    First off to those who have reply, there is no intent in my reply to step on others replies.

    Number one recommendation, rename password field to say UserPassword rather than using password which is a reserve token in ms-access by escaping it with [] e.g. [password].

    In regards to naming parameters, ms-access, they are ordinal position e.g. as Viorel demonstrates.

    You can name the parameter e.g. @UserName, @UserPassword but they are still by ordinal position while SQL-Server is by name and Oracle by default is ordinal unless a property is set then they are name based parameters. 

    Next, there is zero need for a DataTable if you are simply validating their name/password.

    You could look at my code sample where the method below is called from a form. Note they are locked out after three failed attempts.

    Public Class ApplicatioLogin 
        Public Property UserName As String 
        Public Property UserPassword As String 
        Public Property Database As String 
        Public Property DatabasePassword As String 
        Public Property Retries As Integer 
     
        Public Sub New() 
     
        End Sub 
        Private Builder As New OleDb.OleDbConnectionStringBuilder With 
            { 
                .Provider = "Microsoft.ACE.OLEDB.12.0" 
            } 
        ''' <summary> 
        ''' Try to login a user based on proper user name and password. 
        ''' </summary> 
        ''' <returns></returns> 
        ''' <remarks> 
        ''' You should (as done here) keep it a mystery why a login failed as a hacker 
        ''' may be attempting to hack your app 
        ''' </remarks> 
        Public Function Login() As Boolean 
     
            Builder.Add("Jet OLEDB:Database Password", Me.DatabasePassword) 
            Builder.DataSource = Me.Database 
     
            If Not String.IsNullOrWhiteSpace(Me.UserName) AndAlso Not String.IsNullOrWhiteSpace(Me.UserPassword) Then 
                Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString} 
                    Using cmd As New OleDb.OleDbCommand With 
                        { 
                            .Connection = cn, 
                            .CommandText = 
                            "SELECT UserName,UserPassword FROM Users " & 
                            "WHERE UserName = @UserName AND UserPassword = @UserPassword" 
                        } 
                        cmd.Parameters.AddWithValue("@UserName", Me.UserName) 
                        cmd.Parameters.AddWithValue("@UserPassword", Me.UserPassword) 
     
                        Try 
                            cn.Open() 
                        Catch ex As Exception 
                            If ex.Message.ToLower.Contains("not a valid password") Then 
                                Return False 
                            Else 
                                Throw ex 
                            End If 
                        End Try 
     
     
                        Dim Reader = cmd.ExecuteScalar 
                        If Reader IsNot Nothing Then 
                            Retries = 0 
                            Return True 
                        Else 
                            Retries += 1 
                            Return False 
                        End If 
                    End Using 
                End Using 
            Else 
                Return False 
            End If 
        End Function 
     
    End Class 
    The demo uses a pass worded database. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, May 22, 2018 9:37 PM
    Moderator
  • Hello All,

    After some further testing today. I have managed to use both Karen's and Cor's code. Thanks to all for helping me with my code!

     Private Sub Login_button_click(sender As Object, e As EventArgs) Handles Login_Button.Click
            Dim random
            If Not String.IsNullOrEmpty(Password_Box.Text) Then
                Dim dt As New DataTable
                Dim commandtext As String = "SELECT UserName,UserPassword FROM information " &
                            "WHERE UserName = @UserName AND UserPassword = @UserPassword"
                Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\bobby\Documents\DOFE App\Starting_App Access Database\Database.accdb;")
                    Using cmd As New OleDbCommand(commandtext, cn)
                        cn.Open()
                        cmd.Parameters.AddWithValue("@UserName", Username_Box.Text)
                        cmd.Parameters.AddWithValue("@UserPassword", Password_Box.Text)
    
                        Dim answer = cmd.ExecuteScalar
                        If Not answer Is Nothing AndAlso Not answer Is DBNull.Value Then
                            random = CStr(answer)
                            Me.Hide()
                            Later.Show()
                        Else
                            MessageBox.Show("Username or Password was not recognised")
    
                        End If
                    End Using
    
                End Using
    
            Else
                MessageBox.Show("Please supply some text for the search")
            End If
    
        End Sub

    Wednesday, May 23, 2018 4:02 PM
  • But it seems you still do not understand that OleDB does not use named parameters but ordered parameters as in my sample. 

    It does work, but can give confusions. If you set in this case first the parameter for the Password and then for the Username, it goes wrong. While in SQLClient it goes well. 

    Therefore those ?, simply, it could as well been 2 times Robert.  


    Success
    Cor


    Wednesday, May 23, 2018 4:07 PM