Making a single Transaction for multiple items in SQL SERVER 2012

Question

Help me for my POS. I'm doing a Transaction for fast-food. I'm really confuse on building codes for the transaction. I want to do it like this

ID ! Transaction ID ! Product Name !

ID ! TRANSACTIONID ! PRODUCT
1     TR1             DISH1
2     TR1             DISH2
3     TR2             DISH3
4     TR3             DISH4
5     TR3             DISH5
6     TR3             DISH2

ITS MORE LIKE THAT. Sorry if my code has no clarity please understand me. This is my first time asking. Thanks

The below code is my pattern for inserting it into database but transaction ID won't be like on top.

 Private Sub TransactionUpdate()
    Dim pn, pp, pq, pt As String
    If ListView1.Items.Count = Nothing Then Exit Sub
    PanelOrder()
    For Each item As ListViewItem In ListView1.Items
        pn = item.SubItems(0).Text
        pp = item.SubItems(1).Text
        pq = item.SubItems(2).Text
        pt = item.SubItems(3).Text
        SQL.AddParam("@transactionstate", "Served")
        SQL.AddParam("@productname", pn)
        SQL.AddParam("@employeeid", txtusername.Text.ToUpper)
        SQL.AddParam("@employeename", btnlogin.Text)
        SQL.AddParam("@productprice", pp)
        SQL.AddParam("@productquantity", pq)
        SQL.AddParam("@producttotal", pt)


        SQL.ExecQuery("Insert Into Emp_Transaction(ProductName,EmployeeName,TotalPrice,Transaction_Date,Transaction_Time,ProductQuantity,TransactionState) " &
                    "Values(@productname,@employeename,@producttotal,GETDATE(),GETDATE(),@productquantity,@transactionstate)")
        'TransactionID,ProductName,EmployeeName,TotalPrice,Transaction_Date,Transaction_Time,ProductQuantity,TransactionState
        SQL.ExecQuery("Update Emp_Transaction " &
                        "Set ProductID=(SELECT Product.ProductID from Product Where Product.ProductName=Emp_Transaction.ProductName), EmployeeID=(SELECT Employees.EmployeeID from Employees Where Employees.Name=Emp_Transaction.EmployeeName)")
    Next
    If SQL.HasException(True) Then Exit Sub



End Sub

Answer 1

Personally, I would write a stored procedure to do the insert, and then I would call the stored procedure with the same parameters.

The procedure would look like this

CREATE PROCEDURE dbo.AddTransaction @productname      VARCHAR(100)
                                   ,@employeename     VARCHAR(100)
                                   ,@producttotal     INTEGER
                                   ,@productquantity  INTEGER
                                   ,@transactionstate VARCHAR(100)
AS
BEGIN

  DECLARE @ProductId INT, @EmployeeId INT;

  SELECT @ProductId = productID FROM dbo.Product WHERE ProductName = @productname;

  SELECT @EmployeeId = SELECT EmployeeID FROM dbo.Employees WHERE name = @EmployeeName;

  INSERT INTO dbo.Emp_Transaction(ProductName,EmployeeName,TotalPrice,Transaction_Date,Transaction_Time,ProductQuantity,TransactionState
                                 ,ProductId, EmployeeId)
    VALUES(@productname,@employeename,@producttotal,GETDATE(),GETDATE(),@productquantity,@transactionstate
          ,@ProductId, @EmployeeID );

END;

Of course, you might have to change the datatypes a bit.

Having said that, there seems to be at least one problem with your Emp_Transaction table in that it is not "normalised".

Having both ProductId and ProductName in the table is not normalised because one is directly derived from the other one. You should probably remove ProductName

The same goes for EmployeeId and EmployeeName

Another detail is that it is better practice to always specify the schema name of your table, like dbo.Emp_Transaction instead of Emp_Transaction.

Answer 2

You should try the following approach.Try first running your insert statement in the database directly.This will help you to understand if your query is correct or not.Once you do that then you should put the query into your code and then debug the code.