insert in sql using c#

Question

this code is successfully inserting a new value in a SQL db, but only when I insert constant values.
I need help where it says **(?)** in the code below, where I want to insert new values without specifying constants in the code. What I mean is, I want to be able to type any random value in output window and it gets inserted into the SQL db.

private void InsertInfo()
{
    String strConnection = "Data Source=HP\\SQLEXPRESS;database=MK;Integrated Security=true";

    SqlConnection con = new SqlConnection(strConnection);
    string connetionString = null;
    SqlConnection connection ;
    SqlDataAdapter adapter = new SqlDataAdapter();

    connetionString = @"Data Source=HP\SQLEXPRESS;database=MK;Integrated Security=true";
    connection = new SqlConnection(connetionString);
    string sql = "insert into record (name,marks) **values( ?))";**

    try
    {
        connection.Open();
        adapter.InsertCommand = new SqlCommand(sql, connection);
        adapter.InsertCommand.ExecuteNonQuery();
        MessageBox.Show ("Row inserted !! ");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

private void insert_Click(object sender, EventArgs e)
{
    InsertInfo();
}

Agree with @AlexeyRaga. Your code is vulnerable to SQL Injection. — SchmitzIT
– SchmitzIT, Commented Feb 25, 2013 at 8:26
@SchmitzIT the code shown in the question is not vulnerable to SQL injection; it does not show any data values being concatenated into TSQL. It shows some incomplete and not working SQL, but that is fine -we can help with that. Please don't be alarmist. — Marc Gravell
– Marc Gravell, Commented Feb 25, 2013 at 8:28
SQL injections didn't concern me, I just pointed to the post where this question is answered, the solution is given and the example is shown :) — Alexey Raga
– Alexey Raga, Commented Feb 25, 2013 at 8:31

Marc Gravell · Accepted Answer · 2013-02-25 08:24:06Z

2

There is no need to use an adapter here; that is not helping you. Just:

var name = ...
var marks = ...
using(var conn = new SqlConnection(connectionString))
using(var cmd = conn.CreateCommand()) {
    cmd.CommandText = "insert into record (name, marks) values (@name, @marks)";
    cmd.Parameters.AddWithValue("name", name);
    cmd.Parameters.AddWithValue("marks", marks);
    conn.Open();
    cmd.ExecuteNonQuery();
}

or with a tool like "dapper":

var name = ...
var marks = ...
using(var conn = new SqlConnection(connectionString)) {
    conn.Open();
    conn.Execute("insert into record (name, marks) values (@name, @marks)",
        new {name, marks});
}

answered Feb 25, 2013 at 8:24

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zo Has · Accepted Answer · 2013-02-25 08:34:04Z

0

Those '?' are termed as parameters. From what I understand, you are wanting to use a parametrized query for your insert which is a good approach as they save you from chance of a SQL injection. The '?' sing in your query is used when you are using an

OLEDBConnection & Command object.

Normally, you would use '@' symbol to specify a parameter in your query. There is no need for an adapter. You just

//Bind parameters
// Open your Connection
// Execute your query
// Close connection
// return result

Parametrized queries 4 Guys from Rolla

MSDN: How to Protect from SQL injection in ASP.NET

answered Feb 25, 2013 at 8:34

Zo Has

13.1k24 gold badges92 silver badges150 bronze badges

Collectives™ on Stack Overflow

insert in sql using c#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related