password field: Syntax error in INSERT INTO statement.

[Neil Zanella]

Hello,

I am trying to execute ADO.NET INSERT statement where one of the
fields
is coming from a password HTML control. When I access the text with
password.Value and print with Response.Write so as to check that it
is working correctly it displays just fine. However, whenever I
include the password field in one of the SQL INSERT statement's
fields I get the following error:

Exception Details: System.Data.OleDb.OleDbException: Syntax error in
INSERT INTO statement.

Line 85: cmd.ExecuteNonQuery();

When I delete the password field from the insert statement things work
fine (so the problem is no longer related to NTFS file permissions).

Any ideas on how to solve this problem would be greatly appreciated.

Best Regards,

Neil

 

[MWells]

Neil, it's difficult to pinpoint your problem without seeing some code; but
here are a few ideas to get you started;

If you're using a WebControls textbox (and not an HtmlControls input box),
then the correct accessor would be something like txtPassword.Text

Triple-check your SQL. Try modifying it so that the value you're entering
in the textbox is manually assigned to the SqlParam (or embedded in your sql
string, etc). That will help you debug your SQL, your schema, etc while
removing the textbox from the equation.

Try very simple passwords, e.g. "a". If that succeeds, you might be having
a problem with unescaped apostrophes or something similar.

If you can, display the generated Sql in a Trace.Write or a Response.Write,
and copy/paste it into your Sql tool to test it manually.

Best of luck,

/// M

 

[nzanella]

Hello,

Thank you for your reply. I have already tried everything
you suggested. I also had a problem with calling
Response.Write to debug my SQL query as
whenever I use it the parameter holders
which begin with @ are not substituted
when I call Response.Write (is there
some special method I can call on
OleDbCommand to print the actual
query with the parameters
substituted in?)

Anyways, here was my problem: as strange as
it may sound, I renamed a field called "password"
in MS Access to "foo" and did the same in the
query contained in the code. I don't know how
come but that fixed it. Is there something
special about calling a table's field
"password" in MS Access which
causes the query error I was
experiencing????

Thanks,

Neil

 

[MWells]

It might be a reserved word in Access. In Sql Server you can use most
reserved words as column names, but you reference them in your sql using
[brackets].

Since you've identified it as an Access or .NET->Access issue, I'd also try
seaching some of those newsgroups.