none
Last record saved only RRS feed

  • Question

  • I am raelly stuck with a problem that i save multiple records into the database, but querying, i can only see the last record there.

    sometimes it saves all the records but most of the times it only saves last record. really stuck...

     

    Any help.........its urgent please.........

     

     

    Tuesday, June 8, 2010 6:50 AM

All replies

  • Can you please give more details?

    When you are adding records to dataset please make sure to call accept change before update.


    Regards
    Nayan Paregi (MCTS)
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, June 8, 2010 7:42 AM
  • i am not adding the records to the dataset. i have created a table and added the records to the table

     

    then i called the command object and loop through each record and save it one by one.

    i am not using databindings.

     

    but it saves only last record and sometimes even not the last one.... if you wish i can send you my complete code, in zip form...

     

    Please help...........its urgent...

    Tuesday, June 8, 2010 8:24 AM
  • Ok, Can you please just send me code of loop which you are using to insert records.
    Regards
    Nayan Paregi (MCTS)
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, June 8, 2010 8:29 AM
  • Public Function InsertProformaInvoice(ByVal oTableName As DataTable) As Boolean
            Dim P_COMMAND As SqlClient.SqlCommand
            Dim Trans As SqlClient.SqlTransaction
            Trans = DB_CONNECTION.BeginTransaction

            Try
                For Each mRow In oTableName.Rows
                    'SQL_COMMAND = New SqlClient.SqlCommand
                    P_COMMAND = New SqlClient.SqlCommand
                    With P_COMMAND
                        .CommandText = "Usp_InsertProformaInvoice"
                        .CommandType = CommandType.StoredProcedure
                        .Connection = DB_CONNECTION
                        .Transaction = Trans
                        .Parameters.Add("Return", SqlDbType.SmallInt, 4).Value = Nothing
                        .Parameters("Return").Direction = ParameterDirection.ReturnValue
                        .Parameters.Add("@Order_No", SqlDbType.VarChar, 50).Value = mRow.Item(0)
                        .Parameters.Add("@Order_Date", SqlDbType.DateTime, 8).Value = mRow.Item(1)
                        .Parameters.Add("@Article_No", SqlDbType.VarChar, 50).Value = mRow.Item(2)
                        .Parameters.Add("@Quantity", SqlDbType.Decimal, 8).Value = mRow.Item(3)
                        .Parameters.Add("@Rate", SqlDbType.Money, 8).Value = mRow.Item(4)
                        .Parameters.Add("@Unit", SqlDbType.SmallInt, 4).Value = mRow.Item(5)
                        .Parameters.Add("@Party_Code", SqlDbType.VarChar, 50).Value = mRow.Item(6)
                        .Parameters.Add("@Invoice_No", SqlDbType.VarChar, 50).Value = mRow.Item(7)
                        .Parameters.Add("@Invoice_Date", SqlDbType.DateTime, 8).Value = mRow.Item(8)
                        .Parameters.Add("@Ship_Via", SqlDbType.TinyInt, 2).Value = mRow.Item(9)
                        .Parameters.Add("@Terms", SqlDbType.VarChar, 100).Value = mRow.Item(10)
                        .Parameters.Add("@Terms_Of_Payment", SqlDbType.VarChar, 30).Value = mRow.Item(11)
                        .Parameters.Add("@Delivery_Time", SqlDbType.VarChar, 100).Value = mRow.Item(12)
                        .Parameters.Add("@Currency_Code", SqlDbType.SmallInt, 4).Value = mRow.Item(13)
                        .Parameters.Add("@Invoice_To", SqlDbType.VarChar, 100).Value = mRow.Item(14)
                        .Parameters.Add("@Shipment_Type", SqlDbType.VarChar, 50).Value = mRow.Item(15)
                        .Parameters.Add("@ErrMsg", SqlDbType.VarChar, 200).Value = Nothing
                        .Parameters("@ErrMsg").Direction = ParameterDirection.Output
                        .ExecuteNonQuery()

                    End With
                    P_COMMAND.Dispose()
                Next

                If P_COMMAND.Parameters("Return").Value = 200 Then
                    Trans.Rollback()
                    Message = SQL_COMMAND.Parameters("@ErrMsg").Value
                    InsertProformaInvoice = False
                    '     Trans.Dispose()
                Else
                    '    Trans.Commit()
                    '   Trans.Dispose()
                    Message = "Successfully Inserted Proforma Invoice!"
                    InsertProformaInvoice = True
                End If
            Catch ex As Exception
                Trans.Rollback()
                'TRans.Dispose()
                Message = Err.Number & vbCrLf & ex.Message
            Finally
                P_COMMAND.Dispose()
                'TRans.Dispose()

            End Try
        End Function
    Tuesday, June 8, 2010 8:39 AM
  • I think below code is creating problem for you.

    If you see below code I think it is not right place. As you are just checking return value for the last Record if last record fail it will complete rollback transaction, But if some of records failed and last one success then it will commit it. You need to checck this before

    P_COMMAND.Dispose() in your loop. And also if one fail then you need to exit from for loop.

     

     If P_COMMAND.Parameters("Return").Value = 200 Then
            Trans.Rollback()
            Message = SQL_COMMAND.Parameters("@ErrMsg").Value
            InsertProformaInvoice = False
            '   Trans.Dispose()
          Else
            '  Trans.Commit()
            '  Trans.Dispose()
            Message = "Successfully Inserted Proforma Invoice!"
            InsertProformaInvoice = True
          End If
    

    Regards
    Nayan Paregi (MCTS)
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, June 8, 2010 8:49 AM
  • but it says "Successfully Inserted the records", but when i query the database, there are no records there.....

    secondly i have another method that doesn't use this looping but shows similar behavior.

     

    Plz Help!

     

    Tuesday, June 8, 2010 9:15 AM
  • You need to uncomment below code

     '  Trans.Commit()

    Like below

     If P_COMMAND.Parameters("Return").Value = 200 Then
        Trans.Rollback()
        Message = SQL_COMMAND.Parameters("@ErrMsg").Value
        InsertProformaInvoice = False
        '  Trans.Dispose()
       Else
         Trans.Commit()    
        Message = "Successfully Inserted Proforma Invoice!"
        InsertProformaInvoice = True
       End If
    
    

    Regards
    Nayan Paregi (MCTS)
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, June 8, 2010 9:48 AM
  • It was because Transaction is never committed.  :)
    Regards
    Nayan Paregi (MCTS)
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, June 8, 2010 9:50 AM
  • So finally there are two problem in your code

    1. Transaction is never committed. You need to commit transaction in else condtion (uncomment '    Trans.Commit())

    2. You are just checking error code for last row so if any row in between fail still transaction will be committed)

     


    Regards
    Nayan Paregi (MCTS)
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Nayan Paregi Tuesday, June 8, 2010 10:12 AM
    • Marked as answer by Alex LiangModerator Monday, June 14, 2010 10:28 AM
    • Unmarked as answer by NET HAWK Monday, June 14, 2010 12:14 PM
    • Unproposed as answer by NET HAWK Monday, June 14, 2010 12:15 PM
    Tuesday, June 8, 2010 10:00 AM
  • i have traced out the problem.... please give some solution for the below mentioned problem

     

    the records are saved and in the next statement i use a dataset to retrieve the recently saved records to show in a report.

    that procedure of calling a method to retrieve the data and display gives error. and the records are rolled back.

    if i dispose my class object and recreate it for the procedure to access the records, it works fine. but if i dont dispose my class and try to access the records it gives error and i see that the database table is empty.

    i tried it without calling that procedure for retrieving the data, and it is working better. (Under testing this thing) .

    so please guide me now...

    Tuesday, June 8, 2010 10:29 AM
  • Make sure Insert & Get method both are in same transaction if it is different then it might be some locking issue.


    Regards
    Nayan Paregi (MCTS)
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, June 8, 2010 10:43 AM
  • from same transaction if you mean that in between the same BeginTran and committran then yes they are not in the same. so how to tackle it Insertion procedure is different from the Retrieval procedure. how to work around this problem. Please guide...
    Tuesday, June 8, 2010 10:53 AM
  • I massaged your code a bit to look cleaner and for better performance

     

     Public Function InsertProformaInvoice(ByVal oTableName As DataTable) As Boolean
    
        Dim P_COMMAND As SqlClient.SqlCommand
        Dim Trans As SqlClient.SqlTransaction
        Trans = DB_CONNECTION.BeginTransaction
    
        P_COMMAND = New SqlClient.SqlCommand
        P_COMMAND.CommandText = "Usp_InsertProformaInvoice"
        P_COMMAND.CommandType = CommandType.StoredProcedure
        P_COMMAND.Connection = DB_CONNECTION
        P_COMMAND.Transaction = Trans
    
        Try
          Dim success As Boolean = True
          For Each mRow As DataRow In oTableName.Rows
            'SQL_COMMAND = New SqlClient.SqlCommand
    
            With P_COMMAND
              .Parameters.Clear()
              .Parameters.Add("Return", SqlDbType.SmallInt, 4).Value = Nothing
              .Parameters("Return").Direction = ParameterDirection.ReturnValue
              .Parameters.Add("@Order_No", SqlDbType.VarChar, 50).Value = mRow.Item(0)
              .Parameters.Add("@Order_Date", SqlDbType.DateTime, 8).Value = mRow.Item(1)
              .Parameters.Add("@Article_No", SqlDbType.VarChar, 50).Value = mRow.Item(2)
              .Parameters.Add("@Quantity", SqlDbType.Decimal, 8).Value = mRow.Item(3)
              .Parameters.Add("@Rate", SqlDbType.Money, 8).Value = mRow.Item(4)
              .Parameters.Add("@Unit", SqlDbType.SmallInt, 4).Value = mRow.Item(5)
              .Parameters.Add("@Party_Code", SqlDbType.VarChar, 50).Value = mRow.Item(6)
              .Parameters.Add("@Invoice_No", SqlDbType.VarChar, 50).Value = mRow.Item(7)
              .Parameters.Add("@Invoice_Date", SqlDbType.DateTime, 8).Value = mRow.Item(8)
              .Parameters.Add("@Ship_Via", SqlDbType.TinyInt, 2).Value = mRow.Item(9)
              .Parameters.Add("@Terms", SqlDbType.VarChar, 100).Value = mRow.Item(10)
              .Parameters.Add("@Terms_Of_Payment", SqlDbType.VarChar, 30).Value = mRow.Item(11)
              .Parameters.Add("@Delivery_Time", SqlDbType.VarChar, 100).Value = mRow.Item(12)
              .Parameters.Add("@Currency_Code", SqlDbType.SmallInt, 4).Value = mRow.Item(13)
              .Parameters.Add("@Invoice_To", SqlDbType.VarChar, 100).Value = mRow.Item(14)
              .Parameters.Add("@Shipment_Type", SqlDbType.VarChar, 50).Value = mRow.Item(15)
              .Parameters.Add("@ErrMsg", SqlDbType.VarChar, 200).Value = Nothing
              .Parameters("@ErrMsg").Direction = ParameterDirection.Output
              .ExecuteNonQuery()
    
              If P_COMMAND.Parameters("Return").Value = 200 Then
                success = False
                Exit For
              End If
            End With
    
          Next
    
    
          If success Then
            Trans.Commit()
            message = "Successfully Inserted Proforma Invoice!"
            InsertProformaInvoice = True ' or Return True
          Else
    
            Trans.Rollback()
            message = P_COMMAND.Parameters("@ErrMsg").Value
            InsertProformaInvoice = False ' or Return false
          End If
        
        Catch ex As Exception
          Trans.Rollback()
          'TRans.Dispose()
          message = Err.Number & vbCrLf & ex.Message
          InsertProformaInvoice = False ' or Return false
        Finally
          P_COMMAND.Dispose()
          'TRans.Dispose()
    
        End Try
      End Function
    Tuesday, June 8, 2010 6:29 PM
  • Basically it is another call

    If InsertProformaInvoice(..) then

         RetrieveMyDataSet(..)

    Else

         'And error occured in Insert SP....

    End If

    Tuesday, June 8, 2010 6:34 PM
  • Best practice is you open the connection when you need it and you close it when you are done. In the above code, you have the connection open all the time after calling the decrypt method.

    Also, check the stored proc, doesn't it contain try...catch? You maybe getting exceptions .net is not capturing it because it was handled in the stored proc

    Wednesday, June 9, 2010 4:23 AM
  • wouldn't it burden the system each time we open a connection and close it in each function? what is the best way of doing this? as i am switching from VB6, and there we didn't need to close the connection for each function.

     

    in the stored procedure i have used

    Begin Tran

    Commit Tran

    and RollBack Tran on Error

    returning @ErrMsg in each case.

     

    please guide me what will be the best practice for it.?

    Wednesday, June 9, 2010 4:57 AM
  • is the all time open connection creating actual problem??
    Wednesday, June 9, 2010 5:00 AM
  • It is expensive but with connection pool it is less expensive to create a connection. For more info about connection pool, visit

    http://aspalliance.com/1099_Understanding_Connection_Pooling_in_NET

    Leaving the connection open for too long may dispose the connection and you application may throw an execption.

    Someone answer almost the same question here as well

    http://www.daniweb.com/forums/thread91483.html

    Wednesday, June 9, 2010 5:46 AM