- SQL code
select g.group_id as group_id,g.group_name as group_name,n.action_id as action_id,n.action as action,n.action_name as action_name,g.group_info as group_info,g.creator_name as creator_name,g.creator_time as creator_time from (rights_group_manager g left join rights_action_group a on a.group_id=g.group_id) left join rights_action n on a.action_id=n.action_id where g.group_id=1 order by g.group_id asc
上述语句在ACCESS下运行无任何错误，
可是在asp.net环境里运行却出现了错误：
SELECT 子句中包含一个保留字、拼写错误或丢失的参数，或标点符号不正确。
asp.net代码如下：
- C# code
public DataSet GetRoleAuthorityList(int group_id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select g.group_id as group_id,"); strSql.Append("g.group_name as group_name,"); strSql.Append("n.action_id as action_id,"); strSql.Append("n.action as action,"); strSql.Append("n.action_name as action_name,"); strSql.Append("g.group_info as group_info,"); strSql.Append("g.creator_name as creator_name,"); strSql.Append("g.creator_time as creator_time "); strSql.Append(" from (rights_group_manager g left join rights_action_group a on a.group_id=g.group_id) "); strSql.Append(" left join rights_action n on a.action_id=n.action_id "); if (group_id >= 0) { strSql.Append(" where g.group_id=" + group_id + " "); } strSql.Append(" order by g.group_id asc"); return DbHelperOleDb.Query(strSql.ToString());}- C# code
public static DataSet Query(string SQLString){ using (OleDbConnection connection = new OleDbConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (System.Data.OleDb.OleDbException ex) { throw new Exception(ex.Message); } return ds; }}- C# code
public static string connectionString = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["dbPath"]);
------解决方案--------------------------------------------------------
断点调试，看看拼接的Sql语句是否有语法错误。
建议楼主不要拼接sql,用参数化
string sqlCommandText=@"
select
g.group_id , g.group_name, n.action_id, n.action
, n.action_name, g.group_info, g.creator_name, g.creator_time
from
(
rights_group_manager g
left join rights_action_group a on a.group_id = g.group_id
)
left join rights_action n on a.action_id = n.action_id
where g.group_id=@GroupID
order by g.group_id asc";
看看action 是否是关键字