UPDATE query works fine when run from Access but throws SQL syntax error from C # application
I have a very simple Update statement that will update my mail server settings and network credential information ... Query works fine when I run it in Access, but C # keeps giving me an error stating that my SQL syntax is incorrect. .. i have a data access layer (dal class) and an Update instance method inserted into streams ... But the problem must also be because I updated a lot of things this way, but this time it just won't be .. any tips would be much appreciated, thanks in advance.
Update instance method in DAL class .. (assuming this is data access layer :) I'm just a management graduate: 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 code that will do the update =
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();
}
world!
First, don't use string.Format
here. Use parameters and add parameters to the command. You are wide open to SQL injection attacks right now. Think " " Bobby Tables ".
Re "indicating my SQL syntax is incorrect" - can you point out the exact error?
source to share
First of all, you don't have a where clause in your update, so it will update all rows and violate the key constraints causing the error if you have one.
Secondly, running code like this makes you very attractive to SQL Injection, if someone enters a username with a sql command built into it, you could lose all your data.
You have to use parameterized queries. You specify your parameters in the sql command with @paramname instead of using {4} and then use the command object to do accessCommand.parameters.AddWithValue ("@paramname", value)
source to share
I know there is only one row on this table .. just keeping the username and password ... I also added that the expression is unchanged ...
Ok when it comes to parameters, you are right, but I always parameterize my stuff later at the end, it works right first, then I parameterize because you know its messy and harder to debug then ... It's just lazy ..
About the details of my exception, there are two catch blocks you see and the last one catches them ... Exception .. not OleDBException .. and it only talks about a syntax error in the UPDATE statement. Thats it ... While I am debugging I get the sql statement from visual studio, paste into Access and it works fine
You are using CommandType for StoredProcedure, but your query is not the name of a stored procedure, its a SQL query without a where clause.
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 the correct command type CommandType.Text and add a Where clause defining which lines should be touched.
I don't think Access even has stored procedures, so it shouldn't be used with this type of use.
An example command using stored procedures would look something like this:
string sqlCommString = "QCApp.dbo.ColumnSeek";
SqlCommand metaDataComm = new SqlCommand(sqlCommString, sqlConn);
metaDataComm.CommandType = CommandType.StoredProcedure;
The command line for this type is just the name of the stored procedure.
source to share
There is no Update method of mine that takes 3 parameters like string Query, boll IsSp, params OleDBParameter [] .. and I usually do it easily, pass false for sp so it doesn't get caught in if (isSP) {cmd. CommandType = storedproc etc} .. it doesn't do that .. theres also has null validation for the params collection. So I send null when I'm lazy, so it also passes that it only accepts Query .. :) genius right
This is a little asp.net app for a dude I know .. this dude is going to send Mail messages and this is just a screen so he can define his mail server, user and get through so he can send emails around .. there is only one line, so there is no need for a where clause. I'll put it anyway for the sake of "best practices" :) Yes, there is no StoredProcedure object in access, but hopefully santa's suggestion will bring LINQ to OleDB in 2009 :) So this query works fine, it updates the row, but only C # continues give me this exception ... I don't know why really .. no clue