[RESOLVED] Me.txt & #

Hi--
I use a very strict naming convention that helped efficiency quite a bit in VB6. If I had a form that enabled changed to 50 records, I would use an update code that would go something like

For I = 1 to #records
"Update [tablename] Set [blah] = Me("txtparam" & I).Text [...] where [column1] = " & Me("txt" & I).Text
Next

It allowed me to update a great many records without writing a ton of sql statements
(it would read out, for the [column1] example: "txt1" "txt2" etc)

how do I accomplish this in vb.net?

directcast is what i think you're looking for.

It seems you were using a TextBox array?
There is a TextBoxArray control in .NET (it has to be enabled).
I'm not sure how it works though or if it's even the same as the VB version.

Sorry--not an array. I used separate textboxes on the form, titling them txt1 and txt2...all of the '*1" controls belonged together, as did the "*2"s, and all were consistently named so I would put the sql command in a for loop... the way i'm doing it in vb.net is


Dim cmd as new sqlcommand
cmd.connection = connect
cmd.commandtext = "UPDATE [table] SET [column1] = " & txtG1.text & " WHERE [column2] = " & txt1.text
cmd.executescalar()
cmd.dispose
Dim cmd2 as new sqlcommand
cmd2.connection = connect
cmd2.commandtext = "UPDATE [table] SET [column1] = " & txtG2.text & " WHERE [column2] = " & txt2.text
cmd2.executescalar()
cmd2.dispose
...


instead of the for loop method i showed before (which VB.NET doesn't support). In VB6, saying Me("txt" & #).text equated to txt#.Text, where #'s value changes (it reads out the value of the variable)

yeah, duh, definitely not using an array. I'm stupid...lol.
Of course, i would have been using an object array if i was using VB6 or lower. lol

For i As integer = 1 To recordCount Step 1

Next i

I really don't see how .NET doesn't support the For Loop.

like i said before, i think direct cast is what you're looking for to solve this issue.

Take a look at this thread:http://www.vbforums.com/showthread.p...ght=directcast

I browsed the msdn and some articles, I only came across direct cast as a more efficient type converter....ohhh. You're saying I should convert the string "txt" + I into a textbox object? I'll try that.

I'd have to say that, while what you were doing was efficient, it was decidedly risky, unless you were doing something prior to that to prevent SQL injection attacks. Perhaps that isn't an issue for you due to your situation, but it's worth thinking about.

Another thought is that what you are doing is inefficient overall. If all you have is a bunch of Update commands on a series of records, then fill a datatable and manipulate the data in the datatable. Then, when you want to save it, you can replace your entire loop with a single command: Datatable.Update.

I did leave out a step, in that you need to use a CommandBuilder object for that to work, but that's a pretty minor step.

Quote:

---

Originally Posted by theguyinthehat

I browsed the msdn and some articles, I only came across direct cast as a more efficient type converter....ohhh. You're saying I should convert the string "txt" + I into a textbox object? I'll try that.

---

No. The forms has a Controls collection which holds all controls, provided they are not in any container controls such as Panels, Groupboxes, etc.

You can use the Controls collection to find a specific control by its name:

Code:

---

Dim c As Control = Me.Controls("txt1")

---

Of course, because the Controls collection has no idea that "txt1" is a TextBox (for all it knows, it could be a Checkbox or a Button), so it always returns the controls as type "Control". If you know that you are dealing with a TextBox (as you do now), then you can cast the Control object into a TextBox object:

Code:

---

Dim c As Control = Me.Controls("txt1")
Dim t As TextBox = DirectCast(c, TextBox)

---

Now, you can use t as if it was your TextBox (because it is).

Excellent, it worked. I'm not worried about malicious sql injections... our firewall is excellent. I realize that a datagridview may make more sense, but for the people using our database, textboxes are easier for them to navigate. For those interested in the code

Dim c As New Control
Dim t As New TextBox
For i = 1 To 40
c = Me.Controls("txt" & i)
t = DirectCast(c, TextBox)
t.Text = i
Next

and I'll do the same for updates. thanks a lot!

glad you got it sorted.

SQL injection has nothing to do with a firewall. If this program is exposed to users outside your organization, you should most definitely NOT append textbox content directly into a query.

What would happen if somebody added something like this in your textbox:

"1 ; DROP TABLE <one of your table names here"

(I may have the syntax wrong on the statement separator, but you get the idea).

That would just be malicious. Here's a comic on it:

http://xkcd.com/327/

There are much nastier exploits than that one, though.

well i check the contents of the textbox with an 'isInteger' private function.


I've never worried about security before, so I hope you don't mind my asking a few more questions:

The sql server is hosted on the company's intranet, which is very, very secure. We tightly control permissions to these tables, and anyone trusted with access wouldn't insert anything malicious (and if they did, we'd find them out). If someone could hack into our intranet somehow, they'd have a much easier time not using sql injections and simply opening sql enterprise to lift data off our server, right?

If you are validating the contents of the textboxes to confirm that they are integers, and you are not doing this using something like Val, but are using something like Integer.TryParse, then you are all set. The difference between the two is that Val would confirm that at least part the first part of the string can be turned into an integer, regardless of the rest, while Integer.TryParse will only return true if the WHOLE string can be converted into an integer. Therefore, Val will allow a malicious trailer, while TryParse will not.

If the program is ONLY running internally, and only by trusted individuals, then I wouldn't worry about it. After all, if a trusted individual proved to be unworthy of that trust, they would likely be able to do FAR worse than a SQL injection. The real danger is from outward facing forms, and it sounds like you don't have that issue.

And, yes, if somebody hacks into your network, you have much worse problems than SQL injection.

Still, the other point I made is worth a thought. What you are doing to update records from textboxes is reminiscent of how I would have done things under VB6. There is a real reason to stick with existing code (it works), but you should be aware that there are other alternatives available now that may well prove to be better.