help with error when use mysql stored procedure

[minus4]

hi guys:

Hi im fairly new to .net and i am doing a desktop application using mysql 5.5 or something

I am using a stored procedure as below:

1    DELIMITER $$
2    
3    DROP PROCEDURE IF EXISTS `uyrjobs`.`sp_updateclient` $$
4    CREATE DEFINER=`root`@`192.168.1.%` PROCEDURE `sp_updateclient`(
5    IN iclientid INT,
6    IN ibusiness VARCHAR(255),
7    IN icontact VARCHAR(200),
8    IN iln1 VARCHAR(255),
9    IN iln2 VARCHAR(255),
10   IN iln3 VARCHAR(255),
11   IN iln4 VARCHAR(255),
12   IN icounty VARCHAR(150),
13   IN ipostcode VARCHAR(25),
14   IN imobile VARCHAR(25),
15   IN iphone VARCHAR(25),
16   IN ifax VARCHAR(25),
17   IN iemail VARCHAR(255),
18   IN inotes TEXT
19   )
20   BEGIN
21   UPDATE clients
22   SET business=ibusiness,
23   contact=icontact,
24   ln1=iln1,
25   ln2=iln2,
26   ln3=iln3,
27   ln4=iln4,
28   county=icounty,
29   postcode=ipostcode,
30   mobile=imobile,
31   telephone=iphone,
32   fax=ifax,
33   email=iemail,
34   notes=inotes
35   WHERE clients.clientid=iclientid;
36   
37   END $$
38   
39   DELIMITER ;

My update is done when they press the update button on the form with this:

1    Private Sub updateclient(ByVal sender As Object, ByVal e As System.EventArgs)
2            Dim btnClicked As Button = DirectCast(sender, Button)
3            Dim intIndex As Integer = Integer.Parse(btnClicked.Tag)
4    
5            Dim myupdate As New Dbase
6            Dim stringfields() As String = {"iclientid", "ibusiness", "icontact", "iln1" _
7            , "iln2", "iln3", "iln4", "icounty", "ipostcode", "imobile", "iphone", _
8            "ifax", "iemail", "inotes"}
9    
10           Dim stringvalues(13) As String
11           Dim stringtypes(13) As String
12   
13           stringvalues(0) = intIndex
14           stringtypes(0) = "Int32"
15           stringvalues(1) = txtBusiness.Text.ToString
16           stringtypes(1) = "VarChar"
17           stringvalues(2) = txtContact.Text.ToString
18           stringtypes(2) = "VarChar"
19           stringvalues(3) = txtln1.Text.ToString
20           stringtypes(3) = "VarChar"
21           stringvalues(4) = txtln2.Text.ToString
22           stringtypes(4) = "VarChar"
23           stringvalues(5) = txtln3.Text.ToString
24           stringtypes(5) = "VarChar"
25           stringvalues(6) = txtln4.Text.ToString
26           stringtypes(6) = "VarChar"
27           stringvalues(7) = txtcounty.Text.ToString
28           stringtypes(7) = "VarChar"
29           stringvalues(8) = txtpostcode.Text.ToString
30           stringtypes(8) = "VarChar"
31           stringvalues(9) = txtMobile.Text.ToString
32           stringtypes(9) = "VarChar"
33           stringvalues(10) = txtPhone.Text.ToString
34           stringtypes(10) = "VarChar"
35           stringvalues(11) = txtFax.Text.ToString
36           stringtypes(11) = "VarChar"
37           stringvalues(12) = txtEmail.Text.ToString
38           stringtypes(12) = "VarChar"
39           stringvalues(13) = txtClientComments.Text.ToString()
40           stringtypes(13) = "Text"
41   
42           myupdate.Updatedatabase("sp_updateclient", stringfields, stringvalues, stringtypes)
43   
44   
45       End Sub

This then uses the Sub updatedatabase as below:

1        Public Sub Updatedatabase(ByVal mystring As String, ByVal myfields As Array, ByVal myvalues As Array, ByVal mytypes As Array)
2            
3            conn.Open()
4    
5            myCommand = New MySqlCommand(mystring, conn)
6            myCommand.CommandType = CommandType.StoredProcedure
7    
8            Dim i As Integer
9    
10           For i = 0 To UBound(myfields)
11               myparam = myCommand.Parameters.Add(New MySqlParameter(myfields(i).ToString, getDbType(mytypes(i).ToString)))
12               myparam.Value = myvalues(i).ToString
13           Next
14   
15           Try
16               myCommand.ExecuteNonQuery()
17               MessageBox.Show("Update done successfully")
18           Catch myerror As MySqlException
19               MsgBox("There was an error with the update: " & myerror.Message.ToString & " " & myerror.Number.ToString)
20           End Try
21       End Sub
22   
23       Private Function getDbType(ByVal mytype As String)
24   
25           Select Case mytype
26               Case "Int32"
27                   Return MySqlDbType.Int32
28               Case "VarChar"
29                   Return MySqlDbType.VarChar
30               Case "Text"
31                   Return MySqlDbType.LongText
32               Case Else
33                   Return MySqlDbType.VarChar
34           End Select
35   
36       End Function

I then get the following Error:



myCommand.ExecuteNonQuery()

Input string was not in the correct format:

stack trace:

 "   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)    
     at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)    
     at System.String.System.IConvertible.ToInt32(IFormatProvider provider)    
     at System.Convert.ToInt32(Object value)    
     at MySql.Data.Types.MySqlInt32.MySql.Data.Types.IMySqlValue.WriteValue(MySqlStream stream, Boolean binary, Object val, Int32 length)    
     at MySql.Data.MySqlClient.MySqlParameter.Serialize(MySqlStream stream, Boolean binary)    
     at MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlStream stream, String parmName)    
     at MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlStream stream)    
     at MySql.Data.MySqlClient.Statement.BindParameters()    at MySql.Data.MySqlClient.PreparableStatement.Execute()    
     at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)    
     at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()    
     at uyrdesign.Dbase.Updatedatabase(String mystring, Array myfields, Array myvalues, Array mytypes) in C:\Documents and Settings\dave\My Documents\Visual Studio 2008\Projects\uyrdesign\uyrdesign\Dbase.vb:line 137    
     at uyrdesign.clientdetails.updateclient(Object sender, EventArgs e) in C:\Documents and Settings\dave\My Documents\Visual Studio 2008\Projects\uyrdesign\uyrdesign\clientdetails.vb:line 123
     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.IMsoComponentManager.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(ApplicationContext context)
    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
    at uyrdesign.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
    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()"

Bit long but would really appreciate some help, on my own here and i have been trying everything for 3 days now.

Mnay thanks

 

[minus4]

fixed it.

This is what i did for all those that may need it

On my array of field names i added ? before the field name

I then removed the whole data type thing and now i just send this:

If mytypes(i).ToString = "Int32" Then
                myparam = myCommand.Parameters.Add(New MySqlParameter(myfields(i).ToString, Integer.Parse(myvalues(i))))
            Else
                myparam = myCommand.Parameters.Add(New MySqlParameter(myfields(i).ToString, myvalues(i).ToString))
            End If

Works fast, clean and now i have an update for all database updates no matter what stored procedure, or columns or anything.

However if anyone has any ideas on how i can improve this then please let me know

thanks