Sunspots - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Sunspots

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-07-17 : 11:33:30

I have a small asp.net 1.1 app that is misbehaving. What happens is this. The user picks from a drop down with three entries, then types in a value and clicks a button. This calls a sproc which works out which one of three sprocs to call based on the drop down and passes it the typed in value.

99% of the time this works as expected. However, if I pick one particular value in the drop down AND I put the value 12345 into the text box I get an error

quote:
Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails. The statement has been terminated.

Ah I hear you cry it's the sproc, BUT - I don't think it can be as if I call it directly from QA then it works fine (and returns no results).

I've tried to trace the sproc call but as this is SQL server 7 it's a little more difficult than on 2000 but it still gives no clues. In fact it doesn't show the call at all, even when it is successful (probably my fault).

I'd like to think that it's the ASP somehow but I have no idea how it could be as the value 12345 works fine for the other drop down values. Anyone have any clues, suggestions where to look, hints tips etc.

Many thanks

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-07-17 : 11:40:46

Is the exception of type SqlException or some other?


try
{
    //your stuff here
}
catch(SqlException e1)
{
    // Does the error come here? ...
}
catch(Exception e2)
{
   // ... or Here?
}

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-17 : 13:32:32

put a breakpoint in your code and verify for sure which stored proc it is calling. and verify all of the parameters values. Make sure that you also have no implicit casting occurring. for example, if the datatype is integer but you are passing in strings, or vice versa. When using ASP.NET, always use option strict and always declare the datatypes of your sql parameters.

- Jeff

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-07-18 : 06:32:24

Thanks for the advice guys. There appear to be some wierd things going on here

Spirit I tried what you suggested and it hasn't really made any difference I can see as it doesn't seem to get to that point (but see below as to a possible cause), however I did note that when I run the page from the server I get this in the stack trace

quote:
[SqlException: Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.
The statement has been terminated.]

So I presume that this is an SQL issue somehow.

Jeff, I tried your suggestions but this makes even wierder things happen. I sorted out the implicit conversions which is fine. However it's when I try to debug that something wierd is happening.

When it gets to a breakpoint it's almost as though it is debugging a different bit of code to that on screen. The yellow highlight doesn't always highlight all the line and sometimes highlights bits of the line which are blank, also it sometimes goes back to the webpage at unexpected points in the code. I've tried looking at the autos window to see if I can work out what is happening but that hasn't really helped.

So now I am just plain confused. I sort of suspect that this is a conversion issue which I think is what Jeff was hinting at BUT, if that's the case, why is it that it is only for the number 12345 and not for other numbers (that I have found yet). I've tried rearranging the digits, adding and removing digits etc. but nothing else causes this error (yet).

Anyone have the slightest clue here?

many thanks

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-07-18 : 08:22:26

go back to basics....try to ensure what you believe is the affected SP is actually being called....ie you're not running something slightly different already in cache.

then add in more code, bit by bit....until you get the offending item.

have you the parameters in the right order?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-18 : 09:36:07

There's always Debug.WriteLine() if you are having trouble stepping through the code. Output the stored proc called, the parameters and their values, and so on, before calling the sql statement.

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-18 : 10:18:44

Create a trace table
create table trace (id int identity, dte datetime default getdate(), eventtype varchar(100), data varchar(8000))

then in the sp log what happens.
As eventtype put the procedure name - you can add an identifier for what you are logging too.
Also log the spid if it is called simultaneouisly from different clients.

At least log the start and end with parameters on both and anything else you feel necessary

I tend to have a table like this on all my systems and leave some batch jobs logging so that I can see what they are doing and add and remove logging from sps when I feel it's needed.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-07-19 : 07:56:31

Many thanks for your advice guys, much appreciated and very helpful. I'm more confused now but at least I have started to feel I am getting somewhere having implemented your sugggestions.

The Trace table isn't showing anything at all which is a bit wierd as I know it works from QA (using the web authentication) i.e. when I use QA the trace table gets populated.

The call from QA is very simple -

DECLARE @TYPE char(3)
DECLARE @ID varchar(20)

set @type = 'dis'
set @ID = 12345

EXEC [dbo].[sproc_sel_Pix] @TYPE, @ID

SELECT * FROM _trace -- check if it worked as expected

Also the following works as expected

DECLARE @TYPE char(3)
DECLARE @ID varchar(20)

set @type = 'dis'
set @ID = '12345'

EXEC [dbo].[sproc_sel_Pix] @TYPE, @ID

SELECT * FROM _trace -- check if it worked as expected

For my debugging issue I have noticed something that may well be the root of the problem but am not sure what I can do about it

I think that 2 solutions (or projects - I'm not entirely sure which) have got a little confused on my box.

A while ago I tried to rename the solution but gave up as it seemed more complicated than I hoped so I have Project and CurrentProject. When I do some work and copy it to the testing server it is all fine (using the Copy Project) option. On there the application is called Project which is the one that works. However, on my own box this doesn't happen. I made some changes then took a look at the files. The changes went into CurrentProject but when I try to debug I'm pretty sure I get the files from Project. I suspect this is partly down to the Forms Authentication as WEB.CONFIG on my machine is the same as the one on the testing server - if it isn't the testing server gets broken everytime I update the project.

In all it's a mess and I'm not sure how to resolve it properly. The app on the testing server (mostly) works and I want to be sure I don't break that.

Any suggestions?

thanks

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-07-21 : 09:44:02

I've finally got to the bottom of this one, though I'm not sure why it happened

I sorted out the mess with the projects and all was well but I was still getting this issue. The trace table wasn't helping as nothing was going into it and all the parameters were correct. I eventually realised that it was going to a different server! In the code I had set a connection string correctly but hadn't created a sqlconnection object using it. For some reason (thankfully as it may not have come to light otherwise) the connection it was using obviously defaulted or took a value from elsewhere in the code.

Many thanks to all who helped

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-07-21 : 11:09:02

you missed the "

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-07-24 : 02:52:59

Yes, I was hoping no-one would notice that!

Still not sure why it was defaulting to a different server though

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources