syntax error in update statement.

Question

0.00/5 (No votes)

See more:

C#

String uname = tbUser.Text;
            String pass = tbPasswd.Text;
            string ConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\diy.mdb";
            string SqlString = "UPDATE Login SET user='" + uname + "',paswrd='" + pass + "'";
           OleDbConnection conn = new OleDbConnection(ConnString);
           OleDbCommand cmd = new OleDbCommand(SqlString, conn);
           conn.Open();
           cmd.ExecuteNonQuery();
           conn.Close();

Posted 9-Aug-15 20:56pm

Member 11821221

Updated 9-Aug-15 21:30pm

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Accepted Answer · 2015-08-09T21:09:00

Solution 1

use sql parameters and also you need to use [] for reserved keywords like user [^]

C#

using (var connection = new OleDbConnection(ConnString ))
using (var command = connection.CreateCommand())
{
    connection.Open();
    command.CommandText = "UPDATE Login SET [user]=?,paswrd=?";
    command.Parameters.AddWithValue("@user", uname );
    command.Parameters.AddWithValue("@pwd", pass );
command.ExecuteNonQuery();

}

Posted 9-Aug-15 21:09pm

DamithSL

Updated 9-Aug-15 21:51pm

v2

Comments

CPallini 10-Aug-15 3:09am

5

Member 11821221 10-Aug-15 3:20am

Thank you .......

Maciej Los 10-Aug-15 3:36am

Jet for MS Access does not know named parameters with @ prefix. Instead of above use named parameters for MS Access database or standard OleDb parameters (no-named) - via using: ?.

DamithSL 10-Aug-15 3:52am

Thanks Maciej, I have updated my answer.

Maciej Los 10-Aug-15 4:10am

The second part of your answer (code) is wrong still.

DamithSL 10-Aug-15 4:14am

why? AddWithValue is not an issue, when using ? for parameters, only the order of adding parameters is matters, parameter name will be neglected but in sql if the first parameter is username then need to add user name as first parameter, likewise need to follow the order. is there anything else I have missed?

Maciej Los 10-Aug-15 4:19am

If you use unnamed parameters, you can't use AddWithValue method, because you can't pass parameter's value by its name. I would recommend to use Oledbarameters.Add method.

DamithSL 10-Aug-15 4:31am

Maciej, I have test above code and it is working fine,
even with @ parameter names it is working.
if I understand the documentation correctly, it never mentioned you can't use AddWithValue, named parameter in the sql text need to replace with ? and the parameter order need to follow when adding parameters.

Maciej Los 10-Aug-15 5:24am

Interesting... What version of database are you using (2000 or 2003)?

DamithSL 10-Aug-15 5:28am

2003

Maciej Los 10-Aug-15 5:44am

So, export database to 2000 format and check again. As per my experience, code won't execute.

DamithSL 10-Aug-15 14:43pm

Test and it is even work for 2000 access database files as well

Maciej Los 10-Aug-15 15:27pm

5ed! I have no chance to test it now. I believe that it works as well.

syntax error in update statement.

1 solution

Solution 1

Add your solution here

Preview 0

syntax error in update statement.

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us