How to insert data into SQL Server

Question

What the problem on my coding? I cannot insert data to ms sql.. I'm using C# as front end and MS SQL as databases...

name = tbName.Text;
userId = tbStaffId.Text;
idDepart = int.Parse(cbDepart.SelectedValue.ToString());

string saveStaff = "INSERT into tbl_staff (staffName,userID,idDepartment) " +
                   " VALUES ('" + name + "', '" + userId +"', '" + idDepart + "');";

SqlCommand querySaveStaff = new SqlCommand(saveStaff);

try
{
querySaveStaff.ExecuteNonQuery();
}
catch
{
//Error when save data

MessageBox.Show("Error to save on database");
openCon.Close();
Cursor = Cursors.Arrow;
}

You didn't type `22', 1); DELETE FROM tbl_staff; --` exactly right in your `tbStaffId` TextBox. Once you've done this, read up on SQL injection. — lc., Sep 03 '12 at 01:37
Thanks for help.. When i run a program, this error i get.. "ExecuteNonQuery: Connection property has not been initialized." — Azri Zakaria, Sep 03 '12 at 01:40
Sorry... im newbie in C#.. Can someone explain me, what functions for parametes? — Azri Zakaria, Sep 03 '12 at 01:51

score 42 · Accepted Answer · edited Nov 01 '19 at 15:39

42

You have to set Connection property of Command object and use parametersized query instead of hardcoded SQL to avoid SQL Injection.

 using(SqlConnection openCon=new SqlConnection("your_connection_String"))
    {
      string saveStaff = "INSERT into tbl_staff (staffName,userID,idDepartment) VALUES (@staffName,@userID,@idDepartment)";

      using(SqlCommand querySaveStaff = new SqlCommand(saveStaff))
       {
         querySaveStaff.Connection=openCon;
         querySaveStaff.Parameters.Add("@staffName",SqlDbType.VarChar,30).Value=name;
         .....
         openCon.Open();

         querySaveStaff.ExecuteNonQuery();
       }
     }

edited Nov 01 '19 at 15:39

Alexei Levenkov

98,904
14
127
179

answered Sep 03 '12 at 01:42

KV Prajapati

93,659
19
148
186

1

Thanks my friends... is Done! Sorry im newbie in C#...:) – Azri Zakaria Sep 03 '12 at 01:44
Sorry... im newbie in C#.. what functions for parametes? – Azri Zakaria Sep 03 '12 at 01:51
To avoid [SQL Injection](http://msdn.microsoft.com/en-us/library/ms161953(v=sql.105).aspx) – KV Prajapati Sep 03 '12 at 01:55
2

@Chuki2: note that this correct answer does not have a try/catch block, and _does_ have `using` blocks. You should follow this example. – John Saunders Sep 03 '12 at 03:53
Thanks @JohnSaunders, i will do your suggestion...:) – Azri Zakaria Sep 04 '12 at 02:47
3

An explicit Close() is not needed on a connection object that is in a using clause – Aug 15 '16 at 21:29
3

`.AddWithValue()` is recommended. – Si8 Dec 21 '17 at 15:23
I get why an explicit Close() is not needed with `using`, but I don't understand why a try-catch is not needed either (according to @JohnSaunders 's comment). – Floella Sep 04 '19 at 23:21
@Floella Because you should almost never use a try/catch block. I don't have time to educate you on proper exception handling, but here's a thought process you can use. Pretend there is no exception handling at all in the program. Now, what's the worst that could happen? You know that an exception in a method will "bubble up" to the caller of the method if not handled. So if there's no exception handling, any exception will bubble up to the top level of your program, and will probably terminate the process. If you use `using` blocks properly, you will have no lost resources. – John Saunders Sep 06 '19 at 00:22
@Floella OTOH your end users won't see a friendly error message from the exception, and the exception may not be logged to help in troubleshooting. So, at the outermost layers in your program, you want a try/catch block to log the exception, and maybe to say "sorry, we got some kind of error" to the end user. So, in general, use as little exception handling as possible to still meet your requirements. Having try/catch blocks at every level is the worst thing you can do. You'll have better results doing nothing at all, then adding exception handling only where absolutely necessary. – John Saunders Sep 06 '19 at 00:27
@Floella Finally, a hint. When I first start working on a piece of code, one of the first things I'll do is check to see if there is too much exception handling. If too much, I make a copy of the code and I remove almost all try/catch blocks. I then ask a team lead, "please tell me why the code isn't better off with no exception handling?" Sometimes there's a good answer to that question. Most often, there isn't. – John Saunders Sep 06 '19 at 00:32
@adatapost please verify my edit that added missing `querySaveStaff.ExecuteNonQuery();` (without it you get copy-pastes that don't work like https://stackoverflow.com/questions/58661745/how-to-transfer-data-from-a-class-to-an-sql-serer-table#58661745) – Alexei Levenkov Nov 01 '19 at 15:40

score 35 · Answer 2 · edited Oct 13 '13 at 18:50

I think you lack to pass Connection object to your command object. and it is much better if you will use command and parameters for that.

using (SqlConnection connection = new SqlConnection("ConnectionStringHere"))
{
    using (SqlCommand command = new SqlCommand())
    {
        command.Connection = connection;            // <== lacking
        command.CommandType = CommandType.Text;
        command.CommandText = "INSERT into tbl_staff (staffName, userID, idDepartment) VALUES (@staffName, @userID, @idDepart)";
        command.Parameters.AddWithValue("@staffName", name);
        command.Parameters.AddWithValue("@userID", userId);
        command.Parameters.AddWithValue("@idDepart", idDepart);

        try
        {
            connection.Open();
            int recordsAffected = command.ExecuteNonQuery();
        }
        catch(SqlException)
        {
            // error here
        }
        finally
        {
            connection.Close();
        }
    }
}

I think the finally is not required, the purpose of a using statement is to dispose of the connection even in the event of an exception https://msdn.microsoft.com/en-us/library/yh598w02.aspx, am I missing something? — Des Horsley, Nov 24 '15 at 23:02

score -5 · Answer 3 · edited Dec 24 '13 at 19:40

-5

string saveStaff = "INSERT into student (stud_id,stud_name) " + " VALUES ('" + SI+ "', '" + SN + "');";
cmd = new SqlCommand(saveStaff,con);
cmd.ExecuteNonQuery();

edited Dec 24 '13 at 19:40

p.s.w.g

146,324
30
291
331

answered Dec 24 '13 at 19:18

srinivas

15
1

8

This one is open to SQL injection attack. You should use Command.Parameters for the parameters. – S.L. Barth is on codidact.com Dec 24 '13 at 19:41
4

Avoid this example. As S.L.Barth mentioned, this practice is open to SQL injections – Oden May 06 '15 at 07:47

How to insert data into SQL Server

3 Answers3

Linked