execute sql command in asp.net

Use parameterized queries. Here is your statement rewritten to make use of them. I replaced your try/finally with a using block although your try/finally was acceptable.

Parameterized queries prevent errors and Sql Injection Attacks. An error could occur in your existing code if I were to submit a tick as a part of my user name or password. In the current form this would result in an exception. This is because the tick character is used to quote strings in sql syntax.

using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection())
{
    connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Etay\Documents\Visual Studio 2012\WebSites\Josef\Shared\users.mdb";
    using (System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand())
    {
        command.Connection = connection;
        command.CommandText = "INSERT INTO users (userName,passWord,Uname,pic) VALUES (?,?,?,?)";
        command.Parameters.Add(userName);
        command.Parameters.Add(pass);
        command.Parameters.Add(Uname);
        command.Parameters.Add(pic);

        connection.Open();
        command.ExecuteNonQuery();
    }
}

About parameters for an OleDb connection from OleDbCommand.Parameters

Remarks

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

What should I change in my code?

1. Change to parameters (that also fixes the problem that you don;t have quotes around the pic value)

2. Remove the second instance of pass in your values

   command.CommandText = "INSERT INTO users (userName,passWord,Uname,pic) VALUES (@userName, @pass, @Uname, @pic)";
   command.Parameters.Add("@userName").Value = userName;
   .. etc.
   

It's unclear what the type if pic is - you are passing a string but I can;t tell of the column stores a file path or if you are indending to serialize the file and store it in a pinary field.

You set 4 fields after the "INTO" clause, however you're passing 5 parameters:

"INSERT INTO users (userName,passWord,Uname,pic) VALUES ('" + userName + "', '" + pass + "', '" + Uname + "', '" + pass + "', " + pic + ")";

Just add the fifth field, or remove one parameter from the VALUES part

Please check take a look at your Insert statement, it looks like that you provided password value twice.

The number of query values and the destination fields should be same in an INSERT statement.

You have the wrong number parameters in your insert statement. For clarity, why not use string.Format to keep everything uniform? (Assuming these are all string types)

var rawSql = @"Insert INTO Users (userName,passWord,Uname,pic) VALUES ('{0}','{1}','{2}','{3}')";
command.CommandText = string.Format(rawSql, userName, pass, Uname, pic);
command.ExecuteNonQuery();

However, it also looks like you probably want to include that 5th parameter as well - just extend the format :

var rawSql = @"Insert INTO Users (userName,passWord,Uname,pic, privacy) VALUES ('{0}','{1}','{2}','{3}','{4}')";
command.CommandText = string.Format(rawSql, userName, pass, Uname, pic, privacy);
command.ExecuteNonQuery();

Since most of the answers failed to address the SQL Injection vulnerability, here's an example with parameterized queries. In addition to preventing SQL Injection attacks, it also makes it easier to troubleshoot these types of issues, and you don't need to worry about quoting or not quoting parameters.

System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection();
connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Etay\Documents\Visual Studio 2012\WebSites\Josef\Shared\users.mdb";

try
{
    System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
    command.Connection = connection;
    connection.Open();
    command.CommandText = "INSERT INTO users (userName, passWord, Uname, pic, privacy) VALUES (?, ?, ?, ?, ?)";
    command.Parameters.Add(userName);
    command.Parameters.Add(pass);
    command.Parameters.Add(name);
    command.Parameters.Add(pic);
    command.Parameters.Add(privacy);
    command.ExecuteNonQuery();
    Response.Redirect("../HtmlPage.html");
}
finally
{
    connection.Close();
}

Tnx 4 the help

It happend to be a problem with the database - you can not apply a INSERT INTO statement where the column name is "password". "password" is a Reserved word in SQL.

Tnx again,

Etay