[RESOLVED] Parameterized query

[mojo69]

I have this code I would like to change to a parameterized query.

        public String GetLoginRecordID(String User, String Password, String result,String connString)
        {
            String strCmd = "SELECT RecordID FROM SystemLogins WHERE (UserID = '" + User + "')" + 
                    " AND (UserPassword = '" + Password + "') AND (Result = 'VALID') ORDER BY RecordID;";
            Int32 intRecID = 0;
            try
            {
                using (OleDbConnection oleConn = new OleDbConnection(connString))
                {
                    using (OleDbCommand oleComm = new OleDbCommand(strCmd, oleConn))
                    {
                        oleConn.Open();
                        using (OleDbDataReader reader = oleComm.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                intRecID = reader.GetInt32(0);
                                return intRecID.ToString();
                            }
                        }
                    }
                }
                return intRecID.ToString();
            }
            catch (Exception ex)
            {
                return intRecID.ToString();   
            }
        }

Is this what I do?

        public String GetLoginRecordID(String User, String Password, String result,String connString)
        {
            String strCmd = "SELECT RecordID FROM SystemLogins WHERE (UserID = ?)" + 
                    " AND (UserPassword = ?) AND (Result = 'VALID') ORDER BY RecordID;";
            Int32 intRecID = 0;
            try
            {
                using (OleDbConnection oleConn = new OleDbConnection(connString))
                {
                    using (OleDbCommand oleComm = new OleDbCommand(strCmd, oleConn))
                    {
//change to this?
                        oleComm.Parameters.Add(User);      
                        oleComm.Parameters.Add(Password);
                        oleConn.Open();
                        using (OleDbDataReader reader = oleComm.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                intRecID = reader.GetInt32(0);
                                return intRecID.ToString();
                            }
                        }
                    }
                }
                return intRecID.ToString();
            }
            catch (Exception ex)
            {
                return intRecID.ToString();   
            }
        }

[mojo69]

Or is this a better way to do this?

        public string ValidUser(string connString, string User, string Pass)
        {
            OleDbConnection oleConn = new OleDbConnection(connString);
            OleDbCommand oleCmd = new OleDbCommand("SELECT COUNT(*) FROM SystemUsers01 WHERE Userid = @UserID AND Password = @PassWord", oleConn);
            oleCmd.Parameters.AddWithValue("@UserID", User);
            oleCmd.Parameters.AddWithValue("@PassWord", Pass);
            oleConn.Open();
            if ((int)oleCmd.ExecuteScalar() == 0)
            {
                //not found invalid user
                return "ERROR";
            }
            else
            {
                return "GOOD";
            }
        }

[techgnome]

well, I'd recommend using the nested using clauses as you did initially ...

other than that, the parameter forms (? vs @SomeName) is usually dictated by the database in question... SQL Server uses the named parameter format (@SomeName) while Access typically uses the positional format (?) ... but I think you can still use named parameters, but I've never tried personally... so I guess the answer is, find what parameter format works and run with it. If it turns out both work, then Bonus! In that case I'd go with Named Parameters simply because it makes things easier.

FYI - even if you use positional parameters (?) ... you can still name it when you add it...

OleDbConnection oleConn = new OleDbConnection(connString);
            OleDbCommand oleCmd = new OleDbCommand("SELECT COUNT(*) FROM SystemUsers01 WHERE Userid = ? AND Password = ?", oleConn);
            oleCmd.Parameters.AddWithValue("UserID", User);
            oleCmd.Parameters.AddWithValue("PassWord", Pass);

-tg