Having Difficulty with a datareader


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Having Difficulty with a datareader

  1. #1
    Join Date
    Mar 2006
    Posts
    14

    Having Difficulty with a datareader

    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)


    Code:
    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)

    Code:
    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

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    What is the SQL string?
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Mar 2006
    Posts
    14
    Here's the SQL variable string

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

  4. #4
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    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 & "'"
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  5. #5
    Join Date
    Feb 2006
    Posts
    55
    Try this:

    Code:
       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
    David Wiseman
    MCSE (2000/2003), MCSA (2003), MCDBA
    www.wisesoft.co.uk


  6. #6
    Join Date
    Mar 2006
    Posts
    14
    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

Similar Threads

  1. Out of Range while using DataReader
    By kashif_82 in forum ASP.NET
    Replies: 2
    Last Post: 08-06-2005, 02:41 PM
  2. having difficulty with an exercise!!
    By Andy-N in forum Java
    Replies: 2
    Last Post: 07-31-2005, 06:43 PM
  3. Replies: 12
    Last Post: 07-03-2005, 05:56 PM
  4. Datareader in data tier??
    By Nick in forum .NET
    Replies: 1
    Last Post: 10-28-2002, 04:19 PM
  5. Difficulty in starting Java Server Pages
    By stanley in forum Java
    Replies: 2
    Last Post: 11-13-2000, 04:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center