SQL Insert Query not executing without throwing any exception in c#

Question

I wrote an insert function for a discussion forum website to insert new discussion threads in database. The function is as follows:

public int createDiscuss(Discussion dis)
    {
        query = "insert into [MyForums].[dbo].[Discussions](desid, text, date, time, replyto, uid, isPrivate) values(@id, @text, @date, @time, @replyto, @uid, @pvp)";
        string did=getDesID();
        if (did == null)
            return -1;
        try
        {
            com = new SqlCommand(query, con);
            com.Parameters.AddWithValue("@id", did); 
            com.Parameters.AddWithValue("@text", dis.gettext()); 
            com.Parameters.AddWithValue("@date", SqlDateTime.Parse(DateTime.Now.ToString())); 
            com.Parameters.AddWithValue("@time", SqlDateTime.Parse(DateTime.Now.ToString()));
            com.Parameters.AddWithValue("@replyto", dis.getreplyto());
            com.Parameters.AddWithValue("@uid", dis.getuid());
            if (dis.IsPrivate() == false)
            { com.Parameters.AddWithValue("@pvp", 1); }
            else
            { com.Parameters.AddWithValue("@pvp", 2);  }
            con.Open();
            int r=com.ExecuteNonQuery();
            if (r <= 0)
            {
                return -1;
            }
            con.Close();

        }
        catch (Exception)
        {
            return -1;
        }
        return 0;
    }

This function if encounters an error or exception than it return -1 and which is checked by the calling function and then the calling function shows error. The problem is that this insert function is not executing query. When I checked that if the values are properly passing or not to the AddWithValue function as an argument, I found that every value is passed as it was given on the asp.net page. But when control comes to com.ExecuteNonQuery() function. It is returning -1 that is error. Can anyone tell me whats wrong with my code? or how can I check why com.ExecuteNonQuery() not returning any effected row??

This exception showing

A first chance exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll
An exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll but was not handled in user code

Remove the try/catch block and see what exception is generated. Or set through the code. — Strillo
– Strillo, Commented Apr 27, 2012 at 16:16

mafue · Accepted Answer · 2012-04-27 17:40:27Z

2

You should close your connection in a finally block. Any exception you encounter before con.Close(); will result in your connection being left open. That could be the error here (depending on the scope of con)
You should not swallow the exception, but log it. Then you can examine the errors properly. If you don't have logging, get logging. But in the meantime, look at the exception you have caught using the debugger.
You should also catch more specific exceptions, so that you can tell the difference between a connectivity error and a chronic bug in the application. Check which exceptions are likely to be thrown by the methods you are calling. For example, con.Open() can throw these:

SqlException

InvalidOperationException

So catch those first, and catch Exception last.

Depending on the type of exception you can return a different error code and/or log at a different level of severity.

Also, figure out a way (using profiling or just copy+paste) to run that query against the database and see what happens. For example, are you sure this part is right:

        com.Parameters.AddWithValue("@date",
            SqlDateTime.Parse(DateTime.Now.ToString())); 
        com.Parameters.AddWithValue("@time",
            SqlDateTime.Parse(DateTime.Now.ToString()));

@date and @time are identical. Are the database values identical too and are the columns both expecting strings?

edited Apr 27, 2012 at 17:40

answered Apr 27, 2012 at 17:07

mafue

1,8981 gold badge22 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aishwarya Shiva Over a year ago

No it doesn't help. I edited my question with exception. Please check it out.

mafue Over a year ago

@AishwaryaShivaPareek Did you do this part: "figure out a way (using profiling or just copy+paste) to run that query against the database." How did you do it and what happened?

Shai Cohen · Accepted Answer · 2012-04-27 17:01:05Z

2

That being said, SQL Profiler is your friend here. Grab the generated sql from profiler and run it in SQL mgmt studio. I am 99% confident the answer will be obvious. If not, then please update your answer with the generated sql from profiler, and we'll take it from there.

If you need some guidance on SQL Profiler, a simple Google search brings up many options: SQL Profiler Search

edited Apr 27, 2012 at 17:01

answered Apr 27, 2012 at 16:15

Shai Cohen

6,2594 gold badges34 silver badges55 bronze badges

3 Comments

Aishwarya Shiva Over a year ago

where to get SQL Profiler? Its not showing on SQL Server 2008 folder in start menu

Aishwarya Shiva Over a year ago

Ya I found the tutorials already. But I want to ask how to get SQL Profiler? The google searches says that it is found in advanced installation of the SQL Server. But is there any direct download executable available for it? I googled it but didn't found any.

Shai Cohen Over a year ago

SQL Profiler is part of SQL Server installation. Or if you have an MSDN subscription, you can download it from there. If none of these apply to you, try googling "SQL Profiler Alternatives".

Erik Philips · Accepted Answer · 2012-04-27 16:14:34Z

1

You need to do some basic debugging. Setup your code like:

catch(Exception ex)
{
  return -1;
}

Set a break point on return, and see what the Exception is.

answered Apr 27, 2012 at 16:14

Erik Philips

54.8k11 gold badges133 silver badges158 bronze badges

3 Comments

Aishwarya Shiva Over a year ago

It going in try block successfully Erik

Shai Cohen Over a year ago

I think Erik wants you to check if your code goes into the catch block and what there error there is, if any.

Erik Philips Over a year ago

But you are returning -1 which is useless. Set a breakpoint on return -1 and then in the debug/watch window see what the ex.Message is.

Diego · Accepted Answer · 2012-04-27 16:16:25Z

0

you are probably not reaching the int r=com.ExecuteNonQuery(); code. Could be an error on the con.Open(); method.

Could you throw a new exception inside your catch and show us the actual error?

answered Apr 27, 2012 at 16:16

Diego

36.5k21 gold badges94 silver badges138 bronze badges

Comments

JoshL · Accepted Answer · 2012-04-27 16:53:47Z

0

A challenge with your current code is that "-1" is returned in several different scenarios:

Exception occurs
result of getDesID is null
INSERT statement fails to insert a row

One simple way to check this would be to return a different number (say -2 or -3) from each point of failure. That way you can determine where the error is being raised without changing your code much or attaching a debugger.

It appears to me that the issue is not likely your SQL INSERT statement - if the statement itself was invalid or violated a database constraint, SQL Server would raise an exception back to you which would be caught by your Exception handler.

answered Apr 27, 2012 at 16:53

JoshL

11k11 gold badges60 silver badges62 bronze badges

7 Comments

Aishwarya Shiva Over a year ago

Actually before writing the -1 logic I did the same thing. I printed an error message under if (r <= 0) condition that "QUERY not returning anything". So, I think the problem is with the ExecuteNonQuery(), but what problem, I am unable to find out that

Shai Cohen Over a year ago

It is not returning anything because you are calling ExecuteNonQuery()! BTW, have you checked SQL itself? Are records being written when you call the code?

JoshL Over a year ago

Shai, ExecuteNonQuery should return the number of rows inserted. See the MSDN documentation here: msdn.microsoft.com/en-us/library/…

Aishwarya Shiva Over a year ago

No the records are not there. Nothing is inserted to the table.

JoshL Over a year ago

Is it possible that [MyForums].[dbo].[Discussions] is a view instead of a table? If the view is filtering out the row that you're attempting to insert, that may explain why SQL Server is failing to insert the row without giving you an error message.

|

Collectives™ on Stack Overflow

SQL Insert Query not executing without throwing any exception in c#

5 Answers 5

2 Comments

3 Comments

3 Comments

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

3 Comments

3 Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related