Click here to Skip to main content
15,883,705 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
Hey all,
I have my code to I can create a new receipt NEXT I am supposed to add my list of products to another table linked by my receiptId But my database automatically creates the receiptId and the only way to read the receipt from the database is if I have the receiptId So how would I get the receiptId so I could insert my list of names to another table having them linked together?

C#
private void BtnConfirmCart_Click(object sender, RoutedEventArgs e)
        {
            if (listOfP.Count != 0) //!= null?
            {
                string msg = "Are you sure you CONFIRM your cart?";
                MessageBoxResult boxResult = MessageBox.Show(msg, "Confirmation", MessageBoxButton.YesNo);
                if(boxResult == MessageBoxResult.Yes)
                {
                    //Confirm cart /Add to SQL(Create Receipt, Create Product List) /Go to receipt.
                    try
                    {
                        date = DateTime.Now;
                        decimal total = receiptCart.ReceiptTotal;
                        int userid = customer.UserId;
                        Extras.CreateReceipt(userid, total, date);


                    }
                    catch(Exception ex) { MessageBox.Show(ex.Message.ToString()); }

                    Window WindowReceipt = new ReceiptWindow();
                    WindowReceipt.Show();
                    Close();
                }
            }
            else
            {
                BtnGoBack.Visibility = Visibility.Hidden;
                MessageBox.Show("No items in your cart.", "Add items.");
            }
        }
//Create Receipt int or void (Receipt receipt)
        public static int CreateReceipt(int userid, decimal rtotal, DateTime rdate)
        {
            int result = -1;
            using(SqlConnection connection = ConnectionString.GetSqlConnection())
            {
                string sqlCreate = "INSERT INTO Orders(UserId, ReceiptDate, ReceiptTotal) " +
                                               "VALUES(@uid, @date, @rtotal)";
                using(SqlCommand cmdCreate = new SqlCommand(sqlCreate, connection))
                {
                    //cmdCreate.Parameters.AddWithValue("@rid", receipt.ReceiptID);
                    cmdCreate.Parameters.AddWithValue("@uid", userid);
                    cmdCreate.Parameters.AddWithValue("@date", rdate);
                    cmdCreate.Parameters.AddWithValue("rtotal", rtotal);
                    try
                    {
                        connection.Open();
                        result = Convert.ToInt32(cmdCreate.ExecuteScalar());
                    }
                    catch(Exception ex) { throw ex; }
                }                
            }
            return result;
        }
        //Read Receipt
        public static Receipt ReadReceipt(int receiptID)
        {
            Receipt receipt = new Receipt();
            using(SqlConnection connection = ConnectionString.GetSqlConnection())
            {
                string sqlRead = "SELECT * FROM Receipts WHERE ReceiptId = @rid";
                using(SqlCommand cmdRead = new SqlCommand(sqlRead, connection))
                {
                    cmdRead.Parameters.AddWithValue("@rid", receiptID);
                    connection.Open();
                    using(SqlDataReader reader = cmdRead.ExecuteReader())
                    {
                        if(reader != null)
                        {
                            while (reader.Read())
                            {
                                receipt.ReceiptID = Convert.ToInt32(reader["ReceiptId"].ToString());
                                receipt.UserId = Convert.ToInt32(reader["UserId"].ToString());
                                receipt.ReceiptDate = Convert.ToDateTime(reader["ReceiptDate"].ToString());
                                receipt.ReceiptTotal = Convert.ToDecimal(reader["ReceiptTotal"].ToString());
                            }
                        }
                    }
                }
            }
            return receipt;
        }
