0

I am working with C# WinForms and what I need is to immediately get returned value of what I have inserted. So I have statement like this:

INSERT INTO MESSAGES (MESSAGEID, SENDER, RECEIVER, TITLE, MESSAGE, STATUS) 
VALUES (
    ((SELECT MAX(MESSAGEID) FROM MESSAGES) + 1), 
    @Sender, 
    @Receiver, 
    @Title, 
    @Message, 
    0)

and after this I need messageid I just inserted for my code.

Here is full function and where it needs to appear:

private void send_btn_Click(object sender, EventArgs e)
{
    using (FbConnection con = new FbConnection(Properties.Resources.connectionString_EB))
    {
        con.Open();
        using (FbCommand cmd = new FbCommand("INSERT INTO MESSAGES (MESSAGEID, SENDER, RECEIVER, TITLE, MESSAGE, STATUS) VALUES (((SELECT MAX(MESSAGEID) FROM MESSAGES) + 1), @Sender, @Receiver, @Title, @Message, 0)", con))
        {
            cmd.Parameters.AddWithValue("@Sender", EB.CurrentUser.userId);
            cmd.Parameters.Add("@Receiver", FbDbType.Integer);
            cmd.Parameters.AddWithValue("@Title", title_txt.Text);
            cmd.Parameters.AddWithValue("@Message", message_rtxt.Text);

            foreach(Int_String c in receiver_clb.CheckedItems)
            {
                cmd.Parameters["@Receiver"].Value = c._int;
                cmd.ExecuteNonQuery();

                if(EB.Users.Logged(c._int))
                {
                    //Get sent message id
                }
            }

            MessageBox.Show("Message successfully sent!");
        }
        con.Close();
    }
}
Vizel Leonid
  • 456
  • 7
  • 15
UProduction
  • 43
  • 1
  • 7
  • Here is another duplicate for firebird https://stackoverflow.com/q/20271814/495455 – Jeremy Thompson Oct 05 '17 at 06:54
  • 4
    `SELECT MAX(MESSAGEID) FROM MESSAGES) + 1` - please ***DO NOT** do this!! This is **NOT SAFE** in a busy system. If you need unique ID values - use an `INT IDENTITY` column in your SQL Server table and let the database handle the nitty gritty details of handing out the unique ID values.... – marc_s Oct 05 '17 at 06:58
  • I reopened the question, SQL Server questions - in general - don't answer questions about Firebird. – Mark Rotteveel Oct 05 '17 at 08:54
  • If you use Firebird 3, then create a `GENERATED BY DEFAULT AS IDENTITY` column, with earlier versions use a sequence + trigger. – Mark Rotteveel Oct 05 '17 at 08:55
  • Note: when inserting **do not specify the id column**, that will make it generated. If you need to obtain the value, use `INSERT .. RETURNING ..` – Mark Rotteveel Oct 05 '17 at 09:11

0 Answers0