C# INSERT statement using OleDb

[themoonisdown09]

I am using Visual Studio 2003, C# .NET

I can't get this INSERT statement to work. I can get an UPDATE to work, but not this INSERT. Here is my code...

******************

//opens database
string conString = "Provider=Microsoft.Jet.OLEDB.4.0;"
            + "Data Source=C:\\TimeClock\\TimeClock.mdb";
 
OleDbConnection empConnection = new OleDbConnection(conString);
 
//add time clocked in/out in clock table
string insertStatement = "INSERT INTO Clock "
                     + "(Login, Time, Status) "
                     + "VALUES (?, ?, ?)";
 
OleDbCommand insertCommand = new OleDbCommand(insertStatement, empConnection);
 
insertCommand.Parameters.Add("Login", OleDbType.Char).Value = strLogin;
insertCommand.Parameters.Add("Time", OleDbType.Char).Value = strTime;
insertCommand.Parameters.Add("Status", OleDbType.Char).Value = strStatus;
 
empConnection.Open();
 
try
{
int count = insertCommand.ExecuteNonQuery();
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
empConnection.Close();
}

*****************

[axas]

You must write

string insertStatement = "INSERT INTO Clock "
+ "(Login, Time, Status) "
+ "VALUES (@Login, @Time, @Status)";
 
insertCommand.Parameters.Add("@Login", OleDbType.Char).Value = strLogin;
insertCommand.Parameters.Add("@Time", OleDbType.Char).Value = strTime;
insertCommand.Parameters.Add("@Status", OleDbType.Char).Value = strStatus;

I think its right

[themoonisdown09]

It's still saying that there is a syntax error in the INSERT INTO statement. I don't understand. I have a book that shows me how to do it... and I tried AXAS code... and it still says that.

[Enyi]

I tried making a program with your code, and i got the same error.

Here is your problem:

The word 'Time' is a keyword in MS Access 2003, and most likely other versions as well. Therefore, it cannot be used as a column name.

I havn't tried with a different column name for time, but i'm sure it would work.

Good luck!


EDIT: Actually, I'm just thinking. How can update work in this situation? Now I'm questioning myself. I'll just try the program again.

[Enyi]

OK, I just tried changing the column name and it worked just fine with both an UPDATE and INSERT INTO command.

However, when I tried with 'Time' as the column name in an UPDATE statement, it just said "Syntax error in UPDATE statement".

Not sure how you got update to work, but I can't :P ( With a column named 'Time' )

Of course, I'm using MS Access 2003, maybe your verison is different?

Enyi

[themoonisdown09]

I didn't use an UPDATE with this exact statement. It was with another part of my program. I'll try and change the column name when I get home... I'm at work now... ha. Thanks so much for your help... I'll let you know if it works for me too.

By the way... I am using Microsoft Access 2003.

[Elena]

try

string insertStatement = "INSERT INTO Clock "
+ "([Login], [Time], [Status]) "
+ "VALUES (@Login, @Time, @Status)";

[themoonisdown09]

THANK YOU ELENA!!! I changed the column name and I also put the brackets around the names.... don't know which thing helped, but it works now.

[Enyi]

Very nice Elena.

I seen what it does it now. The square brackets explicitly refers to the column name, whereas without square brackets, it could be referring to a column or function or something else. Right?

I would also say in our cases, both helped :D

[dna5122]

You are correct Anyi.

[TracyM]

I came across this same problem with Access and Visual Studio. Visual Studio does not support read and writes in its Adapter to Oledb. Its not bi-directional. Just read only. I know its a major pain. Its the last thing I expected also. I suppose it forces you to use Sqlserver. There are other sources out there for MySql and some other databases but you may not have time.

You have a couple choices here. If available switch to SqlServer DB or try another method involving manipulation of Access:

Take a look at this link and see if it helps.

http://www.geekpedia.com/tutorial158_Connect-to-Access-Database-in-Visual-Studio-.NET.html

Best of Luck,
TracyM

[gts2277]

string insertStatement = "INSERT INTO Clock "
+ "([Login], [Time], [Status]) "
+ "VALUES (@Login, @Time, @Status)";

with the brack is WORK !!!!! and save your time use @ infront of VALUES. Like:

string insertStatement = "INSERT INTO Clock "
+ "([Login], [Time], [Status]) "
+ @"VALUES Login,Time,Status)";