StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server and .NET: insert fails (silently!) in code but not when run manually
text
Body
copied!<p>My insert stored procedure:</p> <pre><code>ALTER procedure proj_ins_all ( @proj_number INT, @usr_id INT, @download DATETIME, @status INT ) as INSERT INTO project (proj_number, usr_id, date_download, status_id) VALUES (@proj_number, @usr_id, @download, @status) select SCOPE_IDENTITY() </code></pre> <p>... runs fine when called manually like so:</p> <pre><code>exec proj_ins_all 9001210, 2, '2009-09-03', 2 </code></pre> <p>... but when called from code:</p> <pre><code>_id = data.ExecuteIntScalar("proj_ins_all", arrParams); </code></pre> <p>... the insert doesn't happen. Now, the identity column <em>does</em> get incremented, and _id <em>does</em> get set to it's value. But the row itself never appears in the table. </p> <p>The best guess I could come up with was an insert trigger which deletes the newly inserted row, but there are no triggers on the table (and why would it work when done manually then?). My other tries were around a guess that the stored procedure is rolling back the insert somehow, and so putting <strong>begin</strong> and <strong>end</strong> and <strong>go</strong>'s and semicolons into the stored proc to properly separate the 'insert' and the 'identity select' bits. That fixed nothing.</p> <p>Any ideas?</p> <p><strong>Update:</strong></p> <p>Thanks all who have helped so far. On Preet's suggestion (first answer) I learned how to use SQL Server Profiler (I can't believe I never knew about it before - I thought it was only useful for performance tuning, didn't realise I could see exactly what query was going to the DB with it).</p> <p>It revealed that the SQL sent by the SqlCommand.ExecuteScalar() method was slightly different from what I was running manually. It was sending:</p> <pre><code>exec proj_ins_all @proj_number=9001810,@usr_id=2,@download='2009-09-03 16:20:11.7130000',@status=2 </code></pre> <p>I ran that manually and voila! An actual SQL server error(!): </p> <p><em>Error converting data type varchar to datetime.</em></p> <p>Since I was testing it manually, I simply shortened the datetime from <strong>'2009-09-03 16:20:11.7130000'</strong> to <strong>'2009-09-03 16:20:11'</strong> and this fixed the error; now the row inserted fine.</p> <p>But this begs the question: why can't Microsoft's SQL Server handle more than 23 characters in that datetime parameter? It was Microsoft's SqlCommand.ExecuteScalar() method that constructed the query like that, not me. This is a problem because <strong>my code still doesn't work.</strong></p> <p>As soon as I got it working manually, I looked at how to set up the SqlParameter for the date in the code so it would send a value like the one that worked. I tried changing the data type from <strong>SqlDbType.DateTime</strong> to <strong>SqlDbType.SmallDateTime</strong>. The profiler showed that this indeed produced a shorter datetime value '2009-09-03 17:15:00', but the insert still failed silently (the original problem). But when I copy-pasted the sql from profiler and tried it manually - it worked. no error. Same deal sending it as varchar - SSMS query window likes it, same query through .net fails silently. </p> <p>:(</p> <p>Any other thoughts? Some 'environment' type setting in SQL server like 'set ansi_nulls off' or whatever that might be different between manual and through-code connections?</p> <p>(Another question is why doesn't sql server give me an error message and generate an exception each time this error occurs?)</p> <p><strong>.Net code</strong></p> <p>On van's request, here is the relevant .NET code (C# ASP.NET):</p> <pre><code>data.MakeInParam("@proj_number", SqlDbType.Int, _projNo), data.MakeInParam("@usr_id", SqlDbType.Int, _usr.Id), data.MakeInParam("@download", SqlDbType.SmallDateTime, _downloadDate), data.MakeInParam("@status", SqlDbType.Int, (int)_status), </code></pre> <p>and MakeInParam has this line:</p> <pre><code>param = new SqlParameter(paramName, DbType); </code></pre> <p>And it gets executed like this:</p> <pre><code>SqlCommand cmd = CreateCommand(procName, prams); object result = cmd.ExecuteScalar(); </code></pre> <p>Note: in the above paramaters, it's <strong>@download</strong> that's the problem. _downloadDate is a nullable DateTime. note that the sql type in this line used to be SqlDbType.DateTime, but I changed it to SqlDbType.SmallDateTime to produce a call that works manually (It still fails when run from code).</p> <p><strong>Update: solved</strong> Thanks to Matt, I finally found and fixed the problem: the custom helper class was missing a cmd.Transaction.Commit() in one of it's functions! It had a bunch of different messy stuff in it and I missed this when going through the code initially.</p> <p>So thanks to Matt and Preet and everyone else who contributed.</p> <p><strong>Lessons learned</strong><br> - SQL Profiler is really useful. Take the time to learn how to use it.<br> - If the DB is swallowing errors, or appears to be doing something according to your code but it's not actually appearing in the DB, check the transaction is being committed.<br> - Don't be so trusting of a DB helper class someone else wrote. Even one that seems simple enough at first. Make sure you understand exactly what it does , as it may be buggy or just not do things the way you think they're always done.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload