0

I know there's already a bunch of these posts but I looked through a lot of them and they all seem specific to one person's needs, so I figured I'd see if someone could help me for my specific needs.

I am using Oledb and c# and I am trying to create an Excel worksheet and populate it but I'm getting the error "Syntax error (missing operator) in query expression" when using this INSERT statement:

for (int i = 0; i <= 20; i++)
{
     command.CommandText = string.Format("INSERT INTO [Table] (Id, Tag, Description) VALUES ('{0}','2','3')", diff1[i]);
     command.ExecuteNonQuery();
}

To explain, I have an array of strings, diff1[], and I'm using the loop to insert each element into its own cell, and it works until it gets to the string element:

"\r\n\"603\",\"FuelPumpTestWarning\",\"<table style=\"\"border: 4px solid black; border-collapse:collapse;\"\">\r\n<tbody>\r\n<tr>\r\n<td bgcolor=\"\"#FF7700\"\" style=\"\"border: 5px solid black; border-collapse:collapse;\"\">\r\n<div style=\"\"width: 0; height: 0; border-left: 18px solid transparent; border-right: 18px solid transparent; border-bottom: 33px solid black; margin: 5px 5px 5px 5px;\"\" >\r\n<p style=\"\"font-family:Arial; color:#ff7700; \r\nfont-size:32px; margin:5px -5px 0px -5px;\"\">!</p>\r\n<p style=\"\"color:black; font-size:32px; margin:-45px 30px; font-family:'Arial Black', Arial; font-weight:'900';\"\">WARNING</p>\r\n</div>\r\n</td>\r\n</tr>\r\n<tr>\r\n<td style=\"\"border: 5px solid black; border-collapse:collapse;\"\">\r\n<p style=\"\"font-size:12px; font-family:'Arial Black', Arial; font-weight:'900'\"\">You are about to perform a test that\r\ninvolves pressurized gasoline being\r\npumped. Before performing this\r\ntest, be sure the area is free and\r\nclear from combustibles, open\r\nflame, welding and/or grinding\r\nsparks, or anything else that could\r\nignite or be an ignition source.<br>\r\n<br>\r\nFAILURE TO FOLLOW THIS WARNING\r\nMAY CAUSE INADVERTENT IGNITION\r\nTHAT COULD RESULT IN SERIOUS\r\nINJURY OR DEATH.</p>\r\n</td>\r\n</tr>\r\n</tbody>\r\n</table>\""

Ignore the fact that the last two columns are being filled with 2s and 3s, that's just a place holder until I can figure out how to do this because they will also be filled using an array of strings. Is there a special character in the string element that it is getting an error on? It makes it through about 10 other elements which look similar to this one, but gets an error on this one.

I've exhausted every option I can think of so any help would be greatly appreciated.

Mikkel Bang
  • 574
  • 2
  • 13
  • 27

2 Answers2

3

Statement has Single Quote for

font-family:'Arial Black', Arial;

You should try this. Replace Single Quote with Double Single Quote.

for (int i = 0; i <= 20; i++)
{
     command.CommandText = string.Format("INSERT INTO [Table] (Id, Tag, Description) VALUES ('{0}','2','3')", diff1[i].Replace("'","''"));
     command.ExecuteNonQuery();
}
Yograj Gupta
  • 9,811
  • 3
  • 29
  • 48
  • Ah almost! Now I get the error "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." – Mikkel Bang Jun 07 '13 at 19:03
  • As the error stating, that field size is smaller than the size of content which you want to insert, you need to limit your data or increase field size. – Yograj Gupta Jun 07 '13 at 19:06
  • How do I increase field size? – Mikkel Bang Jun 07 '13 at 19:10
  • as you are inserting in the excel file, the limit of an excel cell content is 32767 characters, and it cannot be increase. – Yograj Gupta Jun 07 '13 at 19:16
  • but the string element that it is getting an error on is only 1,312 characters – Mikkel Bang Jun 07 '13 at 19:19
  • The problem is you can not insert content more than 255 characters in the excel cell, and if you really want to insert more than 255 characters, you need to check Excel interop. – Yograj Gupta Jun 07 '13 at 19:24
  • You can check this http://stackoverflow.com/questions/4488404/excel-unable-to-insert-more-than-255-chars also. – Yograj Gupta Jun 07 '13 at 19:27
  • Ok. I guess I'm just confused because this string element was originally pulled from an excel cell earlier in my program and the character amount wasn't a problem then. My program is creating a difference file from two csv files, which were originally created from an excel sheet and access table – Mikkel Bang Jun 07 '13 at 19:30
1

try below 

command.CommandText ="INSERT INTO [Table] (Id, Tag, Description) VALUES (@id,'2','3')";

command.Parameters.Add(new SqlParameter("@id", ""));

for (int i = 0; i <= 20; i++)
{
    command.Parameters["@id"].Value = diff1[i];
    command.ExecuteNonQuery();
}
Damith
  • 62,401
  • 13
  • 102
  • 153