//THIS IS MY PLAN TO READ PRODUCTLIST TABLE NOT SURE IF THIS WILL WORK YET
//Retreive all products in list from order
        public static List<ReturnReceiptProductList> GetReceiptProductList(int rid)
        {
            List<ReturnReceiptProductList> productList = new List<ReturnReceiptProductList>();
            ReturnReceiptProductList returnReceipt = new ReturnReceiptProductList();
            using (SqlConnection connection = ConnectionString.GetSqlConnection())
            {
                string sqlList = "SELECT * FROM OrdersList WHERE ReceiptId = @rid";
                using (SqlCommand cmdList = new SqlCommand(sqlList, connection))
                {
                    cmdList.Parameters.AddWithValue("@rid", rid);
                    connection.Open();
                    using (SqlDataReader reader = cmdList.ExecuteReader())
                    {
                        if (reader != null)
                        {
                            while (reader.Read())
                            {
                                Product product = new Product();
                                Receipt receipt = new Receipt();
                                receipt.ReceiptID = Convert.ToInt32(reader["ReceiptId"].ToString());
                                receipt.UserId = Convert.ToInt32(reader["UserId"].ToString());
                                //product.ProductId = Convert.ToInt32(reader["ProductId"].ToString());
                                product.ProductName = reader["ProductName"].ToString();
                                product.ProductQuantity = Convert.ToInt32(reader["ProductQuantity"].ToString());
                                product.ProductPrice = Convert.ToDecimal(reader["ProductPrice"].ToString());
                                product.ProductTax = Convert.ToDecimal(reader["ProductTax"].ToString());
                                returnReceipt = new ReturnReceiptProductList(product, receipt);
                                productList.Add(returnReceipt);
                            }
                        }
                    }
                }
            }
            return productList;


What I have tried:

inserting data but to link another table i need the id inserted in the same window and i cannot read from table because i need that specific id inserted...
Posted
Updated 8-Dec-18 17:24pm

1 solution

Taken that the id is created using an identity definition[^] there are three system functions that return lastly inserted identity value:
- @@IDENTITY (Transact-SQL) | Microsoft Docs[^]
- IDENT_CURRENT (Transact-SQL) | Microsoft Docs[^]
- SCOPE_IDENTITY (Transact-SQL) | Microsoft Docs[^]

They all serve a different purpose but in your specific case @@IDENTITY should give you the correct value if you execute it right after the insert statement.

A simple way to fetch the value is to execute a query like the following. It returns a single row containing the last added identity value
SQL
SELECT @@IDENTITY
 
Share this answer
 
Comments
TheBigBearNow 9-Dec-18 0:15am    
I am going to try that right now I have seen @@identity before but never understood it
TheBigBearNow 9-Dec-18 0:27am    
Does this look like im using it correctly?

        public static int CreateReceipt(int userid, decimal rtotal, DateTime rdate)
        {
            int result = -1;
            using(SqlConnection connection = ConnectionString.GetSqlConnection())
            {
                string sqlCreate = "INSERT INTO Orders(UserId, ReceiptDate, ReceiptTotal) " +
                                               "VALUES(@uid, @date, @rtotal)";
                using(SqlCommand cmdCreate = new SqlCommand(sqlCreate, connection))
                {
                    //cmdCreate.Parameters.AddWithValue("@rid", receipt.ReceiptID);
                    cmdCreate.Parameters.AddWithValue("@uid", userid);
                    cmdCreate.Parameters.AddWithValue("@date", rdate);
                    cmdCreate.Parameters.AddWithValue("rtotal", rtotal);
                    try
                    {
                        connection.Open();
                        string sqlSELECT = "SELECT @@IDENTITY FROM Orders";
                        cmdCreate.ExecuteNonQuery();
                        SqlCommand cmdSelect = new SqlCommand(sqlSELECT, connection);
                        result = Convert.ToInt32(cmdSelect.ExecuteScalar());

                    }
                    catch(Exception ex) { throw ex; }
                }                
            }
            return result;
        }
Wendelius 9-Dec-18 0:50am    
Basically looks good but I think there's a small misunderstanding. The @@IDENTITY does not return an inserted value on a row basis, it it simply returns a single value. So when querying like this
SELECT @@IDENTITY FROM Orders
You would get as many rows as the Orders table has and the same value on each row.

So the following should suffice (and use less resources)
string sqlSELECT = "SELECT @@IDENTITY";SqlCommand cmdSelect = new SqlCommand(sqlSELECT, connection);result = Convert.ToInt32(cmdSelect.ExecuteScalar());

As a side note, don't forget to use using statements also for the second command.
TheBigBearNow 9-Dec-18 3:24am    
Thank you! i got it working exactly how i wanted with what you said.
Wendelius 9-Dec-18 4:08am    
Glad to be of service.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900