select @@identity

[yshie]

SQL SERVER

private void buttonDone_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.PEdbConnectionString))
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Orders VALUES(@OrderDate, @Email);SELECT @OrderID=@@IDENTITY", conn))
{
cmd.Parameters.Add("@OrderDate", SqlDbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = User.Text; cmd.Parameters.Add("@OrderID",SqlDbType.Int).Direction=ParameterDirection.Output;

conn.Open();
cmd.ExecuteNonQuery();
int idResult = Convert.ToInt16(cmd.ExecuteScalar());
}
}
}

I'm just new with this @@identity. what i want to do is insert values in my table. OrderID is autonumber. after the insert, i want to get the OrderID inserted because i will use it in another insert in another table.. but i haven't included the other code here because i want to solve it one by one. and i don't know if this code is correct. pls help!

[TheCPUWizard]

Not quite. The combined SQL will be executed as one command, so you dont want a "NonQuery" followed bot a "Scalar", just a "Scalar".

On a related design note, you should get in the habit of using stored procedures rather than direct SQL. You can implement better security and the database engine (especially SQLServer) can create optimized query plans to improve performance.

[yshie]

cmd.ExecuteNonQuery();
int idResult = Convert.ToInt16(cmd.ExecuteScalar());

so instead of the above 2 statements, i do this...
int idResult = Convert.ToInt16(cmd.ExecuteScalar());

but when i check the value, it is zero.. y? or maybe i didn't understand you.

[TheCPUWizard]

Actually I mis read your post....

SELECT @OrderID=@@IDENTITY

Add a new parameter called @OrderID, set the parameter direction to Out. Then use an ExecuteNonQuery()

Sorry for the confusion....


ps: If you make this a stored proc, you can also test it directly from SQL Management Studio

[yshie]

private void buttonDone_Click(object sender, EventArgs e)
{
    using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.PEdbConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO Orders VALUES(@OrderDate, @Email);SELECT @OrderID=@@IDENTITY", conn))
        {
            cmd.Parameters.Add("@OrderDate", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = User.Text; cmd.Parameters.Add("@OrderID",SqlDbType.Int).Direction=ParameterDirection.Output;

            conn.Open();
            cmd.ExecuteScalar();
            cmd.ExecuteNonQuery();
            int idResult = (int)cmd.Parameters["@OrderID"].Value;
        }

        using (SqlCommand cmd1 = new SqlCommand("INSERT INTO OrderDetails VALUES(@OrderID, @MenuID)", conn))
        {

            int i = 0;
            while (i < listViewOrders.Items.Count)
            {
            cmd1.Parameters.Add("@OrderID", SqlDbType.Int).Value = idResult;
            cmd1.Parameters.Add("@MenuID", SqlDbType.Int).Value = comboBoxProdName.SelectedValue;
                                           
            cmd1.ExecuteScalar();
            cmd1.Parameters.Clear();
            i++;
            }

            MessageBox.Show("Successful");
        }                    
    }
}

i got this code now.. the added code is the one that inserts values on the 2nd table as i was saying in my thread. but it got error..

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_OrderDetails_Orders1". The conflict occurred in database "C:\...", table "dbo.Orders", column 'OrderID'.
The statement has been terminated.

pls help!

[TheCPUWizard]

Go back and EDIT your EXISTING post, and use code tags. Please go back and re-read the FAQ. Your post is nearly unreadable, and not worth the time it would take to re-format it to be readable.

We really try to help those who show at least a little consideration.....