Getting the next Form No

[Eager_Beever]

I am using C# 2005 and SQL Server Express 2005. My table "PreRegistration" has a field named "FormNo" of data type smallint.

In my dataentry form, i need to display the next available FormNo in the text box. The program should find the last alloted FormNo, add 1 to it and display. If the table is empty, the suggested FormNo should be 1. It is not necessary to accept the suggested FormNo. The user can change it. If the last suggested FormNo was 125, it should suggest 126. But if the user changes it to 725, then the next FormNo should be 726.

I tried the following function :

private Int16 getNextSerial()
        {
            int intRCtr;
            string strConnString = @"server = .\SQLExpress; integrated security = SSPI; database = Data_main";
            string strCountRecords = @"Select Count(0) from PreRegistration";

            SqlConnection sqlConnCountRecords = new SqlConnection(strConnString);
            SqlCommand sqlCommCountRecords = new SqlCommand(strCountRecords, sqlConnCountRecords);

            sqlConnCountRecords.Open();
            intRCtr = sqlCommCountRecords.ExecuteNonQuery();
            
            if (intRCtr > 0)
            {
                string strMaxFormNo = @"Select Max(FormNo) from PreRegistration";
                SqlCommand sqlCommMaxFormNo = new SqlCommand(strMaxFormNo, sqlConnCountRecords);

                sqlConnCountRecords.Close();
                sqlConnCountRecords.Open();
                SqlDataReader sqlReaderMaxFormNo;

                sqlReaderMaxFormNo = sqlCommMaxFormNo.ExecuteReader();
                sqlReaderMaxFormNo.Read();
                intRCtr = sqlReaderMaxFormNo.GetInt16(0);
                intRCtr++;
            }
            else
            {
                intRCtr = 1;
            }
            return (Int16)intRCtr;
        }

Using "Select Count", returns a count of rows affected (therefore it always is 0). But I want it to return the result to a variable. It is necessary to check whether the table is empty or not, because "Select Max" can not be used on an empty table. I know, it should be very simple to implement, but unfortunately I am not able to solve it.

Please help me.

Thank you.

Lalit Kumar Barik
India

PS. Since I was not sure, whether it should be posted in C# forum or in Database forum, I am posting in both. Please excuse.

[Naik Dhiren]

select isnull(Max(formno),0) as FormNo from PreRegistration

use above query evenif table is empty.

[Eager_Beever]

select isnull(Max(formno),0) as FormNo from PreRegistration

use above query evenif table is empty.

I am sorry, but I could not implement it properly. (I am rather new to C# and still learning the tricks).

Lalit Kumar Barik
India