dcsimg
Results 1 to 7 of 7
  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2003
    Location
    Wigan, UK
    Posts
    278

    Is this correct using MAX

    Hi

    I have a column called DiaryCallCode which is a number in a table called Diary. I want to find the maximum number in that column so i can add one for the next row i insert.

    I was thinking of using the following but how would you handle the first time it runs when there are no rows at all without getting any null errors.

    Dim cmd As OleDbCommand = New OleDbCommand("SELECT MAX(DiaryCallCode) FROM Diary", Con)
    DiaryCallNo = cmd.ExecuteScalar
    DiaryCallNo += 1

    Thanks

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,813

    Re: Is this correct using MAX

    Is there a particular reason that you want to set the number yourself, rather than letting the database system do it automatically?

    Letting the database system do it automatically solves lots of different issues, including this one, and dealing with multiple users adding a record at the same time, and several others.

    To do that simply set the field properties in the database (as Identity or AutoNumber etc), and in your .Net code leave the field (and value) out of your Insert statement (assuming that is how you are adding a record).


    To 'fix' the code you have, you could add something like this:
    Code:
    DiaryCallNo = cmd.ExecuteScalar
    If DiaryCallNo Is DBNull.Value Then
      DiaryCallNo = 0
    End If
    DiaryCallNo += 1

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2003
    Location
    Wigan, UK
    Posts
    278

    Re: Is this correct using MAX

    The reason i dont use the autonumber is because there will be times when i insert related rows in which i use the same number as another existing row.

    I dont like the thoughts of it being a 'fix' as you put it to be honest but will try it.


    Thanks for your time.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2003
    Location
    Wigan, UK
    Posts
    278

    Re: Is this correct using MAX

    Dim DiaryCallNo As Integer

    Dim cmd As OleDbCommand = New OleDbCommand("SELECT MAX(DiaryCallCode) FROM Diary", Con)
    DiaryCallNo = cmd.ExecuteScalar

    If DiaryCallNo Is DBNull.Value Then
    DiaryCallNo = 0
    End If

    DiaryCallNo += 1

    I get the error 'Is' operator does not accept operands of type 'Integer'. Operands must be reference or nullable types.

    Any Ideas ?

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    98,682

    Re: Is this correct using MAX

    You've declared DiaryCallNo as type Integer so how can it possibly be DBNull.Value? What will happen there is that your app will crash if ExecuteScalar returns DBNull.Value because it cannot assign that to an Integer variable. You need to declare a variable as type Object and assign the result of ExecuteScalar to that, then test whether that is DBNull.value, then cast and assign to a separate Integer variable if it is not DBNull.Value.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,709

    Re: Is this correct using MAX

    You could use IsNull in the query itself. That will neatly sidestep the problem:-
    Code:
    SELECT isnull(MAX(DiaryCallCode), 0) FROM Diary
    This assumes you're using sql server. If you're using some other db it shouldn't be too hard to track down the equivalent function.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,709

    Re: Is this correct using MAX

    The reason i dont use the autonumber...
    Just a thought but you want to be careful using this approach as it can cause problems if it interleaves in a multi user environment.

    E.g.
    Lets say the last number is 10
    User 1 Asks for the next number and gets 11
    Before User 1 Creates a new Diary record, User 2 asks for the next number and gets... 11
    User 1 now creates their new record
    User 2 now creates their new record

    You will have two records with the same Diarycallcode.

    There are two ways to prevent this:
    1. Wrap the whole thing in a transaction. Note that transactions lock rows and tables (I'm pretty sure it'll lock the table in this case as you're selecting a max) so, if you take this approach, make sure you open and commit the transaction quickly. So no big long operations in between and DEFINITELY no waiting for user interaction.
    2. Get the next id at the point you want to use it as part of the Insert query. E.g.
    Code:
    Insert into Diary (Id, SomeField1, SomeField2)
    Select Max(DiaryCallCode) + 1, @SomeValue1, @SomeValue2)
    From Diary
    I prefer approach 2. It actually has an implicit transaction in it but, because it's contained in the same statement as the insert, there's no chance of you mucking up and leaving the transaction open unnecessarily.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.