同一条SQL语句，不同的地方有着不同的显示，为什么呢解决办法
日期：2014-05-18 浏览次数：20444 次
同一条SQL语句，不同的地方有着不同的显示，为什么呢
上述语句在ACCESS下运行无任何错误，
可是在asp.net环境里运行却出现了错误：
SELECT 子句中包含一个保留字、拼写错误或丢失的参数，或标点符号不正确。
asp.net代码如下：
------解决方案--------------------
断点调试，看看拼接的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 是否是关键字
- 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 是否是关键字
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
