Insert via stored proc. and incomplete data recorded

  • Hello,

    I was hoping someone could point me in the right direction. I have a program written in VB.NET that inserts data into our SQL Server via a stored procedure. Every now and then one specific row does not insert correctly: Either nothing was inserted, or only a portion of the data was recoreded. Here are the facts:

    1.) The six digit integer I am inserting is validated application side.

    2.) There is no way for the program to function if a valid six digit integer is not obtained and validated prior to insertion.

    3.) After the insertion this one row is the only one prone to invalid data issues, but it is very random (Only 22 errors with over 500 inserts).

    4.) 6 other rows are inserted with the stored procedure and are not missing any data.

    I don't understand how this could be happening. Please help. THANKS!

  • You have not provided anywhere near enough information for anybody to be able to help.

    Try this link. http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hello, thanks for the reply. Let me clarify. Here is the code application side that inserts the data:

    Private Sub LogPackage(ByVal RecordId As Integer)

    'Loop through the records in the log and add to database

    For i As Integer = 0 To (dgLog.Rows.Count - 1)

    Dim strValues(4) As String

    'Id

    strValues(0) = RecordId

    'RxNumber (6 digit number)

    strValues(1) = dgLog.Rows(i).Cells(3).Value.ToString

    'ProductName

    strValues(2) = dgLog.Rows(i).Cells(6).Value.ToString

    'Quantity

    strValues(3) = dgLog.Rows(i).Cells(4).Value.ToString

    'Charge

    strValues(4) = dgLog.Rows(i).Cells(5).Value.ToString

    Dim dsData As New DataSet

    Dim objConnection As New SqlConnection

    Dim objCommand As New SqlCommand

    Dim objAdapter As New SqlDataAdapter

    Try

    objConnection.ConnectionString = "Server=Server;Database=ShippingLog;User Id=UserId;Password=UsersPassword"

    objConnection.Open()

    objCommand.CommandType = CommandType.StoredProcedure

    objCommand.CommandText = "InsertShipmentDetails"

    objCommand.CommandTimeout = 0

    objCommand.Parameters.AddWithValue("@MailLogId", CInt(strValues(0)))

    objCommand.Parameters.AddWithValue("@RxNumber", strValues(1))

    objCommand.Parameters.AddWithValue("@ProductName", strValues(2))

    objCommand.Parameters.AddWithValue("@Quantity", strValues(3))

    objCommand.Parameters.AddWithValue("@Charge", strValues(4))

    objCommand.Parameters.AddWithValue("@spstat", 1)

    objCommand.Parameters.AddWithValue("@errmsg", "")

    objCommand.Parameters.AddWithValue("@recn", 0)

    objCommand.Connection = objConnection

    objCommand.ExecuteNonQuery()

    objConnection.Close()

    objCommand.Dispose()

    Catch ex As Exception

    MsgBox(ex.ToString)

    End Try

    Next

    Here is the stored procedure:

    USE [ShippingLog]

    GO

    /****** Object: StoredProcedure [dbo].[InsertShipmentDetails] Script Date: 6/25/2012 7:42:05 AM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[InsertShipmentDetails]

    @MailLogId int,

    @RxNumber nvarchar(6),

    @ProductName nvarchar(255),

    @Quantity nvarchar(255),

    @Charge money = null,

    @spstat int OUTPUT,

    @errmsg varchar(200) OUTPUT,

    @recn int OUTPUT

    AS

    DECLARE @numrecs int

    SET NOCOUNT ON

    SET @spstat = 1 -- go ahead and set to ok

    SET @errmsg = '' -- set default error message to nothing

    SET @recn = 0 -- set default record number to zero

    BEGIN TRY

    BEGIN TRANSACTION

    INSERT INTO ShipmentDetails (MailLogId, RxNumber, ProductName, Quantity, Charge)

    VALUES (@MailLogId, @RxNumber, @ProductName, @Quantity, @Charge) --notice they are the input parameters above

    SET @numrecs = @@rowcount

    if @numrecs=0

    BEGIN

    SET @spstat = -1

    SET @errmsg = 'No record added'

    SET @recn = 0

    END

    COMMIT TRANSACTION

    RETURN @spstat

    END TRY

    BEGIN CATCH

    DECLARE @ErrorNo int,

    @Severity int,

    @State int,

    @LineNo int,

    @errmessage varchar(1000)

    SELECT @ErrorNo = ERROR_NUMBER(),

    @Severity = ERROR_SEVERITY(),

    @State = ERROR_STATE(),

    @LineNo = ERROR_LINE(),

    @errmessage = ERROR_MESSAGE()

    ROLLBACK TRAN

    SET @errmsg = CONVERT(varchar(200), @errmessage)

    SET @spstat = 0 --error occured

    END CATCH

    My issue is that the cell RxNumber for the inserted row sometimes does not record the whole value of what I requested be inserted. I pass in "123456" and sometimes (22 errors out of 500 row inserts) nothing is recorded for this column only, or only a partial is recorded like "1", "12", "1234", "12345", etc...

    I don't understand how that could happen and need help correcting this random issue. Thanks!

  • OK this is still nowhere near enough info but thanks for the proc code.

    How about ddl for the table you are trying to insert to?

    What are the parameters for the rows that failed?

    Also can you provide some details about what did or did not insert for these parameters?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I can't see anything in the code of the stored procedure that would truncate the data for the RxNumber. I would start by debugging the VB.NET code to ensure what is being sent to the stored procedure, and probably even put some logging in the stored procedure to write the data to another table as well for auditing/debugging.

    In a test enveironment see if the application has the same problem with same data.

  • In your VB code you can comment out the time out setting, to use the server default setting:

    ' objCommand.CommandTimeout = 0

    In your sp code, put BEGIN TRANSACTION before BEGIN TRY, also is a good practice name transactions.

    BEGIN TRANSACTION trShipmentDetails

    BEGIN TRY

    INSERT INTO ShipmentDetails (MailLogId, RxNumber, ProductName, Quantity, Charge)

    VALUES (@MailLogId, @RxNumber, @ProductName, @Quantity, @Charge) --notice they are the input parameters above

    SET @numrecs = @@rowcount

    if @numrecs=0

    BEGIN

    SET @spstat = -1

    SET @errmsg = 'No record added'

    -- SET @recn = 0 not necessary because you have previously assign this value

    END

    ELSE

    SET @recn = @numrecs

    COMMIT TRANSACTION trShipmentDetails

    END TRY

    BEGIN CATCH

    DECLARE @ErrorNo int,

    @Severity int,

    @State int,

    @LineNo int,

    @errmessage varchar(1000)

    SELECT @ErrorNo = ERROR_NUMBER(),

    @Severity = ERROR_SEVERITY(),

    @State = ERROR_STATE(),

    @LineNo = ERROR_LINE(),

    @errmessage = ERROR_MESSAGE()

    ROLLBACK TRAN trShipmentDetails

    SET @errmsg = CONVERT(varchar(200), @errmessage)

    SET @spstat = 0 --error occured

    SET @recn = 0

    END CATCH

    RETURN @spstat

    You're using and output parameter in your sp, but you're not getting it in your VB code, perhaps this could help you to find where is the failure:

    Dim recno As Integer

    objCommand.ExecuteNonQuery()

    Integer.TryParse(command.Parameters(command.Parameters.Count - 1).Value.ToString(), recno)

    If recno = 0 Then...

    I hope this will be useful 4 you 🙂

    Best regards!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply