Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert via stored proc. and incomplete data recorded Expand / Collapse
Author
Message
Posted Saturday, June 23, 2012 3:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 1, 2013 8:13 PM
Points: 15, Visits: 55
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!
Post #1320297
Posted Monday, June 25, 2012 8:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1320654
Posted Monday, June 25, 2012 8:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 1, 2013 8:13 PM
Points: 15, Visits: 55
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!
Post #1320670
Posted Monday, June 25, 2012 8:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1320679
Posted Monday, June 25, 2012 9:27 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 20,863, Visits: 32,901
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1320694
Posted Tuesday, June 26, 2012 1:12 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, May 2, 2014 4:11 PM
Points: 645, Visits: 377
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!
Post #1321494
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse