Why doesn't my alter SQL Statement work?

[ttheobal]

I'm writing an asp page that gets userid, password, and new password from a form. The asp then does a connect to Oracle, and issues an alter user.

My Connection to Oracle works. I did the following.
-----------------------------------------------------------
On Error Resume Next
Set objConn = Server.CreateObject("ADODB.Connection&quot
objConn.Open "dsn=PRD16;uid=" & Userid & ";pwd=" & Password & ";"
If objConn.errors.count > 0 then
Response.write(&quot;Database Errors Occured&quot; & &quot;<P>&quot
For counter= 0 to objConn.errors.count
Response.write(&quot;Error #&quot; & objConn.errors(counter).number & &quot;<P>&quot
Response.write(&quot;Error desc. -> &quot; & objConn.errors(counter).description & &quot;<P>&quot
Next
Else
' Connection worked
End if
------------------------------------------------------------

What doesn't work is the alter user command. the command in Sql would be:

alter user userid identified by &quot;NewPassword&quot;;

What I coded in asp is.
------------------------------------------------------------
On Error Resume Next
objSQL = Server.CreateObject(&quot;ADODB.Command&quot
set objSQL.ActiveConnection = objConn
StrCommand = &quot;alter user &quot; & Userid & &quot; identified by &quot;&quot;&quot; & NewPassword & &quot;&quot;&quot;;&quot;
objSQL.execute StrCommand
If objSQL.errors.count > 0 then
Response.write( objSQL.errors.count & &quot;Errors Occured while setting password&quot; & &quot;<P>&quot
Response.write objSQL.errors.count
For counter= 0 to objSQL.errors.count
Response.write(&quot;Error #&quot; & objSQL.errors(counter).number & &quot;<P>&quot
Response.write(&quot;Error desc. -> &quot; & objSQL.errors(counter).description & &quot;<P>&quot
Next
else
response.write(&quot;nO ERRORS&quot
end if
----------------------------------------------------------

This doesn't work. I don't get an error returned from the DB. Funny thing is that the &quot;nO ERRORS&quot; doesn't display either, as if it jumps over the entire IF Structure.

I'm a true asp newbie, so any help would be most appreciated.

Thanks.

 

[foxbox]

TTheobal,
first: i know nothing of Oracle.

2.Are you sure your statement works in interactive SQL
alter user userid identified by &quot;NewPassword&quot;;

to me this looks a little bit inconsequent. are you sure it is not:

alter user &quot;ttheobal&quot; identified by &quot;NewPassword&quot;;
or
alter user ttheobal identified by NewPassword;



3. Are you sure that
objConn.Open &quot;dsn=PRD16;uid=&quot; & Userid & &quot;;pwd=&quot; & Password & &quot;;&quot;
opens a database connection with enough rights for such a command?









br
Gerard

 

[Bastien]

try single quotes around Password

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[ttheobal]

Thanks for your reply

1. I know very little about Oracle also

2. Using SQL Plus, the only format that works is
alter user ttheobal identified by &quot;NewPassword&quot;;
single quotes, quotes around userid, or no quotes at all all return errors.

3. Yes, as I'm using my userid, and it has the rights perform the alter.

4. My next question. Is my syntaxx correct? Should I be using the ADODB.Command Class?

 

[talon121a]

I was once there with the ASP newbie thing a loong time ago, and I know how frustrating it was to try to get projects done ( yes, I had a job I did ASP work before I was good at it )

A good practice in the ASP world is using debug statements. (ie. Response.Write &quot;Hi there I just did this..&quot; )

You would be really surprised to see how far your code gets with that.

Post yur ASP questions, im usualyl good with em, and havent been here lookin at forum posts lately but I'll get better with that and answer the best I can.



--------
Jason Burton
Starloop International
jasonburton@us.starloop.com
(im confident with my spam fighting solution)

 

[onpnt]

1. I know very little about Oracle also
thats not a bad place to be. joking... Oracle or PL-SQL in general can have some irritants in syntax differences. a good PL-SQL reference guide usually can get you through that.

2. Using SQL Plus, the only format that works is
alter user ttheobal identified by &quot;NewPassword&quot;;
single quotes, quotes around userid, or no quotes at all all return errors.
from my knowledge of PL the syntax is correct just to further your tests in plus. so in all this SQL statement is correct I believe.

4. My next question. Is my syntaxx correct? Should I be using the ADODB.Command Class?

to simplify this function I would change to a simple
objConn.Open
StrCommand = &quot;alter user &quot; & Userid & &quot; identified by &quot;&quot;&quot; & NewPassword & &quot;&quot;&quot;;&quot;
objConn.Execute(StrCommand)

that is all you really need provided the objConn is your connection object etc..

however your syntax ofr the command appears correct.

ahve you tried to take out the error handling and see what errors are generated. I like to do that as the On Error handling in vbscript / VB isn't that informative mostly and jsut a good way to provide the user with a fancy appearance to a error occurance

_____________________________________________________________________

[sub]
Hakuna matata!![/sub]

 

[medic]

Try removing some extra quotes in your code to make it look like this:

StrCommand = "alter user " & Userid & " identified by " & NewPassword & ";"

 

[ttheobal]

Problem Solved.

Here's how I did it.

Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.Open &quot;dsn=PRD16A;uid=&quot; & Userid & &quot;;pwd=&quot; & Password & &quot;;&quot;
If objConn.errors.count > 0 then
Response.write(&quot;<h2>Database Errors Occured</h2>&quot; & &quot;<P>&quot
For counter= 0 to objConn.errors.count
Response.write(&quot;<B>Error #&quot; & objConn.errors(counter).number & &quot;<BR>&quot
Response.write(&quot;Error desc. -> &quot; & objConn.errors(counter).description & &quot;<P></B>&quot
counter = counter +1
Next
Else
On Error Resume Next
StrCommand = &quot;alter user &quot; & Userid & &quot; identified by &quot;&quot;&quot; & NewPassword & &quot;&quot;&quot;;&quot;
objConn.Execute(StrCommand)
If objConn.errors.count > 0 then
Response.write(&quot;<h2>Database Errors Occured</h2>&quot; & &quot;<P>&quot
Response.write(&quot;Error occured at an attempt to connect to set new password.&quot; & &quot;<P>&quot
For counter= 0 to objConn.errors.count
Response.write(&quot;<B>Error #&quot; & objConn.errors(counter).number & &quot;<BR>&quot
Response.write(&quot;Error desc. -> &quot; & objConn.errors(counter).description & &quot;<P></B>&quot
counter = counter +1
Next
else
Response.write(&quot;<H2>Password Changed Successfully.</h2> <P>&quot
Response.write(&quot;Your Password for Process Central has been changed.&quot
end if

Thanks for your help. However with my solution comes another bug, but I will post that seperately.