Question

C# cmd.ExecuteNonQuery()问题

public int RunSql(string strSql, IDataParameter[] parameters)
{
int n = -1;
objConn.Open();
SqlCommand cmd = new SqlCommand(strSql, objConn);
try
{
foreach (SqlParameter parm in parameters)
{
cmd.Parameters.Add(parm);
}

n = (int)cmd.ExecuteNonQuery();
}
catch { }

return n;
}
跟踪strSql为"Select * from [Users] where [password]=@Password and user_id=@UserID"。两个参数也都赋值，数据库连接也是正确的，可是执行ExecuteNonQuery方法之后，n值还是为-1。到SQL2005数据库查询有相应的password和user_id匹配的一条记录的。请问是哪里出了问题？

Answer 1

*********************在库里面执行***************
create proc SearchInfo
(
@UserID int,
@Password varchar(20)
)
as
begin
Select count(*) from [Users] where [password]=@Password and user_id=@UserID
end

**********************************************

呵呵,你为什么不在库里写一个存储过程呢?
然后在外面调用存储过程就简单多了
(执行效率高\安全性高)

**************C#里调用*********************
public int RunSql(string strSql, IDataParameter[] parameters)
{
int n = -1;
objConn.Open();
SqlCommand cmd = new SqlCommand(strSql, objConn);
cmd.commandtype = ....stored..;//这里很重要
try
{
foreach (SqlParameter parm in parameters)
{
cmd.Parameters.Add(parm);
}

n = (int)cmd.ExecuteNonQuery();
}
catch { }

return n;
}

**********************************************
通常写成这样较好
public class DataAccess
{
SqlConnection conn = null;
SqlCommand comm = null;
public DataAccess()
{
string str = ConfigurationManager.AppSettings["ConnectionString"].ToString();
conn = new SqlConnection(str);
}

/// <summary>
/// 打开连接
/// </summary>
public void Open()
{
if (conn.State != ConnectionState.Open || conn != null)
{
this.conn.Open();
}
}

/// <summary>
/// 关闭连接
/// </summary>
public void Close()
{
if (conn.State != ConnectionState.Closed)
{
this.conn.Close();
}
}

/// <summary>
/// 返回一个sqlcommand类型实例
/// </summary>
/// <param name="str"></param>
/// <param name="isProc"></param>
/// <param name="paralist"></param>
/// <returns></returns>
private SqlCommand command(string str,bool isProc,SqlParameter[] paralist)
{
comm = new SqlCommand();
comm.CommandText = str;
comm.Connection = conn;
if (isProc)
{
comm.CommandType = CommandType.StoredProcedure;
}
else
{
comm.CommandType = CommandType.Text;
}
if (paralist != null)
{
foreach (SqlParameter sp in paralist)
{
comm.Parameters.Add(sp);
}
}
return comm;
}

/// <summary>
/// 增删改
/// </summary>
/// <param name="str"></param>
/// <param name="isProc"></param>
/// <param name="paralist"></param>
/// <returns></returns>
public int QueryNonExcute(string str, bool isProc, SqlParameter[] paralist)
{
this.Open();
SqlCommand sqlcomm = command(str, isProc, paralist);
int nI = sqlcomm.ExecuteNonQuery();
this.Close();
return nI;
}

/// <summary>
/// 查询
/// </summary>
/// <param name="str"></param>
/// <param name="isProc"></param>
/// <param name="paralist"></param>
/// <returns></returns>
public DataSet QueryExcute(string str, bool isProc, SqlParameter[] paralist)
{
SqlCommand sqlcomm = command(str, isProc, paralist);
SqlDataAdapter sda = new SqlDataAdapter(sqlcomm);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
}

Answer 2

ID
=
“txt_ID.Text”
是字符串，而你在数据库表中ID为长整形，类型不匹配无法删除相应表的字段。

Answer 3

txt_ID.Text
看看你数据库中
ID字段的类型如果是Int型。Txt_id.Text
就也必须int型的

Answer 4

strSql应该是："Select * from [Users] where [password]=? and user_id=?"，用问号代替参数

Answer 5

strSql = "Select count(*) from [Users] where [password]=@Password and user_id=@UserID";

int n = (int)cmd.ExecuteScalar();