How do i update table with ID's from user input?

I want to be able to input what rows (ID's) that should be updated in an UPDATE query.

How would you create a UI for this task, and how should i build the query string

My previous solution was this:

- I created a bunch of textboxes (approx 20), cause i usually did not need to update more than 20 rows.
- For each row i needed to update, i filled in the ID in a textbox. Usually, there would be some empty textboxes left.
- When clicking a "Update" button I ran a hardcoded a query, that took the values from the textboxes as arguments in the WHERE clause.

Example (simplified)

"Update Orders SET Status = 'Shipped' WHERE ID IN( ";
+ txtbox1 + txtbox2 + txtbox3 and so on.

Of course, i controlled the data first, and added quotes correctly.

In practicle, this solution works fine. However, i have a feeling that there are better ways to do this codewise.

Does anyone have a suggestion how i could solve this in a better way

Answer this question

How do i update table with ID's from user input?

rtaiss
The end user is me, so security/input validation is not a issue...

Other controls wouldn't be possible if i'm going to type the ID's my self...

But theres not other way to build the query either I mean, maybe there are some way you can spesify what rows to update Like creating an array to with the arguments And add this argument array to some query method So that i don't need to build up the query string the hard way
siavoshkc

It sounds inevitable.

You are coding a query builder or wizard.

Instead of textboxes, you might want to experiment with other controls such as checkboxes, radio buttons, and DDL's.

Text boxes leave too much room for error when you're building a query.

You want to ensure that the end user have little to no control over the input (text) if possible.

Adamus
jcsam
I see :) Thanks - my apologies
hrubesh
OK, so how do you narrow down twenty textboxes from 10,000 records
Jassim Rahma
I think I may have misread however why don't you just fill a dataAdapter/dataset with your records, then whenever the user changes values bound to a UI control, the dataset has changes to be made to the database, so when calling the dataAdapter.Update() method, it will automatically update the database depending on what the users changed.
flypp3r

If you are the end user use an IDE and connect directly to the DB. Why do you need a form

There must be some consistency to the update where you could write a sp_ or simply save the query.

Adamus
Jive Dadson
What I would do, even if it was only me that uses the app, I'd use a multi-select listbox or listview to display details of the record, displaying the ID number as a comment. That avoids mis-typing the ID number. Then have a button that updates the database, *one* record at a time to keep the UPDATE query simple...
turczytj
Hmm... Not a bad suggestion. But mis-typing haven't really been a problem, and with 10 000 records that list could get a little messy...

Actually i'm happy with the multiple textboxes solution i have - i just wondered if there were some other obviously better ways to do it codewise. But i assume there aren't, so i think i will redo my solution.

Thanks for the replies anyway!
How do i update table with ID's from user input?

Answer this question