Having Difficulty with a datareader

[johnwooton]

Hey All,

I was working on a site and had an issue with the data reader. Maybe I am missing somethng:

Here's the error:
System.Data.OleDb.OleDbException: No value given for one or more required parameters

Here's the code that's causing it: (the line in bold was highlighted)

Line 12:           txtuser.Text & "'"
Line 13:           Dim Cmd as New OLEDBCommand(strSQL,Conn)
Line 14:           Dim Dr as OLEDBDataReader = _
Line 15:             Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Line 16:      If Dr.Read()

Here's a bit more from the code for you to see: (I obviously omitted the connection variable and the SQL string)

Dim Conn as New OLEDBConnection(strConn)
          Conn.Open()
          Dim strSQL as string = "SELECT (...)
          Dim Cmd as New OLEDBCommand(strSQL,Conn)
          Dim Dr as OLEDBDataReader = _
            Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
     If Dr.Read()
          If Dr("password").ToString = txtpassword.text Then
              lblLoginMsg.text = "Welcome " & txtuser.text & "<br />" & _
                    "<a href='" & Dr("mycp").ToString & "'>Control Panel</a>"
          Else
              lblLoginMsg.text = "Invalid Password"
          End If
     Else
          lblLoginMsg.text = "Username Not Valid"
     End If
     Dr.Close
    End Sub

What is it that I am missing or messing up?
J

[Phil Weber]

What is the SQL string?

[johnwooton]

Here's the SQL variable string

Dim strSQL as string = "SELECT [password].[login], [mycp].[login], [cptext].[login] FROM [login] WHERE [username] = '" & _
txtuser.Text & "'"

[Phil Weber]

Not sure what database you're using, but the table name usually precedes the column name. Try this:

Dim strSQL as string = "SELECT [login].[password], [login].[mycp], [login].[cptext] " & _
"FROM [login] WHERE [username] = '" & txtuser.Text & "'"

[Wiseman82]

Try this:

   Dim Conn As New OLEDBConnection(strConn)
        Conn.Open()
        Dim strSQL As String = "SELECT [login].[password], [login].[mycp], [login].[cptext] " & _
                                "FROM [login] WHERE [username] = ?"
        Dim Cmd As New OleDbCommand(strSQL, Conn)
        Cmd.Parameters.Add("@p1", OleDbType.VarChar).Value = txtUser.text
        Dim Dr As OleDbDataReader = _
          Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

        If Dr.Read() Then
            If Dr("password").ToString = txtpassword.text Then
                lblLoginMsg.text = "Welcome " & txtuser.text & "<br />" & _
                       "<a href='" & Dr("mycp").ToString & "'>Control Panel</a>"
            Else
                lblLoginMsg.text = "Invalid Password"
            End If
        Else
            lblLoginMsg.text = "Username Not Valid"
        End If
        Dr.Close()

Note - It's better to use parameters than building a SQL statement dynamically.
e.g. "WHERE UserName = ?" and use a parameter rather than
"WHERE UserName = '" & txtUserName.Text & "'"

Parameters help prevent SQL injection attacks and you should use them when possible.

Other possible improvements - you could maybe use a stored procedure (If your DB allows). I'd also recommend hashing users passwords.
ASP.NET 2.0 has built in membership providers you might want to take a look at.

Hope this helps,

David

[johnwooton]

Thank you to both of you for your helpful advice!

Phil: The SQL statement switch worked! That was the first time I've had to get more than two sections from the same table, but I am expanding my knowledge in the SQL arena. Thank you.

Wiseman: Thank you, I did apply that to the script. I know that it's always better to use parameters, but the last time I used them, I got into a rut and couldn't find my way out. This time though, I've had no problems. Thanks!

J