38

I'm having an issue at the moment which I am trying to fix. I just tried to access a database and insert some values with the help of C#

The things I tried (worked)

String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES ('abc',      'abc', 'abc', 'abc')";

A new line was inserted and everything worked fine, now I tried to insert a row using variables:

String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id, @username, @password, @email)";

command.Parameters.AddWithValue("@id","abc")
command.Parameters.AddWithValue("@username","abc")
command.Parameters.AddWithValue("@password","abc")
command.Parameters.AddWithValue("@email","abc")

command.ExecuteNonQuery();

Didn't work, no values were inserted. I tried one more thing

command.Parameters.AddWithValue("@id", SqlDbType.NChar);
command.Parameters["@id"].Value = "abc";

command.Parameters.AddWithValue("@username", SqlDbType.NChar);
command.Parameters["@username"].Value = "abc";

command.Parameters.AddWithValue("@password", SqlDbType.NChar);
command.Parameters["@password"].Value = "abc";

command.Parameters.AddWithValue("@email", SqlDbType.NChar);
command.Parameters["@email"].Value = "abc";

command.ExecuteNonQuery();

May anyone tell me what I am doing wrong?

Kind regards

EDIT:

in one other line I was creating a new SQL-Command

var cmd = new SqlCommand(query, connection);

Still not working and I can't find anything wrong in the code above.

Kaf
  • 33,101
  • 7
  • 58
  • 78
voskart
  • 767
  • 1
  • 7
  • 15
  • 12
    Where are you setting [`command.CommandText`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtext(v=vs.110).aspx)? – canon Nov 13 '13 at 14:39
  • Have you tried adding your parameters (in the Parameters.Add) without the @? E.g. `command.Parameters.AddWithValue("id","abc") ` – Sam Nov 13 '13 at 14:42
  • 2
    ^ he's saying you need `command.CommandText = query` otherwise your query isn't associated with command at all – Jonesopolis Nov 13 '13 at 14:42
  • 2
    You said `@password` - but you meant `@saltedPasswordHash`, right? ;p – Marc Gravell Nov 13 '13 at 14:44
  • 1
    What is your error message from DB? IMHO U have primary unique key and row with this value is already set. Sou try change id for another string than 'abc' – MartinB Nov 13 '13 at 14:45
  • I've edited the original entry. I am creating a new command and adding the query into it, should work this way as well I suppose. – voskart Nov 13 '13 at 14:57

8 Answers8

65

I assume you have a connection to your database and you can not do the insert parameters using c #.

You are not adding the parameters in your query. It should look like:

String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id,@username,@password, @email)";

SqlCommand command = new SqlCommand(query, db.Connection);
command.Parameters.Add("@id","abc");
command.Parameters.Add("@username","abc");
command.Parameters.Add("@password","abc");
command.Parameters.Add("@email","abc");

command.ExecuteNonQuery();

Updated:

