|
I have a problem with a stored procedure.
I create it with this code below, and when I refresh in MS Server Management, procedure is there. As soon as I refresh my application (ASP.NET) that cunsumes this procedure, I get an Object reference not set to an instance of an object, error.
I go back to MS Server Management, hit refresh and stored procedure that previously was there, is gone...
That explains the error, but I don't know why's this happening.
I've noticed that when I change the name of a procedure, then it works:
[spForumPost] - work
[spForumGetNumberOfPosts] - don't work
Sql is OK since I have run it.
Any ideas?
if exists(select * from dbo.sysobjects where id = object_id(N'[spForumGetNumberOfPosts]') and OBJECTPROPERTY(id, N'isProcedure') = 1)
drop procedure [spForumGetNumberOfPosts]
GO
CREATE PROCEDURE [spForumGetNumberOfPosts]
(
@SubForumId int
)
-- WITH ENCRYPTION
AS
begin
select Count(*) as PostsNumber
from posts left join threads on posts.threads_id = threads.threads_id
where
threads.subforums_id = @SubForumId;
end -- end spForumGetPostsNum
|
|
|
|
|
Kasic Slobodan wrote: I get an Object reference not set to an instance of an object
You might want to show your C#/VB code rather than the SQL as it looks more likely the problem is there rather than with the SQL.
|
|
|
|
|
Sorry about that...
When I execute the sql from the procedure, everything runs great, no error.
Here's the code:
MSSqlConn is a class for connecting to MSSql database.
public static int getPostsNum(int podForumId)
{
int tmp;
MSSqlConn s = new MSSqlConn();
s.OpenConn("EXEC [spForumGetNumberOfPosts] " + podForumId);
s.dr.Read();
tmp = (int)s.dr["PostsNumber"];
s.CloseConn();
return tmp;
}
|
|
|
|
|
So which line is causing the exception?
Kasic Slobodan wrote: s.OpenConn("EXEC [spForumGetNumberOfPosts] " + podForumId);
It is a bad idea to inject values into SQL Strings - it can lead to SQL Injection Attacks. Also ADO.NET has the facility to handle calling stored procedures directly.
e.g.
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@paramName", paramValue);
Kasic Slobodan wrote: s.dr.Read();
Does this return true or false - you don't check it. If it returns false then there is nothing to read and the following line could fail.
Kasic Slobodan wrote: return tmp;
Sorry - this more code review like than helping with your problem - As a general rule nothing should be called tmp, temp, temporary or varients thereof. I generally use the name "result" for any value that is to be returned out of a method. It makes the purpose clearer and when reading the code later makes it easier to understand what is going on.
|
|
|
|
|
Iam sorry. I haven't seen this post untill now.
Anyway, s.dr.Read() is line which is cousing the error.
s.dr.Read() is false.
Whenever condition I set, it always displays the error at s.dr.Read() line.
It's ok. From code review I can learn something.
Thanks for code review.
|
|
|
|
|
Hey ,
don worry , this is a small flaw that u would have missed in your ADO.Net Code, debug it carefully ..and peacefully,
Peace is all that takes to succeed
|
|
|
|
|
I don't see anything wrong in the code and either in store proceure.
When I take sql query from a procedure, and put it in s.OpenConn() method, there is no error, only if Iam doing it with a stored procedure.
Like I said, when I run a application and go back to MS Server Manager and refresh, the stored procedure is gone (any other procedures is still present), like I have roll back transaction (for that procedure) or something like that.
Similar method for getting the number of Threads works fine, I only have a problem with spForumGetNumberOfPosts stored procedure.
I get object reference error, probably becouse the stored procedure Iam trying to execute, exists no more, wich is true. I don't know why procedure always disapiers?
|
|
|
|
|
Kasic Slobodan wrote: Like I said, when I run a application and go back to MS Server Manager and refresh, the stored procedure is gone (any other procedures is still present), like I have roll back transaction (for that procedure) or something like that.
Just a wee thought - Stored procedures with names starting with "sp" are reserved by SQL Server to refer to system stored procedures. This may or may not be part of the problem (I've never used the convention of starting stored procedure names with an "sp" so I don't know the results)
|
|
|
|
|
I've tried removing sp prefix, but no luck. Same error, same stored procedure.
|
|
|
|
|
Just a thought - save the SP, and then verify that it is present. Stop the database engine and start it again, then verify that it is still present. If it isn't then it looks as though it is being rolled back.
Once you have done this, you can then run your application. What I would do as well, is to check your code to make sure that you don't have anything in your code that executes a DROP PROCEDURE command.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I did what you told me.
After stopping MSSql Server and running it again, sp wasn't beign deleted, but when I was run my application, error raise again, and sp was removed from database.
Iam not aware of any code/command that I have put in any part of my application that should delete sp.
Only drop procedure is in procedure decralation.
Iam trully confused...
|
|
|
|
|
That wouldn't do it. When SQL Server sees SPs starting with sp_ the engine looks in Master first before looking for yours.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
You should be able to use the SQL Profiler[^] to figure out what is deleting the stored procedure.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Unfortunatly, I don't know how to use it  .
|
|
|
|
|
Slobodane nisam video da li si dodelio ko je owner sp
|
|
|
|
|
Ako mislis na [dbo].[proc_name], to nisam, ali sam probao i tako i opet nece.
Ne verujem da bi to bio problem, jer ostale procedure rade :?.
|
|
|
|
|
Svaki problem koji je resen nije vise problem...
Ja na 2000 nemam takvih problema a na 2005 jos nisam presao
Pozdrav
|
|
|
|
|
Nisam ni ja imao problema sa MSSQL05 do sada...
Ma, sigurno je neka glupost u pitanju. Obicno bude tako.
Pozdrav.
|
|
|
|
|
Solved!
My colleague has noticed that Iam missing GO statement after each stored procedure, so every procedure that is last, will not work.
And, yeah, thanks everyone for your time...
|
|
|
|
|
Hi,
I want to Fetch all user table's name and imformation from my MS Access DataBase using ASP.net 2.0 and C#...
I tried with msysobjects but it shows error retraiving problem form database in ASP.NET 2.0
Is there Any Idea or Other Way
Thanks in advance...
Viral
|
|
|
|
|
|
Is there any way to update a table columns automatically. For example:
We have a table(tblFirm) which columns are: FirmName,IsOpen
And another table(tblTime) which stores time information: OpenTime,CloseTime
What i want to do ; between the time intervals of OpenTime&CloseTime , DB will automatically set IsOpen's value to true, otherwise false. Is there anyway to do this?
Happy Coding...
.:: Something is Wrong ::.
|
|
|
|
|
This is better handled with a VIEW. I very rarely store duplicated or calculated data in a table. I only violate this rule when a frequently run query would be greatly enhanced. In psuedo-code on SQL Server it would look something like this:
CREATE VIEW FirmOpen
AS
SELECT
FirmName,
CASE
GETDATE() BETWEEN OpenTime AND CloseTime THEN 1
ELSE 0
END AS IsOpen
FROM
tblFirm
INNER JOIN
tblTime
ON (tblFirm.FirmName = tblTime.FirmName)
You will need to modify the INNER JOIN to reflect your foreign keys and handle the Date/Time calculations correctly. Once complete, you can use the FirmOpen like any other table (except in the case of UPDATE/INSERT).
If you still want to field to update automatically, you will need tocreate a SQL Job that does the UPDATE and schedule it to run every minute (poor choice in my opinion).
|
|
|
|
|
query for retrieving duplicate rows in a table?
|
|
|
|
|
select * from <tablename> where <columnname> in(select <columnname> from <tablename> group by <columnname> having count(<columnname>)>1)
G.Praveen Kumar.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
