Member Avatar for 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.

  • 2 Contributors
  • 2 Replies
  • 101 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Eager_Beever

Dani AI

Generated

A simple, robust approach is to run one scalar query that yields 0 when the table is empty, then add 1 to that value client-side. The root issue in 's routine was using ExecuteNonQuery for a SELECT (ExecuteNonQuery is for DML) and reading results without handling NULL. was correct to suggest treating MAX(FormNo) as zero when no rows exist. Use ExecuteScalar to fetch a single value reliably (ExecuteScalar).

Example pattern (safe conversion, using blocks, overflow check):

using (var conn = new SqlConnection(connString))
using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = "SELECT COALESCE(MAX(FormNo), 0) FROM PreRegistration";
    conn.Open();
    var raw = cmd.ExecuteScalar();
    int max = (raw == DBNull.Value ? 0 : Convert.ToInt32(raw));
    int nextInt = max + 1;
    if (nextInt > short.MaxValue) throw new InvalidOperationException("FormNo would exceed smallint range.");
    return (short)nextInt;
}

Important cautions and alternatives: this client-side suggestion can collide under concurrent users. For guaranteed uniqueness and to avoid race conditions, prefer a DB-generated number (IDENTITY or a SQL Server SEQUENCE) and let the server assign the value; if manual overrides are required, enforce a UNIQUE constraint on FormNo and handle duplicate-key errors by retrying the obtain/insert flow. If reserving the next number inside a transaction is required, use appropriate locking (for example SELECT MAX(...) WITH (UPDLOCK, HOLDLOCK)) or server-side sequences (CREATE SEQUENCE). Also consider changing the column type to INT if the smallint range may be exceeded.

Member Avatar for Naik Dhiren

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

use above query evenif table is empty.

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.