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.....

[yshie]

im sorry! tnx..

[TheCPUWizard]

Much more readable.

1) You did not add an output parameter to the first SQLCommand of @OrderID....(Opps, I missed it, it actually is at the end of the line, instead of a line be itself....), so that is OK...

2) In the OrdeDetails table, the OrderID should not be an identity column, or restricted to unique values....

[yshie]

yeah, my orderID in the 2nd table is not an autonumber field.. so, what are other possible causes for this?

[TheCPUWizard]

Generate a "Create Script" for your table and indices...and post it.

[yshie]

creating script is new to me. so, i tried to search and downloaded Sql Server Script - WK Software. i click on table and Generate Sql Script and this is what i got.. just let me know if i did it incorrectly. tnx for the help!

SET IDENTITY_INSERT [Orders] ON
GO
SET IDENTITY_INSERT [Orders] OFF
GO

SET IDENTITY_INSERT [OrderDetails] ON
GO
SET IDENTITY_INSERT [OrderDetails] OFF
GO

[TheCPUWizard]

Just go into SQL Managment Studio. Select the table, and select "Generate Script"..... It will create a SQL file that contains "CREATE TABLE........"

[yshie]

USE [C:\DOCUMENTS AND SETTINGS\G\MY DOCUMENTS\VISUAL STUDIO 2005\PROJECTS\PS\PS\PEDB.MDF]
GO
/****** Object: Table [dbo].[Orders] Script Date: 04/29/2007 16:12:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[Email] [varchar](70) NOT NULL,
[Total] [decimal](9, 2) NOT NULL,
[Status] [varchar](15) NOT NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([Email])
REFERENCES [dbo].[Customers] ([Email])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]




USE [C:\DOCUMENTS AND SETTINGS\G\MY DOCUMENTS\VISUAL STUDIO 2005\PROJECTS\PS\PS\PEDB.MDF]
GO
/****** Object: Table [dbo].[OrderDetails] Script Date: 04/29/2007 16:14:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderDetails](
[OrderID] [int] NOT NULL,
[MenuID] [int] NOT NULL,
[Price] [decimal](9, 2) NOT NULL,
[Quantity] [int] NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[OrderDetails] WITH CHECK ADD CONSTRAINT [FK_OrderDetails_Menu] FOREIGN KEY([MenuID])
REFERENCES [dbo].[Menu] ([MenuID])
GO
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_Menu]
GO
ALTER TABLE [dbo].[OrderDetails] WITH CHECK ADD CONSTRAINT [FK_OrderDetails_Orders1] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
GO
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_Orders1]