在使用带参数的sql时(sql server 2000),出现以下问题:
两种方法调用同一个数据库操作函数,只是在红色部分有区别,即传递参数名时是否带"@"符号。
方法一在我本机测试无误,而用方法二时返出现错误:“必须声明变量@paraName”
而我同事机器正好和我的结果相反。
现在初步怀疑是数据库设置问题,因为我们用的不是同一个数据库,数据库版本都是sql server 2000. 如果朋友们有知道此问题的请指教!
1
数据库操作函数
2 /// <summary>
3 /// 向数据库里插长sql字段
4 /// </summary>
5 /// <param name="strSQL"> SQL语句 </param>
6 /// <param name="fs"></param>
7 /// <returns> 影响的记录数 </returns>
8 public static int ExecuteSqlInsertLongSql( string strSQL, string textValue, string strParaName)
9 {
10 string strConn = System.Configuration.ConfigurationSettings.AppSettings[ " sqlserver " ];
11 using (SqlConnection connection = new SqlConnection(strConn))
12 {
13 SqlCommand cmd = new SqlCommand(strSQL, connection);
14 string paraName = strParaName;
15 System.Data.SqlClient.SqlParameter Sqlpara = new SqlParameter();
16 Sqlpara.ParameterName = paraName;
17 Sqlpara.SqlDbType = System.Data.SqlDbType.Text;
18 // orapara.OracleType = System.Data.OracleClient.OracleType.LongVarChar;
19 Sqlpara.Size = textValue.Length + 1 ; // int.MaxValue; //
20 Sqlpara.Value = textValue.ToString();
21 cmd.Parameters.Add(Sqlpara);
22 try
23 {
24 connection.Open();
25 int rows = cmd.ExecuteNonQuery();
26 return rows;
27 }
28 catch (System.Data.SqlClient.SqlException E)
29 {
30 strErrMsg = E.Message;
31 throw new Exception(E.Message);
32 }
33 finally
34 {
35 cmd.Dispose();
36 connection.Close();
37 }
38 }
39 }
40 使用方法一:
41 string strHtmlValue = this .HtmlEditor1.Text.Replace( " ' " , " '' " );
42 sql = " insert into wjb(scrq,scr,wjsm,wjnr,wjlxbm,shzt,wjm) values(' " + this .lb_fbrq.Text.Trim() + " ',' " + userID + " ',' " + this .txt_bt.Text.Trim() + " ',@paraName,'03','2',' " + str_slm + " ') " ;
43
44
45 if (conn.ExecuteSqlInsertLongSql(sql,strHtmlValue, " @paraName " ) == 1 )
46 // .
47
48 使用方法二:
49 string strHtmlValue = this .HtmlEditor1.Text.Replace( " ' " , " '' " );
50
51 sql = " insert into wjb(scrq,scr,wjsm,wjnr,wjlxbm,shzt,wjm) values(' " + this .lb_fbrq.Text.Trim() + " ',' " + userID + " ',' " + this .txt_bt.Text.Trim() + " ',@paraName,'03','2',' " + str_slm + " ') " ;
52
53
54 if (conn.ExecuteSqlInsertLongSql(sql,strHtmlValue, " paraName " ) == 1 )
55 // .
2 /// <summary>
3 /// 向数据库里插长sql字段
4 /// </summary>
5 /// <param name="strSQL"> SQL语句 </param>
6 /// <param name="fs"></param>
7 /// <returns> 影响的记录数 </returns>
8 public static int ExecuteSqlInsertLongSql( string strSQL, string textValue, string strParaName)
9 {
10 string strConn = System.Configuration.ConfigurationSettings.AppSettings[ " sqlserver " ];
11 using (SqlConnection connection = new SqlConnection(strConn))
12 {
13 SqlCommand cmd = new SqlCommand(strSQL, connection);
14 string paraName = strParaName;
15 System.Data.SqlClient.SqlParameter Sqlpara = new SqlParameter();
16 Sqlpara.ParameterName = paraName;
17 Sqlpara.SqlDbType = System.Data.SqlDbType.Text;
18 // orapara.OracleType = System.Data.OracleClient.OracleType.LongVarChar;
19 Sqlpara.Size = textValue.Length + 1 ; // int.MaxValue; //
20 Sqlpara.Value = textValue.ToString();
21 cmd.Parameters.Add(Sqlpara);
22 try
23 {
24 connection.Open();
25 int rows = cmd.ExecuteNonQuery();
26 return rows;
27 }
28 catch (System.Data.SqlClient.SqlException E)
29 {
30 strErrMsg = E.Message;
31 throw new Exception(E.Message);
32 }
33 finally
34 {
35 cmd.Dispose();
36 connection.Close();
37 }
38 }
39 }
40 使用方法一:
41 string strHtmlValue = this .HtmlEditor1.Text.Replace( " ' " , " '' " );
42 sql = " insert into wjb(scrq,scr,wjsm,wjnr,wjlxbm,shzt,wjm) values(' " + this .lb_fbrq.Text.Trim() + " ',' " + userID + " ',' " + this .txt_bt.Text.Trim() + " ',@paraName,'03','2',' " + str_slm + " ') " ;
43
44
45 if (conn.ExecuteSqlInsertLongSql(sql,strHtmlValue, " @paraName " ) == 1 )
46 // .
47
48 使用方法二:
49 string strHtmlValue = this .HtmlEditor1.Text.Replace( " ' " , " '' " );
50
51 sql = " insert into wjb(scrq,scr,wjsm,wjnr,wjlxbm,shzt,wjm) values(' " + this .lb_fbrq.Text.Trim() + " ',' " + userID + " ',' " + this .txt_bt.Text.Trim() + " ',@paraName,'03','2',' " + str_slm + " ') " ;
52
53
54 if (conn.ExecuteSqlInsertLongSql(sql,strHtmlValue, " paraName " ) == 1 )
55 // .