Coexisting SQL Reader & UPDATE

In my current application, I have an administration form that fills in labels and checked states via data entered into the database using a similar user input field. What the admin page does is it first lists all the record names in a listview, then on select, it fills in the form based on what the records contain. This means labels text change, and check states change based on the string "True" or "False". This was done using the SQL Reader command.

Within the same form, the read checkboxes are editable via the admin. When the admin edits the controls, he will click the update button at the bottom and the database will UPDATE .. WHERE UserName = (Scalar for ListBox1.SelectedValue)

I've used the exact same UPDATE command in my form for the user, except the only difference was @. the WHERE clause-- I had it updating based on a GUID. I know my SQL statement is correct, but it just won't update the data.

Is it possible that the READER, which starts (and closes) on pageload cannot coexist within the same form as the UPDATE code?

My code is incredibly long, so for the purposes of a short post I'm not including any bit of it-- but if you would like to see it, just let me know.

Why do you need to use a GUID? Why not use an identity integer primary key? Update will then be a simple stored procedure and a bit of wrapper code.

|||

I thought that too, but the GUID's working fine on the other pages-- thats not what I have a problem with. I'm modifying an existing structure, and if the GUID scheme works, I'd rather not make more work for myself.

On the form in question, GUID doesn't even exist as an instance on it. ListBox1.SelectedValue is what I'm trying to get as my WHERE arguement-- and it does pass, no errors generated, but the form does not update.

|||

When you run it in debug mode with your breakpoints set, where does it stop at? When you click update does it call to execute your sql statement?

|||

when I set breakpoints, I have it set at the NonQuery, and everything inputs correctly: my SQL statement is properly formed down to the ListBox selected value translating into an EXISTING column name (eg, Current user is Test, Test is databound to listbox. Test's data is displayed, modified, then "updated". Break. SQL statement reads 'UPDATE Login SET ... WHERE UserName = 'Test', properly formed and existing), the ExecuteNonQuery goes through, there are no errors generated. and the redirect coded after the update processes. Display test's data again, and no changes were made.

|||

Figured it out.

I made two functions, "ReadMe()" and "UpdateMe()", and called them on load and on button click. However, although all my statements were correct, ButtonClick calls a postback, and with the reader executing onpostback, the database automatically rolled back the variables.

Solution: I changed the ReadMe() function to execute on ListBox1.SelectedIndex changed, and the update was successful.

Whoops!