SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert via stored proc. and incomplete data recorded


Insert via stored proc. and incomplete data recorded

Author
Message
SirWittles
SirWittles
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 116
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!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61965 Visits: 17954
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.

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)
SirWittles
SirWittles
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 116
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!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61965 Visits: 17954
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.

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)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93889 Visits: 38955
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.

Cool
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)
Narud
Narud
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1769 Visits: 507
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search