Click here to Skip to main content
11,576,977 members (49,982 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: MySQL Exception VB.NET
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
Comments
CHill60 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 10-Nov-13 8:14am
   
I agree (but sometimes needs must!) ... perhaps its a problem with versions of MySQL and/or VB.net?

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 251
1 DamithSL 155
2 Abhinav S 130
3 OriginalGriff 105
4 Afzaal Ahmad Zeeshan 85
0 OriginalGriff 715
1 Sergey Alexandrovich Kryukov 631
2 Abhinav S 528
3 F-ES Sitecore 420
4 Suvendu Shekhar Giri 365


Advertise | Privacy | Mobile
Web03 | 2.8.150603.1 | Last Updated 9 Nov 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100