StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODatareader skips first result
text
Body
copied!<p>I have a fairly complex SQL query that pulls different types of products from a database based on a customer ID. It pulls three different types of products, identified by their unique identifier number ranges (i.e., IDs 1000-1999 are one type of product, 2000-2999 are another, and 3000-3999 are yet another).</p> <pre><code>SELECT b.fldMachineName, m2.fldRotaryPressName, mids.fldMachine_ID FROM dbo.tblCustomerGeneralInfo c LEFT JOIN dbo.tblMachine_IDs mids ON c.fldCustomer_ID = mids.fldCustomer_ID LEFT JOIN dbo.tblBobstFlatDieSpecs b ON mids.fldMachine_ID = b.fldMachine_ID LEFT JOIN dbo.tblDieInfo m1 ON mids.fldMachine_ID = m1.fldMachine_ID LEFT JOIN dbo.tblRotaryDieSpecs sm ON m1.fldMachine_ID = sm.fldMachine_ID LEFT JOIN dbo.tblRotaryPresses m2 ON sm.fldRotaryPress_ID = m2.fldRotaryPress_ID WHERE c.fldCustomer_ID = '20' ORDER BY fldRotaryPressName </code></pre> <p>c.fldCustomer_ID is the only piece of user input that I need, and when I run this query against the database in SQL Server Management Studio Express, it runs fine. However, when I wrap this in a using statement in the web part I am writing in C# for SharePoint, it does not return the first row that it should, instead returning a null value. For instance, if in SSMS I get three results (say, '1001,' '2008,' and 3045') then my datareader will return only two results, with a null value for the first (i.e., 'null,' '2008,' and '3045'). Here is my code in C#:</p> <pre><code> con.Open(); using (SqlCommand cmd = new SqlCommand("SELECT b.fldMachineName, m2.fldRotaryPressName, mids.fldMachine_ID " + "FROM dbo.tblCustomerGeneralInfo c " + "LEFT JOIN dbo.tblMachine_IDs mids ON c.fldCustomer_ID = mids.fldCustomer_ID " + "LEFT JOIN dbo.tblBobstFlatDieSpecs b ON mids.fldMachine_ID = b.fldMachine_ID " + "LEFT JOIN dbo.tblDieInfo m1 ON mids.fldMachine_ID = m1.fldMachine_ID " + "LEFT JOIN dbo.tblRotaryDieSpecs sm ON m1.fldMachine_ID = sm.fldMachine_ID " + "LEFT JOIN dbo.tblRotaryPresses m2 ON sm.fldRotaryPress_ID = m2.fldRotaryPress_ID " + "WHERE c.fldCustomer_ID = @CustomerID " + "ORDER BY fldRotaryPressName", con)) { cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar, 4).Value = customers.SelectedValue.ToString(); </code></pre> <p>Our SQL profiler shows the same query being passed regardless of if it's done through SSMS or from the web part, except for the literal being used versus the parameter. Also, if I change the parameter to a literal, I have the same result. Is there a discrepancy in the way that C# handles SQL queries as opposed to SSMS, or have I somehow implemented it incorrectly?</p> <p>Here is the code for pulling the data from the reader into the dropdownlist, for the sake of completeness:</p> <pre><code> dr.read(); while (dr.Read()) { try { string itemValue = Convert.ToString(dr["fldMachine_ID"]); string flatName = Convert.ToString(dr["fldMachineName"]); if (!string.IsNullOrEmpty(flatName)) { items.Add(flatName, itemValue); } string rotaryName = Convert.ToString(dr["fldRotaryPressName"]); if (!string.IsNullOrEmpty(rotaryName)) { items.Add(rotaryName, itemValue); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } // Bind list to ddl. machines.DataSource = items; machines.DataValueField = "Value"; machines.DataTextField = "Key"; machines.DataBind(); machines.Enabled = true; } </code></pre> <p>I am completely stumped, and I really appreciate any help I can get.</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