locked
is this the best way to handle user input validation? RRS feed

  • Question

  •  

     I am trying to figure out the best way to handle some user input.  The user is going to enter a number that represents an employees id. I want to be able to verify that this number is valid before it is written to the database.

     

     I am thinking about writing a stored proc that will count the number of rows in the employee table. The EmpID is the primary key in this table and is incremented automatically. So, a valid employee id would fall between 1 and the number of rows in the table. This is why I want to use count to see what the total number of rows are in the table.

     

    This would allow me to write some code that would have conditional logic. This logic would be used after my stored proc returned the number of rows in the table. So if the user enters a number that falls between 1 and the total number of rows then it would be valid.

     

    Does this sound like an okay way to do this? Does anyone have any other ideas?

     

                                                                   -thanks

     

     

                                  

    Saturday, January 12, 2008 9:01 PM

Answers

  • This falls within a tricky area.  Technically, you aren't guaranteed that the autonumber will generate a number that is Highest_Current_Value + 1.  The only guarantee you're given is that it will be unique to that table.  If you're going to use an autonumber, then you would be better off letting the user enter all of the information needed to create an employee, and then after you write to the table, tell the user what that employee's number is.

    If you're working with a system that can potentially have multiple people putting data into the database, then you won't really know what the new employee's number is until after it's written to the table anyway.  If there will only be a single point of input then you can supply a provisional number prior to running your insert, however you would still be better off telling the user what the new number is afterward.

    If it's just a matter of making sure that the employee number isn't already in the database, then you can perform a SELECT EmpID FROM TableName and return all of the values.  Create a datareader to read through each row returned and throw the numbers into a List<int>.  Call list.Sort(), and then place list[ list.Count - 1 ] + 1 into a read-only text box and mark that field as "Provisional".

    HTH
    Jason
    Sunday, January 13, 2008 2:13 AM
  • Jason appointments are right.

    If you have a "hole" on the index sequence, say, you have the key numbers 1, 2, 3 and 5, so the #4 is missing, therefore your COUNT(*) will be 4 but your next value will be 5 + 1, not 4 + 1. These things can give you a big headache.

    But I have a different idea to the query. If I'd need the last PK number on the table, I'd simply use:

    Code Block

    SELECT EmpID FROM employee ORDER BY EmpID LIMIT 1


    Hope this helps.
    Sunday, January 13, 2008 4:44 AM
  •  Rachmaninoff wrote:
    Jason appointments are right.

    If you have a "hole" on the index sequence, say, you have the key numbers 1, 2, 3 and 5, so the #4 is missing, therefore your COUNT(*) will be 4 but your next value will be 5 + 1, not 4 + 1. These things can give you a big headache.

    But I have a different idea to the query. If I'd need the last PK number on the table, I'd simply use:

    Code Block

    SELECT EmpID FROM employee ORDER BY EmpID LIMIT 1


    Hope this helps.


    This is essentially the point I was making.  If #4 is missing, but there is a #5, then the next number in the autonumber sequence will almost always be '6'.

    The SQL query posted by Rachmaninoff is much quicker and more elegant than my solution.  But this still should be noted as a Provisional value.  If you'll only have 1 program interfacing with the database, then the provision will be almost always be correct.  However, if there may be multiple people interfacing, then two people may get "6" as a provisional, but one of those people will ultimately pull the number '7' from the sequence.

    Jason
    Sunday, January 13, 2008 4:54 AM
  • SQL Server doesn't give you a 'provisional' number.  You can calculate a provisional number by using the
    SELECT * FROM [Table] SORT BY EmpId DESC LIMIT 1
    query.  This will give you the highest number already in the database.  If this query is run by two users, each will get the same number.  Your provisional will be this number + 1.  So you'll have two users looking at the same provisional number. 

    When you call the query
    INSERT INTO [Table] (EmpName, [etc]) VALUES ('Bob', [etc])
    your first record will likely use the provisional, while a second call by the second user will insert a number 1 higher than the provisional they were looking at.


    So, in reality, you (the programmer) are creating the provisional number - the number that you are guessing will be used by the next record - based upon the highest value already there.  The reason you would do this is because if a record is deleted, the ID that was used by that record will not be re-used by a new record later on.  That number is now gone forever, and your highest value in the EmpID field no longer accurately reflects the number of rows you have in your table.

    Jason
    Sunday, January 13, 2008 4:26 PM

