INSERT INTO with ADO.NET

[jasonjasonjason]

Hi,

I'm having a problem when trying to executre an SQL statement. It's a very strange problem I've got to say...

Let me explain myself -
I have an Access DB. I've managed to execute a SELECT sql statement and show the records on a console window. Now I want to insert information to the database.
The following sql statement will be executed just fine:
INSERT INTO Users (UserId) VALUES(1)
The problem starts when I want to insert string values, the next statement will not go through and will end up in an exception - "Syntax error in INSERT INTO statement":
INSERT INTO Users (UserId, UserName) VALUES (1, 'Test')

There is no problem with the syntax because on an SQL application it ran without any problems...

Thanks

 

[wsmall73]

try a double quotes instead of single quotes around "Test"

 

[jasonjasonjason]

It didn't help... same error occured...

 

[chiph]

Try using a parameter to supply your string argument. I don't have the ODBC drivers on my machine, so I'll use the SQL classes:

String MySQL = "INSERT INTO Users (UserId, UserName) VALUES (@UserId, @UserName)";

SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);

SqlParameter Parm1 = MyCommand.CreateParameter();
Parm1.DbType = Dbtype.Integer;
Parm1.ParameterDirection = ParameterDirection.Input;
Parm1.Name = "@UserId"
Parm1.Value = iSomeUserIDVariable;

SqlParameter Parm2 = MyCommand.CreateParameter();
Parm2.DbType = Dbtype.String;
Parm2.ParameterDirection = ParameterDirection.Input;
Parm2.Size = 40;  // Size of field in characters
Parm2.Name = "@UserName";
Parm2.Value = sSomeUserNameVariable;

MyCommand.Parameters.Add(Parm1);
MyCommand.Parameters.Add(Parm2);

int iNumRowsAffected = MyCommand.ExecuteNonQuery();

One other question: is your UserId column an Autonumber column? If so, you can't insert into it, as the database will supply that value.

Chip H.

 

[Axien]

Try this one:

System.Data.OleDb.OleDbCommand oleCmd = new
System.Data.OleDb.OleDbCommand();

oleCmd.Connection = oleDbConnection;
// oleDbConnection you must decleare yourself
oleCmd.CommandText = "INSERT INTO Users (UserId, UserName) VALUES (1, 'Test')";

try{
oleCmd.Connection.Open();
oleCmd.ExecuteNonQuery();
oleCmd.Connection.Close();
}
catch(Exception err){
MessageBox.Show(err.Message);
}