Querying Access Databases

[Andruil]

Hi I am having some trouble using ado.net and was hoping someone could
help me understand what i've messed up. I am currently using ado.net
from inside a C# asp.net project but i've also tried to do the same
thing with just C# and ado.net and it gives me the same grief.

OleDbConnection cn = new OleDbConnection();
string strSQL = "INSERT INTO USER (FName, LName, UserName, " +
"City,State,Country,email) VALUES ('First', 'Last', " +
" 'userName', 'Portland', 'OR', 'USA', '(e-mail address removed)')";
OleDbCommand myCommand;
cn.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" +
@"data source = C:\WebDB.mdb";
cn.Open();
try
{

myCommand = new OleDbCommand(strSQL, cn);
lblTest.Text = "";
myCommand.ExecuteNonQuery();
}
catch (OleDbException ex)
{
lblTest.Text += String.Format("<li>Error: {0}", ex.Errors[0].Message);
}


try
{

strSQL = "select * from user";
myCommand = new OleDbCommand(strSQL, cn);
myCommand.ExecuteScalar();

}
catch (OleDbException ex)
{
lblTest.Text += String.Format("<li>Error: {0}", ex.Errors[0].Message);
}
cn.Close();

I run this and this is the error messages that I get

Error: Syntax error in INSERT INTO statement.
Error: Syntax error in FROM clause.

First one from the first try / catch and the second from the second try
/ catch block.

I can connect to the database and I can get the schema with the
following command
(I know this works cause i've printed it out.)
DataTable dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new
object[] { null, null, null, "TABLE" });


What have I done wrong?

Thanks for any help
Curtis

 

[Jim Hughes]

USER and maybe some others are "Reserved Words" per
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q286335

Either rename the tables and fields (preferred) or wrap them in square
brackets.

INSERT INTO [USER] ...

You will also want to look into using parameter queries instead of string
concatentation to prevent sql injection attacks and broken queries because
of single quotes.

 

[Andruil]

Aah ok that would be my problem. Thank you I greatly appreciate it. I
was missing the []'s from my query. The only point to the previous
query was to make a connection and verify that I could get / set data
in my database. Any thoughts as to a good place to learn more about
parameter queries?

Thanks again

 

[Brendan Reynolds]

Some of the links in the list at the URL below should be of interest ...
http://search.microsoft.com/search/results.aspx?qu=jet+query+parameter