June 23, 2012 at 3:23 pm
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!
June 25, 2012 at 8:24 am
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/
June 25, 2012 at 8:47 am
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!
June 25, 2012 at 8:54 am
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/
June 25, 2012 at 9:27 am
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.
June 26, 2012 at 1:12 pm
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