When did Password become a Reserved Word?

  • Thread starter Thread starter Charles Law
  • Start date Start date
C

Charles Law

I thought I would bring this back up to the top, as I have resolved my
original problem.

I discovered that using ADO.NET, OleDb and the CommandBuilder, I got a
syntax error when a column in my table was called "Password".

Was everyone else aware that Password could not be used as a column name?
Although Access 2000 seems happy with the name, OleDb in ADO.NET is not
[note that ADO was happy]. Are there any other new reserved words, or words
that cause this problem that people know about?

TIA

Charles
 
Charles Law said:
I thought I would bring this back up to the top, as I have resolved my
original problem.

I discovered that using ADO.NET, OleDb and the CommandBuilder, I got a
syntax error when a column in my table was called "Password".
Click to expand...

Charles
Note the solution is to put a brackets around the keyword.

e.g. (using David's code)

strSQL = "CREATE TABLE KeywordCheck (ID int PRIMARY KEY, [Password] varchar(32))"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "SELECT ID, [Password] FROM KeywordCheck"
Set rs = Cn.Execute(strSQL)

strSQL = "INSERT INTO KeywordCheck (ID, [Password]) VALUES (1, '1')"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "UPDATE KeywordCheck SET [Password] = 'One' WHERE ID = 1"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "DELETE FROM KeywordCheck WHERE [Password] = 'One'"
Cn.Execute strSQL, , adExecuteNoRecords

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com
 
Hi Carl

Yes. It's not so much the problem of using a reserved word, as I prefer not
to use them when I know which ones to avoid. It's more to do with what still
seems to me to be the inconsistency. In Access 2000 I can create a table
with Password as a column name. In ADO I would populate the column like
this:

rs("Password") = "something"

and retrieve it with

pwd = rs("Password")

Now when I do this in ADO.NET, because I use the command builder to generate
the SQL, it throws a wobbly.

Do you know of a way to get at the final SQL (without all the ?, ?, ? ...)
so that I can check it in TOAD, for example?

Charles


Carl Prothman said:
Charles Law said:
I thought I would bring this back up to the top, as I have resolved my
original problem.

I discovered that using ADO.NET, OleDb and the CommandBuilder, I got a
syntax error when a column in my table was called "Password".
Click to expand...

Charles
Note the solution is to put a brackets around the keyword.

e.g. (using David's code)

strSQL = "CREATE TABLE KeywordCheck (ID int PRIMARY KEY, [Password] varchar(32))"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "SELECT ID, [Password] FROM KeywordCheck"
Set rs = Cn.Execute(strSQL)

strSQL = "INSERT INTO KeywordCheck (ID, [Password]) VALUES (1, '1')"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "UPDATE KeywordCheck SET [Password] = 'One' WHERE ID = 1"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "DELETE FROM KeywordCheck WHERE [Password] = 'One'"
Cn.Execute strSQL, , adExecuteNoRecords

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com
Click to expand...
 
Hi David

Thanks for that. As I have just replied to Carl, lower down this thread, it
still seems to be inconsistent to me. That is, Access 2000 allows me to
create the column without complaint, and in ADO I could write

rs("Password") = "something"

and

pwd = rs("Password")

Now, using ADO.NET and the command builder, I get a syntax error. It seems
to me to be the command builder at fault here. Is there an alternative to
the command builder, but still using the syntax I have used above?

Charles
 
The real problem is that Access is inconsistent and allows sloppy practices
like using keywords for column names. I don't think ADO did any sort of
validation on the SQL, but the wizards in .Net appear to be a little more
helpful in ensuring good coding practices.

I don't know if that has been corrected in the latest versions of Access,
but of course I would never name columns like that :)

Colin

Charles Law said:
Hi Carl

Yes. It's not so much the problem of using a reserved word, as I prefer not
to use them when I know which ones to avoid. It's more to do with what still
seems to me to be the inconsistency. In Access 2000 I can create a table
with Password as a column name. In ADO I would populate the column like
this:

rs("Password") = "something"

and retrieve it with

pwd = rs("Password")

Now when I do this in ADO.NET, because I use the command builder to generate
the SQL, it throws a wobbly.

Do you know of a way to get at the final SQL (without all the ?, ?, ? ...)
so that I can check it in TOAD, for example?

Charles


Carl Prothman said:
Charles Law said:
I thought I would bring this back up to the top, as I have resolved my
original problem.

I discovered that using ADO.NET, OleDb and the CommandBuilder, I got a
syntax error when a column in my table was called "Password".
Click to expand...

Charles
Note the solution is to put a brackets around the keyword.

e.g. (using David's code)

strSQL = "CREATE TABLE KeywordCheck (ID int PRIMARY KEY, [Password] varchar(32))"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "SELECT ID, [Password] FROM KeywordCheck"
Set rs = Cn.Execute(strSQL)

strSQL = "INSERT INTO KeywordCheck (ID, [Password]) VALUES (1, '1')"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "UPDATE KeywordCheck SET [Password] = 'One' WHERE ID = 1"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "DELETE FROM KeywordCheck WHERE [Password] = 'One'"
Cn.Execute strSQL, , adExecuteNoRecords

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com
Click to expand...
Click to expand...
 
Hi Colin

Do you have a preferred naming scheme? I would be interested in case it is
something I should consider adopting.

Thanks

Charles


Colin Young said:
The real problem is that Access is inconsistent and allows sloppy practices
like using keywords for column names. I don't think ADO did any sort of
validation on the SQL, but the wizards in .Net appear to be a little more
helpful in ensuring good coding practices.

I don't know if that has been corrected in the latest versions of Access,
but of course I would never name columns like that :)

Colin

Charles Law said:
Hi Carl

Yes. It's not so much the problem of using a reserved word, as I prefer not
to use them when I know which ones to avoid. It's more to do with what still
seems to me to be the inconsistency. In Access 2000 I can create a table
with Password as a column name. In ADO I would populate the column like
this:

rs("Password") = "something"

and retrieve it with

pwd = rs("Password")

Now when I do this in ADO.NET, because I use the command builder to generate
the SQL, it throws a wobbly.

Do you know of a way to get at the final SQL (without all the ?, ?, ? ....)
so that I can check it in TOAD, for example?

Charles


Carl Prothman said:
I thought I would bring this back up to the top, as I have resolved my
original problem.

I discovered that using ADO.NET, OleDb and the CommandBuilder, I got a
syntax error when a column in my table was called "Password".


Charles
Note the solution is to put a brackets around the keyword.

e.g. (using David's code)

strSQL = "CREATE TABLE KeywordCheck (ID int PRIMARY KEY, [Password] varchar(32))"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "SELECT ID, [Password] FROM KeywordCheck"
Set rs = Cn.Execute(strSQL)

strSQL = "INSERT INTO KeywordCheck (ID, [Password]) VALUES (1, '1')"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "UPDATE KeywordCheck SET [Password] = 'One' WHERE ID = 1"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "DELETE FROM KeywordCheck WHERE [Password] = 'One'"
Cn.Execute strSQL, , adExecuteNoRecords

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com
Click to expand...
Click to expand...
Click to expand...
 
¤ I thought I would bring this back up to the top, as I have resolved my
¤ original problem.
¤
¤ I discovered that using ADO.NET, OleDb and the CommandBuilder, I got a
¤ syntax error when a column in my table was called "Password".
¤
¤ Was everyone else aware that Password could not be used as a column name?
¤ Although Access 2000 seems happy with the name, OleDb in ADO.NET is not
¤ [note that ADO was happy]. Are there any other new reserved words, or words
¤ that cause this problem that people know about?
¤

See the following:

ACC2002: Microsoft Jet 4.0 Reserved Words
http://support.microsoft.com/default.aspx?scid=kb;en-us;321266

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;EN-US;286335

ODBC Reserved words:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappcpr_17.asp


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Hi Paul

That's exactly the kind of thing I was after. It now explains why Password
is a wrong'un.

Thanks very much.

Charles


Paul Clement said:
¤ I thought I would bring this back up to the top, as I have resolved my
¤ original problem.
¤
¤ I discovered that using ADO.NET, OleDb and the CommandBuilder, I got a
¤ syntax error when a column in my table was called "Password".
¤
¤ Was everyone else aware that Password could not be used as a column name?
¤ Although Access 2000 seems happy with the name, OleDb in ADO.NET is not
¤ [note that ADO was happy]. Are there any other new reserved words, or words
¤ that cause this problem that people know about?
¤

See the following:

ACC2002: Microsoft Jet 4.0 Reserved Words
http://support.microsoft.com/default.aspx?scid=kb;en-us;321266

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;EN-US;286335

ODBC Reserved words:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappcpr_17.asp


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
Click to expand...
Click to expand...
 
Nothing formal. Singular nouns for table names, descriptive names for
columns, no underscore characters (use mixed case instead). Sometimes using
a reserved word is the best name for your table or column, and then you just
have to put the square brackets around it.

Colin

Charles Law said:
Hi Colin

Do you have a preferred naming scheme? I would be interested in case it is
something I should consider adopting.

Thanks

Charles


Colin Young said:
The real problem is that Access is inconsistent and allows sloppy practices
like using keywords for column names. I don't think ADO did any sort of
validation on the SQL, but the wizards in .Net appear to be a little more
helpful in ensuring good coding practices.

I don't know if that has been corrected in the latest versions of Access,
but of course I would never name columns like that :)

Colin

prefer
not
Click to expand...
resolved
my
Click to expand...
got
a
syntax error when a column in my table was called "Password".


Charles
Note the solution is to put a brackets around the keyword.

e.g. (using David's code)

strSQL = "CREATE TABLE KeywordCheck (ID int PRIMARY KEY, [Password]
varchar(32))"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "SELECT ID, [Password] FROM KeywordCheck"
Set rs = Cn.Execute(strSQL)

strSQL = "INSERT INTO KeywordCheck (ID, [Password]) VALUES (1, '1')"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "UPDATE KeywordCheck SET [Password] = 'One' WHERE ID = 1"
Cn.Execute strSQL, , adExecuteNoRecords

strSQL = "DELETE FROM KeywordCheck WHERE [Password] = 'One'"
Cn.Execute strSQL, , adExecuteNoRecords

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com
Click to expand...
Click to expand...
Click to expand...
 
Charles,

You can still use row("Password") = "MyPassword" in ADO.NET.

Regarding the CommandBuilder, setting QuotePrefix to "[" and
QuoteSuffix to "]" will cause the CommandBuilder to delimit table
and column names with those characters.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Hi David

Actually, I did see Prefix/Suffix in my wanderings, and passed them by
because I just assumed that they would have default values relating to the
database technology, e.g. [ and ] for OleDb/Access. I should have given it
more thought ;-)

Thanks very much.

Charles
 
Back
Top