"A quotation mark delimiter is missing from the end of the query." error in WinCE PDA application

Question

In my WinCE PDA application, I am doing comparing the barcode value I scanned with the one in my database to generate a table.

I build the query like this:

for (int i = 0; i < listBox2.Items.Count; i++)
{
    if (i == 0)
    {
        sb.Append("Select * from ToolsBar where BarcodeValue in (");
    }

    sb.Append("'" + listBox2.Items[i] + "',");

}

sb.Length = sb.Length - 1;
sb.Append(")");

And use it here:

cmd.CommandText = sb.ToString();
cmd.CommandType = CommandType.Text;

cmd.Connection = con;
con.Open();

reader = cmd.ExecuteReader(); // this is where the error came out  "A quotation mark delimiter is missing from the end of the query." 
reader.Close();
SqlCeDataAdapter ad = new SqlCeDataAdapter(sb.ToString(), con);
DataSet ds = new DataSet();
ad.Fill(ds);
dataGrid2.DataSource = ds.Tables[0];

con.Close();
sb.Length = 0;

@kaihuacheng: What @Martheen means is what string does your `StringBuilder` hold? — Abhishek, Aug 31 '15 at 03:45
...@kaihuacheng we would like to see what your query is, as it's likely that is where the error is. — Brendan Green, Aug 31 '15 at 03:51
Debug the app, when the exception comes out, point your cursor to sb, paste the content here — Martheen, Aug 31 '15 at 03:52
@Martheen this is the loop for (int i = 0; i < listBox2.Items.Count; i++) { if (i == 0) { sb.Append("Select * from ToolsBar where BarcodeValue in ("); } sb.Append("'" + listBox2.Items[i] + "',"); } sb.Length = sb.Length - 1; sb.Append(")"); — jason, Aug 31 '15 at 03:55
Looks good at the first glance, but I really need you to just run the app until the exception comes out, then paste the content here. It really depends on the content of listbox2 which should be dynamic — Martheen, Aug 31 '15 at 03:59
Does one of your `listBox2.Items` values have a quote mark in them (e.g. `O'Connor`). If they do, you'll need to escape them. — Brendan Green, Aug 31 '15 at 03:59
@Martheen reader = cmd.ExecuteReader(); this is the error comes out — jason, Aug 31 '15 at 04:03
When the error comes out, point your cursor to either cmd.CommandText or sb in Visual Studio — Martheen, Aug 31 '15 at 04:04
@BrendanGreen No, my list box only hold string value. no quotation mark — jason, Aug 31 '15 at 04:04
@Martheen sb = {Select * from ToolsBar where BarcodeValue in ('C1D10001} — jason, Aug 31 '15 at 04:09
As an alternative to Martheen's suggestion, since you seem to be having issues debugging, right before calling `cmd.ExecuteReader()`, do this `System.Diagnostics.Debug.Write(sb.ToString());`. This will output the query to the output panel in Visual Studio. Copy that and update your question with the query. I know this has been asked multiple times, but quite honestly without it, you won't get this solved. — Brendan Green, Aug 31 '15 at 04:10
@BrendanGreen even i add System.Diagnostics.Debug.Write(sb.ToString()); it still gives the same problem — jason, Aug 31 '15 at 04:15
What you posted is missing both a closing quote and closing bracket. Are you sure that the loop you pasted in your question is correct? Is there any other alteration done to the string builder between it getting created and the query executing? — Brendan Green, Aug 31 '15 at 04:16
Try changing the listbox append from sb.Append("'" + listBox2.Items[i] + "',"); to sb.Append("'" + listBox2.Items[i].ToString() + "',"); I suspect the content comes from barcode scan, and either your barcode reading logic or the firmware doesn't strip the ending — Martheen, Aug 31 '15 at 04:18
On second though, it probably doesn't work since adding stuff to string should call .ToString implicitly — Martheen, Aug 31 '15 at 04:19
Sigh. Try @BrendanGreen suggestion. MSDN says Length altering should pose no problem, but .NET CF got hidden gotchas everywhere. Also try to clear listbox items from non-alphanumeric using [this](http://stackoverflow.com/questions/3210393/how-do-i-remove-all-non-alphanumeric-characters-from-a-string-except-dash) — Martheen, Aug 31 '15 at 04:25

score 1 · Accepted Answer · answered Aug 31 '15 at 04:18

1

Alternate loop to build your query which removes the need to alter the string length:

for (int i = 0; i < listBox2.Items.Count; i++)
{
    if (i == 0)
    {
        sb.Append("Select * from ToolsBar where BarcodeValue in (");
        sb.Append("'" + listBox2.Items[i] + "'");
    }
    else
    {
        sb.Append(",'" + listBox2.Items[i] + "'");
    }
}

sb.Append(")");

answered Aug 31 '15 at 04:18

Brendan Green

11,676
5
44
76

Does the content of sb still exactly the same? Try removing non-alphanumeric characters [first](http://stackoverflow.com/questions/3210393/how-do-i-remove-all-non-alphanumeric-characters-from-a-string-except-dash) – Martheen Aug 31 '15 at 04:27
even i get rid of non-alphanumeric , the error still is the same – jason Aug 31 '15 at 04:32
Okay, that's literally impossible. The loop clearly must at least have a ")" even if there's nothing in listbox, yet your app just skip them. Try [step by step debugging](http://csharp.net-tutorials.com/debugging/stepping-through-the-code/) to confirm that your loop did go through the last sb.Append(")") and don't went anywhere else before sending it to cmd.CommandText – Martheen Aug 31 '15 at 04:38
Possibly related to [this](https://social.msdn.microsoft.com/Forums/vstudio/en-US/d98ab0f8-e1ce-4981-8e0d-42a454916890/appending-null-character-to-stringbuilder-results-in-incorrect-string?forum=netfxbcl). Normally you won't have null characters but with barcode reading involving non-managed call, you might have errand ending. Remove them using sb.Append("'" + listbox2.Items[i].Replace("\0", string.Empty) + "'"); – Martheen Aug 31 '15 at 04:47
vs2008 does not let me use Replace. – jason Aug 31 '15 at 04:58
now my sb changed to sb = {Select * from ToolsBar where BarcodeValue in ('CD)} but the problem is still the same – jason Aug 31 '15 at 05:05
Thank you very much @Martheen, Brendan Green . the problem is solved . You guys are great! – jason Aug 31 '15 at 05:11
Wait, how? Did you just use Brendan's loop as it is, or do you change something else there? – Martheen Aug 31 '15 at 05:19
@Martheen YES, i changed sb.Append(")"); to sb.Append("')"); – jason Aug 31 '15 at 05:21

"A quotation mark delimiter is missing from the end of the query." error in WinCE PDA application

1 Answers1