Why ExecuteNonQuery() cannot execute UPDATE command

[ssailor]

Hi, everybody,
I am rusty with VB. In the following code,
------------------------------------------------------
Dim dbCmd1 As OleDbCommand = New OleDbCommand("UPDATE
tblLogin SET Password = '1'") ' WHERE UserID = 'Admin'")
Dim dbCmd1 As OleDbCommand = New OleDbCommand("SELECT *
FROM tblLogin WHERE UserID = 'Admin'")
'" + g_userID + "'")
dbCmd1.Connection = g_loginConn
dbCmd1.Connection = g_loginConn
#1: dbCmd.ExecuteNonQuery() 'update syntax error
#2: dbCmd.ExecuteNonQuery() 'ok

-------------------------------------------------------------
the statement at line #1 causes a "update syntax error" while
the statement at
line #2 is OK. Does the ExecuteNonQuery() cannot execute the Update
command or
for some other reasons? I am very appreciated if someone give me some
hints.

 

[ssailor]

I am very sorry for my carelessness. The above code is incorrect. I
rewrite the code as the following,

------------------------------------------------------
Dim dbCmd1 As OleDbCommand = New OleDbCommand("UPDATE
tblLogin SET Password = '1'")
Dim dbCmd2 As OleDbCommand = New OleDbCommand("SELECT *
FROM tblLogin)
dbCmd1.Connection = g_loginConn
dbCmd2.Connection = g_loginConn
#1: dbCmd1.ExecuteNonQuery() 'update syntax error
#2: dbCmd2.ExecuteNonQuery() 'ok

-------------------------------------------------------------


But the line #1 still cannot be executed and the compiler gives a
"Update syntax error" message, while the line #2
is OK. Why?

 

[ssailor]

Dim dbCmd1 As OleDbCommand = New OleDbCommand("UPDATE
tblLogin SET Password = '1'")

When I change the column name "Password" to "Pwd", the error
disappears!
Does the "Password" name conflicts to some reserved name?
Damn! It took me nearly a whole day to solve this problem.

 

[zacks]

When I change the column name "Password" to "Pwd", the error
disappears!
Does the "Password" name conflicts to some reserved name?
Damn! It took me nearly a whole day to solve this problem.

You didn't say what SQL Provider you are using, but the column
referenced in an UPDATE statement SET command MUST be EXACTLY the same
as the name of the column in the Providers "back-end". What made you
think the name of the column was "Password" as opposed to "Pwd"?

PS: Case doesn't usually matter.

 

[rowe_newsgroups]

You didn't say what SQL Provider you are using, but the column
referenced in an UPDATE statement SET command MUST be EXACTLY the same
as the name of the column in the Providers "back-end". What made you
think the name of the column was "Password" as opposed to "Pwd"?

PS: Case doesn't usually matter.

Perhaps he meant he changed the database column to "Pwd" as well as
the query? If so, the OP should have been able to use [Password] in
the query to refer to a column named the same as the protected keyword
"Password."

Thanks,

Seth Rowe

 

[zacks]

You didn't say what SQL Provider you are using, but the column
referenced in an UPDATE statement SET command MUST be EXACTLY the same
as the name of the column in the Providers "back-end". What made you
think the name of the column was "Password" as opposed to "Pwd"?

PS: Case doesn't usually matter.

Perhaps he meant he changed the database column to "Pwd" as well as
the query? If so, the OP should have been able to use [Password] in
the query to refer to a column named the same as the protected keyword
"Password."

You're right, I forgot Password is a keyword. That's one reason why,
when I am generating queries on the fly in VB code, I like to enclose
every column and table reference with square brackets.

 

[ssailor]

rowe_newsgroups write:

You didn't say what SQL Provider you are using, but the column
referenced in an UPDATE statement SET command MUST be EXACTLY the same
as the name of the column in the Providers "back-end". What made you
think the name of the column was "Password" as opposed to "Pwd"?

PS: Case doesn't usually matter.

Perhaps he meant he changed the database column to "Pwd" as well as
the query? If so, the OP should have been able to use [Password] in
the query to refer to a column named the same as the protected keyword
"Password."

Right, this is what I meant.
I used the Access database and created the table in the Access. I
didn't know the cloumn can be quoted in "[ ]". Thank you!