CODEWITHSUNDEEP
c#.netsql-serverdatabase
Mayuri Koul
Mayuri Koul

Reputation: 55

insert in sql using c#

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();
}

Upvotes: 0

Views: 2037

Answers (2)

Zo Has
Zo Has

Reputation: 13018

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

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062494

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

Upvotes: 2

Related Questions