Pavithra_S
asked on
How to execute stored procedures on mysql from C#?
{"ERROR [HYT00] Incorrect number of arguments for PROCEDURE test expected 5, got 0"} System.Data.Odbc.OdbcExcep
Trying to insert form data into a table thru an sp after the click of a button..
Trying to insert form data into a table thru an sp after the click of a button..
OdbcCom.CommandType = System.Data.CommandType.StoredProcedure;
OdbcParameter pname = new OdbcParameter();
pname.Value = txtbox_Name.Text;
pname.Direction = System.Data.ParameterDirection.Input;
OdbcParameter padd1 = new OdbcParameter();
padd1.Direction = System.Data.ParameterDirection.Input;
padd1.Value = txtbox_Add1.Text;
OdbcParameter padd2 = new OdbcParameter();
padd2.Direction = System.Data.ParameterDirection.Input;
padd2.Value = txtbox_Add2.Text;
OdbcParameter pstate = new OdbcParameter();
pstate.Direction = System.Data.ParameterDirection.Input;
pstate.Value = txtbox_St.Text;
OdbcParameter pzip = new OdbcParameter();
pzip.Direction = System.Data.ParameterDirection.Input;
pzip.Value = txtbox_Zip.Text;
OdbcCom.Parameters.Add(pname);
OdbcCom.Parameters.Add(padd1);
OdbcCom.Parameters.Add(padd2);
OdbcCom.Parameters.Add(pstate);
OdbcCom.Parameters.Add(pzip);
OdbcCon.Open();
int i = OdbcCom.ExecuteNonQuery();
OdbcCon.Close();
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have a zipcode column which was int,
and in my code pzip.Value = txtbox_Zip.Text;
I was trying to convert this to int before calling the stored proc, which was throwing the invalid cast exception in my guess,
Now I have changed this column to varchar and savign this as text...
How do you pass an integer value from a textbox to a stored procedure? In my guess this is quite simple but I am doing something wrong... is there any property that I need to set??
sreeven
I will try the sample u have given and will let u knw..
newbieal
Thanks for the link I wanted to know more about executenonquery
phlipjonathan
I need to use executequery... I usually use executescalar when I am sql statement directly without using the commands..
and in my code pzip.Value = txtbox_Zip.Text;
I was trying to convert this to int before calling the stored proc, which was throwing the invalid cast exception in my guess,
Now I have changed this column to varchar and savign this as text...
How do you pass an integer value from a textbox to a stored procedure? In my guess this is quite simple but I am doing something wrong... is there any property that I need to set??
sreeven
I will try the sample u have given and will let u knw..
newbieal
Thanks for the link I wanted to know more about executenonquery
phlipjonathan
I need to use executequery... I usually use executescalar when I am sql statement directly without using the commands..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_insertcompany` $$
CREATE DEFINER=`srguser`@`%` PROCEDURE `test`(IN name varchar(30),IN add1 varchar(50), IN add2 varchar(20),IN state varchar(15), IN zip varchar(10),OUT last_inserted_ID INT)
BEGIN
Insert into company (Name, Address1, Address2, State, Zip) values ('name', 'add1', 'add2','state','zip');
select @@IDENTITY as 'last_inserted_id';
END $$
DELIMITER ;
The above is my Sp on mysql
I am able to create it but when I cal this procedure from C# its giving the
ERROR HYT00 "Can't return the result set in the given context"
Can u help me with this?? Thanks for all the help! really appreciate it
DROP PROCEDURE IF EXISTS `sp_insertcompany` $$
CREATE DEFINER=`srguser`@`%` PROCEDURE `test`(IN name varchar(30),IN add1 varchar(50), IN add2 varchar(20),IN state varchar(15), IN zip varchar(10),OUT last_inserted_ID INT)
BEGIN
Insert into company (Name, Address1, Address2, State, Zip) values ('name', 'add1', 'add2','state','zip');
select @@IDENTITY as 'last_inserted_id';
END $$
DELIMITER ;
The above is my Sp on mysql
I am able to create it but when I cal this procedure from C# its giving the
ERROR HYT00 "Can't return the result set in the given context"
Can u help me with this?? Thanks for all the help! really appreciate it
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DELIMITER $$
DROP PROCEDURE IF EXISTS `gps_srg`.`sp_insertcustom
CREATE PROCEDURE `gps_srg`.`sp_insertcustom
BEGIN
INSERT INTO customer (CompanyID,FirstName,LastN
END $$
DELIMITER ;
Some one Please tell me what is wrong with this statement!! its throwing some syntax error on line 4
Thanks a lot!
DROP PROCEDURE IF EXISTS `gps_srg`.`sp_insertcustom
CREATE PROCEDURE `gps_srg`.`sp_insertcustom
BEGIN
INSERT INTO customer (CompanyID,FirstName,LastN
END $$
DELIMITER ;
Some one Please tell me what is wrong with this statement!! its throwing some syntax error on line 4
Thanks a lot!
ASKER
regarding the
ERROR HYT00 "Can't return the result set in the given context"
I found an article which uses transactions in C#.. apparently we need to use transactions in C# for multiple sql statements...
http://msdn.microsoft.com/en-us/library/system.data.odbc.odbctransaction.aspx
That worked for me! Thanks for all the answers,
For people using C# and Mysql this might be really important....
ERROR HYT00 "Can't return the result set in the given context"
I found an article which uses transactions in C#.. apparently we need to use transactions in C# for multiple sql statements...
http://msdn.microsoft.com/en-us/library/system.data.odbc.odbctransaction.aspx
That worked for me! Thanks for all the answers,
For people using C# and Mysql this might be really important....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I still havent found a complete solution, i am still having trouble inserting the integer into the mysql table.. will open as a different question bu giving a more precise picture of the problem,, thanks!!
ASKER
I just got rid of the error but now I am facing an issue with the invalid cast exception for
int i = OdbcCom.ExecuteNonQuery();
Any ideas?
Previously I was callinf the Stored procedure without parameters.. I was adding the parameters but failed to call the sp with it...