Link to home
Start Free TrialLog in
Avatar of zipzip
zipzip

asked on

Why SELECT MAX() function always return 10?

My query  :
SELECT  MAX(FIE_FINO) AS Expr1 FROM     TBL_FIE

MSSQL SERVER : FIE_FINO is parameter that auto increment by 1

when comes to 10, the query always return 10
MSSQL data still will still continue to increase by 1, which mean 11,12,13,14...

but the query still return 10.

WHY THIS HAPPEN and how to solve it?
Avatar of Lacutah
Lacutah
Flag of United States of America image

Here's two guesses:
1.  Either your somehow querying catched data
2.  Is FIE_FINO a text field?  (Shouldn't be since it's auto incriment...)
and as a bonus, #3:
Are you adding rows to your local datatable, then querying SQL Server before you add your rows to the server?
Avatar of Mikal613
maybe its not a Autoincrement any more?
try to run DBCC CHECKIDENT
Avatar of zipzip
zipzip

ASKER

This is my Add Record button :

Private Sub Button3_Click_2(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        Dim result = DialogResult

        If ComboBox12.SelectedValue = "" Then
            MessageBox.Show("WORK DONE cannot be blank", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            ComboBox12.Focus()
        Else
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_FINO").Value = TextBox1.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_JNO").Value = ComboBox1.SelectedValue
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_RNO").Value = TextBox4.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_CUSACNO").Value = TextBox5.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_TSIZE").Value = TextBox6.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_SNO").Value = TextBox7.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_PAT").Value = TextBox19.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_WRKDN").Value = ComboBox12.SelectedValue
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_NGRT").Value = TextBox20.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_P1").Value = TextBox8.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_P2").Value = TextBox9.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_P3").Value = TextBox10.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_P4").Value = TextBox11.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_P5").Value = TextBox12.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_P6").Value = TextBox13.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_R10").Value = TextBox14.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_R12").Value = TextBox15.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_R14").Value = TextBox16.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_R20").Value = TextBox17.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_R22").Value = TextBox18.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_FSTINSP").Value = ComboBox2.SelectedValue
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_RPR").Value = ComboBox3.SelectedValue
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_BUFF").Value = ComboBox4.SelectedValue
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_SKIV").Value = ComboBox5.SelectedValue
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_FILL").Value = ComboBox6.SelectedValue
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_BUILD").Value = ComboBox7.SelectedValue
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_CURING").Value = ComboBox8.SelectedValue
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_FNLINSP").Value = ComboBox9.SelectedValue
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_STAP").Value = ComboBox10.SelectedValue
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_DATE").Value = DateTimePicker1.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_PERIOD").Value = DateTimePicker2.Text
            SqlDataAdapter1.InsertCommand.Parameters("@FIE_RMK").Value = TextBox3.Text

            Dim result1 As Integer
            SqlConnection3.Open()
            SqlCommand2.Parameters("@FINO").Value = TextBox1.Text
            result1 = SqlCommand2.ExecuteScalar
            SqlConnection3.Close()

            SqlDataAdapter1.InsertCommand.Parameters("@FIE_DNO").Value = result1 + 1

            result = MessageBox.Show("Do you want to add this record?", "Add Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
            If result = DialogResult.Yes Then
                DataSet301.Clear()
                SqlConnection1.Open()
                SqlDataAdapter1.InsertCommand.ExecuteNonQuery()
                MessageBox.Show("Record Added Successfully!", "Add Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
                SqlConnection4.Open()
                SqlDataAdapter4.SelectCommand.Parameters("@FIE_FINO").Value = TextBox1.Text
                SqlDataAdapter4.Fill(DataSet301, "TBL_FIE")
                SqlConnection4.Close()
                SqlConnection1.Close()

                TextBox4.Text = ""
                TextBox5.Text = ""
                TextBox6.Text = ""
                TextBox7.Text = ""
                ComboBox12.SelectedValue = ""
                TextBox8.Text = ""
                TextBox9.Text = ""
                TextBox10.Text = ""
                TextBox11.Text = ""
                TextBox12.Text = ""
                TextBox13.Text = ""
                TextBox14.Text = ""
                TextBox15.Text = ""
                TextBox16.Text = ""
                TextBox17.Text = ""
                TextBox18.Text = ""

                TextBox8.Text = ""
                TextBox9.Text = ""
                TextBox10.Text = ""
                TextBox12.Text = ""
                TextBox2.Text = result1 + 1


            ElseIf result = DialogResult.No Then
                MessageBox.Show("Record cancelled!", "Add Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
                SqlConnection1.Close()

                TextBox4.Text = ""
                TextBox5.Text = ""
                TextBox6.Text = ""
                TextBox7.Text = ""
                ComboBox12.SelectedValue = ""
                TextBox8.Text = ""
                TextBox9.Text = ""
                TextBox10.Text = ""
                TextBox11.Text = ""
                TextBox12.Text = ""
                TextBox13.Text = ""
                TextBox14.Text = ""
                TextBox15.Text = ""
                TextBox16.Text = ""
                TextBox17.Text = ""
                TextBox18.Text = ""

                TextBox8.Text = ""
                TextBox9.Text = ""
                TextBox10.Text = ""
                TextBox12.Text = ""
            End If
        End If
    End Sub
If FIE_FINO is an autoincriment on the SQL server, then why do you specify it in your insert statement?  Somethings not quite right here...
Avatar of zipzip

ASKER

FIE_FINO is a document number that will increase by 1 each time I click New Button

For example : FINO = 1
when click NEW , FINO = 1+1, = 2

I use method SELECT MAX(FIE_FINO) to get maximum value of this column then add it by 1 to get a new document number.

This method works fine for starting number 0 till 9, 11 till unlimited.

Just that when it comes to 9 +1,
Query SELECT MAX(FIE_FINO) will return 10, even though my database already have 11,12,13...

This is how I test the error:
Manually insert 11,12,13,14,15...
Then RUN query builder : SELECT MAX(FIE_FINO) as Expr1 FROM TBL_FIE
Expr1 return 10.

Thus, this cause my textbox shows 10 when I click NEW button.

So WHY THIS HAPPEN?
ASKER CERTIFIED SOLUTION
Avatar of Lacutah
Lacutah
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of zipzip

ASKER

I'm speechless... I can't believe I didn't think of that!

Thanks!!

But what is CAST function do?
The CAST function converts one data type to another - in this case it's converting a string (as it's currently stored in your database table) to an integer.  This is speculation #2 in my origional comment.  AutoIncriment is usally handled inside the SQL server itself - and it has to be an integer (or numeric) data type.