SQL - Syntax error from string missing from query
In my application, I am getting weird error in several places. Basically, I call a stored procedure on the database using the SqlManager method and executeNonQuery()
, but I get an exception:
Incorrect syntax around '44444'
in Visual Studio. The point is, none of my values are equal to "44444" and this string is not in a stored procedure. When I run a trace using SQL Server Profiler I don't see the value.
I tried to sort out this problem and the only problem that came up next to it was an issue with a similar problem where an empty value was pushed into a foreign key (none of my values are null).
The function in which I am calling the stored procedure:
Try
Dim p As New List(Of SQLParametre)()
p.Add(New SQLParametre("@InvcNum", invcNum, SQLParametreType.String))
p.Add(New SQLParametre("@Sale", sale, SQLParametreType.Decimal))
p.Add(New SQLParametre("@Commission", commission, SQLParametreType.Decimal))
p.Add(New SQLParametre("@UpdtDate", DateTime.Today, SQLParametreType.Date))
m_Manager.executeNonQuery("TheSP", p)
Return True
Catch ex As Exception
m_Log.WriteLog("theSP crashed", ex.Message)
Return False
End Try
Stored procedure:
ALTER PROCEDURE [dbo].[TheSP]
@InvcNum varchar(9),
@Sale money,
@Commission money,
@UpdtDate as smalldatetime
AS
UPDATE tbInvc SET
Sale = Sale + @Sale,
Comm = Comm + @Commission,
updateDate = @UpdtDate
WHERE (Num = @InvcNum)
Has anyone ever got a similar issue where a syntax error was thrown by a string that did not contain a query / stored procedure?
EDIT: I am using Visual Studio 2012 and SQL Server Management Studio 2012 and have fixed a typo in the stored procedure due to the change in column names.
UPDATES
After running the stored procedure in Management Studio, I found that the error appears there too, so the error shouldn't be in the vb code.
The error generated by Management Studio is also different from the error I get on an exception in VS2012:
Msg 102, Level 15, State 1, Procedure tbInvc_UTrig, Line 14
Invalid syntax near '44444'.
source to share