之前的数据库是ACCESS,SQL语句中用到了IIF函数,然后现在数据库转到了SQL SERVER,将其中的IIF语句改成了case函数,语句如下:
Select Distinct(XMMC) As XMMC, XMBH, RKSJ, SFXCPC, XCTKQK, SFBMSH, (Case When JSSCYJ IS NOT NULL Then '有' Else '无' End) As YWSCYJ, (Case When GZLXD IS NOT NULL Then '有' Else '无' End) As YWLXD From XMDATA Order By RKSJ Desc, XMMC Desc注:该语句在SQL SERVER中运行正常然后用下面函数打开运行该SQL语句并返回DataTable
//输入参数:SQL语句
//输出参数:
public static DataTable GetDataTable(string strSQL, string strTableName)
{
OleDbDataAdapter oleDBSQLDataAdapter = null;
DataSet objSQLDataSet = null;
DataTable objDataTable = null;
string strTipInfo = ""; try
{
if (clsDBOper.oleConn != null || strSQL != "" || strTableName != "")
{
//创建一个OleDbDataAdapter对象,并获取指定数据表的信息
oleDBSQLDataAdapter = new OleDbDataAdapter(strSQL, clsDBOper.oleConn);
//创建DataSet对象
objSQLDataSet = new DataSet();
//通过OleDbDataAdapter对象的Fill()方法,将数据表信息添加到DataSet对象中
oleDBSQLDataAdapter.Fill(objSQLDataSet, strTableName);
if (objSQLDataSet == null)
{
strTipInfo = "创建DataTable对象失败。";
return null;
} objDataTable = objSQLDataSet.Tables[strTableName]; if (objSQLDataSet == null)
{
strTipInfo = "创建DataTable对象失败。";
return null;
}
else
{
strTipInfo = "创建DataTable对象顺利完成。";
//返回DataTable对象的信息
return objDataTable;
}
}
else
{
return null;
}
}
catch (Exception e)
{
strTipInfo = "创建DataTable对象发生错误,错误信息:->" + e.Message;
return null;
}
finally
{
//释放所有空间
if (oleDBSQLDataAdapter != null)
{
oleDBSQLDataAdapter.Dispose();
oleDBSQLDataAdapter = null;
}
if (objSQLDataSet != null)
{
objSQLDataSet.Dispose();
objSQLDataSet = null;
}
}
}结果运行错误,"创建DataTable对象发生错误,错误信息:->没有为命令对象设置命令。"很奇怪,为什么在SQL SERVER中能够正常运行,但是在C#程序中就运行失败呢
Select Distinct(XMMC) As XMMC, XMBH, RKSJ, SFXCPC, XCTKQK, SFBMSH, (Case When JSSCYJ IS NOT NULL Then '有' Else '无' End) As YWSCYJ, (Case When GZLXD IS NOT NULL Then '有' Else '无' End) As YWLXD From XMDATA Order By RKSJ Desc, XMMC Desc注:该语句在SQL SERVER中运行正常然后用下面函数打开运行该SQL语句并返回DataTable
//输入参数:SQL语句
//输出参数:
public static DataTable GetDataTable(string strSQL, string strTableName)
{
OleDbDataAdapter oleDBSQLDataAdapter = null;
DataSet objSQLDataSet = null;
DataTable objDataTable = null;
string strTipInfo = ""; try
{
if (clsDBOper.oleConn != null || strSQL != "" || strTableName != "")
{
//创建一个OleDbDataAdapter对象,并获取指定数据表的信息
oleDBSQLDataAdapter = new OleDbDataAdapter(strSQL, clsDBOper.oleConn);
//创建DataSet对象
objSQLDataSet = new DataSet();
//通过OleDbDataAdapter对象的Fill()方法,将数据表信息添加到DataSet对象中
oleDBSQLDataAdapter.Fill(objSQLDataSet, strTableName);
if (objSQLDataSet == null)
{
strTipInfo = "创建DataTable对象失败。";
return null;
} objDataTable = objSQLDataSet.Tables[strTableName]; if (objSQLDataSet == null)
{
strTipInfo = "创建DataTable对象失败。";
return null;
}
else
{
strTipInfo = "创建DataTable对象顺利完成。";
//返回DataTable对象的信息
return objDataTable;
}
}
else
{
return null;
}
}
catch (Exception e)
{
strTipInfo = "创建DataTable对象发生错误,错误信息:->" + e.Message;
return null;
}
finally
{
//释放所有空间
if (oleDBSQLDataAdapter != null)
{
oleDBSQLDataAdapter.Dispose();
oleDBSQLDataAdapter = null;
}
if (objSQLDataSet != null)
{
objSQLDataSet.Dispose();
objSQLDataSet = null;
}
}
}结果运行错误,"创建DataTable对象发生错误,错误信息:->没有为命令对象设置命令。"很奇怪,为什么在SQL SERVER中能够正常运行,但是在C#程序中就运行失败呢
public static DataTable GetDataTable(string strSQL)
{
if(String.Empty.Equals(strSQL)) return null;
using(SqlDataAdapter da=new SqlDataAdapter(sql,new SqlConnection(ConnectionString)))
{
DataTable dt=new DataTable();
da.Fill(dt);
return dt;
}
or将这段改成如下:
//创建DataSet对象
DataTable dt=new DataTable();
//通过OleDbDataAdapter对象的Fill()方法,将数据表信息添加到DataSet对象中
try{
oleDBSQLDataAdapter.Fill(dt);
return dt;
}
catch(Exception er){throw er;}
换为isnull(replace(YWSCYJ,isnull(YWSCYJ,'无'),'有'),'无') as YWSCYJ试试.
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds.Tables[0];
}