Select Max + 1

.NET
1

Hi,

I have the following insert query:-


        string connStr = "Data Source=SQLB23.webcontrolcenter.com;User ID=wbsd;Password=******";
        SqlConnection dbConn = new SqlConnection(connStr);

        dbConn.Open();

        string insertStatement = "INSERT INTO hussaini_messages ([message_id], [recipient_id], [sender_id], [subject], " +
        "[message], [thread_id], [date_added]) VALUES (@message_id, @recipient_id, @sender_id, @subject, " +
        "@message, @thread_id, GetDate())";

        SqlCommand insertCommand = new SqlCommand(insertStatement, dbConn);

        insertCommand.Parameters.Add("@message_id", SqlDbType.Int).Value = 0;
        insertCommand.Parameters.Add("@recipient_id", SqlDbType.VarChar).Value = DropDownList1.SelectedValue;
        insertCommand.Parameters.Add("@sender_id", SqlDbType.Int).Value = System.Convert.ToInt32(Session["MEM_ID"].ToString());
        insertCommand.Parameters.Add("@subject", SqlDbType.VarChar).Value = TextBox2.Text;
        insertCommand.Parameters.Add("@message", SqlDbType.VarChar).Value = TextBox3.Text;
        insertCommand.Parameters.Add("@thread_id", SqlDbType.Int).Value = 0;

        insertCommand.ExecuteNonQuery();

        dbConn.Close();

        Response.Redirect("inbox.aspx");

however i need to SELECT the max message_id and increment by 1 the value inserted. how can i do this?

Regards
Billy

2

did not test it but its something like this.

SET @message_id = (SELECT MAX(message_id) + 1 FROM hussaini_messages); INSERT …

Any reason for not using an auto increment/identity column?

3

Yes i cant have the message_id as AutoIncr as it refers to a specific set of messages. I already have a primary key relating to individual messages but i dont really need to do anything with that.

So anyway, will something like this work:-


        string insertStatement = "SET @message_id = (SELECT MAX(message_id) + 1 FROM hussaini_messages); " +
        "INSERT INTO hussaini_messages ([message_id], [recipient_id], [sender_id], [subject], " +
        "[message], [thread_id], [date_added]) VALUES (@message_id, @recipient_id, @sender_id, @subject, " +
        "@message, @thread_id, GetDate())";
4

yup

5

Hey i get this error:-

Must declare the scalar variable “@message_id”.
Must declare the scalar variable “@message_id”.

This is my code:-


        string connStr = "Data Source=SQLB23.webcontrolcenter.com;User ID=wbsd;Password=*******";
        SqlConnection dbConn = new SqlConnection(connStr);

        dbConn.Open();

        string insertStatement = "SET @message_id = (SELECT MAX(message_id) + 1 FROM hussaini_messages); " +
        "INSERT INTO hussaini_messages ([message_id], [recipient_id], [sender_id], [subject], " +
        "[message], [thread_id], [date_added]) VALUES (@message_id, @recipient_id, @sender_id, @subject, " +
        "@message, @thread_id, GetDate())";

        SqlCommand insertCommand = new SqlCommand(insertStatement, dbConn);

        //insertCommand.Parameters.Add("@message_id", SqlDbType.Int).Value = 0;
        insertCommand.Parameters.Add("@recipient_id", SqlDbType.VarChar).Value = DropDownList1.SelectedValue;
        insertCommand.Parameters.Add("@sender_id", SqlDbType.Int).Value = System.Convert.ToInt32(Session["MEM_ID"].ToString());
        insertCommand.Parameters.Add("@subject", SqlDbType.VarChar).Value = TextBox2.Text;
        insertCommand.Parameters.Add("@message", SqlDbType.VarChar).Value = TextBox3.Text;
        insertCommand.Parameters.Add("@thread_id", SqlDbType.Int).Value = 0;

        insertCommand.ExecuteNonQuery();

        dbConn.Close();

        Response.Redirect("inbox.aspx");

Any ideas?

Thanks again :slight_smile:

Regards

6

Yes, as you need to declare the variable before using it. declare @message_id int.

But you can just do it as a sub select in the query:


string insertStatement = "INSERT INTO hussaini_messages ([message_id], [recipient_id], [sender_id], [subject], " +

        "[message], [thread_id], [date_added]) VALUES ( (SELECT (MAX(message_id) + 1) FROM hussaini_messages), @recipient_id, @sender_id, @subject, " +

        "@message, @thread_id, GetDate())";
7

So what happens when you have two users inserting a message? Or, don’t write your own IDENTITY implementation if you can help it.

8

Perfect,

Thanks NightStalker-DNS your suggestion worked fine. :smiley:

Regards
Billy