using(SqlConnection connection = new SqlConnection(_connectionString))
{
    String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id,@username,@password, @email)";

    using(SqlCommand command = new SqlCommand(query, connection))
    {
        command.Parameters.AddWithValue("@id", "abc");
        command.Parameters.AddWithValue("@username", "abc");
        command.Parameters.AddWithValue("@password", "abc");
        command.Parameters.AddWithValue("@email", "abc");

        connection.Open();
        int result = command.ExecuteNonQuery();

        // Check Error
        if(result < 0)
            Console.WriteLine("Error inserting data into Database!");
    }
}
Andrew Paes
  • 1,940
  • 1
  • 15
  • 20
  • 9
    Please note the overload of `Add()` shown here has been deprecated for a while. – Lasse Christiansen Sep 04 '14 at 12:58
  • 2
    Link documenting the deprecation: http://msdn.microsoft.com/en-us/library/9dd8zze1(v=vs.110).aspx – Lasse Christiansen Sep 04 '14 at 13:19
  • you need to open a connection before `ExecuteNonQuery` – Yar Jul 09 '15 at 05:23
  • 4
    If Add() is deprecated, then what's the new way to insert? – christianleroy Sep 18 '15 at 01:48
  • MSDN documentation re: deprecated Add() replacement: https://www.google.de/?gws_rd=ssl#q=sqlparametercollection.add+method+depreciation+replacement – infowanna Oct 02 '15 at 10:13
  • 3
    Add() is deprecated, Try AddWithValue – Shahdat Nov 28 '16 at 21:48
  • Its best practice to put the SqlCommand (and the SqlConnection) in a "using" statement. – Ronnie Jan 11 '17 at 09:43
  • 6
    Note for those reading these comments: Only `Add(parameterName, value)` is deprecated. The [`Add(parameterName, sqlDbType)`](https://msdn.microsoft.com/en-us/library/wbys3e9s(v=vs.110).aspx) and similar methods that specify the data type are perfectly fine and not going anywhere. For example, you can still use `command.Parameters.Add("@username",SqlDbType.NChar).Value = "abc"`. What's deprecated is *not specifying the parameter's data type*. – Bacon Bits Jun 21 '17 at 21:13
11

Try

String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id,@username, @password, @email)";
using(SqlConnection connection = new SqlConnection(connectionString))
using(SqlCommand command = new SqlCommand(query, connection))
{
    //a shorter syntax to adding parameters
    command.Parameters.Add("@id", SqlDbType.NChar).Value = "abc";

    command.Parameters.Add("@username", SqlDbType.NChar).Value = "abc";

    //a longer syntax for adding parameters
    command.Parameters.Add("@password", SqlDbType.NChar).Value = "abc";

    command.Parameters.Add("@email", SqlDbType.NChar).Value = "abc";

    //make sure you open and close(after executing) the connection
    connection.Open();
    command.ExecuteNonQuery();
}
Jabberwocky
  • 768
  • 7
  • 18
theLaw
  • 1,261
  • 2
  • 11
  • 23
  • I am already using the exact same code. I am opening a connection, creating a SQLCommand with the query and the connection and executing the same steps. No clue why it is not working – voskart Nov 13 '13 at 14:59
  • I'm sorry, didn't see that you used the Add method. It did work now. I have no clue why this is the only way but I am glad that it does finally work. Thanks so much – voskart Nov 13 '13 at 15:06
  • 4
    There is no need to call `connection.Close()` if your `SqlConnection` object is in using block, `Dispose()` will do that for you. – hkarask Jan 29 '15 at 08:22
5

The most common mistake (especially when using express) to the "my insert didn't happen" is : looking in the wrong file.

If you are using file-based express (rather than strongly attached), then the file in your project folder (say, c:\dev\myproject\mydb.mbd) is not the file that is used in your program. When you build, that file is copied - for example to c:\dev\myproject\bin\debug\mydb.mbd; your program executes in the context of c:\dev\myproject\bin\debug\, and so it is here that you need to look to see if the edit actually happened. To check for sure: query for the data inside the application (after inserting it).

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • OP says non parameterized query works fine. Since `abc` has already been inserted for username, database may not allow `abc` again if there is a unique key constraint set up for username. – Kaf Nov 13 '13 at 14:50
  • @Kaf hmmm; interesting - but you would expect a unique constraint violation as an exception in that case – Marc Gravell Nov 13 '13 at 14:54
