StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>So many things wrong here...</p> <ol> <li>Never NEVER <strong>NEVER</strong> use dynamic SQL like that. What if one of your "state" has an apostrophe in it?</li> <li>Unless this is .Net 1.0 or 1.1, you should NOT be using an ArrayList. Use <code>System.Collections.Generic.List<string></code> instead.</li> <li>Don't create 99 SqlCommand objects. Create <strong>1</strong> SqlCommand object, and update the parameter value on each iteration through the loop.</li> <li>Create your SqlCommand (and even more importantly, SqlConnection) object with a <code>using</code> statement, to make sure the unmanaged resources are released promptly if an exception is thrown.</li> <li><em>Most of all,</em> all this becomes moot when you realize you can update multiple records in one sql statement, and that sql has a nice easy "UPPER" function.</li> </ol> <p>Since it looks like you could use an example of the correct way to build this kind of query, I'll assume for a moment that #5 is somehow not an option and that you really do need to pull all this data down to the application and then update it back record by record (hint: you don't). Here is how you should build that code:</p> <pre><code>using (SqlConnection cn1 = new SqlConnection("connection string here")) //inbound data using (SqlCommand cmd1 = new SqlCommand("SELECT uniqueid, State FROM myList", cn1)) using (SqlConnection cn2 = new SqlConnection("connection string here")) using (SqlCommand cmd2 = new SqlCommand("UPDATE myList SET State= @State WHERE uniqueID= @ID", cn2)) { SqlParameter StateParam = cmd2.Parameters.Add("@State", SqlDbType.VarChar, 50); SqlParameter IDParam = cmd2.Parameters.Add("@ID", SqlDbType.Int); cn1.Open(); cn2.Open(); using (SqlDataReader rdr = cmd1.ExecuteReader()) { while (rdr.Read()) { StateParam.Value = rdr["State"].ToString().ToUpper(); IDParam.Value = rdr["uniqueID"]; cmd2.ExecuteNonReader(); } } } </code></pre> <p>Note that this is just to demonstrate <code>using</code> blocks and parameterized queries. You should <strong>NOT</strong> use this code. Instead, take a good look at my point #5. This can and should all be done in a single sql UPDATE statement.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload