SQL query not getting executed

Question

I'm trying to execute a SQL command that inserts values into the table. The code is as below:

    public static bool Add(string username, string friend_username, int status, string msg, string key, string reject_key)
{
    using (SqlConnection con = new SqlConnection(Config.ConnectionString))
    {
        con.Open();
        StringBuilder query = new StringBuilder();
        query.Append("Insert into friends(username,friend_username,status,msg,date_added,val_key,reject_key)values");
        query.Append("(@username,@friend_username,@status,@msg,@date_added,@key,@reject_key)");

        using (SqlCommand cmd = new SqlCommand(query.ToString(), con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@username", username));
            cmd.Parameters.Add(new SqlParameter("@friend_username", friend_username));
            cmd.Parameters.Add(new SqlParameter("@status", status));
            cmd.Parameters.Add(new SqlParameter("@msg", msg));
            cmd.Parameters.Add(new SqlParameter("@date_added", DateTime.Now));
            cmd.Parameters.Add(new SqlParameter("@key", key));
            cmd.Parameters.Add(new SqlParameter("@reject_key", reject_key));
            cmd.ExecuteNonQuery();
        }
    }
    return true;
}

The 'Add' function gets called here :

    private void Process_Approve_Action(int mtype, long groupid, long content_id, string usr)
{
    // approval status = 0(
    int status = 0;
    switch (mtype)
    {
        case 4: // friend invitation
            string request_username = usr;
            string friend_username = Page.User.Identity.Name;

            //FriendsBLL.Update_Status(request_username, friend_username, 0);
            //// also add invited user as their own friend
            FriendsBLL.Add(friend_username, request_username, status, "", "", "");
            Config.ShowMessageV2(msg, Resources.vsk.message_inbox_06, "Success!", 1); //Friend invitation accepted.
            break;

    }

I've tried debugging the Add function and the debugger doesn't go past 'cmd.ExecuteNonQuery();' and breaks out

What am I doing wrong here?

What do you mean with _breaks out_? Did you get any exception with a precise error message? — Steve, Oct 25 '15 at 21:53
@Steve I'm not getting any error message. The debugger doesn't go to the next line and nothing gets added to the table — Ahmed Mujtaba, Oct 25 '15 at 21:54
I would try to change the line that add the status parameter to _ _cmd.Parameters.Add("@status", SqlDbType.Int).Value = status;_ — Steve, Oct 25 '15 at 22:01
@AhmedMujtaba Very important. Does your connection string contain `|DataDirectory| ` or something similar? Are you sure in debug/release you connect to the same DB — Lukasz Szozda, Oct 25 '15 at 22:01
@lad2025 Here's my connection string : I have no issues with executing other SQL query functions. This function I have in the same manner as the others but for some reason it doesn't work — Ahmed Mujtaba, Oct 25 '15 at 22:03
Maybe it is executing but against the wrong database or it just doesn't insert anything. You should try to narrow your symptom and work out if it is doing this or not — Nick.Mc, Oct 25 '15 at 22:32
Add a `Try....Catch` block and see if an exception is being thrown — keyboardP, Oct 25 '15 at 22:55

score 1 · Answer 1 · answered Oct 25 '15 at 21:49

1

Add space before VALUES because you have syntax error in SQL:

query.Append("Insert into friends(username,friend_username,status,msg,date_added,val_key,reject_key) values");

answered Oct 25 '15 at 21:49

Lukasz Szozda

162,964
23
234
275

I removed the space before VALUES but it still doesn't work – Ahmed Mujtaba Oct 25 '15 at 21:56
@AhmedMujtaba Could you be more specific with doesn't work? – Lukasz Szozda Oct 25 '15 at 21:57
query isn't getting executed. Nothing get's added to the table even though I have all the parameters with values. – Ahmed Mujtaba Oct 25 '15 at 21:59
@AhmedMujtaba And no **error message/warning/stacktrace/debug info**? – Lukasz Szozda Oct 25 '15 at 21:59
No I get no errors. The strange thing is that the same function works fine when I call it from another class. – Ahmed Mujtaba Oct 25 '15 at 22:01
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/93315/discussion-between-ahmed-mujtaba-and-lad2025). – Ahmed Mujtaba Oct 25 '15 at 22:11

score 1 · Answer 2 · answered Oct 25 '15 at 22:11

The SqlParameter class has many constructors, but two constructors are well know to cause problems when the parameter value is an integer with a zero value. (And in your example the status variable has a zero value)

The first constructor takes a string and an object, the second one takes a string and an SqlDbType. This scenario leads to the following problem.

If you call the SqlParameter constructor and pass an integer with a value of zero the constructor called is the one that consider your parameter an SqlDbType of value zero. And you end up with a parameter with a NULL value.

If the column of your database doesn't accept a NULL value you have a big time scratching your head to understand why your code fails.

I have started to use always this kind of syntax when adding parameters

 cmd.Parameters.Add("@status", SqlDbType.Int).Value = status;

By the way, the MSDN explain it as well

score 0 · Answer 3 · edited May 23 '17 at 12:03

0

Have a look at this SQL Insert Query Using C#

Some say you need to open the connection right before execute non query. Also found that your cmd.Parameters.Add() function is deprecated, use cmd.Parameters.AddWithValue() instead.

Complete doco: https://msdn.microsoft.com/en-us/library/9dd8zze1(v=vs.110).aspx

edited May 23 '17 at 12:03

Community

1
1

answered Oct 25 '15 at 22:00

Chris Wijaya

1,276
3
16
34

1

Also AddWithValue has a lot of problems http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ , I would not suggest to use it – Steve Oct 25 '15 at 22:26

score 0 · Answer 4 · answered Oct 28 '15 at 02:11

Use

cmd.Parameters.Add("@PARAMETERNAME", SqlDbType.VarChar).Value = variableName;

Another possible issue is where your connection string is placed

using(SqlConnection con = new SqlConnection("Your Connection String Here");


 NOTE: if you have this kind of code you should no longer be doing the

con.Open() since you already placed it in a using() which indicates that if there's an open connection you use it, if not, open a connection.

SQL query not getting executed

4 Answers4