How to Pass Variable into SQLcommand Statement and Insert into Database Table

How to pass variable into SqlCommand statement and insert into database table

Add a parameter to the command before executing it:

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

c# variable into sql command

Here's just one very simple method:

var yourTextValue = "this is text";
using (var db = new SqlConnection()) {
    db.Open();

    var command =
        new SqlCommand("INSERT INTO dokimastikospinakas (pliroforia) VALUES (@textValue);", db);
    command.Parameters.AddWithValue("@textValue", yourTextValue);

    command.ExecuteNonQuery();
}

EDIT: You'd actually need some connection string for SqlConnection constructor, of course. And modified variable names by popular demand.

Trying to insert int variable into SQL Server table

Hope productId is not the primary key of the basket table.

Then,instead of

 SqlCommand cmd = new SqlCommand("insert into basket (productId, email) values( productId,'" + email + "')", con);

Modify like below

 SqlCommand cmd = new SqlCommand("insert into basket (productId, email) values( @productId,@email)", con);
cmd.Parameters.Add(new SqlParameter("@productId",productId );
cmd.Parameters.Add(new SqlParameter("@email",email );

Why suggested to modify is to avoid SQLInjection attack. If you are unaware about that please go through the below link and learn it

https://en.wikipedia.org/wiki/SQL_injection

Variable number of inserts into database

If you are going to do it one-by-one, this would be the pattern. Not shown is robust data validation or exception handling. You must also change the parameters to meet your needs, they obviously won't (all) be varchar of length 10. This is partly psuedo code:

using (SqlConnection con = new SqlConnection("the connection string"))
using (SqlCommand cmd = con.CreateCommand())
{
    cmd.CommandType = CommandType.Text; // assuming not a stored procedure
    
    cmd.CommandText = "INSERT INTO [Your_Table_Name] (val1, val2, etc.) VALUES (@param1, @param2, etc.);"
    
    cmd.Parameters.Add(new SqlParameter("@param1", SqlDbType.VarChar, 10));
    cmd.Parameters.Add(new SqlParameter("@param2", SqlDbType.VarChar, 10));
    //etc.
    
    con.Open();
    
    for (int i = 0; i < row count; i++)
    {
        cmd.Parameters["param 1"].Value = //whatever value from row
        cmd.Parameters["param 2"].Value = //whatever other value from row
        //etc
        
        int numberOfRowsAffected =  cmd.ExecuteNonQuery();
    }
}

Passing values from variables to Insert query in C#, ASP.NET for SQL Server database

"(Select [SubCategory].ID from SubCategory where Kategorie = @SubCategory)," +
"(SELECT [BusinessSector5].ID FROM BusinessSector5 where Description_DE = @BusinessSector5));",con))

remove quotes from your query

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 to insert the row into the table of SQL database?

There are many problems in the code shown. The most important ones are the way in which you read the values and how do you try to insert in the data. Inside the form TypeService you create a new instance of the AllDataDB class where you store the results. This instance is no more the one you have passed in input, so you need to read the values from the instance created by the TypeService form (stored in the global field Class). The second problem is the string concatenation of your values. This is a well known problem leading to parsing problems and sql injection. It is fixed by a parameterized query as shown below

private void NewServe_Click(object sender, EventArgs e)
{ 
    // Do no pass an instance of Class here, just pass null 
    // or remove it at all if you don't plan to reuse the form for updating 
    TypeService form = new TypeService(null);
    if (form.ShowDialog() == DialogResult.OK)
    { 
        // Add the using statement to ensure a proper release of resources.
        using SqlConnection Con = new SqlConnection(connectionString);
        Con.Open();
        // Parameterized query
        string Que = "INSERT INTO type_service VALUES(@id,@name,@price);";
        SqlCommand cmd = new SqlCommand(Que, Con);
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = form.Class.id_serv;    
        cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = form.Class.name;
        cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = form.Class.price;
        cmd.ExecuteNonQuery();

        SqlDataAdapter sqlDa = new SqlDataAdapter("SELECT * FROM type_service", Con);
        DataTable d = new DataTable(); 
        sqlDa.Fill(d);
        dataGridView3.AutoGenerateColumns = false;
        dataGridView3.DataSource = d;
    }
}

There are other minor problems. In the TypeService form and in the class AllDataDB you have used global fields instead of the more flexible way of using properties.

public class AllDataDB
{ 
    public int id_serv {get;set;}
    public double price {get;set;}
    public string name {get;set;}
}

as well

public AllDataDB Class {get;set;}

also, given the null passed to the constructor of the TypeService form you now need to ensure to not use that Class properties without cheching for its null value

SQL server: insert into using select and other parameter

I think the third sql statement should be:

INSERT INTO [project_manager] (project_id, manager_id) SELECT project_id, @ID FROM [projects] WHERE project_name = @Name

---

Related Topics