发生错误 不知道到底是哪里有问题 OdbcCommand cmd = new OdbcCommand肯定可以 写法有问题 有写过成功的贴下啦
OdbcConnection conn = new OdbcConnection("Dsn=MysqlDsn"); OdbcCommand cmd = new OdbcCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = "CALL InsertIt(?)"; //这里需要注意(1) cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("t", OdbcType.VarChar, 20); cmd.Parameters["t"].Value = "niaaahao"; cmd.ExecuteNonQuery(); Response.Write("Hello World");嘿嘿,细心的朋友估计发现了,我在注释 (1)的地方 --- 这个是可以的 OdbcConnection conn = new OdbcConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]); conn.Open(); OdbcCommand comm = conn.CreateCommand(); string sql = "call sp_myPro(@a)"; comm.CommandText = sql; comm.CommandType = CommandType.StoredProcedure; OdbcParameter para = new OdbcParameter("@a", OdbcType.Int, 11); para.Direction = ParameterDirection.Output; comm.Parameters.Add(para); comm.ExecuteNonQuery(); conn.Close(); string result = para.Value.ToString(); 存储过程 CREATE PROCEDURE `sp_myPro`(OUT param1 INTEGER(11)) NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT '' BEGIN select count(id) into param1 smallbelial 幕后黑手-Boss等级: 结帖率:100% 楼主 发表于: 2008-11-21 09:31:27 在asp.net中,我利用odbc调用mysql的存储过程。别的都没有问题 就是当存储过程有 out 类型的参数时,得不到他的值。 尔用MysqlClient来连接就可以。 存储过程为:SQL code?12345 CREATE PROCEDURE `t2`(out x varchar(30)) Begin set x = "Hello world"; end; 下面时利用 MySqlClient 的方式来连接的代码:C# code?123456789101112 MySqlConnection conn = new MySqlConnection("server=localhost;uid=root;pwd=sa;database=netschool"); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = conn; conn.Open(); cmd.CommandText = "t2"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("x", MySqlDbType.VarChar, 30); cmd.Parameters[0].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); string a = cmd.Parameters[0].Value.ToString(); Response.Write(a); 尔我用odbc的连接方式为:C# code?12345678910111213 OdbcConnection conn = new OdbcConnection("Dsn=MysqlDsn"); OdbcCommand cmd = new OdbcCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = "call t2(@a)"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@a", OdbcType.VarChar, 20); cmd.Parameters[0].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); string a = cmd.Parameters[0].Value.ToString(); Response.Write(a);
Sql代码 CREATE PROCEDURE `yuan_user_cc`(in xname varchar(100),in xemail varchar(100),in xpassword varchar(100),out xid integer) begin insert into yuan_user(name,email,password)values(xname,xemail,xpassword); select id into xid from yuan_user where name=xname; end C#代码 private string shuju_lianjie(string name, string email, string password) {
Int16 id;//返回的id值 MySqlConnection mysqlcon; MySqlDataAdapter mysqldata = new MySqlDataAdapter(); DataSet dataset = new DataSet(); mysqlcon = new MySqlConnection("server=localhost; user id=root; password=123; database=yuan_test; pooling=false;charset=gb2312"); mysqldata.SelectCommand = new MySqlCommand(); mysqldata.SelectCommand.Connection = mysqlcon; mysqldata.SelectCommand.CommandText = "yuan_user_cc"; mysqldata.SelectCommand.CommandType = CommandType.StoredProcedure; //设置参数,添加到数据库 MySqlParameter name_parameter = new MySqlParameter("?xname", MySqlDbType.VarChar,100);//mysql的存储过程参数是以?打头的!!!! name_parameter.Value = name; mysqldata.SelectCommand.Parameters.Add(name_parameter); //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ MySqlParameter email_parameter = new MySqlParameter("?xemail", MySqlDbType.VarChar, 100);//mysql的存储过程参数是以?打头的!!!! email_parameter.Value = email; mysqldata.SelectCommand.Parameters.Add(email_parameter); //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ MySqlParameter password_parameter = new MySqlParameter("?xpassword", MySqlDbType.VarChar, 100);//mysql的存储过程参数是以?打头的!!!! password_parameter.Value = password; mysqldata.SelectCommand.Parameters.Add(password_parameter); //++++++++++++++++++++++++++返回值++++++++++++++++++++++++++++++++++++++++++ MySqlParameter id_parameter = new MySqlParameter("?xid", MySqlDbType.Int16,15);//mysql的存储过程参数是以?打头的!!!! id_parameter.Direction=ParameterDirection.Output; mysqldata.SelectCommand.Parameters.Add(id_parameter); //+++++++++++++++++++++++++打开数据库,插入数据,返回id值+++++++++++++++++++ try { mysqldata.Fill(dataset, "yuan_user_cc");
写法有问题
有写过成功的贴下啦
OdbcCommand cmd = new OdbcCommand();
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "CALL InsertIt(?)"; //这里需要注意(1)
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("t", OdbcType.VarChar, 20);
cmd.Parameters["t"].Value = "niaaahao";
cmd.ExecuteNonQuery();
Response.Write("Hello World");嘿嘿,细心的朋友估计发现了,我在注释 (1)的地方 --- 这个是可以的
OdbcConnection conn = new OdbcConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]);
conn.Open();
OdbcCommand comm = conn.CreateCommand();
string sql = "call sp_myPro(@a)";
comm.CommandText = sql;
comm.CommandType = CommandType.StoredProcedure;
OdbcParameter para = new OdbcParameter("@a", OdbcType.Int, 11);
para.Direction = ParameterDirection.Output;
comm.Parameters.Add(para);
comm.ExecuteNonQuery();
conn.Close(); string result = para.Value.ToString(); 存储过程
CREATE PROCEDURE `sp_myPro`(OUT param1 INTEGER(11))
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
select count(id) into param1 smallbelial
幕后黑手-Boss等级:
结帖率:100% 楼主 发表于: 2008-11-21 09:31:27
在asp.net中,我利用odbc调用mysql的存储过程。别的都没有问题
就是当存储过程有 out 类型的参数时,得不到他的值。
尔用MysqlClient来连接就可以。
存储过程为:SQL code?12345 CREATE PROCEDURE `t2`(out x varchar(30)) Begin set x = "Hello world"; end; 下面时利用 MySqlClient 的方式来连接的代码:C# code?123456789101112 MySqlConnection conn = new MySqlConnection("server=localhost;uid=root;pwd=sa;database=netschool"); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = conn; conn.Open(); cmd.CommandText = "t2"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("x", MySqlDbType.VarChar, 30); cmd.Parameters[0].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); string a = cmd.Parameters[0].Value.ToString(); Response.Write(a);
尔我用odbc的连接方式为:C# code?12345678910111213 OdbcConnection conn = new OdbcConnection("Dsn=MysqlDsn"); OdbcCommand cmd = new OdbcCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = "call t2(@a)"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@a", OdbcType.VarChar, 20); cmd.Parameters[0].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); string a = cmd.Parameters[0].Value.ToString(); Response.Write(a);
CREATE PROCEDURE `yuan_user_cc`(in xname varchar(100),in xemail varchar(100),in xpassword varchar(100),out xid integer) begin
insert into yuan_user(name,email,password)values(xname,xemail,xpassword);
select id into xid from yuan_user where name=xname; end C#代码
private string shuju_lianjie(string name, string email, string password)
{
Int16 id;//返回的id值
MySqlConnection mysqlcon;
MySqlDataAdapter mysqldata = new MySqlDataAdapter();
DataSet dataset = new DataSet();
mysqlcon = new MySqlConnection("server=localhost; user id=root; password=123; database=yuan_test; pooling=false;charset=gb2312");
mysqldata.SelectCommand = new MySqlCommand();
mysqldata.SelectCommand.Connection = mysqlcon;
mysqldata.SelectCommand.CommandText = "yuan_user_cc";
mysqldata.SelectCommand.CommandType = CommandType.StoredProcedure;
//设置参数,添加到数据库
MySqlParameter name_parameter = new MySqlParameter("?xname", MySqlDbType.VarChar,100);//mysql的存储过程参数是以?打头的!!!!
name_parameter.Value = name;
mysqldata.SelectCommand.Parameters.Add(name_parameter);
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
MySqlParameter email_parameter = new MySqlParameter("?xemail", MySqlDbType.VarChar, 100);//mysql的存储过程参数是以?打头的!!!!
email_parameter.Value = email;
mysqldata.SelectCommand.Parameters.Add(email_parameter);
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
MySqlParameter password_parameter = new MySqlParameter("?xpassword", MySqlDbType.VarChar, 100);//mysql的存储过程参数是以?打头的!!!!
password_parameter.Value = password;
mysqldata.SelectCommand.Parameters.Add(password_parameter);
//++++++++++++++++++++++++++返回值++++++++++++++++++++++++++++++++++++++++++
MySqlParameter id_parameter = new MySqlParameter("?xid", MySqlDbType.Int16,15);//mysql的存储过程参数是以?打头的!!!!
id_parameter.Direction=ParameterDirection.Output;
mysqldata.SelectCommand.Parameters.Add(id_parameter);
//+++++++++++++++++++++++++打开数据库,插入数据,返回id值+++++++++++++++++++
try
{
mysqldata.Fill(dataset, "yuan_user_cc");
id = (Int16)id_parameter.Value;
return id.ToString();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
return String.Empty;
}
}