小弟在做一个数据录入时遇到了一个奇怪的问题,在sql manager 2007 for mysql中运行一段存储过程能够正常运行,但是一但在c#中调用就报System.Exception: ERROR [23000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''AddMembers'' at line 1using System.Collections; //这个是封装odbc的类
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Odbc;
using System;
public class MyWDABase : IDisposable
{
public string MySQLODBC;
public string MySQLODBCClStr;
public OdbcConnection MySQLODBCConnectionStr;
public OdbcCommand MySQLODBCCommand;
public OdbcDataAdapter MySQLODBCDataAdapter;
public OdbcDataReader MySQLODBCDataReader;
public string MyConString = "Dsn=MyConnect;uid=root;pwd=abcd1234a/";
public string MySevers = "SERVER\\SERVER";
public string MyDBMS = "dnt2";
public string MyLogin = "sa";
public string MyPW = "abcd1234a/";
public string MySQLTestConStr; // 检测冗余的调用
private bool disposedValue = false; // IDisposable
protected virtual void Dispose(bool disposing)
{
if (!this.disposedValue)
{
if (disposing)
{
// TODO: 显式调用时释放非托管资源
if ((MySQLODBCConnectionStr != null))
{
MySQLODBCConnectionStr.Close();
MySQLODBCConnectionStr.Dispose();
MySQLODBCConnectionStr = null;
}
if ((MySQLODBCDataReader != null))
{
MySQLODBCDataReader.Close();
MySQLODBCDataReader = null;
}
if ((MySQLODBCDataAdapter != null))
{
MySQLODBCDataAdapter.Dispose();
MySQLODBCDataAdapter = null;
}
if ((MySQLODBCCommand != null))
{
MySQLODBCCommand.Dispose();
MySQLODBCCommand = null;
}
}
// TODO: 释放共享的非托管资源
}
this.disposedValue = true;
}
public MyWDABase()
{
MySQLTestConStr = MyConString;
MySQLODBCConnectionStr = new OdbcConnection(MySQLTestConStr);
}
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Odbc;
using System;
public class MyWDABase : IDisposable
{
public string MySQLODBC;
public string MySQLODBCClStr;
public OdbcConnection MySQLODBCConnectionStr;
public OdbcCommand MySQLODBCCommand;
public OdbcDataAdapter MySQLODBCDataAdapter;
public OdbcDataReader MySQLODBCDataReader;
public string MyConString = "Dsn=MyConnect;uid=root;pwd=abcd1234a/";
public string MySevers = "SERVER\\SERVER";
public string MyDBMS = "dnt2";
public string MyLogin = "sa";
public string MyPW = "abcd1234a/";
public string MySQLTestConStr; // 检测冗余的调用
private bool disposedValue = false; // IDisposable
protected virtual void Dispose(bool disposing)
{
if (!this.disposedValue)
{
if (disposing)
{
// TODO: 显式调用时释放非托管资源
if ((MySQLODBCConnectionStr != null))
{
MySQLODBCConnectionStr.Close();
MySQLODBCConnectionStr.Dispose();
MySQLODBCConnectionStr = null;
}
if ((MySQLODBCDataReader != null))
{
MySQLODBCDataReader.Close();
MySQLODBCDataReader = null;
}
if ((MySQLODBCDataAdapter != null))
{
MySQLODBCDataAdapter.Dispose();
MySQLODBCDataAdapter = null;
}
if ((MySQLODBCCommand != null))
{
MySQLODBCCommand.Dispose();
MySQLODBCCommand = null;
}
}
// TODO: 释放共享的非托管资源
}
this.disposedValue = true;
}
public MyWDABase()
{
MySQLTestConStr = MyConString;
MySQLODBCConnectionStr = new OdbcConnection(MySQLTestConStr);
}
解决方案 »
- mysql 分布式存储问题????
- mysql最大连接数
- 请教一个SQL,小弟不才.请教公司很多同事.都不会.
- mysql slave如何仅仅同步master的个别表?
- linux mysql表只能查询,不能更新和插入
- Postgres的permission deniey问题
- 调用my sql 数据库时? 如何实现安汉字得拼音字母进行排序?
- 那位兄弟能给出JAVA联MYSQL的例子
- 大家的mysql 数据同步机制replication是如何监控的
- Mysql 更新数据慢不成功的问题
- 今天才到了一篇文章讲php中文分词全文索引,附地址,想问下这么做数据量上千万,速度有多快?
- mysql导数据问题
public void MySQLODBCOpenConnection()
{
try
{
MySQLODBCConnectionStr.Open();
} catch (System.Exception OleDBExceptionErr)
{
throw new System.Exception(OleDBExceptionErr.Message, OleDBExceptionErr.InnerException);
}
}
public void MySQLODBCCloseConnection()
{
MySQLODBCConnectionStr.Close();
}
public void InitializeMySQLODBCCommand()
{
if (MySQLODBCCommand == null)
{
try
{
MySQLODBCCommand = new OdbcCommand(MySQLODBC, MySQLODBCConnectionStr);
if (!MySQLODBC.ToUpper().StartsWith("SELECT") & !MySQLODBC.ToUpper().StartsWith("INSERT") & !MySQLODBC.ToUpper().StartsWith("UPDATE") & !MySQLODBC.ToUpper().StartsWith("DELECT"))
{
MySQLODBCCommand.CommandType = CommandType.StoredProcedure; }
}
catch (System.Exception OleDBExceptionErr)
{
throw new System.Exception(OleDBExceptionErr.Message, OleDBExceptionErr.InnerException);
} }
}
public void MySQLODBCAddParameter(string Name, OdbcType Type, int Size, object Value)
{
try
{
MySQLODBCCommand.Parameters.Add(Name, Type, Size).Value = Value;
}
catch (OdbcException OleDbExceptionErr)
{
throw new System.Exception(OleDbExceptionErr.Message, OleDbExceptionErr.InnerException); }
}
public void InitializeMySQLODBCDataAdapter()
{
try
{
MySQLODBCDataAdapter = new OdbcDataAdapter();
MySQLODBCDataAdapter.SelectCommand = MySQLODBCCommand;
} catch (OdbcException OleDbExceptionErr)
{
throw new System.Exception(OleDbExceptionErr.Message, OleDbExceptionErr.InnerException); }
}
public void FillMySQLODBCDataSet(ref DataSet oDataSet, string TableName)
{
try
{
InitializeMySQLODBCCommand();
InitializeMySQLODBCDataAdapter();
MySQLODBCDataAdapter.Fill(oDataSet, TableName);
}
catch (System.Exception OleDBExceptionErr)
{
throw new System.Exception(OleDBExceptionErr.Message, OleDBExceptionErr.InnerException); }
MySQLODBCCommand.Dispose();
MySQLODBCCommand = null;
MySQLODBCDataAdapter.Dispose();
MySQLODBCDataAdapter = null;
}
public void FillMySQLODBCDubleDataSet(ref DataSet oDataSet)
{
try
{
InitializeMySQLODBCCommand();
InitializeMySQLODBCDataAdapter();
MySQLODBCDataAdapter.Fill(oDataSet);
}
catch (System.Exception OleDBExceptionErr)
{
throw new System.Exception(OleDBExceptionErr.Message, OleDBExceptionErr.InnerException); }
MySQLODBCCommand.Dispose();
MySQLODBCCommand = null;
MySQLODBCDataAdapter.Dispose();
MySQLODBCDataAdapter = null;
}
public void FillMySQLODBCDataTable(ref DataTable oDatatable)
{
try
{
InitializeMySQLODBCCommand();
InitializeMySQLODBCDataAdapter();
MySQLODBCDataAdapter.Fill(oDatatable);
}
catch (System.Exception OleDBExceptionErr)
{
throw new System.Exception(OleDBExceptionErr.Message, OleDBExceptionErr.InnerException);
}
MySQLODBCCommand.Dispose();
MySQLODBCCommand = null;
MySQLODBCDataAdapter.Dispose();
MySQLODBCDataAdapter = null; }
public int MySQLODBCExecuteStoredProcedure()
{
int functionReturnValue = 0;
try
{
MySQLODBCOpenConnection();
functionReturnValue = MySQLODBCCommand.ExecuteNonQuery();//这里出的错
}
catch (System.Exception OleDBExceptionErr)
{
throw new System.Exception(OleDBExceptionErr.Message, OleDBExceptionErr.InnerException);
}
finally
{
MySQLODBCCloseConnection();
}
return functionReturnValue;
}
public object MySQLODBCExecuteScalar()
{
object functionReturnValue;
try
{
MySQLODBCOpenConnection();
functionReturnValue = MySQLODBCCommand.ExecuteScalar();
}
catch (System.Exception OleDBExceptionErr)
{
throw new System.Exception(OleDBExceptionErr.Message, OleDBExceptionErr.InnerException);
}
finally
{
MySQLODBCCloseConnection();
}
return functionReturnValue;
}
public int MySQLODBCTLWDExecuteStoredProcedure()
{
int functionReturnValue = 0;
try
{
functionReturnValue = MySQLODBCCommand.ExecuteNonQuery();
}
catch (System.Exception OleDBExceptionErr)
{
throw new System.Exception(OleDBExceptionErr.Message, OleDBExceptionErr.InnerException);
}
return functionReturnValue;
}
#region " IDisposable Support "
// 添加此代码是为了正确实现可处置模式。
public void Dispose()
{
// 不要更改此代码。请将清理代码放入上面的 Dispose(ByVal disposing As Boolean) 中。
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
public string MySqlDataInsert(string username, string password, string Email, string regip)//传参数
{
DataSet chkdata = new DataSet();
base.MySQLODBC = "select uname from sdb_members where uname='" + username + "';";
base.FillMySQLODBCDataSet(ref chkdata, "chkName");
if (chkdata.Tables[0].Rows.Count > 0)
{
return "该用户已经存在.";
}
else
{
string TimeSpan = DateDiff(Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")), new DateTime(1970, 1, 1, 0, 0, 0));
base.MySQLODBC = "'AddMembers'";
base.InitializeMySQLODBCCommand();
base.MySQLODBCAddParameter("unameStr", System.Data.Odbc.OdbcType.VarChar, username.Length, username);
base.MySQLODBCAddParameter("passwordStr", System.Data.Odbc.OdbcType.VarChar, password.Length, password);
base.MySQLODBCAddParameter("emailStr", System.Data.Odbc.OdbcType.VarChar, Email.Length, Email);
base.MySQLODBCAddParameter("regtimestr", System.Data.Odbc.OdbcType.Int, TimeSpan.Length, Convert.ToInt64(TimeSpan));
base.MySQLODBCAddParameter("regIpstr", System.Data.Odbc.OdbcType.VarChar, regip.Length, regip);
if (base.MySQLODBCExecuteStoredProcedure() < 0)
{
return "帐号添加出错误";
}
return "帐号添加成功.";
}
}
}这个是调用的存储过程。CREATE DEFINER = 'root'@'localhost' PROCEDURE `Creatmember`(
IN `unameStr` varchar(50),
IN `passwordStr` varchar(32),
IN `emailStr` varchar(200),
IN `regtimestr` int(10),
IN `regIpstr` varchar(16)
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
INSERT INTO sdb_members SET member_lv_id = 1,uname= `unameStr`,name='',lastname=null,firstname=null,password= `passwordStr`,area='mainland:',mobile='',tel='',email=`emailStr`,zip='',addr='',province=null,city=null,order_num='0',refer_id=null,refer_url=null,b_year='1900',b_month='1',b_day='1',sex='0',addon=null,wedlock='0',education=null,vocation=null,interest=null,advance=0,advance_freeze=0,point_freeze=0,point_history=0,point=0,score_rate=null,reg_ip=`regIpstr`,regtime= `regtimestr`,state='0',pay_time=null,biz_money='0',pw_answer='',pw_question='',fav_tags=null,custom=null,cur=null,lang=null,unreadmsg='0',disabled='false',re=null,role_type='wholesale',re_type='b1';
INSERT INTO sdb_member_mattrvalue SET attr_id='11',member_id=(select max(member_id) from sdb_members),value='999999';
END;
base.MySQLODBC = "Creatmember"; //changed by ACMAIN
base.InitializeMySQLODBCCommand();
CALL Creatmember(5个参数)
{
if (MySQLODBCCommand == null)
{
try
{
MySQLODBCCommand = new OdbcCommand(MySQLODBC, MySQLODBCConnectionStr);
if (!MySQLODBC.ToUpper().StartsWith("SELECT") & !MySQLODBC.ToUpper().StartsWith("INSERT") & !MySQLODBC.ToUpper().StartsWith("UPDATE") & !MySQLODBC.ToUpper().StartsWith("DELECT"))
{
MySQLODBCCommand.CommandType = CommandType.StoredProcedure; }
}
catch (System.Exception OleDBExceptionErr)
{
throw new System.Exception(OleDBExceptionErr.Message, OleDBExceptionErr.InnerException);
} }
}
这里就是了吧。
{
object functionReturnValue;
try
{
MySQLODBCOpenConnection();
functionReturnValue = MySQLODBCCommand.ExecuteScalar();
}
catch (System.Exception OleDBExceptionErr)
{
throw new System.Exception(OleDBExceptionErr.Message, OleDBExceptionErr.InnerException);
}
finally
{
MySQLODBCCloseConnection();
}
return functionReturnValue;
}
这里我已经关闭连接了啊,而且这个数据类在使用mssqlserver的时候从来没出过错,改成access的时候也没出过错啊。