InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 00:40:27 GMT20http://www.sqlservercentral.com/Forums/Topic1320297-324-1.aspxIn your VB code you can comment out the time out setting, to use the server default setting:[code="vb"]' objCommand.CommandTimeout = 0[/code]In your sp code, put BEGIN TRANSACTION before BEGIN TRY, also is a good practice name transactions.[code="sql"]BEGIN TRANSACTION trShipmentDetailsBEGIN 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 trShipmentDetailsEND TRYBEGIN 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 = 0END CATCHRETURN @spstat[/code]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:[code="vb"] Dim recno As Integer objCommand.ExecuteNonQuery() Integer.TryParse(command.Parameters(command.Parameters.Count - 1).Value.ToString(), recno) If recno = 0 Then... [/code]I hope this will be useful 4 you :-)Best regards!Tue, 26 Jun 2012 13:12:08 GMTNarudhttp://www.sqlservercentral.com/Forums/Topic1320297-324-1.aspxI 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.Mon, 25 Jun 2012 09:27:51 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1320297-324-1.aspxOK 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?Mon, 25 Jun 2012 08:54:27 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1320297-324-1.aspxHello, thanks for the reply. Let me clarify. Here is the code application side that inserts the data:[code="vb"] 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[/code]Here is the stored procedure:[code="sql"]USE [ShippingLog]GO/****** Object: StoredProcedure [dbo].[InsertShipmentDetails] Script Date: 6/25/2012 7:42:05 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER 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 OUTPUTASDECLARE @numrecs intSET NOCOUNT ONSET @spstat = 1 -- go ahead and set to okSET @errmsg = '' -- set default error message to nothing SET @recn = 0 -- set default record number to zeroBEGIN 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 @spstatEND TRYBEGIN 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 occuredEND CATCH[/code]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!Mon, 25 Jun 2012 08:47:25 GMTadocityhttp://www.sqlservercentral.com/Forums/Topic1320297-324-1.aspxYou have not provided anywhere near enough information for anybody to be able to help.Try this link. [url=http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx]http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx[/url]Mon, 25 Jun 2012 08:24:04 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1320297-324-1.aspxHello,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!Sat, 23 Jun 2012 15:23:15 GMTadocity