northwind sample database insert data in 2 table update 3rd table RRS feed

  • Question

  • hi,

    i want to insert data in order, orderDetail tables and update stock in product. i want to use vb code instead of PROCEDURE.

    I need code.

    Thank You.

    MUHAMMAD ANZAR E-mail : Mobile # :0092-3215096959

    Monday, December 16, 2019 8:45 AM

All replies

  • IMHO, you should learn how to use ADO.NET Entity Framework. 

    The tutorial link looks like it doesn't work but it does, just start scrolling down the page.

    VS2017 and 2019 use EF 6 and what the tutorial does it works EF 6 too, the code.


    Monday, December 16, 2019 9:34 AM
  • As mentioned, Entity Framework is a good choice but since you are well into your project that is not a good choice but your next project Entity Framework should be considered.

    I'm not supplying code for this question but will provided pointers.

    1. Wrap the entire process in a transaction within a try-catch.
    2. Insert a new order, use cmd.CommandText = "Select @@Identity" to get the new key via CInt(cmd.ExecuteScalar) which you assign to a variable.
    3. Create a order detail record for each product and use the new key from step 2.
    4. To update stock, see code block below
    5. After the last insert to a commit on the transaction
    6. If there were errors, in the catch of the try do a Rollback on the transaction


    Using cn As New SqlConnection With {.ConnectionString = _connectionString}
    Dim trans As SqlTransaction = cn.BeginTransaction("CreateOrder")
    Using cmd As New SqlCommand With {.Connection = cn, .CommandText = sql, .Transaction = trans}

    Sample update product

    Public Function CanOrder(ByVal id As Integer, ByVal Adding As Boolean) As Boolean
    	Dim result = False
    	Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
    		Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
    			cmd.CommandText = $"SELECT Quantity FROM  Products WHERE id = {id}"
    			Dim quantity = CInt(Fix(cmd.ExecuteScalar()))
    			result = quantity > 0
    			If Adding Then
    				quantity -= 1
    				quantity += 1
    			End If
    			' * We could forego this check which would give a negative Quantity which then
    			' * a query could run that has Quantity less than zero which would be how many
    			' * are needed for backorders
    			If quantity > 0 Then
    				cmd.CommandText = "UPDATE [dbo].[Products] SET [Quantity] = @Quantity WHERE id = @Id"
    				cmd.Parameters.AddWithValue("@Quantity", quantity)
    				cmd.Parameters.AddWithValue("@id", id)
    			End If
    		End Using
    	End Using
    	Return result
    End Function

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    profile for Karen Payne on Stack Exchange

    Monday, December 16, 2019 1:10 PM