CODEWITHSUNDEEP
c#sqlasp.netsql-server
Ahmed Mujtaba
Ahmed Mujtaba

Reputation: 2248

SQL query not getting executed

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?

Upvotes: 0

Views: 461

Answers (4)

JC Borlagdan
JC Borlagdan

Reputation: 3628

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.

Upvotes: 0

Steve
Steve

Reputation: 216313

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

Upvotes: 1

Chris Wijaya
Chris Wijaya

Reputation: 1286

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

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

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");

Upvotes: 1

Related Questions