Thread: OleDB connection

  1. #1
    Registered User
    Join Date
    Nov 2011
    Posts
    39

    OleDB connection

    i originally wrote this code in visual basic and it worked fine but when i tried rewriting it in c# it raised an error saying 'dr is a variable but is being used like a method' , please how do i fix this?

    Code:
    public bool validateuser(string password, string username)
    {
        OleDbCommand cmd = new OleDbCommand();
        dbconnection db = new dbconnection();
        cmd.Connection = db.connection;
        cmd.CommandText = "select * from userlogin where password = ? and username = ?";
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("", password);
        cmd.Parameters.AddWithValue("", username);
    
        bool found = false;
        OleDbDataReader dr = default(OleDbDataReader);
        dr = cmd.ExecuteReader;
        if (dr.Read) {
            found = true;
        }
    
        return found;
    }

  2. #2
    Registered User
    Join Date
    Mar 2009
    Location
    england
    Posts
    209
    Code:
    OleDbDataReader dr = cmd.ExecuteReader();
    And

    Code:
    if (dr.Read())
    The clue was in the exception.

    Also don't forget to Dispose cmd, db and dr, or better yet, use 'using' statements.
    Last edited by theoobe; 11-29-2012 at 06:36 PM.

  3. #3
    Registered User
    Join Date
    Nov 2011
    Posts
    39
    This is the full code it now gives me the same error when i try using dr("username").(dr is a variable but is being used like a method)
    i am now learning c# and am trying to search and retreive from a database.

    Code:
    public bool validateuser(string password, string username)
    {
        namespace ConsoleApplication1
    {
        class @dbconnection
        {
            private static OleDbConnection conn = new OleDbConnection();
    
            static void con()
            {
                conn.ConnectionString = "Provider=MSDAORA;Data Source=xe;Persist Security Info=True;User ID=GOL;Password=kaRkaRr0t";
                conn.Open();
            }
    
             public new OleDbConnection connection
             {
                get
                {
                     return conn;
                }              
             }      
        }
    
        class @test
        {
            private static string user;
            public static string username
            {
                get
                {
                    return user;
                }
                set
                {
                    user = value;
                }
            }
    
            public static void getname()
            {
                OleDbCommand cmd = new OleDbCommand();
                dbconnection db = new dbconnection();
                cmd.Connection = db.connection;
                cmd.CommandText = "select * from userlogin where password = 'user'";
                cmd.CommandType = CommandType.Text;
                
                OleDbDataReader dr =  default(OleDbDataReader);
                dr = cmd.ExecuteReader();
                if(dr.Read())
                {
                    username = dr("username") ;
                }
           
            }
        }
    }

  4. #4
    the hat of redundancy hat nvoigt's Avatar
    Join Date
    Aug 2001
    Location
    Hannover, Germany
    Posts
    3,130
    That's a lot of overhead to get confused. Try to keep your programs simpler while learning new things.

    Code:
    using System;
    using System.Data.OleDb;
    
    namespace ConsoleApplication1
    {
      internal class Program
      {
        public static bool ValidateUser(string password, string username)
        {
          const string connectionString = "Provider=MSDAORA;Data Source=xe;Persist Security Info=True;User ID=GOL;Password=kaRkaRr0t";
          const string sql = "SELECT COUNT(*) FROM USERLOGIN WHERE USERNAME = @name AND PASSWORD = @password";
    
          using (var connection = new OleDbConnection(connectionString))
          {
            using (var command = new OleDbCommand(sql, connection))
            {
              command.Parameters.AddWithValue("@name", username);
              command.Parameters.AddWithValue("@password", password);
    
              return Convert.ToInt32(command.ExecuteScalar()) > 0;
            }
          }
        }
    
        public static void Main(string[] args)
        {
          const string user = "test";
          const string password = "secret";
    
          bool isValid = ValidateUser(password, user);
    
          Console.WriteLine("{0}/{1} is {2}", user, password, isValid ? "valid" : "invalid");
        }
      }
    }
    I couldn't test it here, but it should demostrate what steps you have to take to authenticate somebody.
    Last edited by nvoigt; 12-01-2012 at 11:46 AM. Reason: replaced ExecuteReader with ExecuteScalar
    hth
    -nv

    She was so Blonde, she spent 20 minutes looking at the orange juice can because it said "Concentrate."

    When in doubt, read the FAQ.
    Then ask a smart question.

  5. #5
    Registered User
    Join Date
    Nov 2011
    Posts
    39
    I'm not really looking for how to authenticate i'm just looking for how to retrieve a value from a database.

  6. #6
    Registered User
    Join Date
    Mar 2009
    Location
    england
    Posts
    209
    Use square brackets...

    Code:
    dr["username"]
    Returning to my earlier point about disposing your database objects, it is bad practice to leave database connections lingering, which is what you are doing by failing to dispose these objects.

  7. #7
    Registered User
    Join Date
    Nov 2011
    Posts
    39
    Okay i'll dispose the database objects but with your answer to my question(using square brackets) i tried it and got this error :

    "Only assignment, call, increment, decrement, and new object expressions can be used as a statement"

  8. #8
    Registered User
    Join Date
    Mar 2009
    Location
    england
    Posts
    209
    Probably needs casting...

    Code:
    if(dr.Read())
        username = (String)dr["username"];

  9. #9
    Gawking at stupidity
    Join Date
    Jul 2004
    Location
    Oregon, USA
    Posts
    3,218
    Quote Originally Posted by nvoigt
    return Convert.ToInt32(command.ExecuteReader()) > 0;
    I think you meant command.ExecuteScalar(). Or can you convert a DataReader to Int32 and I just never knew it?
    If you understand what you're doing, you're not learning anything.

  10. #10
    Registered User
    Join Date
    Nov 2011
    Posts
    39
    this is the same way i did it in vb and i understood it but now i'm haiving trouble implementing it in c#, i've tried "username = (string)dr["username"]" and i still get the same error so i don't think that is the problem. The main issue is i want to retrieve the username from the database into a variable using oledb, so if anyone has another way of doing it could you please show me.

  11. #11
    the hat of redundancy hat nvoigt's Avatar
    Join Date
    Aug 2001
    Location
    Hannover, Germany
    Posts
    3,130
    Quote Originally Posted by itsme86 View Post
    I think you meant command.ExecuteScalar(). Or can you convert a DataReader to Int32 and I just never knew it?
    Oops, absolutely! I'll change it in the above code.
    hth
    -nv

    She was so Blonde, she spent 20 minutes looking at the orange juice can because it said "Concentrate."

    When in doubt, read the FAQ.
    Then ask a smart question.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Ftp Connection with C?
    By ulaas in forum C Programming
    Replies: 9
    Last Post: 12-03-2009, 10:56 AM
  2. Using OleDb in C#?
    By jcafaro10 in forum C# Programming
    Replies: 10
    Last Post: 06-29-2009, 10:28 AM
  3. OLEDB Provider Properties
    By IfYouSaySo in forum Windows Programming
    Replies: 0
    Last Post: 04-26-2006, 12:26 AM
  4. Writing OLEDB Provider
    By IfYouSaySo in forum Windows Programming
    Replies: 0
    Last Post: 04-06-2006, 04:55 PM
  5. OLEDB Connection String to Excel
    By gozlan in forum C# Programming
    Replies: 4
    Last Post: 07-06-2003, 01:42 PM