Please help,error in c# code - beginner

[johnpdavidson]

im trying to query and display a dictionary saved in a local sql databse.

i get an error

SqlDataReader dr = cmd.ExecuteReader(); - SqlException was unhandled - incorrect syntax near 'CHARINDEX'

help much appricated



view plaincopy to clipboardprint?
01.using System;
02.using System.Collections.Generic;
03.using System.ComponentModel;
04.using System.Data;
05.using System.Drawing;
06.using System.Linq;
07.using System.Text;
08.using System.Windows.Forms;
09.using System.Text.RegularExpressions;
10.using System.Collections;
11.using System.Data.Sql;
12.using System.Data.SqlClient;
13.using System.Data.SqlTypes;
14.using System.Data.Common;
15.using System.Web.Configuration;
16.using System.Configuration;
17.
18.namespace JD
19.{
20. public partial class Crabble : Form
21. {
22. string Letters;
23.
24. public Crabble()
25. {
26. InitializeComponent();
27. }
28.
29. private void Crabble_Load(object sender, EventArgs e)
30. {
31. textCrabble.Focus();
32. }
33.
34. private void buttonClose_Click(object sender, EventArgs e)
35. {
36. this.Hide();
37. }
38.
39. private void buttonCrabble_Click(object sender, EventArgs e)
40. {
41. Letters = textCrabble.Text.ToUpper();
42. bool valid = true;
43. bool print = true;
44. Regex re = new Regex(@"[^A-Za-z\*]");
45. StringBuilder sb = new StringBuilder();
46.
47. if (re.IsMatch(Letters))
48. {
49. valid = false;
50. MessageBox.Show("Letters and Asterisks only please.");
51. }
52.
53. if (valid)
54. {
55. string Alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
56. ArrayList alphabet = new ArrayList();
57.
58. for (int i = 0; i < Alpha.Length; i++)
59. {
60. alphabet.Add(Alpha[i]);
61. }
62.
63. int len = Letters.Length;
64. bool blank = false;
65. if (Letters.IndexOf("*") >= 0)
66. blank = true;
67. string querystart = "SELECT CrabbleDictionary FROM CrabbleJohn WHERE";
68. string querymid = "";
69. string queryend = @"AND CrabbleLength <= " + len.ToString() +
70. " ORDER BY CrabbleLength, CrabbleDictionary";
71.
72. if (!blank)
73. {
74. foreach (char letter in Letters)
75. {
76. alphabet.Remove(letter);
77. }
78. foreach (char remainingletter in alphabet)
79. {
80. querymid += @"And Not CHARINDEX('" + remainingletter.ToString()
81. + "',[CrabbleDictionary])>0 ";
82. }
83. querymid = querymid.Substring(4);
84. }
85. else
86. {
87. queryend = queryend.Substring(4);
88. }
89. string query = querystart + querymid + queryend;
90. string connect = ConfigurationManager.ConnectionStrings
91. ["CrabbleJohn"].ConnectionString;
92. using (SqlConnection conn = new SqlConnection(connect))
93. {
94. SqlCommand cmd = new SqlCommand(query, conn);
95. conn.Open();
96. SqlDataReader dr = cmd.ExecuteReader();
97. if (dr.HasRows)
98. {
99. while (dr.Read())
100. {
101. int pos;
102. string totest = Letters;
103. string word = dr[0].ToString();
104. for (int i = 0; i < word.Length; i++)
105. {
106. pos = totest.IndexOf(word[i]);
107. if (pos >= 0)
108. {
109. totest = totest.Remove(pos, 1);
110. print = true;
111. }
112. else
113. {
114. if (!blank)
115. {
116. print = false;
117. break;
118. }
119. else
120. {
121. pos = totest.IndexOf("*");
122. if (pos >= 0)
123. {
124. totest = totest.Remove(pos, 1);
125. print = true;
126. }
127. else
128. {
129. print = false;
130. break;
131. }
132. }
133. }
134. }
135. if (print)
136. {
137. sb.Append(word + "<br />");
138. }
139. }
140. }
141. dr.Close();
142. textResults.Text = sb.ToString();
143. }
144. }
145. }
146. }
147.}

[memeloo]

help much appricated

remove the line numbers and add the code tags... perhaps then I will read it. it's not even possible to copy it to visual studio.

[mariocatch]

i get an error

SqlDataReader dr = cmd.ExecuteReader(); - SqlException was unhandled - incorrect syntax near 'CHARINDEX'

help much appricated

querymid += @"And Not CHARINDEX('" + remainingletter.ToString() 
+ "',[CrabbleDictionary])>0 ";

The error tells you EXACTLY where the problem is. It looks like some sort of escape sequence issue. Mess around with it on your own and see what ya come up with.

[memeloo]

have you tested this query in the sql management studio? does it work?