关于将dropdownlist中选择的值插入数据库的问题

Question

我用了4三个dropdownlist控件从数据库里提出了一些数据.
现在想通过选择dropdownlist中的项,得到当前选的值
然后再将得到的值插入另一表里现提示如下错误:
System.Data.SqlClient.SqlException: 在此上下文中不允许使用 'MonitorValue'。此处只允许使用常量、表达式或变量。不允许使用列名。代码如下：
string TypeValue = TypeList.SelectedItem.Value;
　　　　　　string AccessValue = null;
string MonitorValue = null;
string AlermValue = null;
if (AccessList.SelectedIndex != -1)
{
AccessValue = AccessList.SelectedItem.Value;
}
if (MonitorList.SelectedIndex != -1)
{
MonitorValue = MonitorList.SelectedItem.Value;
}
if (AlermList.SelectedIndex != -1)
{
AlermValue = AlermList.SelectedItem.Value;
} System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.Append("insert into Device(Access_ID,MonitorPolicyID,AlarmPolicyID,Device_Name,");
sql.Append("Device_Code,DeviceType_ID,Device_Address,Device_Longitude,Device_Latitude,");
sql.Append("Device_BuyTime,Device_BuyOrder,Device_Logpath,Device_Note)");
            sql.Append("values (" + "AccessValue,MonitorValue,AlermValue,E_DevName.Text");
sql.Append("E_code.Text,TypeValue,E_DevAdder.Text,E_longitude.Text,E_latitude.Text,");
sql.Append("E_code.Text,Typevalue,E_DevAdder.Text,E_longitude.Text,E_latitude.Text,");
sql.Append("+E_BuyTime.Text,E_BuyOrder.Text,E_Logpath.Text,E_Note.Text)"); // System.Web.HttpContext.Current.Response.Write("<script language=javascript>alert('"+MonitorList.SelectedItem.Value+"');</script>"); SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnString"]); 
conn.Open();
System.Data.SqlClient.SqlCommand comm = new SqlCommand(sql.ToString(),conn); //sql 
comm.ExecuteNonQuery();

Answer 1

设置断点看看你的sql最后的字符串时什么？

Answer 2

try
System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.Append("insert into Device(Access_ID,MonitorPolicyID,AlarmPolicyID,Device_Name,");
sql.Append("Device_Code,DeviceType_ID,Device_Address,Device_Longitude,Device_Latitude,");
sql.Append("Device_BuyTime,Device_BuyOrder,Device_Logpath,Device_Note)");
sql.Append("values (" + AccessValue+","+"MonitorValue"+","+"AlermValue"+","+E_DevName.Text+",");
sql.Append(E_code.Text+","+TypeValue+","+E_DevAdder.Text+","+E_longitude.Text+","+E_latitude.Text+",");
sql.Append(E_code.Text+","+Typevalue+","+E_DevAdder.Text+","+E_longitude.Text+","+E_latitude.Text+",");
sql.Append(E_BuyTime.Text+","+E_BuyOrder.Text+","+E_Logpath.Text+","+E_Note.Text+")");

Answer 3

你先别执行 把 SQL语句read出来看看
Response.Write(sql.ToString());或者设置断点看看SQL语句什么地方错误

Answer 4

string MonitorValue = null; 应该为DBNull.Value
你的sql语句好象还有问题
断下点看sql.ToString()的值是什么我一般用string.Format("insert into table(a,b) values({0},{1})",a,b)
这比较好调试 排bug

Answer 5

