同一条SQL语句，不同的地方有着不同的显示，为什么呢解决办法

同一条SQL语句，不同的地方有着不同的显示，为什么呢

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 是否是关键字

查看更多 下一篇