2
static SqlConnection myConnection;

    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        myConnection = new SqlConnection("server=localhost;" +
                                                      "Trusted_Connection=true;" +
             "database=zxc; " +
                                                      "connection timeout=30");
        try
        {

            myConnection.Open();
            label1.Text = "connect successful";

        }
        catch (SqlException ex)
        {
            label1.Text = "connect fail";
            MessageBox.Show(ex.Message);
        }
    }

    private void Form1_Load(object sender, EventArgs e)
    {

    }

    private void button2_Click(object sender, EventArgs e)
    {
        String st = "INSERT INTO supplier(supplier_id, supplier_name)VALUES(" + textBox1.Text + ", " + textBox2.Text + ")";
        SqlCommand sqlcom = new SqlCommand(st, myConnection);
        try
        {
            sqlcom.ExecuteNonQuery();
            MessageBox.Show("insert successful");
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
robin
  • 25
  • 2
0
private void button1_Click(object sender, EventArgs e)
    {
        String query = "INSERT INTO product (productid, productname,productdesc,productqty) VALUES (@txtitemid,@txtitemname,@txtitemdesc,@txtitemqty)";
        try
        {
            using (SqlCommand command = new SqlCommand(query, con))
            {

                command.Parameters.AddWithValue("@txtitemid", txtitemid.Text);
                command.Parameters.AddWithValue("@txtitemname", txtitemname.Text);
                command.Parameters.AddWithValue("@txtitemdesc", txtitemdesc.Text);
                command.Parameters.AddWithValue("@txtitemqty", txtitemqty.Text);


                con.Open();
                int result = command.ExecuteNonQuery();

                // Check Error
                if (result < 0)
                    MessageBox.Show("Error");

                MessageBox.Show("Record...!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                con.Close();
                loader();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            con.Close();
        }
    }
jps
  • 20,041
  • 15
  • 75
  • 79
0
public static string textDataSource = "Data Source=localhost;Initial 
Catalog=TEST_C;User ID=sa;Password=P@ssw0rd";
public static bool ExtSql(string sql) {
    SqlConnection cnn;
    SqlCommand cmd;
    cnn = new SqlConnection(textDataSource);
    cmd = new SqlCommand(sql, cnn);
    try {
        cnn.Open();
        cmd.ExecuteNonQuery();
        cnn.Close();
        return true;
    }
    catch (Exception) {
        return false;
    }
    finally {
        cmd.Dispose();
        cnn = null;
        cmd = null; 
    }
}
Sam
  • 1,542
  • 2
  • 13
  • 27
0

I have just wrote a reusable method for that, there is no answer here with reusable method so why not to share...
here is the code from my current project:

public static int ParametersCommand(string query,List<SqlParameter> parameters)
{
    SqlConnection connection = new SqlConnection(ConnectionString);
    try
    {
        using (SqlCommand cmd = new SqlCommand(query, connection))
        {   // for cases where no parameters needed
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters.ToArray());
            }

            connection.Open();
            int result = cmd.ExecuteNonQuery();
            return result;
        }
    }
    catch (Exception ex)
    {
        AddEventToEventLogTable("ERROR in DAL.DataBase.ParametersCommand() method: " + ex.Message, 1);
        return 0;
        throw;
    }

    finally
    {
        CloseConnection(ref connection);
    }
}

private static void CloseConnection(ref SqlConnection conn)
{
    if (conn.State != ConnectionState.Closed)
    {
        conn.Close();
        conn.Dispose();
    }
}
Jonathan Applebaum
  • 5,738
  • 4
  • 33
  • 52
-2
class Program
{
    static void Main(string[] args)
    {
        string connetionString = null;
        SqlConnection connection;
        SqlCommand command;
        string sql = null;

        connetionString = "Data Source=Server Name;Initial Catalog=DataBaseName;User ID=UserID;Password=Password";
        sql = "INSERT INTO LoanRequest(idLoanRequest,RequestDate,Pickupdate,ReturnDate,EventDescription,LocationOfEvent,ApprovalComments,Quantity,Approved,EquipmentAvailable,ModifyRequest,Equipment,Requester)VALUES('5','2016-1-1','2016-2-2','2016-3-3','DescP','Loca1','Appcoment','2','true','true','true','4','5')";
        connection = new SqlConnection(connetionString);

        try
        {
            connection.Open();
            Console.WriteLine(" Connection Opened ");
            command = new SqlCommand(sql, connection);                
            SqlDataReader dr1 = command.ExecuteReader();         

            connection.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Can not open connection ! ");
        }
    }
}
ManoDestra
  • 6,325
  • 6
  • 26
  • 50
  • 2
    For **Inserting** you don't need to use execute reader , instead of reader we should use **ExecuteNonquery** for insert , update and delete to verify the affected rows.. Using block and parameterized queries are explained by the **@theLaw**.. parameterized queries is very effective in case of sql injections and if we use Using Block we don't need to bother about closing connections.. – Sabareeshwari Kannan Sep 02 '16 at 04:25