从Access运行时，UPDATE查询工作正常，但从C＃app中引发SQL语法错误

I have a very simple Update statement that will update mail server settings and network credentials info... Query works fine when I run it in Access but C# keeps giving me the error stating that my SQL Syntax is wrong ... I have a dataaccess layer (dal class) and Update instance method pasted belows ... But the problem must be sth else cuz I have updated lots of stuff this way but this time it just won't do .. any clues will be greatly appreciated. Thx in advance.

我有一个非常简单的Update语句,它将更新邮件服务器设置和网络凭据信息...当我在Access中运行它时,查询工作正常,但C#一直给我错误,说明我的SQL语法错误...我有一个数据访问图层(dal类)和更新实例方法粘贴在下面...但问题必须是......否则我已经用这种方式更新了很多东西,但这次它不会做...任何线索将不胜感激。 Thx提前。

Update instance method in DAL class .. (this is supposed to be a Data Access Layer :) I'm just a management graduate :P

更新DAL类中的实例方法..(这应该是一个数据访问层:)我只是一个管理毕业生:P

public int UpdateRow(string Query, bool isSP, params OleDbParameter[] args)
{
    int affectedRows = -1;
    using (con = new OleDbConnection(connStr))
    {
        using (cmd = con.CreateCommand())
        {
            cmd.CommandText = Query;
            if (isSP)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            if (args != null)
            {
                foreach (OleDbParameter prm in args)
                {
                    cmd.Parameters.Add(prm);
                }
            }

            try
            {
            con.Open();
            affectedRows = cmd.ExecuteNonQuery();
            }
            catch(OleDbException ex)
            {
            throw ex;
            }
            catch (Exception ex)
            {
            throw ex;
            }
        }
    }
    return affectedRows;
}

And the ASP.NEt codebehind that will do the updating =

ASP.NEt代码隐藏将进行更新=

protected void Update_Click(object sender, EventArgs e) {
DAL dal = new DAL();
string upt = string.Format("UPDATE [MailConfig] SET Server='{0}', Username='{1}', Password='{2}', AddressFrom='{3}', DisplayName='{4}'",server.Text,username.Text,password.Text,replyto.Text,displayname.Text);
dal.UpdateRow(upt,false,null);
LoadData();
}

peace!

8 个解决方案

#1

Trying wrapping your field names in [ ]. I have had problems in the past with certain field names such as a username and password and count, etc, being recognized as reserved words and screwing up the sql giving me an error.

尝试在[]中包装您的字段名称。我过去遇到过某些字段名称的问题,例如用户名和密码以及计数等,被识别为保留字并搞砸sql给我一个错误。

#2

First off - don't use string.Format here. Use parameters, and add parameters to the command. Right now, you are wide open to SQL injection attacks. Think "Bobby Tables".

首先 - 不要在这里使用string.Format。使用参数,并向命令添加参数。现在,您对SQL注入攻击持开放态度。想想“Bobby Tables”。

Re "stating that my SQL Syntax is wrong" - can you please quote the exact error?

重新说“我的SQL语法错误” - 你能否引用确切的错误?

#3

First of all, you have no where clause in your Update, so it will update all rows, and violate key constraints causing an error, if you have any.

首先,您的更新中没有where子句,因此它将更新所有行,并违反导致错误的关键约束(如果有的话)。

Second, running that kind of code makes you very vunerable to SQL Injection, if someone enters a username that has a sql command embedded in it, you could lose all your data.

其次,运行这种代码会使您对SQL注入非常有价值,如果有人输入嵌入了sql命令的用户名,您可能会丢失所有数据。

You should use parameterized queries. You specify your parameters in the sql command with @paramname instead of using {4}, and then with the command object do accessCommand.parameters.AddWithValue("@paramname", value)

您应该使用参数化查询。您可以使用@paramname而不是{4}在sql命令中指定参数,然后使用命令对象执行accessCommand.parameters.AddWithValue(“@ paramname”,value)

#4

I know there is only one row on that table .. just keeping username pasword etc ... I have also added where statement no change ...

我知道那张桌子上只有一行..只保留用户名密码等...我还添加了where语句没有变化......

