3

In my application, I get a weird error at multiple locations. Basically, I call a stored procedure in the database using an SqlManager and the executeNonQuery() method but I get an exception saying:

Incorrect syntax near '44444'

in Visual Studio. The thing is, none of my values are equal to '44444' and that string is not in the stored procedure. When I start a trace with the SQL Server Profiler, I do not see the value.

I've tried googling this issue and the only issue that came near this was someone having a similar issue when a null value was putted in a foreign key (none of my values are null).

Function where I call 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

The 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)

Did someone ever get a similar issue where a syntax error was being thrown by a string not in the query/stored procedure?

EDIT: I'm using Visual studio 2012 and SQL Server Management Studio 2012 and fixed a typo in the stored procedure from changing the column's names.

UPDATES

After running the stored procedure in Management Studio, I saw the error appearing there too, so the error should not be in the vb code.

The error thrown by Management Studio is also a different one than the one I get in the exception in VS2012:

Msg 102, Level 15, State 1, Procedure tbInvc_UTrig, Line 14
Incorrect syntax near '44444'.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hugo Trudel
  • 225
  • 3
  • 12
  • yeah, sorry, I was changing the table columns names to easier to type ones for the sake of the question (and clarity) – Hugo Trudel Jan 24 '13 at 20:42
  • Can you print the `p` as a string before you call the `executeNonQuery` method? – Jonathan Leffler Jan 24 '13 at 20:42
  • 2
    What the heck is a SQLParametre – Sam Axe Jan 24 '13 at 20:45
  • I could, but I'm using the SQL server profiler and I can see the SP being called on the server (and working since there are some writes). I'm checking this instead of printing the P list in a string since it allows me to see exactly what query the server is receiving. Also, while doing step by step, I checked all the value to make sure they were not null. Basically, everything has a value and there are no format errors. – Hugo Trudel Jan 24 '13 at 20:45
  • Have you tried running the sp in SMS to see what kind of error, if any, you get? – Melanie Jan 24 '13 at 20:46
  • @Dan, good question, my guess is an sqlParameter, checking this now, I jsut started this intership and I am not totally used to their system – Hugo Trudel Jan 24 '13 at 20:47
  • @Melanie: The error appears in SMS too. – Hugo Trudel Jan 24 '13 at 20:49
  • @Dan, custom made SqlParameter used in the dll, was made before I came in, it is used in all of their app, so I do not think this will be the problem. – Hugo Trudel Jan 24 '13 at 20:49
  • Does the error occur no matter what @InvcNum is? – Melanie Jan 24 '13 at 20:50
  • yipes. also, what are the values of table.Sale, table.Comm and `@Sale` and `@Commission` on the record that fails? – Sam Axe Jan 24 '13 at 20:53
  • are there any triggers on the table? – Sam Axe Jan 24 '13 at 20:53
  • @Melanie, Yes, I just tested it with 3 existing InvcNum and the error appears nonetheless. – Hugo Trudel Jan 24 '13 at 20:53
  • @dan @InvcNum='000080220',@Sale=$0.2500,@Commission=$0.0625,@UpdtDate='2013-01-24 00:00:00' I tried removing the dollar sign thinking it might be a formatting error, but that wasn't the case. I also tried it with the date using the short format (no time, only the date) to no avail. – Hugo Trudel Jan 24 '13 at 20:56
  • and if you use those values in an `update` query (NOT via the sp) in SSMS, does it work or do you get an error? – Sam Axe Jan 24 '13 at 20:58
  • Maybe this custom made `m_Manager.executeNonQuery` does something funny. – Olivier Jacot-Descombes Jan 24 '13 at 21:02
  • @Dan-o Yes, it still throws that exception. ALthought, i just realised that the SMS error has more details: Msg 102, Level 15, State 1, Procedure tbInvc_UTrig, Line 14 Incorrect syntax near '44444'. U_trig means a trigger I think... – Hugo Trudel Jan 24 '13 at 21:04
  • @olivier, not it doesnt, the error isn't vs side – Hugo Trudel Jan 24 '13 at 21:06
  • 2
    looks like a trigger on the table is tossing the error – Sam Axe Jan 24 '13 at 21:06
  • @dan, yes it seems to be a trigger, jsut found it, something about certain field (which are NOT updated by the update query) being null... Thanks for the help, now that I have a source, I can start working on solutions (feel free to post that comment as an answer and I'll give it to you! :) – Hugo Trudel Jan 24 '13 at 21:08
  • `tbInvc_UTrig` this looks suspiciously a name connected to a trigger – Steve Jan 24 '13 at 21:09
  • This discussion is growing too long and hard to follow. However it contains good information which should be integrated into the the question or an answer. Please do that and if needed, continue the discussion in the chat! – markus Jan 24 '13 at 23:05

1 Answers1

3

As per the comment thread: The issue appears to be with a trigger attached to the table.

Sam Axe
  • 33,313
  • 9
  • 55
  • 89