System overflow exception in VB.nET/Mysql Based authentication

Question

1.00/5 (1 vote)

See more:

HI Friends I am calling a procedure on MySQL based db server from my vb.net based application .
Now I haven't used any Int variable in my code but I am getting overflow exception for INt32 conversion as shown below..

Below is stack trace for the same

System.OverflowException: Value was either too large or too small for an Int32.
   at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
   at System.Int32.Parse(String s, IFormatProvider provider)
   at MySql.Data.Types.MySqlInt32.MySql.Data.Types.IMySqlValue.ReadValue(MySqlPacket packet, Int64 length, Boolean nullVal)
   at MySql.Data.MySqlClient.NativeDriver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue valObject)
   at MySql.Data.MySqlClient.Driver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue value)
   at MySql.Data.MySqlClient.ResultSet.ReadColumnData(Boolean outputParms)
   at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlDataReader.Read()
   at MySql.Data.MySqlClient.StoredProcedure.ProcessOutputParameters(MySqlDataReader reader)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at ALMTestBeta.ATB_DBConnector.create_session(String userid) in C:\Users\Satyam Mishra\Documents\SharpDevelop Projects\ATB\ALMTestBeta\ATB_DAL\ATB_DBConnector.vb:line 82
   at ALMTestBeta.AWM.InitiateAWM() in C:\Users\Satyam Mishra\Documents\SharpDevelop Projects\ATB\ALMTestBeta\ATB_BL\AWM_BL\AWM.vb:line 55
   at ALMTestBeta.AuthenticationWindow.CPGMLauncher_ButtonClick(Object sender, EventArgs e) in C:\Users\Satyam Mishra\Documents\SharpDevelop Projects\ATB\ALMTestBeta\ATB_UI\AWM_UI\AuthenticationWindow.vb:line 43
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   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.SendMessage(HandleRef hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
   at System.Windows.Forms.Control.SendMessage(Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.Control.ReflectMessageInternal(IntPtr hWnd, Message& m)
   at System.Windows.Forms.Control.WmCommand(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.GroupBox.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.CallWindowProc(IntPtr wndProc, IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
   at System.Windows.Forms.NativeWindow.DefWndProc(Message& m)
   at System.Windows.Forms.Control.DefWndProc(Message& m)
   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.IMsoComponentManager.FPushMessageLoop(IntPtr 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(ApplicationContext context)
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
   at ALMTestBeta.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

NOw the coding which is calling the procedure is

Public Function create_session(ByVal userid As String) As String
    Dim session_id As String
    session_id="-1"

    myCommand=New MySqlCommand()
    myCommand.Connection=atbconn

    myCommand.CommandText="p_create_chk_session"
    myCommand.CommandType=CommandType.StoredProcedure


    myCommand.Parameters.AddWithValue("p_user_id",userid)
    myCommand.Parameters("p_user_id").Direction=ParameterDirection.Input

    myCommand.Parameters.AddWithValue("p_session_id",MySqlDbType.VarChar)
    myCommand.Parameters("p_session_id").Direction=ParameterDirection.Output


    Try
        myCommand.ExecuteNonQuery()
        session_id=CType(myCommand.Parameters("p_session_id").Value,String)

    Catch myprocedureException As MySqlException
        MsgBox ("ErrorGenerated in session creation " & myprocedureException.Number & "has occured " & myprocedureException.Message)
    End Try
    Return session_id

End Function

and called procedure is as below

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE p_create_chk_session
				(IN p_user_id varchar(45),
				OUT p_session_id varchar(45)
				)
BEGIN
	Declare v_session_exists int;
	set v_session_exists=0;
	set v_session_exists=(select count(1) from atb_session_records where user_id=p_user_id );

	if v_session_exists=0 then
		-- create a new session, 0 means there are no older entry for the same user.
	Set p_session_id= f_datetoINT((select sysdate() from dual));
	
	insert into atb_session_records (user_id,
									user_session_id
									)
									values
									(
									p_user_id,
									p_session_id
									);
	Else
		-- session already exists, return the status as -1
		set p_session_id=-1;
	End if ;
	

END

as you can see I haven't used any of the integer variable here.
I cant understand as I haven't used any int so how-come i m getting int32 conversion exception.

Please help , i m kind of on deadline on this.
thanks in advance

Posted 8-Nov-13 19:24pm

Shruti Sagar Mishra

Add a Solution

Comments

CHill60 9-Nov-13 8:27am

I might be wrong but isn't the function f_datetoINT returning an integer? If so then you shouldn't just be assigning it to a varchar ... if it's returning a varchar then the name of the function is so so wrong.

Shruti Sagar Mishra 9-Nov-13 9:50am

Thanks for the reply ..I agree that the name of the function is wrong I will update this function.
But actually this returning a varchar.So I dont think would be the problem here, apart from the exception the insert query is working perfectly fine in the procedure stated above.

CHill60 9-Nov-13 11:47am

Ok. Good to know that the procedure works "stand-alone". Could it be the line myCommand.Parameters.AddWithValue("p_session_id",MySqlDbType.VarChar) ... you've used AddWithValue but are passing a type as that value ... the type is probably an integer under the covers

Shruti Sagar Mishra 9-Nov-13 23:48pm

I checked it. This is the way we declare the output parameter fr a procedure
parameter is declaration different for input parameter we declare the value of the parameter
for output parameter we provide the type of the parameter instead.

Shruti Sagar Mishra 9-Nov-13 23:50pm

I have checked it. But this is the way to declare an output parameter , i/p and o/p parameter declaration is same , apart from the fact that in o/p we need to provide the type of the parameter instead of the actual value as I have given in the code

CHill60 10-Nov-13 7:12am

Yes - I was having a senior moment I think. If you try changing the commandtext to "CALL p_create_chk_session (@p_user_id, @p_session_id)" does it still fail [check the syntax - I haven't got an IDE at the moment]

Shruti Sagar Mishra 10-Nov-13 7:58am

Now this is embarrassing,,
I tried to run the procedure manually it was working fine.
But I came across a new funny situation.
I Changed the
mycommand.parameters.addwithvalue to mycommand.parameters.add
And rest all I put same, and this time worked.
Now the question is infact the .add property is now obsolete , everywhere at mysql references they have mentioned to use addwithValue instead of add, then howcome this is failing.
SO right now I have chnaged the function for parameter.add statement,But I am getting warnings for the same .I can proceed with this but doesnt look neat to me

CHill60 10-Nov-13 8:14am

I agree (but sometimes needs must!) ... perhaps its a problem with versions of MySQL and/or VB.net?

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)