Help Needed!!!!!!

[ajinkya112]

Hi everyone,

From couple of days i am trying to write a code that would generate Id automatically
and store it in table. I have a table "STU". It has two columns Id and Name.
I have taken a form which has two text boxes and a button.
text box1 is for ID and it is disabled, textbox2 is for name where user has to enter a
name. I want the ID which is generated automatically, to be displayed in textbox1 and then store
it with the name that user has entered in textbox2. If anybody can suggest a code or a logic
that would be great.

thank you
ajinkya

[darkagn]

There are two ways that you can tackle this problem. Either make the ID field in the table an identity field then read it back after you insert the name, or you can retrieve the biggest ID number and add 1 to it using this value to insert.

[Mitja Bonca]

As darkagn said. Heres an example:

private void PopulatingTextBoxes()
        {
            int id = GetNewId();
            textBox1.Text = id.ToString();
        }

        private int GetNewId()
        {
            int id = 0;
            using (SqlConnection sqlConn = new SqlConnection("ConnectionString"))
            {
                string query = "SELECT (MAX)ID FROM STU";
                SqlCommand cmd = new SqlCommand(query, sqlConn);
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (reader.GetValue(0) == DBNull.Value)
                            id = 1;
                        else
                        {
                            id = (int)reader[0]; //read the higest id from table!
                            id++;                //higher id by 1, to get new id!
                        }
                    }
                }
                return id;
            }
        }

Mitja

[ajinkya112]

Hey Mitja,

Thanks a ton for your reply.
That really worked.

Hey, i am sorry to as for more, but there's a catch.

I want to ID to be in the form of 'S1', 'S2'...and so on.
But as in the above code you have declared 'id' as int, hence
after i enter first Id as 'S1', it gives error.
Can suggest some code so that i can rectify this error.
Sorry if I am asking for more.

Thanks again

[prvnkmr194]

Change the datatype in your database to TEXT
and make the following changes to your code
logic behind this code is that, you just extract the integer part from your id and increase one to it with initial S

if (reader.Read())
                    {
                        if (reader.GetValue(0) == DBNull.Value)
                            id ="S1";
                        else
                        {
                            id =Convert.ToString( reader[0]) ; //read the higest id from table!
                            id="s" + Convert.ToString(Convert.ToInt64(id.Remove(0, 1)) + 1);      //higher id by 1, to get new id!
                        }
                    }

Here id is string variable
I hope it will help you

[Mitja Bonca]

This will not work then. Why? 1st of all, your database`s column of id has to be a varchar type (so string, not integer). And 2nd of all, when retrieving the value out of DB, you have to retrieve it into a string value.
So,

private void PopulatingTextBoxes()
        {
            string id = GetNewId();
            textBox1.Text = id;
        }

        private string GetNewId()
        {
            string id = null;
            using (SqlConnection sqlConn = new SqlConnection("ConnectionString"))
            {
                string query = "SELECT (MAX)ID FROM STU";
                SqlCommand cmd = new SqlCommand(query, sqlConn);
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (reader.GetValue(0) == DBNull.Value)
                            id = "S1";
                        else
                        {
                            id = (string)reader[0]; //read the higest id from table!
                            //now you have to higher only the number by 1:
                            int number = Convert.ToInt32(id.Substring(1, id.Length - 1));
                            number++;
                            id = "S" + number.ToString();
                        }
                    }
                }
                return id;
            }
        }

Hope this helps,
Mitja