September 2, 2012 at 11:12 am
Hi,
I wasn't sure how to phrase my query so wasn't sure what to search for.
I'm attempting the simplest of updates - e.g. "UPDATE CRnumbers SET StudyLocationID = " + CLocation.Text + " WHERE RegNumber = "+UserEntry.text
UserEntry might be '12345', but how do I update for multiple entries?
So if UserEntry has '12345,12347,12399' my super-newbie method would be to separate the comma separated values and loop through each one updating each individually, but this is obviously not the way to go.
How do I structure my SQL to update all 3 of those values in one hit? Do I create a table from my CSVs and use that somehow or is there a simple command that understands multiple values like that?
Thanks in advance, folks.
September 2, 2012 at 8:10 pm
You didn't say what's in CLocation.Text. Is that also a delimited string with the same number of values? Or perhaps just a single value? Or some combination?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 3, 2012 at 6:09 am
Sorry, yes (and thanks for replying).
CLocation.text is just a single value.
September 3, 2012 at 7:24 am
simonwestb (9/2/2012)
Hi,I wasn't sure how to phrase my query so wasn't sure what to search for.
I'm attempting the simplest of updates - e.g. "UPDATE CRnumbers SET StudyLocationID = " + CLocation.Text + " WHERE RegNumber = "+UserEntry.text
UserEntry might be '12345', but how do I update for multiple entries?
So if UserEntry has '12345,12347,12399' my super-newbie method would be to separate the comma separated values and loop through each one updating each individually, but this is obviously not the way to go.
How do I structure my SQL to update all 3 of those values in one hit? Do I create a table from my CSVs and use that somehow or is there a simple command that understands multiple values like that?
Thanks in advance, folks.
There is a simple "command"
"UPDATE CRnumbers SET StudyLocationID = " + CLocation.Text + " WHERE RegNumber IN ( "+UserEntry.text + ")"
September 3, 2012 at 12:34 pm
Excellent! That's exactly what I needed. Thank you.
September 3, 2012 at 8:10 pm
simonwestb (9/3/2012)
Excellent! That's exactly what I needed. Thank you.
Now all you have to do is to make sure no one can pull any SQL Injection on you. And, yeah... there are [font="Arial Black"]LOTS [/font]of articles on that subject because so many people have been nailed by it on things just like this.
I can help avoid SQL Injection on the comma delimited string of numbers but what goes into "CLocation.Text"? And, yeah... I'm strongly recommending that you DON'T use that code until you make it bullet proof. It's just waiting to be attacked.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2012 at 1:07 pm
Thanks for the tip.
Security is something I was planning to tackle at a later stage when I'd got the bulk of my functionality working. I realise that'll mean changing a lot of my code, but I'm trying to take things one step at a time. Most of queries are in the VB end too, which I know I'll need to transform into stored procedures sooner or later.
Fun times ahead I think!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply