SQL Insert Query Using C#

SQL Insert Query Using C#

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

How do I to insert data into an SQL table using C# as well as implement an upload function?

You should use parameters in your query to prevent attacks, like if someone entered '); drop table ArticlesTBL;--' as one of the values.

string query = "INSERT INTO ArticlesTBL (ArticleTitle, ArticleContent, ArticleType, ArticleImg, ArticleBrief,  ArticleDateTime, ArticleAuthor, ArticlePublished, ArticleHomeDisplay, ArticleViews)";
query += " VALUES (@ArticleTitle, @ArticleContent, @ArticleType, @ArticleImg, @ArticleBrief, @ArticleDateTime, @ArticleAuthor, @ArticlePublished, @ArticleHomeDisplay, @ArticleViews)";

SqlCommand myCommand = new SqlCommand(query, myConnection);
myCommand.Parameters.AddWithValue("@ArticleTitle", ArticleTitleTextBox.Text);
myCommand.Parameters.AddWithValue("@ArticleContent", ArticleContentTextBox.Text);
// ... other parameters
myCommand.ExecuteNonQuery();

(xkcd)

Insert string to SQL using C#

You are not setting the value of parameter.You should either Use Parametrs.Add like this:

command.Parameters.Add("@IP", SqlDbType.VarChar).Value = "0.0.0.0";
//equals to:
//command.Parameters.Add("@IP", SqlDbType.VarChar); 
//command.Parameters["@IP"].Value = "0.0.0.0";

or (not recommended):

command.Parameters.AddWithValue("@IP", "0.0.0.0");

SQL Insert statement syntax

Looks like your last sql statement is not correct. Maybe the reason is usage of apostrophe in your sql stament, but you should not care about it. I explan in the middle of my answer why you shouldn't care.

SqlCommand command1 = new SqlCommand("INSERT INTO CurrentSit VALUES (" + sitid1 + ",'" + incident1.ToString("YYYY-mm-DD") + "', '" + nature1 + "', '" + name1 + "', '" + charges1 + "'", con);

To find out what is exactly the problem, you can specify column names instead. But I suggest you to use parameterized query not even necessary to specify your column names.

SqlCommand command1 = new SqlCommand("INSERT INTO CurrentSit VALUES(@sitid1, @incident1, @nature1, @name1, @charges1)", con);

command1.Parameters.AddWithValue("@sitid1", sitid1);
command1.Parameters.AddWithValue("@incident1", incident1.ToString("YYYY-mm-DD"));
command1.Parameters.AddWithValue("@nature1", nature1);
command1.Parameters.AddWithValue("@name1", name1);
command1.Parameters.AddWithValue("@charges1", charges1);

command1.ExecuteNonQuery();

You should always use parameterized queries. This kind of codes are open for SQL Injection attacks.

Also as Marc mentioned, there is no point to use ExecuteReader() for this sql statement because it is only INSERT data, doesn't returns any data. Because of that, you only need to use ExecuteNonQuery() in this case.

How to insert data to a database in C#

Because you haven't executed your command:

....
cmd.Parameters.AddWithValue("@lname", textBox2.Text);

cmd.ExecuteNonQuery();

The fixed version of your insert statement should be something like this:

"INSERT INTO tesTable (fname,lname) VALUES (@fname,@lname)"

This also needs to be added, that specifying the type directly and use the Value property is more better than AddWithValue:

cmd.Parameters.Add("@lname", SqlDbType.VarChar).Value = textBox2.Text;

Can we stop using AddWithValue() already?

Insert Records into SQL Server Database using C# Data Objects

You can insert the record into SQL Database without need for DataAdapter just by using Command object as shown in the following code snippet (just pass your Insert SQL statement string):

void SqlExecuteCommand(string InsertSQL)
{
    try
    {
        using (SqlConnection _connSqlCe = new SqlConnection("Conn String to SQL DB"))
        {
            _connSql.Open();
            using (SqlCommand _commandSqlCe = new SqlCommand(CommandSQL, _connSql))
            {
                _commandSql.CommandType = CommandType.Text;
                _commandSql.ExecuteNonQuery();
            }
        }
    }
    catch { throw; }
}

The general format of SQL INSERT query string is shown below:

INSERT INTO YourTable (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

You can further extend this solution by adding parameters to the SQL String/Command in order to protect against possibility of SQL injection (see the following example):

INSERT INTO YourTable (column1,column2,column3,...)
VALUES (@param1,@param2,@param3,...);

_commandSql.Parameters.Add("@param1","abc");
_commandSql.Parameters.Add("@param2","def");
_commandSql.Parameters.Add("@param3","ghijklm");

You can also use the alternative syntax for SQL Parameters, like for e.g.:

_commandSql.Parameters.Add("@param1", SqlDbType.NChar).Value = "abc";
_commandSql.Parameters.Add("@param2", SqlDbType.NChar).Value = "def";
_commandSql.Parameters.Add("@param3", SqlDbType.NVarChar).Value = "ghijklm";

Pertinent to your particular question, it should be like:

"INSERT INTO xxMyDatabasexx (ParticipantID, Verifier, Notes, [Date]) VALUES  (@participantID, @userVerify, @notes, @dt)"

 _commandSql.Parameters.Add("@ParticipantID",SqlDbType.NChar).Value= participantID;
 _commandSql.Parameters.Add("@userVerify",SqlDbType.NChar).Value= userVerify ;
 _commandSql.Parameters.Add("@notes",SqlDbType.NVChar).Value= properRow ;
 _commandSql.Parameters.Add("@dt",SqlDbType.DateTime).Value= DateTime.Now;

Note: in case ParticipantID is the IDENTITY (i.e. Autonumber) Column, then do not include it in INSERT statement.

Hope this may help.

---

Related Topics