string TypeValue = TypeList.SelectedItem.Value;
string AccessValue = null;
string MonitorValue = null;
string AlermValue = null;
if (AccessList.SelectedIndex != -1)
{
AccessValue = AccessList.SelectedItem.Value;
}
if (MonitorList.SelectedIndex != -1)
{
MonitorValue = MonitorList.SelectedItem.Value;
}
if (AlermList.SelectedIndex != -1)
{
AlermValue = AlermList.SelectedItem.Value;
}System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.Append("insert into Device(Access_ID,MonitorPolicyID,AlarmPolicyID,Device_Name,");
sql.Append("Device_Code,DeviceType_ID,Device_Address,Device_Longitude,Device_Latitude,");
sql.Append("Device_BuyTime,Device_BuyOrder,Device_Logpath,Device_Note)");
//**********************
sql.Append("values (" + "'"+AccessValue,MonitorValue+"','"+AlermValue,E_DevName.Text+"'");
sql.Append("'"+E_code.Text,TypeValue+"','"+E_DevAdder.Text+"','"+E_longitude.Text+"','"+E_latitude.Text+"',");
sql.Append("'"+E_code.Text,Typevalue+"','"+E_DevAdder.Text+"','"+E_longitude.Text+"','"+E_latitude.Text+"',");
sql.Append("'"+E_BuyTime.Text+"','"+E_BuyOrder.Text+"','"+E_Logpath.Text,E_Note.Text+"')");
//*************************************//System.Web.HttpContext.Current.Response.Write("<script language=javascript>alert('"+MonitorList.SelectedItem.Value+"');</script>");SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnString"]); 
conn.Open();
System.Data.SqlClient.SqlCommand comm = new SqlCommand(sql.ToString(),conn);//sql 
comm.ExecuteNonQuery();
覆盖测试

Answer 6

insert into Device(Access_ID,MonitorPolicyID,AlarmPolicyID,Device_Name,
Device_Code,DeviceType_ID,Device_Address,Device_Longitude,Device_Latitude,
Device_BuyTime,Device_BuyOrder,Device_Logpath,Device_Note)
values (125,MonitorValue,AlermValue,dsf,dsf,4,dsf, , ,dsf,4,dsf, , ,2005-6-3,dsf,dsf,)

Answer 7

会不会因为我的
MonitorList
AlermList　这个两dropdwonlist无值所至呢

Answer 8

再试一下这个 我上面多加了两个引号 System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.Append("insert into Device(Access_ID,MonitorPolicyID,AlarmPolicyID,Device_Name,");
sql.Append("Device_Code,DeviceType_ID,Device_Address,Device_Longitude,Device_Latitude,");
sql.Append("Device_BuyTime,Device_BuyOrder,Device_Logpath,Device_Note)");
sql.Append("values (" + AccessValue+","+MonitorValue+","+AlermValue+","+E_DevName.Text+",");
sql.Append(E_code.Text+","+TypeValue+","+E_DevAdder.Text+","+E_longitude.Text+","+E_latitude.Text+",");
sql.Append(E_code.Text+","+Typevalue+","+E_DevAdder.Text+","+E_longitude.Text+","+E_latitude.Text+",");
sql.Append(E_BuyTime.Text+","+E_BuyOrder.Text+","+E_Logpath.Text+","+E_Note.Text+")");

Answer 9

System.Data.SqlClient.SqlException: 第 1 行: ',' 附近有语法错误。

Answer 10

System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.Append("insert into Device( Access_ID,MonitorPolicyID,AlarmPolicyID,Device_Name,");
sql.Append("Device_Code,DeviceType_ID,Device_Address,Device_Longitude,");
sql.Append("Device_Latitude,Device_BuyTime,Device_BuyOrder, Device_Logpath, Device_Note)");
sql.Append(" values({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12})");
string strSQL = string.Format(sql.ToString(),AccessValue,MonitorValue,AlermValue,E_DevName.Text,
E_code.Text,TypeValue,E_DevAdder.Text,E_longitude.Text,E_latitude.Text,
E_BuyTime.Text,E_BuyOrder.Text,E_Logpath.Text,E_Note.Text);SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnString"]); 
conn.Open();
System.Data.SqlClient.SqlCommand comm = new SqlCommand(strSQL,conn); //sql 
comm.ExecuteNonQuery();