Question

帮忙挑毛病 C# asp.net “语法错误 (操作符丢失) 在查询表达式''中”错误

下列代码中在names，address，tel这些textbox中只要输入空格，@这样的字符就报错，错误信息是：
System.Data.OleDb.OleDbException: 语法错误 (操作符丢失) 在查询表达式 'ssf sf sf sfsdfds ' 中
表达式时而换成：'12345@qq.com' 或者'2015.3.26 21:16:52'
我都疯掉了！语句没有问题啊 为啥有空格和@就不行？？？

string str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=";
string str_sourcefile = "~/App_Data/SKDATA.accdb";
OleDbConnection cnn;
OleDbCommand cmd;
OleDbDataReader datar;

string str_sql;
string str_conn = str_cnn + MapPath(str_sourcefile);
cnn = new OleDbConnection(str_conn);
cnn.Open();

str_sql = "INSERT INTO S_SET_shopseting ( [names], address, tel, fax, email, webshop, makeman, makedate ) VALUES ( ";
str_sql += this.names.Text + ", ";
str_sql += this.address.Text + ", ";
str_sql += this.tel.Text + ", ";
str_sql += this.fax.Text + ", ";
str_sql += this.email.Text.ToString() + ", ";
str_sql += this.Onlineshop.Text.ToString() + ", ";
str_sql +="admin"+", ";
string i = DateTime.Now.ToString();
str_sql += i + ")";
cmd = new OleDbCommand(str_sql, cnn);

Answer 1

OleDbConnection cnn;
OleDbCommand cmd;
string str_conn = "....";
cnn = new OleDbConnection(str_conn);
string str_sql = "insert into s_set_shopseting(names,address,tel,fax,email,webshop,makeman,makedate) values(@names,@address,@tel,@fax,@email,@webshop,@makeman,@makedate)";
cmd = new OleDbCommand(str_sql, cnn);
cmd.Parameters.AddWithValue("@names", this.names.Text);
cmd.Parameters.AddWithValue("@address", this.address.Text);
cmd.Parameters.AddWithValue("@tel", this.fax.Text);
//...
cmd.ExecuteNonQuery();

 你原来是用拼接sql语句的方式，一旦这种拼接方式拼接出来的sql句子有问题，那么就会出异常，甚至是可以注入攻击。

解决办法就是使用参数形式，参考上面代码。

Answer 2

str_sql = "INSERT INTO S_SET_shopseting ([names], address, tel, fax, email, webshop, makeman, makedate ) VALUES ";
        str_sql += "('" + this.names.Text  + "', '";
        str_sql += this.address.Text + "', '";
        str_sql += this.tel.Text + "', '";
        str_sql += this.fax.Text + "', '";
        str_sql += this.email.Text.ToString() + "', '";
        str_sql += this.Onlineshop.Text.ToString() + "', '";
        str_sql +="admin"+"', '";
        string i = DateTime.Now.ToString();
        str_sql += i  + "')";

g关键：将与文本对应字段的拼接字符串用单引号引起来

Answer 3

字符串两边没有加"
str_sql +=("\""+ this.fax.Text + "\", ");