1

I'm trying to put together a program that will update a database every half an hour, I have got the timer and everything working the only bit I'm having trouble with is the actual SQL part itself.

When I run the below block of code within a test console application then it works perfectly and it update the database but when I then try it in the service I have created it just keeps throwing out the following error: System.Data.SqlClient.SqlException(0x80131904): Unclosed quotation mark after the character string ')'.' But I don't get any error in the console version.

string test = "firstline";
string test2 = "Secondline";

SqlConnection myConnection = new SqlConnection("Data Source=***.***.***.**,1433;Network Library=DBMSSOCN; Initial Catalog = Backups; User ID = BackupsUser; Password = ******; ");
try
{
    myConnection.Open();
    SqlCommand myCommand = new SqlCommand("INSERT INTO Backups (outcome, reason, company) VALUES ('test',@firstline,@secondline)", myConnection);
    myCommand.Parameters.Add(new SqlParameter ("@firstline", test));
    myCommand.Parameters.Add(new SqlParameter ("@secondline", test2));
    myCommand.ExecuteNonQuery();
    myConnection.Close();
    WriteErrorLog("Database has been updated");
}
catch (Exception e)
{
    WriteErrorLog(e.ToString());
}
EpicKip
  • 4,015
  • 1
  • 20
  • 37
xjacksssss
  • 49
  • 9
  • 1
    Execute it with SQL Profiler running and see what statements are being executed. – Alex K. May 24 '17 at 12:49
  • 3
    I see nothing wrong with this code. You should put your connection inside a `using` block though. –  May 24 '17 at 12:52
  • Is there a reason the 'test' string is quoted, but the other parameters are not quoted? – dahui May 24 '17 at 12:53
  • 2
    @dahui It is a literal string, the others are parameters. – Colin Mackay May 24 '17 at 12:54
  • Perhaps try what was done here https://stackoverflow.com/a/12939528/6530134 – Timothy G. May 24 '17 at 12:59
  • As well as SqlConnection, the SqlCommand should also be in a using block, which will close the connection, so you do not need myConnection.Close(). – Polyfun May 24 '17 at 12:59
  • My best guess is that the values of test and test2 are not what you think they are when you run this code in a service. SQL Profiler will show you what is really being executed. – Polyfun May 24 '17 at 13:02
  • Thanks guys working through all your suggestions now, will update you all asap – xjacksssss May 24 '17 at 13:04
  • It may be due to the single quote is in one of the parameter value, can you check what are the input values are coming from the service you were mentioned – Arulkumar May 24 '17 at 13:04
  • after changing out the 'test' and replacing it with a parameter this fixes it and allows it to run! – xjacksssss May 24 '17 at 13:15
  • @Arulkumar sql parameters don't work like that. No, that's not possible. –  May 24 '17 at 13:51
  • @xjacksssss is it possible one of those apostrophes isn't an apostrophe? The backtick looks pretty similar, for example. Try deleting each one, and retyping the apostrophe, and see if that helps. –  May 24 '17 at 14:31
  • @Amy they are both definitely apostrophes – xjacksssss May 24 '17 at 14:56

0 Answers0