All replies

  • This falls within a tricky area.  Technically, you aren't guaranteed that the autonumber will generate a number that is Highest_Current_Value + 1.  The only guarantee you're given is that it will be unique to that table.  If you're going to use an autonumber, then you would be better off letting the user enter all of the information needed to create an employee, and then after you write to the table, tell the user what that employee's number is.

    If you're working with a system that can potentially have multiple people putting data into the database, then you won't really know what the new employee's number is until after it's written to the table anyway.  If there will only be a single point of input then you can supply a provisional number prior to running your insert, however you would still be better off telling the user what the new number is afterward.

    If it's just a matter of making sure that the employee number isn't already in the database, then you can perform a SELECT EmpID FROM TableName and return all of the values.  Create a datareader to read through each row returned and throw the numbers into a List<int>.  Call list.Sort(), and then place list[ list.Count - 1 ] + 1 into a read-only text box and mark that field as "Provisional".

    HTH
    Jason
    Sunday, January 13, 2008 2:13 AM
  • Jason appointments are right.

    If you have a "hole" on the index sequence, say, you have the key numbers 1, 2, 3 and 5, so the #4 is missing, therefore your COUNT(*) will be 4 but your next value will be 5 + 1, not 4 + 1. These things can give you a big headache.

    But I have a different idea to the query. If I'd need the last PK number on the table, I'd simply use:

    Code Block

    SELECT EmpID FROM employee ORDER BY EmpID LIMIT 1


    Hope this helps.
    Sunday, January 13, 2008 4:44 AM
  •  Rachmaninoff wrote:
    Jason appointments are right.

    If you have a "hole" on the index sequence, say, you have the key numbers 1, 2, 3 and 5, so the #4 is missing, therefore your COUNT(*) will be 4 but your next value will be 5 + 1, not 4 + 1. These things can give you a big headache.

    But I have a different idea to the query. If I'd need the last PK number on the table, I'd simply use:

    Code Block

    SELECT EmpID FROM employee ORDER BY EmpID LIMIT 1


    Hope this helps.


    This is essentially the point I was making.  If #4 is missing, but there is a #5, then the next number in the autonumber sequence will almost always be '6'.

    The SQL query posted by Rachmaninoff is much quicker and more elegant than my solution.  But this still should be noted as a Provisional value.  If you'll only have 1 program interfacing with the database, then the provision will be almost always be correct.  However, if there may be multiple people interfacing, then two people may get "6" as a provisional, but one of those people will ultimately pull the number '7' from the sequence.

    Jason
    Sunday, January 13, 2008 4:54 AM
  • Thanks to both of you for the help.

     

    I didn't know this about SQL Server. I didn't know that there can be a provisional value of  "6" given to two users at the same time. When does SQL Server change this so that there are no duplicates? Is it right before it writes to the table?

    I am assuming that it figures this out when this is being taken from memory and just before it is written to the table.

     

                                            Thanks, It looks like I was about to develop a bad habit.

    Sunday, January 13, 2008 3:37 PM
  • SQL Server doesn't give you a 'provisional' number.  You can calculate a provisional number by using the
    SELECT * FROM [Table] SORT BY EmpId DESC LIMIT 1
    query.  This will give you the highest number already in the database.  If this query is run by two users, each will get the same number.  Your provisional will be this number + 1.  So you'll have two users looking at the same provisional number. 

    When you call the query
    INSERT INTO [Table] (EmpName, [etc]) VALUES ('Bob', [etc])
    your first record will likely use the provisional, while a second call by the second user will insert a number 1 higher than the provisional they were looking at.


    So, in reality, you (the programmer) are creating the provisional number - the number that you are guessing will be used by the next record - based upon the highest value already there.  The reason you would do this is because if a record is deleted, the ID that was used by that record will not be re-used by a new record later on.  That number is now gone forever, and your highest value in the EmpID field no longer accurately reflects the number of rows you have in your table.

    Jason
    Sunday, January 13, 2008 4:26 PM
  • Thanks for your time.

     

    Sunday, January 13, 2008 5:23 PM
  •  

    Hi dear

     

    At the end of the thread you have asked that

    "Does this sound like an okay way to do this? Does anyone have any other ideas?"

     

    Well If you requirments is what you have asked in the thread then why u r asking the user to input the ID of the employee?

     

    In the SQL server set the DataType of the employee id = int

    then Set the identity of the column = Yes.

     

    After doing this you dont need to prompt the user to enter the ID of the employee. It will be inserted automatically.

     

     

    Regards,

    Shahid Riaz Bhatti

    MCP, MCAD

    Sunday, January 13, 2008 8:06 PM