Ok when it comes to parameters, you are right, but I always parameterize my stuff later at the end, first it gots to work properly then I parameterize cuz you know its messy and harder to debug then ... This is just lazyness ..

好的,当涉及到参数时,你是对的,但我总是在最后的时候参数化我的东西,首先它是gots工作正常然后我参数化你知道它的凌乱和更难调试然后...这只是懒惰..

About the details of my exception, there are two catch blocks as you see, and the last one catches it ... the Exception .. not the OleDBException .. and it only says Syntax error in UPDATE statement. Thats it ... While I debug, get the sql statement from visual studio, paste in Access and it works fine

关于我的异常的细节,你看到有两个catch块,最后一个捕获它... Exception ..而不是OleDBException ..它只是在UPDATE语句中显示语法错误。多数民众赞成...当我调试时,从visual studio获取sql语句,粘贴Access,它工作正常

#5

No my Update method takes 3 parameters, like string Query, boll IsSp , params OleDBParameter[] .. and i usually do it the easy way, pass false for sp, so it doesnot fall into if(isSP){cmd.CommandType = storedproc etc..} .. it doesnot do that .. theres also a null check for params collection .. so I send null when I'm lazy, so it also passes that takes only the Query .. :) genius right

没有我的Update方法需要3个参数,比如字符串查询,boll IsSp,params OleDBParameter [] ..我通常这样做很简单,为sp传递false,所以它不属于if(isSP){cmd.CommandType = storedproc等.. .. ..它不这样做.. theres也是params集合的空检查..所以我在懒惰时发送null,所以它也传递只需要查询.. :)天才吧

#6

This is a small asp.net app for a dude I know .. this dude is going to send Mail messages around, and this is just a screen for him to define his mail server, user and pass so he can send emails around .. there is only one row so no need for a where statement .. I will put it anyway for sake of "best practices" :) Yes there is no StoredProcedure thing in access, but hopefully santa clause will bring LINQ to OleDB in 2009 :) So this query works fine, it does update the row but just C# keeps giving me this exception ... I dunno why really .. no clue

这是一个我认识的小家伙的asp.net应用程序..这个家伙将要发送邮件消息,这只是他定义他的邮件服务器,用户和通行证的屏幕,所以他可以发送电子邮件..只有一行,所以不需要where语句..无论如何我会把它放在“最佳实践”:)是的,访问中没有StoredProcedure的东西,但希望圣诞老人条款将在2009年将LINQ带到OleDB :)所以这个查询工作正常,它确实更新了行,但只是C#一直给我这个例外...我不知道为什么真的......没有头绪

#7

Oh sure, this is my first time here, so I didn't want to pollute it actually .. You're right I will pay big attention to that from now on ... site is great by the way thanks for all ...

哦,当然,这是我第一次来这里,所以我实际上并不想污染它...你说得对,从现在开始我会非常注意...网站很棒,感谢大家... 。

#8

You are using a CommandType of StoredProcedure, but your query is not a stored procedure name, its a sql query without a where clause.

您正在使用StoredProcedure的CommandType,但您的查询不是存储过程名称,它是没有where子句的SQL查询。

UPDATE [MailConfig] 
SET Server='{0}', 
    Username='{1}', 
    Password='{2}', 
    AddressFrom='{3}', 
    DisplayName='{4}'"

So you need to remove the command type line, or change it to a correct command type CommandType.Text, and add a Where clause specifying what rows are to be affected.

因此,您需要删除命令类型行,或将其更改为正确的命令类型CommandType.Text,并添加Where子句,指定要受影响的行。

I don't think Access even has Stored Procedures, so there's no using to use that command type with it.

我认为Access甚至没有存储过程,所以没有使用它的命令类型。

An example of a command that does use stored procedures would be something like:

使用存储过程的命令示例如下:

string sqlCommString = "QCApp.dbo.ColumnSeek";
SqlCommand metaDataComm = new SqlCommand(sqlCommString, sqlConn);
metaDataComm.CommandType = CommandType.StoredProcedure;

The command string for that type is just the name of the stored proc.

该类型的命令字符串只是存储过程的名称。

#1