CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2007
    Posts
    42

    select @@identity

    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!

  2. #2
    Join Date
    Mar 2002
    Location
    St. Petersburg, Florida, USA
    Posts
    12,125

    Re: select @@identity

    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.
    TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
    2008, 2009,2010
    In theory, there is no difference between theory and practice; in practice there is.

    * Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
    * How NOT to post a question here
    * Of course you read this carefully before you posted
    * Need homework help? Read this first

  3. #3
    Join Date
    Apr 2007
    Posts
    42

    Re: select @@identity

    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.

  4. #4
    Join Date
    Mar 2002
    Location
    St. Petersburg, Florida, USA
    Posts
    12,125

    Re: select @@identity

    Actually I mis read your post....

    Code:
    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
    TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
    2008, 2009,2010
    In theory, there is no difference between theory and practice; in practice there is.

    * Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
    * How NOT to post a question here
    * Of course you read this carefully before you posted
    * Need homework help? Read this first

  5. #5
    Join Date
    Apr 2007
    Posts
    42

    Re: select @@identity

    Code:
    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!
    Last edited by yshie; April 28th, 2007 at 01:52 PM.

  6. #6
    Join Date
    Mar 2002
    Location
    St. Petersburg, Florida, USA
    Posts
    12,125

    Re: select @@identity

    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.....
    TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
    2008, 2009,2010
    In theory, there is no difference between theory and practice; in practice there is.

    * Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
    * How NOT to post a question here
    * Of course you read this carefully before you posted
    * Need homework help? Read this first

  7. #7
    Join Date
    Apr 2007
    Posts
    42

    Re: select @@identity

    im sorry! tnx..

  8. #8
    Join Date
    Mar 2002
    Location
    St. Petersburg, Florida, USA
    Posts
    12,125

    Re: select @@identity

    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....
    TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
    2008, 2009,2010
    In theory, there is no difference between theory and practice; in practice there is.

    * Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
    * How NOT to post a question here
    * Of course you read this carefully before you posted
    * Need homework help? Read this first

  9. #9
    Join Date
    Apr 2007
    Posts
    42

    Re: select @@identity

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

  10. #10
    Join Date
    Mar 2002
    Location
    St. Petersburg, Florida, USA
    Posts
    12,125

    Re: select @@identity

    Generate a "Create Script" for your table and indices...and post it.
    TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
    2008, 2009,2010
    In theory, there is no difference between theory and practice; in practice there is.

    * Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
    * How NOT to post a question here
    * Of course you read this carefully before you posted
    * Need homework help? Read this first

  11. #11
    Join Date
    Apr 2007
    Posts
    42

    Re: select @@identity

    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

  12. #12
    Join Date
    Mar 2002
    Location
    St. Petersburg, Florida, USA
    Posts
    12,125

    Re: select @@identity

    Just go into SQL Managment Studio. Select the table, and select "Generate Script"..... It will create a SQL file that contains "CREATE TABLE........"
    TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
    2008, 2009,2010
    In theory, there is no difference between theory and practice; in practice there is.

    * Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
    * How NOT to post a question here
    * Of course you read this carefully before you posted
    * Need homework help? Read this first

  13. #13
    Join Date
    Apr 2007
    Posts
    42

    Re: select @@identity

    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]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured