March 24, 2010 at 10:40 am
I have a VB.Net console application that works with a SQL server DB using stored procedures.
I have tested this console application on my local PC repeatedly and everything works just fine, no errors. The table gets updated just as it should; the stored procedure runs without any errors.
I am using SQL 2005 Express on my PC and SQL 2008 on the DEV server.
As soon as I deploy this Table and SP to our DEV server for user testing, it does NOT update my table and does NOT log any errors in my VB.net app? Below are the table script and the SP script that I am having trouble with. The two BIT columns are ones I am trying to update. On the DEV server, these two columns just do not get updated. My log indicates that the SP was called and no errors were generated, yet when I look at the tables, the columns have not been updated? This is driving me nuts, can someone help me out, please!!
Thanks to all that reply!
======================================================
USE [DevDB]
GO
/****** Object: Table [dbo].[EDI_InvoicesLog] Script Date: 03/24/2010 09:37:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EDI_InvoicesLog](
[Company] [int] NULL,
[Vendor] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Invoice] [varchar](22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ImportDate] [smalldatetime] NOT NULL CONSTRAINT [DF_EDI_InvoicesLog_ImageDate] DEFAULT (getdate()),
[TIFF_Created] [bit] NOT NULL CONSTRAINT [DF_EDI_InvoicesLog_TIFF_Created] DEFAULT ((0)),
[LawsonHold] [bit] NOT NULL CONSTRAINT [DF_EDI_InvoicesLog_LawsonHold] DEFAULT ((0))
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
======================================================
USE [DevDB]
GO
/****** Object: StoredProcedure [dbo].[EDI_InvoicesLog_Update] Script Date: 03/24/2010 09:36:55 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[EDI_InvoicesLog_Update]
@CMP int,
@VND char(9),
@INV varchar(50),
@TIFF bit = null,
@HOLD bit = null
AS
Declare @rtncode int
IF @TIFF IS NOT NULL
Begin
begin transaction
--Insert record
UPDATE dbo.EDI_InvoicesLog SET TIFF_Created=@TIFF Where Company=@CMP and Vendor=@VND and Invoice=@INV;
--Check for error on update
if @@ROWCOUNT = 0
begin
rollback transaction
select @rtncode = 1
return @rtncode
end
else
begin
commit transaction
select @rtncode = 0
return @rtncode
end
end
IF @HOLD IS NOT NULL
Begin
begin transaction
--Insert record
UPDATE dbo.EDI_InvoicesLog SET LawsonHold=@HOLD Where Company=@CMP and Vendor=@VND and Invoice=@INV;
--Check for error on update
if @@ROWCOUNT = 0
begin
rollback transaction
select @rtncode = 2
return @rtncode
end
else
begin
commit transaction
select @rtncode = 0
return @rtncode
end
end
March 24, 2010 at 11:56 am
Without seeing the VB.Net, it's a bit hard to speculate ... especially in terms of what's getting logged and what log you're referring to and what kind of error checking you're doing on the app side for successful connection and whatnot.
First thing is that I would clean up the code a bit ... my suggestion:
CREATE Procedure [dbo].[EDI_InvoicesLog_Update]
@CMP int,
@VND char(9),
@INV varchar(50),
@TIFF bit = null,
@HOLD bit = null
AS
IF @TIFF IS NOT NULL
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.EDI_InvoicesLog SET TIFF_Created=@TIFF Where Company=@CMP and Vendor=@VND and Invoice=@INV;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
RETURN 1;
END CATCH
END --@TIFF IS NOT NULL
IF @HOLD IS NOT NULL
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.EDI_InvoicesLog SET LawsonHold=@HOLD Where Company=@CMP and Vendor=@VND and Invoice=@INV;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
RETURN 2;
END CATCH
END --@HOLD IS NOT NULL
RETURN 0;
See what actual return value your VB.Net is receiving. You can also add an output parameter if you want to pass back more detailed information about the specific ERROR_NUMBER() in the case of an error.
Also, just double check to make sure the @TIFF and @HOLD aren't null since the proc won't do anything at all (other than return 0) if they both are.
March 24, 2010 at 12:45 pm
I will try out your changes for the SP, ASAP. Right now I am working with the SQL DBA.
Here is the sub in VB.Net code that calls this SP:
Public Function SQL_Update_Flags(ByVal strCmp As String, ByVal strVnd As String, ByVal strInv As String, ByVal intFlag As Int16, ByVal blnFlag As Boolean) As Boolean
'==Function to update the flags in EDI invoice log table
Dim connSQL As New SqlConnection(strImgSQLDB)
Dim cmdSQL As New SqlCommand
Dim intRtnCode As Int16 = -1
Try
cmdSQL.CommandText = "dbo.EDI_InvoicesLog_Update"
cmdSQL.CommandType = CommandType.StoredProcedure
cmdSQL.Parameters.Add("@CMP", SqlDbType.Int).Value = CInt(strCmp)
cmdSQL.Parameters.Add("@VND", SqlDbType.Char, 9).Value = strVnd
cmdSQL.Parameters.Add("@INV", SqlDbType.VarChar, 50).Value = strInv
'==Update TIFF flag
If intFlag = 1 Then
cmdSQL.Parameters.Add("@TIFF", SqlDbType.Bit).Value = blnFlag
Else
cmdSQL.Parameters.Add("@TIFF", SqlDbType.Bit).Value = System.DBNull.Value
End If
'==Update Lawson Hold created flag
If intFlag = 2 Then
cmdSQL.Parameters.Add("@HOLD", SqlDbType.Bit).Value = blnFlag
Else
cmdSQL.Parameters.Add("@HOLD", SqlDbType.Bit).Value = System.DBNull.Value
End If
cmdSQL.Connection = connSQL
connSQL.Open()
intRtnCode = cmdSQL.ExecuteScalar
'==Check Return code
If intRtnCode = 0 Then
AppLog.WriteToLog(strLogFileFolder & strLogFileName, "Updated SQL Flag for Cmp/Vnd/Inv: " & strCmp & "," & strVnd & ", " & strInv, , True)
Return True
Else
Return False
End If
Catch ex As Exception
HandleError(ex, "SQL_Update_Flags")
Return False
Finally
If connSQL.State = ConnectionState.Open Then connSQL.Close()
End Try
End Function
March 24, 2010 at 2:05 pm
ExecuteScalar() does not return the RETURN value of the stored procedure. I returns the first column of the first row of the result set. That is why you are not detecting any "error" in the .NET app.
Instead of :
RETURN 0
use:
select 0 (or select 1 for error). Otherwise you need to set up another output paramater (0) for the actual return code, and it won't be returned by ExecuteScalar()
The probability of survival is inversely proportional to the angle of arrival.
March 24, 2010 at 2:55 pm
If you don't want to interfere in what output you're getting from the proc, there is a way you can get the return value directly.
In your VB.Net code declare another parameter of Int type and specify that it is a return value.
myCmd.Parameters.Add(New SqlParameter("@n_return", SqlDbType.Int))
myCmd.Parameters("@n_return").Direction = ParameterDirection.ReturnValue
ExecuteNonQuery() will fire the proc and fill that return param with the value from the proc.
March 24, 2010 at 4:29 pm
Ok, so the SQL DBA had me add "SET NOCOUNT ON", in all my SP's. That definitely helped as the bit field column started getting updated.
I also added the return value code that was suggested.
Now the problem is that the SP is not updating all the records, it seems like each time it runs over my set of records it does not update the last record. This happens when we run the app from the DEV server.
We just tested the app from my PC, but pointing to the DEV SQL 2008 server and NOT my local SQL 2005 Express DB. It worked just fine; it updated all the rows and columns in my SQL table?
Anyone have any ideas what it could be now?
March 24, 2010 at 5:32 pm
I'm not sure if I really understand what you're saying happens where. A few things to keep in mind, however ...
When your sproc hits the "RETURN", it's done - it exits and doesn't do anything else.
To be a bit more specific about exactly what's happening, I would highly suggest adding another parameter, make it an output and within the CATCH sections of your code assign the value of ERROR_NUMBER() to that variable.
SET NOCOUNT ON just suppresses the automatic return of the rows affected number, it should not impact what your sproc is doing internally.
March 25, 2010 at 10:39 am
Ok, this is totally driving me nuts!! I have NEVER had a problem like this before!!!
Once again the code and the SP all work fine when executed from my Desktop PC. The same code and SP works fine on the server, except it always drops a row or two from the process!!
We just tested the exact same set of data from my PC and then on the SERVER. From my PC it worked like a charm!!! On the server it worked just fine, except it dropped one invoice from the proces!!! OMG!!!!
This morning before these tests I broke up the update SP into two SP's, one for each column, just to make them simpler and eliminate any possible issues.
Here is the code in the two SP's
USE [BBBCustom]
GO
/****** Object: StoredProcedure [dbo].[EDI_InvoicesLog_UpdateHOLD] Script Date: 03/25/2010 12:26:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[EDI_InvoicesLog_UpdateHOLD]
@CMP int,
@VND char(9),
@INV varchar(50),
@HOLD bit,
@ERRNUM int = -1 output
AS
SET NOCOUNT ON;
Declare @rtncode int
SELECT @rtncode = -1
IF @HOLD IS NULL RETURN @rtncode
IF @HOLD IS NOT NULL
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.EDI_InvoicesLog SET LawsonHold=@HOLD Where Company=@CMP and Vendor=@VND and Invoice=@INV;
COMMIT TRANSACTION;
Select @rtncode = 0;
RETURN @rtncode;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
Select @ERRNUM = ERROR_NUMBER();
Select @rtncode = 1;
RETURN @rtncode;
END CATCH
END --@HOLD IS NOT NULL
GO
/****** Object: StoredProcedure [dbo].[EDI_InvoicesLog_UpdateTIFF] Script Date: 03/25/2010 12:26:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[EDI_InvoicesLog_UpdateTIFF]
@CMP int,
@VND char(9),
@INV varchar(50),
@TIFF bit,
@ERRNUM int = -1 output
AS
SET NOCOUNT ON;
Declare @rtncode int
SELECT @rtncode = -1
IF @TIFF IS NULL RETURN @rtncode
IF @TIFF IS NOT NULL
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.EDI_InvoicesLog SET TIFF_Created=@TIFF Where Company=@CMP and Vendor=@VND and Invoice=@INV;
COMMIT TRANSACTION;
Select @rtncode = 0;
RETURN @rtncode;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
Select @ERRNUM = ERROR_NUMBER();
Select @rtncode = 1;
RETURN @rtncode;
END CATCH
END --@TIFF IS NOT NULL
GO
Here is the relevant code from VB.Net app:
The SUB Create_LawsonHold() loops through my SQL table data and runs the update for each row. This is where the one row always get missed/dropped from the update!!
Public Sub Create_LawsonHold()
'==Sub to read list of all invoices, from SQL log table, that need a Lawson Hold record created
Dim connSQL As New SqlConnection(strImgSQLDB)
Dim cmdSQL As New SqlCommand
Dim drdReader As SqlDataReader
Dim intCount As Int16 = 0
Try
cmdSQL.CommandText = "dbo.EDI_Invoices_UpdateLawson"
cmdSQL.CommandType = CommandType.StoredProcedure
cmdSQL.Parameters.Add("@TODAY", SqlDbType.SmallDateTime).Value = Date.Today.ToShortDateString
cmdSQL.Connection = connSQL
connSQL.Open()
drdReader = cmdSQL.ExecuteReader()
AppLog.WriteToLog(strLogFileFolder & strLogFileName, "Sub Create_LawsonHold Started...")
'==No data, leave sub
If Not drdReader.HasRows Then
AppLog.WriteToLog(strLogFileFolder & strLogFileName, "Sub Create_LawsonHold: NO DATA Found")
Exit Try
End If
AppLog.WriteToLog(strLogFileFolder & strLogFileName, "Sub Create_LawsonHold: DATA Found for " & Date.Today.ToShortDateString)
While drdReader.Read
intCount += 1
'==Insert new Lawson hold record into table DBAPHLI
Lawson_HoldRecord(drdReader("Company").ToString, drdReader("Vendor").ToString, drdReader("Invoice").ToString)
'==Check for new Lawson hold record in table DBAPHLI to confirm Insert
If CheckLawsonHoldRecord(drdReader("Company"), drdReader("Vendor"), drdReader("Invoice")) Then
SQLUpdate_HOLD_Flag(drdReader("Company"), drdReader("Vendor"), drdReader("Invoice"), True)
End If
End While
AppLog.WriteToLog(strLogFileFolder & strLogFileName, "Sub Create_LawsonHold: intCount is " & intCount)
Catch ex As Exception
HandleError(ex, "Create_LawsonHold")
Finally
If connSQL.State = ConnectionState.Open Then connSQL.Close()
AppLog.WriteToLog(strLogFileFolder & strLogFileName, "Sub Create_LawsonHold Completed.")
End Try
End Sub
Public Function SQLUpdate_TIFF_Flag(ByVal strCmp As String, ByVal strVnd As String, ByVal strInv As String, ByVal blnFlag As Boolean) As Boolean
Dim connSQL As New SqlConnection(strImgSQLDB)
Dim cmdSQL As New SqlCommand
Dim intRtnCode As Int16 = -1
Dim intERRnum As Int16 = -1
Try
cmdSQL.CommandText = "dbo.EDI_InvoicesLog_UpdateTIFF"
cmdSQL.CommandType = CommandType.StoredProcedure
cmdSQL.Parameters.Add("@CMP", SqlDbType.Int).Value = CInt(strCmp)
cmdSQL.Parameters.Add("@VND", SqlDbType.Char, 9).Value = strVnd
cmdSQL.Parameters.Add("@INV", SqlDbType.VarChar, 50).Value = strInv
cmdSQL.Parameters.Add("@TIFF", SqlDbType.Bit).Value = blnFlag
cmdSQL.Parameters.Add(New SqlParameter("@rtncode", SqlDbType.Int))
cmdSQL.Parameters("@rtncode").Direction = ParameterDirection.ReturnValue
cmdSQL.Parameters.Add(New SqlParameter("@ERRNUM", SqlDbType.Int))
cmdSQL.Parameters("@ERRNUM").Direction = ParameterDirection.Output
cmdSQL.Connection = connSQL
connSQL.Open()
cmdSQL.ExecuteNonQuery()
intRtnCode = cmdSQL.Parameters("@rtncode").Value
If Not IsDBNull(cmdSQL.Parameters("@ERRNUM").Value) Then
intERRnum = cmdSQL.Parameters("@ERRNUM").Value
End If
'==Check Return code
If intRtnCode = 0 Then
AppLog.WriteToLog(strLogFileFolder & strLogFileName, "Updated SQL TIFF Flag for Cmp/Vnd/Inv: " & strCmp & "," & strVnd & ", " & strInv, , True)
Return True
Else
AppLog.WriteToLog(strLogFileFolder & strLogFileName, "SQLUpdate_TIFF_Flag SP Error Number for Cmp/Vnd/Inv: " & strCmp & "," & strVnd & ", " & strInv & "," & intERRnum, , True)
Return False
End If
Catch ex As Exception
HandleError(ex, "SQLUpdate_TIFF_Flag")
Return False
Finally
If connSQL.State = ConnectionState.Open Then connSQL.Close()
End Try
End Function
Public Function SQLUpdate_HOLD_Flag(ByVal strCmp As String, ByVal strVnd As String, ByVal strInv As String, ByVal blnFlag As Boolean) As Boolean
Dim connSQL As New SqlConnection(strImgSQLDB)
Dim cmdSQL As New SqlCommand
Dim intRtnCode As Int16 = -1
Dim intERRnum As Int16 = -1
Try
cmdSQL.CommandText = "dbo.EDI_InvoicesLog_UpdateHOLD"
cmdSQL.CommandType = CommandType.StoredProcedure
cmdSQL.Parameters.Add("@CMP", SqlDbType.Int).Value = CInt(strCmp)
cmdSQL.Parameters.Add("@VND", SqlDbType.Char, 9).Value = strVnd
cmdSQL.Parameters.Add("@INV", SqlDbType.VarChar, 50).Value = strInv
cmdSQL.Parameters.Add("@HOLD", SqlDbType.Bit).Value = blnFlag
cmdSQL.Parameters.Add(New SqlParameter("@rtncode", SqlDbType.Int))
cmdSQL.Parameters("@rtncode").Direction = ParameterDirection.ReturnValue
cmdSQL.Parameters.Add(New SqlParameter("@ERRNUM", SqlDbType.Int))
cmdSQL.Parameters("@ERRNUM").Direction = ParameterDirection.Output
cmdSQL.Connection = connSQL
connSQL.Open()
cmdSQL.ExecuteNonQuery()
intRtnCode = cmdSQL.Parameters("@rtncode").Value
If Not IsDBNull(cmdSQL.Parameters("@ERRNUM").Value) Then
intERRnum = cmdSQL.Parameters("@ERRNUM").Value
End If
'==Check Return code
If intRtnCode <> -1 Then
AppLog.WriteToLog(strLogFileFolder & strLogFileName, "Updated SQL HOLD Flag for Cmp/Vnd/Inv: " & strCmp & "," & strVnd & ", " & strInv, , True)
Return True
Else
AppLog.WriteToLog(strLogFileFolder & strLogFileName, "SQLUpdate_HOLD_Flag SP Error number for Cmp/Vnd/Inv: " & strCmp & "," & strVnd & ", " & strInv & "," & intERRnum, , True)
Return False
End If
Catch ex As Exception
HandleError(ex, "SQLUpdate_HOLD_Flag")
Return False
Finally
If connSQL.State = ConnectionState.Open Then connSQL.Close()
End Try
End Function
Public Sub Lawson_HoldRecord(ByVal strCmp As String, ByVal strVnd As String, ByVal strInv As String)
Dim OleDBConn As New OleDbConnection(strAS400)
Dim cmdOleDbSQL As New OleDbCommand
Dim OleDbParm As OleDbParameter
Try
'==open connection
OleDBConn.Open()
'==parm1 - Company
OleDbParm = cmdOleDbSQL.Parameters.Add("@COMPANY", OleDbType.Char, 5)
OleDbParm.Direction = ParameterDirection.Input
OleDbParm.Value = strCmp
'==parm2 - Vendor
OleDbParm = cmdOleDbSQL.Parameters.Add("@VENDOR", OleDbType.Char, 6)
OleDbParm.Direction = ParameterDirection.Input
OleDbParm.Value = Trim(strVnd)
'==parm3 - Invoice
OleDbParm = cmdOleDbSQL.Parameters.Add("@INVOICE", OleDbType.Char, 22)
OleDbParm.Direction = ParameterDirection.Input
OleDbParm.Value = strInv
'==Call Stored procedure to Insert record
cmdOleDbSQL.CommandText = strLawsonSPLib & ".LSSINHLD"
cmdOleDbSQL.CommandType = CommandType.StoredProcedure
cmdOleDbSQL.Connection = OleDBConn
cmdOleDbSQL.CommandTimeout = 10
Console.WriteLine("Starting Execution of AS400 SP Call for Key: " & strCmp & ", " & strVnd & ", " & strInv)
AppLog.WriteToLog(strLogFileFolder & strLogFileName, "Starting Execution of AS400 SP Call for Key: " & strCmp & ", " & strVnd & ", " & strInv)
'==Run Stored procedure on AS400
cmdOleDbSQL.ExecuteNonQuery()
Console.WriteLine("Completed Execution of AS400 SP Call for Key: " & strCmp & ", " & strVnd & ", " & strInv)
AppLog.WriteToLog(strLogFileFolder & strLogFileName, "Completed Execution of AS400 SP Call for Key: " & strCmp & ", " & strVnd & ", " & strInv)
Catch ex As Exception
AppLog.WriteToLog(strLogFileFolder & strLogFileName, "AS400 SP (" & strLawsonSPLib & ".LSSINHLD) Execution FAILED for Key: " & strCmp & ", " & strVnd & ", " & strInv)
AppLog.WriteToLog(strLogFileFolder & strLogFileName, ex.ToString)
Finally
If OleDBConn.State = ConnectionState.Open Then OleDBConn.Close()
End Try
End Sub
March 25, 2010 at 11:19 am
We need to see what is going on in the method: CheckLawsonHoldRecord()
Evidently it somehow does not find the row and thus does not update it.
Just as a comment, I would not write code that opens (creates) a new db connection for each row, does a single insert and/or update then closes it. Yes, .NET and server does cache connections and so forth but that is still a lot of needless overhead especially if you had a large set of rows to process. If it were me, I'd open the insert/update connection once in the main Lawson loop then close it when finished.
If all this is happening on the same server I'd do it in a stored proc and forget the RBAR code.
The probability of survival is inversely proportional to the angle of arrival.
March 25, 2010 at 11:40 am
March 25, 2010 at 11:46 am
The Sub CheckLawsonHoldRecord is just doing a simple SQL select over the AS400 table to confirm that the Company, Vendor, Invoice key combo has been inserted by the AS400 SP we called earlier.
Also here is an update on the issue...
When I looked at the log for the application on the server, I dont see some of the new comments I placed in my code. It appears the server is running an older version of my code.
Each time I test the code on the server, I am using the scheduled task entry to execute the application and NOT just double clicking the EXE.
March 25, 2010 at 11:56 am
bteraberry (3/25/2010)
If you have .NET 3.5 and SQL 2008 you can use a table valued parameter to pass the whole table of what you want updated all at the same time.But it does sound like the issue here is in your vb code.
How can it be my code, when the very same code works when executed from my desktop PC and using the DEV SQL 2008 Server as the DB?
March 25, 2010 at 12:15 pm
We've all been there. Use process of elimination. The SQL procs you have are extremely simple. Once you verify they're exactly the same on the other server you can be sure that the problem doesn't lie there. I don't think there is anything in your proc that would be affected by any server side settings.
Either run in debug with breakpoints or figure out what works best for you to understand and capture the exact steps your vb code is executing. Capture the information from every loop ... get every call it's making to your database as it loops through your records and you will get to the root of the problem.
The big thing to remember, there is always a reason, it's just a matter of finding it.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply