[RESOLVED] help with Oracle ExecuteScalar

[daimous]

hi guys! can you help me with this..i have a code below which has an error but i can't figure out what is the error..

OracleConnection OraConn = new OracleConnection(OraConnStr);
string qryStr;
string encPswrd;

encPswrd = RndCrypt(txtAddPassword.Text, txtAddUsername.Text.ToUpper());
qryStr = @"select count(*) from users where username='" + txtAddUsername.Text.ToUpper() +
                                          "' and password = @encPswrd and admin_user='TRUE'";
OracleCommand OraCmd = new OracleCommand(qryStr,OraConn);
OraCmd.Parameters.AddWithValue(@"encPswrd", encPswrd);
MessageBox.Show("EXECUTE: " + (OraCmd.ExecuteScalar().ToString ()));
//Error occurs in (int)OraCmd.ExecuteScalar() 
if ((int)OraCmd.ExecuteScalar() != null || txtAddUsername.Text == "abs")
{
      //If it has value do something here
}
else
{
    //If value is null do something here
}

error:

System.Data.OracleClient.OracleException was unhandled
Message="ORA-01036: illegal variable name/number\n"
Source="System.Data.OracleClient"
ErrorCode=-2146232008
Code=1036
StackTrace:
at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean& mustRelease, SafeHandle& handleToBind)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteScalarInternal(Boolean needCLStype, Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteScalar()
at RefNumGen.frmindex.BtnAdd_Click(Object sender, EventArgs e) in C:\Documents and Settings\daimous\My Documents\Visual Studio 2005\Projects\BCMD\WindowsApplication1\frmindex.cs:line 648
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at RefNumGen.Program.Main() in C:\Documents and Settings\daimous\My Documents\Visual Studio 2005\Projects\BCMD\WindowsApplication1\Program.cs:line 17
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

[jmcilhinney]

1. Why are you using a parameter for password and not user name?
2. I'm pretty sure that you don't mean this:

OraCmd.Parameters.AddWithValue(@"encPswrd", encPswrd);

but rather this:

OraCmd.Parameters.AddWithValue("@encPswrd", encPswrd);

3. I'm pretty sure that you don't mean this:

MessageBox.Show("EXECUTE: " + (OraCmd.ExecuteScalar().ToString ()));

but rather this:

MessageBox.Show("EXECUTE: " + (OraCmd.CommandText));

4. I'm fairly sure that oracle doesn't use "@" to prefix a parameter. That's an SQL Server convention. I'm pretty sure that MySQL and Oracle use "?" and ":" but I can't remember which one is which. A quick online search for sample code will tell you.

[daimous]

oh...thanks for that...I think ":" use to prefix a parameter but im not sure of it...but, i have already replace the "@" with ":" and i think it works but it has still an error.

code:

OracleConnection OraConn = new OracleConnection(OraConnStr);
OraConn.Open ();

encPswrd = RndCrypt(txtAddPassword.Text, txtAddUsername.Text.ToUpper());
qryStr = @"select count(*) from users where username='" + txtAddUsername.Text.ToUpper() +
"' and password = :pencPswrd and admin_user='TRUE'";
OracleCommand OraCmd = new OracleCommand(qryStr,OraConn);
OraCmd.Parameters.AddWithValue(":pencPswrd", encPswrd);
MessageBox.Show("QRYSTR1: " + qryStr);
MessageBox.Show("EXECUTE: " + (OraCmd.CommandText));
if ((int)OraCmd.ExecuteScalar() != null || txtAddUsername.Text == "abs")
{

}
else
{

}

error:

System.InvalidCastException was unhandled
Message="Specified cast is not valid."
Source="RefNumGen"
StackTrace:
at RefNumGen.frmindex.BtnAdd_Click(Object sender, EventArgs e) in C:\Documents and Settings\daimous\My Documents\Visual Studio 2005\Projects\BCMD\WindowsApplication1\frmindex.cs:line 650
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.PerformClick()
at System.Windows.Forms.Form.ProcessDialogKey(Keys keyData)
at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData)
at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData)
at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData)
at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData)
at System.Windows.Forms.Control.PreProcessMessage(Message& msg)
at System.Windows.Forms.Control.PreProcessControlMessageInternal(Control target, Message& msg)
at System.Windows.Forms.Application.ThreadContext.PreTranslateMessage(MSG& msg)
at System.Windows.Forms.Application.ThreadContext.System.Windows.Forms.UnsafeNativeMethods.IMsoComponen t.FPreTranslateMessage(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at RefNumGen.Program.Main() in C:\Documents and Settings\daimous\My Documents\Visual Studio 2005\Projects\BCMD\WindowsApplication1\Program.cs:line 17
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

[jmcilhinney]

Firstly, there's no need or point comparing the return value of the query to null. It's returning an int and an int can never be null. That query is returning the number of rows that match certain criteria. You want to know whether that is zero or non-zero. There is no chance that the query will not return a value.

Also, why are you still using a parameter for password and not for user name? There is no reason not to use a parameter for user name. You should also not be using txtAddUsername.Text.ToUpper() twice. Call it once and assign the result to a variable, then pass that variable to your RndCrypt method and the user name parameter.

Finally, for the actual issue, what line does the exception get thrown on?

Additionally, shouldn't you be testing for the user name being "abs" first? It looks like if that is true then the query doesn't even need executing.

[daimous]

thanks for that info...anyway, i was able to figure out the solution i've change the

if ((int)OraCmd.ExecuteScalar() != null || txtAddUsername.Text == "abs")

with this code

 if (int.Parse(OraCmd.ExecuteScalar().ToString()) != 0)

since it is where error occurs.I get rid of the

 txtAddUsername.Text == "abs"

becuase its nothing important to my code its just my way of debugging..anyway, I just would like to ask if is there any better solution than that?

[jmcilhinney]

I haven't used Oracle so I don't know if it has some differences to SQL Server in this regard but I can't see why it would. COUNT is always going to return a value so I can't see why you shouldn't just be able to cast the result of ExecuteScalar directly to an int. Having said that, for debugging purposes you should make use of the debugger. Assign the result to an object variable and use the IDE to see what type of object it refers to and what its value is. As far as I can see it should always be an int and its value will always be zero or greater.

[daimous]

okey thanks...maybe i'll try to use the IDE for debugging..