none
Make transactiones between tables RRS feed

  • Question

  • hi
    am making an inventory application with vb.net 2008 and  SQL SERVER 2008 and i have this table of ITEMS and Orders , what i want is when i order a certain quantity , it will automatically be subtracted from the quantity of the item in the items table , ho is that done ?
    and how to allow null for certain columns  ?
    • Moved by VMazurModerator Thursday, December 10, 2009 11:19 AM (From:ADO.NET DataSet)
    Tuesday, November 10, 2009 9:49 PM

Answers

  • Hi NaYaR,

     

    There are two types of transaction that are related to your question.

    1) Transaction in .NET code.

    For example,

                StudentEntities context = new StudentEntities();

                DbTransaction dbTran = null;

                try

               {

                    context.Connection.Open();

                    dbTran = context.Connection.BeginTransaction();

                    //The manipulations to DB

                    dbTran.Commit();

                }

                catch (Exception ex)

                {

                    dbTran.Rollback();

                }

                finally

                {

                    context.Connection.Close();

                }

    If one of the manipulation in the scope of transaction, it will rollback all the operations that are done before.

    You can handle the exception by code by this method.

     

    2) Transaction in DB.

    That means you write a transaction in DB and call it in code. You can refer to this document about writing the transaction,

    http://msdn.microsoft.com/en-us/library/ms188929.aspx

    http://www.codeproject.com/KB/database/sqlservertransactions.aspx
    You’ll have to handle the exception or error in the DB script.

     

    For your problem, you can first choose the type of transaction that meets your requirements.

    Then you can write the T-SQL command for it.

    If you have any problem in writing T-SQL command, you can post in Transact-SQL forum for better answer,

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads

    Since this forum is for dataset, if you have any questions or concerns about dataset, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun_Feng Tuesday, November 17, 2009 8:03 AM
    Monday, November 16, 2009 8:53 AM

All replies

  • hi
    am making an inventory application with vb.net 2008 and  SQL SERVER 2008 and i have this table of ITEMS and Orders , what i want is when i order a certain quantity , it will automatically be subtracted from the quantity of the item in the items table , ho is that done ?
    and how to allow null for certain columns  ?
    • Merged by Jeff Shan Monday, November 16, 2009 9:47 AM Merge it to keep them in the same topic
    Tuesday, November 10, 2009 9:50 PM
  • when you creat a table allow it as null, meaning that you dont need to write data in it. for you first question youu need to do a query first. First of asll you need to creat a connection to your database.
    Dim Quantity as Intinger=0
     Using CnEt As New SqlConnection(your connection String)
                Dim command As New SqlCommand("Select * From Table_Item Where Item_DI =@ITEMID", CnEt)
                CnEt.Open()
                command.Parameters.AddWithValue"@ITEMID",Me.TextBox3.Tex)
                Dim RReader As SqlDataReader=command.ExecuteReader()
    
    
                With RReader
                    If .HasRows Then
                        .Read()
                        Dim CounteMe As Integer = .FieldCount
                        Quantity=Item("QuantityINstock").ToString
                        Quantity=Quantity-me.textbox.text  
    RReader.Close
    CnEt.Close
                           
                       End If
                   End With
             End Using
    
    here you can write the new code for you to save the new quantity value.
    
    open your table



    Don't judge me, just Upgrade me. Thanks!
    Tuesday, November 10, 2009 10:43 PM
  • thanks a lot for your reply, but am a beginner could you tell me where to write the code exactly and explain it to me
    the two tables  i have are : ITEMS with ITEM ID and QuantityINstock and Orders ITem ID and OrderedQuantity
    i cant see where you stated itemID(@items)=itemID(@orders)
    thanks again
    Wednesday, November 11, 2009 2:26 AM
  • if you beginner, how come you are doing something like this? You have to read book that deal with VB.Net, even if I expalin you, you will not grasp anything att all.

    Let me ask you mosethign. do you have the connection?

    if so post here and may be I acn help you.
    Don't judge me, just Upgrade me. Thanks!
    Thursday, November 12, 2009 9:35 PM
  • well i designed the sql database and then i added it to project at the visual studio so i guess the connection is made but i couldnt find where
    and i read a book about vb.net
    so if explained i ll get it
    thanks again
    and if its ok with you add me to your messenger nayar_2010@hotmail.com so we could speak online
    Friday, November 13, 2009 2:05 AM
  • Hi NayaR,

    Could you give more description about your problem?
    How is that related to transactiones?
    Would you mind providing the table schemas of the releated tables?

    Best Regards
    Yichun Feng
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, November 13, 2009 9:05 AM
  • well the the two tables are :
    items with columns : item id - item name - company - description - quantity - min quantity - price
    orders with columns : order id - item id - quantity

    i want two things :
    first when i place an order i want the quantity placed in orders to be subtracted from the one in items based upon the item id
    second i want to show up a msg whenever the quantity in items is less than the min quantity


    i thought this is made by transactions

    thanks
    Saturday, November 14, 2009 2:39 AM
  • Hi NaYaR,

     

    There are two types of transaction that are related to your question.

    1) Transaction in .NET code.

    For example,

                StudentEntities context = new StudentEntities();

                DbTransaction dbTran = null;

                try

               {

                    context.Connection.Open();

                    dbTran = context.Connection.BeginTransaction();

                    //The manipulations to DB

                    dbTran.Commit();

                }

                catch (Exception ex)

                {

                    dbTran.Rollback();

                }

                finally

                {

                    context.Connection.Close();

                }

    If one of the manipulation in the scope of transaction, it will rollback all the operations that are done before.

    You can handle the exception by code by this method.

     

    2) Transaction in DB.

    That means you write a transaction in DB and call it in code. You can refer to this document about writing the transaction,

    http://msdn.microsoft.com/en-us/library/ms188929.aspx

    http://www.codeproject.com/KB/database/sqlservertransactions.aspx
    You’ll have to handle the exception or error in the DB script.

     

    For your problem, you can first choose the type of transaction that meets your requirements.

    Then you can write the T-SQL command for it.

    If you have any problem in writing T-SQL command, you can post in Transact-SQL forum for better answer,

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads

    Since this forum is for dataset, if you have any questions or concerns about dataset, please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun_Feng Tuesday, November 17, 2009 8:03 AM
    Monday, November 16, 2009 8:53 AM
  • hi Malange
    i finally got the code you wrote but am not using any text boxes in the form, am using a datagrid to display the orderdetails so how would that be ?
    thanks best regards

    hi Yichun Feng
    could YOU SHOW ME A A SAMPLE CODE SOLUTION FOR MY PROBLEM PLEASE
    THANKS
    Wednesday, December 9, 2009 2:10 PM